本教程以MySQL8为主版本(内容兼顾MySQL5.7)。
所有MySQL文章的目录为:总目录 https://blog.csdn.net/zyplanke/article/details/102968014
触发器是与表相关的数据库对象,在满足表数据库定义的变化条件是触发执行
创建触发器
创建触发器的语法为:
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } ot
trigger_time
是触发动作时间。可以是BEFORE
或 AFTER
表示触发器在要修改数据之前或之后执行本触发器内容。(注意,触发器不能创建在临时表上)。
trigger_event
指示激活触发器的操作类型:
INSERT
:每当在表中插入新行时(例如,通过,和 语句)INSERT
, 触发器就会激活UPDATE
:只要修改一行(例如,通过UPDATE
语句),触发器就会激活DELETE
:每当从表中删除一行时(例如,通过DELETE
和REPLACE
语句),触发器就会激活。DROP TABLE
和TRUNCATE TABLE
表上的语句不会激活此触发器。
在触发器定义的BODY内,可以使用别名OLD
和NEW,来指示数据发生变化前后的记录内容。例如数据发生变化前后的某列。 OLD.
col_name 和
NEW.
col_name
触发器使用举例
先创建一个用于保存日志的表:
mysql> create table change_log(change_time timestamp, message varchar(512));
Query OK, 0 rows affected (0.01 sec)
创建触发器
mysql> delimiter $$
mysql> create trigger tgr_ins_employee
after insert on employee for each row
begin
insert into change_log values (current_timestamp(), concat('insert data:', NEW.name) );
end;
$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
对employee插入一条数据库后,再查看change_log表的结果,发现触发器已经生效:
mysql> insert into employee values (2001, 'xiaoming', 2001);
Query OK, 1 row affected (0.00 sec)
mysql> select * from change_log;
+---------------------+----------------------+
| change_time | message |
+---------------------+----------------------+
| 2019-11-23 23:21:22 | insert data:xiaoming |
+---------------------+----------------------+
1 row in set (0.00 sec)
INSERT INTO ... ON DUPLICATE KEY UPDATE 特别说明
INSERT ... ON DUPLICATE KEY UPDATE... 该语法的用途是为了解决重复性,当数据库中存在某个记录时,执行这条语句会更新update它,而不存在这条记录时,会插入insert。
判断重复的标准是:如果插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复,则认为重复。
其语法是(后面Update的字段只涉及需要修改的字段,不用该表中所有字段都出现):
INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field8=value8, ...;
对于这种INSERT场景,该表上触发器的出发顺序说明如下:
如果属于非重复的记录(既实际是INSERT动作):顺序为:BEFORE INSERT → AFTER INSERT
如果属于重复的记录(既实际是UPDATE动作):顺序为:BEFORE INSERT → BEFORE UPDATE→ AFTER UPDATE。既虽然实际执行的是UPDATE,也会触发BEFORE INSERT。