【MySQL基础】10：触发器（Trigger）

 

     本教程以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。