-- mysql数据库程序设计笔记:
-- =========第八章:触发器======================== 触发器:触发执行特定事件。(关联表对象,当特定事件出现时,触发激活) 目的:保护表数据,(保证表数据完整性和一致性。) 1、新建触发器: 格式:create trigger 数据库名.触发器名称 触发时刻 insert on 表名 for each row set 触发动作和条件; (其中触发时刻为:before 或 after ) 格式速记: create trigger 库.触 时刻 insert on 表 for each row set 条件; 举例:给表创建一个触发器,每次向表插入一行数据,插入时,把str设定为“one student ” mysql> create trigger db_school.trName after insert on tb_student for each row set @str='one student'; Query OK, 0 rows affected (0.20 sec) 验证触发器: 先插入数据: insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201664','王梦天','女','1980-01-22','山西大同','汉','1班','1423372333344'); 查询触发器: mysql> select @str; +-------------+ | @str | +-------------+ | one student | +-------------+ 1 row in set (0.00 sec) 2、删除触发器: 格式:drop trigger is exists 数据库名.触发器名; 举例: mysql> drop trigger db_school.trName; Query OK, 0 rows affected (0.00 sec) 3、更新触发器: 格式:create trigger 数据库名.触发器名称 before update on 表名 for each row set 触发动作和条件; (其中触发时刻只能是:before ) (触发动作中:new为可修改,old为只读) 格式速记: create trigger 库.触 before update on 表 for each row set 条件; 举例:在数据db_school创建一个触发器trUpdate,在每次更新学生表时将nation列值设定为native值: mysql> create trigger db_school.trupdate before update on tb_student for each row set new.nation=old.native; Query OK, 0 rows affected (0.16 sec) 触发器验证: 先更新数据:把王梦天同学名字改为张飞: mysql> update tb_student set studentName='张飞' where studentName='王梦天'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 查看表: mysql> select * from tb_student where studentName='张飞'; +----+-----------+-------------+------+------------+----------+----------+---------+---------------+ | id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID | +----+-----------+-------------+------+------------+----------+----------+---------+---------------+ | 24 | 201664 | 张飞 | 女 | 1980-01-22 | 山西大同 | 山西大同 | 1班 | 1423372333344 | +----+-----------+-------------+------+------------+----------+----------+---------+---------------+ 1 row in set (0.00 sec) 可以看到nation变成了native列的值,因此说触发器是被执行的。
标签:触发器,set,mysql,编程,trigger,student,Mysql,row From: https://www.cnblogs.com/liuguiqing/p/17278532.html