原文:https://www.cnblogs.com/liuguiqing/p/17278532.html
-- =========第八章:触发器========================
触发器:触发执行特定事件。(关联表对象,当特定事件出现时,触发激活)
目的:保护表数据,(保证表数据完整性和一致性。)
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列的值,因此说触发器是被执行的。