MySQL/MariaDB触发器详解

共计 10557 个字符，预计需要花费 27 分钟才能阅读完成。

触发器用来实现在永久表上进行某些操作时触发启动另一操作。

1. 创建触发器

以下是 MariaDB 中 create trigger 的语法：MySQL 不支持 or replace 和 if not exists 子句。

CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON tbl_name FOR EACH ROW
    trigger_body

触发器只能建立在永久表上，不能建立在视图和临时表上。MySQL/MariaDB 中的触发器只支持行级触发器(即每行都触发一次触发器)，不支持数据库级别和服务器级别的触发器。MySQL/MariaDB 中的触发器虽然都是基于表的，却存储在数据库下，理解这一点很重要，以后查看、删除、引用 trigger 的时候都是通过数据库名称来引用的，而不是使用表来引用。

before 和 after 是触发时间，insert/update/delete 是触发事件 。 例如 before insert 表示插入记录之前触发程序。其中 before 触发器类似于 SQL Server 中的 instead of 触发器，作用在检查约束之前。而 after 触发器和 SQL Server 中一样，在检查约束之后才生效。

下图为 SQL Server 中 instead of 和 after 触发器的工作位置。在 MySQL/MariaDB 中是一样的，只要把 MySQL/MariaDB 中的概念和 SQL Server 中的概念对应起来即可。后文 中有对该图的分析。

在 MySQL 中，一张表只能有一个同时间、同事件的触发器，所以 MySQL 中不支持基于列的触发器。例如，一张表中可以存在 before insert 触发器和before update，所以每张表最多只能有 6 个触发器。但是 MariaDB 10.2.3 中可以为同时间、同事件创建多个触发器。

在 MySQL/MariaDB 中，使用 old 和 new 表 分别表示触发器激活后的新旧表，在 SQL Server 中使用的是 inserted 和 deleted 表，其实它们的意义是等价的。但是坑爹的是 MySQL/MariaDB 中只能引用这两张表中的列，而无法直接引用这两张表。例如可以引用 old.col_name，但是不能直接select * from old 这样引用 old 表。

old 表表示删除目标记录之后将删除的记录保存在 old 表中，即 deleted 表。new 表表示向表中插入新记录之前，新记录保存在 new 表中，即 inserted 表。或者说，只要涉及了 insert 相关的操作就有 new 表，只要涉及了 delete 相关的操作就有 old 表，而 update 操作基本可以认为是先 delete 再 insert 的行为，所以也会触发这两张表。

注意，即使是 after 触发器，也是先将数据填充到 old、new 表中，再执行 DML 语句，最后激活触发器执行触发器中的语句。

在下面的小节中会分别验证不同事件不同时间的触发器行为。在验证它们之前，先创建示例数据。

CREATE DATABASE IF NOT EXISTS test ;

USE test ;

CREATE OR REPLACE TABLE emp (
    emp_no INT (11) NOT NULL,
    mgr_no INT (11) DEFAULT NULL,
    emp_name VARCHAR (30) DEFAULT NULL,
    PRIMARY KEY (emp_no)
)
INSERT INTO emp (emp_no, mgr_no, emp_name) VALUES
    (1, NULL, ‘David’),
    (2, 3, ‘Mariah’),
    (3, 1, ‘Tommy’),
    (4, 1, ‘Jim’),
    (5, 3, ‘Selina’),
    (6, 4, ‘John’),
    (8, 3, ‘Monty’);

查看该表数据。

再创建一个极其简单的审核表 audit，该表前两列为自增列和注释列，后面的列结构等同于 emp 表。

DROP TABLE IF EXISTS  audit;
CREATE TABLE audit AS SELECT * FROM emp WHERE 1=0;
ALTER TABLE audit ADD id INT AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE audit ADD note CHAR(50) AFTER id;

2.insert 触发器

insert 触发器的作用是：当向表中插入数据的时候，将会激活触发器。有两类：before 和 after 触发器，分别表示数据插入到表中之前和数据插入到表中之后激活触发器。

注意，只要向表中插入了新行，就会激活 insert 触发器。插入新行的动作不仅仅只有 insert 语句，还有其他插入操作，例如 load data 语句、replace 语句等等。

# 创建 before insert 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo1
    BEFORE INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,’before insert’,new.emp_no,new.mgr_no,new.emp_name);
    END$$
DELIMITER ;

# 创建 after insert 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo2
    AFTER INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,’after insert’,new.emp_no,new.mgr_no,new.emp_name);
    END$$
DELIMITER ;

before insert触发器的作用是：当向表 emp 中 insert 数据时，将首先激活该触发器，该触发器首先会将待插入数据填充到 new 表中，再向审核表 audit 中插入一行数据，并标明此次触发操作是 ”before insert”。触发器执行结束后，才开始向 emp 表中插入数据。

after insert触发器的作用是：当向表 emp 中 insert 数据时，将先将数据填充到 new 表中，再插入到 emp 表，之后激活该触发器，该触发器会向审核表 audit 中插入一行数据，并标明此次触发操作是 ”after insert”。

现在向 emp 表中插入数据进行测试。

INSERT INTO emp VALUES(10,3,’longshuai’);

插入之后，查看 audit 表。

MariaDB [test]> select * from audit;
+—-+—————+——–+——–+———–+
| id | note          | emp_no | mgr_no | emp_name  |
+—-+—————+——–+——–+———–+
|  1 | before insert |    10 |      3 | longshuai |
|  2 | after insert  |    10 |      3 | longshuai |
+—-+—————+——–+——–+———–+

可以看到，一次 insert 操作触发了 before insert 和 after insert 两个触发器。且无论是 before 还是 after insert 触发器都有 new 表的存在。

在 mariadb 10.2.3 版本之后，一个表中可以为同一时间、同一事件创建多个触发器(在 mysql 中不允许)。例如：

# 创建第二个 after insert 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo3
    AFTER INSERT ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(null,’after insert2′,new.emp_no,new.mgr_no,new.emp_name);
    END$$
DELIMITER ;
show triggers;

此处删除新建的这个 trigger，注意删除 trigger 的时候是通过数据库名称来也引用 trigger 的，而不是 table 名称。

drop trigger test.trig_demo3;

3.delete 触发器

delete 触发器的作用是：当删除表中数据记录的时候，将会激活触发器。

有两类 insert 触发器：before 和 after 触发器，分别表示表中记录被删除之前和表中数据被删除之后激活触发器。

注意，delete 触发器只在表中记录被删除的时候才会被激活。例如 delete 语句、replace 语句。但是 drop 语句、truncate 语句不会激活 delete 触发器，因为它们是 DDL 语句，而 MySQL/MariaDB 不支持 DDL 触发器，它们并没有对表中的记录执行 delete 操作。

# 创建 before delete 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo3
    BEFORE DELETE ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(NULL,’before delete’,old.emp_no,old.mgr_no,old.emp_name);
    END$$
DELIMITER ;

# 创建 after delete 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo4
    AFTER DELETE ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(NULL,’after delete’,old.emp_no,old.mgr_no,old.emp_name);
    END$$
DELIMITER ;

这两个 delete 事件的触发器作用很简单，先将待删除的记录插入到 old 表中，再在删除表中的记录之前、之后，向审核表 audit 中插入一行 ’before delete’ 或 ’after delete’ 的审核日志。

现在删除 emp 表中的一行记录进行测试。

delete from emp where emp_no=10;

删除 emp 表中数据之后，查看 audit 表。

MariaDB [test]> SELECT * FROM audit;
+—-+—————+——–+——–+———–+
| id | note          | emp_no | mgr_no | emp_name  |
+—-+—————+——–+——–+———–+
|  1 | before insert |    10 |      3 | longshuai |
|  2 | after insert  |    10 |      3 | longshuai |
|  3 | before delete |      0 |  NULL | NULL      |
|  4 | after delete  |      0 |  NULL | NULL      |
+—-+—————+——–+——–+———–+

可见，一次 delete 操作触发了 before delete 和 after delete 触发器。且删除记录前后 old 表都存在。

4.update 触发器

update 触发器的作用是：当表中数据记录被修改的时候，将会激活触发器。

有两类 update 触发器：before 和 after 触发器，分别表示表中记录被修改之前和表中数据被修改之后激活触发器。

注意，update 操作可以认为是先 delete 再 insert，因此它将填充 old 表和 new 表。

# 创建 before update 触发器
DELIMITER $$
CREATE OR REPLACE TRIGGER test.trig_demo5
    BEFORE UPDATE ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(NULL,’before update from new’,new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,’before update from old’,old.emp_no,old.mgr_no,old.emp_name);
    END$$
DELIMITER ;

# 创建 after update 触发器
DELIMITER $$ 
CREATE OR REPLACE TRIGGER test.trig_demo6
    AFTER UPDATE ON test.emp FOR EACH ROW
    BEGIN
        INSERT INTO audit VALUES(NULL,’after update from new’,new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,’after update from old’,old.emp_no,old.mgr_no,old.emp_name);
    END$$
DELIMITER ;

before update触发器的作用是：当更新 emp 表中的一条记录时，首先将表中该行记录插入到 old 表中，待更新结果插入到 new 表中，然后激活触发器，向审核表中写入数据，最后修改 emp 表中的记录。
after update触发器的作用是：当更新 emp 表中的一条记录时，首先将表中该行记录插入到 old 表中，待更新结果插入到 new 表中，然后修改 emp 表中的记录，最后激活触发器，向审核表中写入数据。

更新 emp 表中一行记录。

update emp set emp_no=7 where emp_no=8;

查看 audit 表。

MariaDB [test]> select * from audit;
+—-+————————+——–+——–+———–+
| id | note                  | emp_no | mgr_no | emp_name  |
+—-+————————+——–+——–+———–+
|  1 | before insert          |    10 |      3 | longshuai |
|  2 | after insert          |    10 |      3 | longshuai |
|  3 | before delete          |      0 |  NULL | NULL      |
|  4 | after delete          |      0 |  NULL | NULL      |
|  5 | before update from new |      7 |      3 | Monty    |
|  6 | before update from old |      8 |      3 | Monty    |
|  7 | after update from new  |      7 |      3 | Monty    |
|  8 | after update from old  |      8 |      3 | Monty    |
+—-+————————+——–+——–+———–+

可以看到，一次 update 操作触发了 before update 触发器和 after update 触发器，并且 update 操作时，new 和 old 两张表中都有新旧数据。上面的结果中 from new 对应的是更新后的数据，来源于更新前填充的 new 表，from old 对应的是更新前的旧数据，来源于更新前填充的 old 表。

5. 通过 on duplicate key update 分析触发器触发原理

在 MySQL/MariaDB 中，如果向表中插入的数据有重复冲突检测时会阻止插入。解决这个问题的其中一个方法就是使用 on duplicate key update 子句。这个子句应用在 insert 字句中，但其中涉及到了 update 操作，那到底会触发哪些触发器呢？

这里先清空上面的 audit 表。

TRUNCATE audit;

首先测试下使用 on duplicate key update 子句插入无重复的记录。注意，emp 表的 emp_no 列具有主键属性，它不允许出现重复值。

INSERT INTO emp VALUES(15,5,’xiaofang’) ON DUPLICATE KEY UPDATE emp_name=’xiaofang’;

查看 audit 表。

MariaDB [test]> select * from audit;
+—-+—————+——–+——–+———-+
| id | note          | emp_no | mgr_no | emp_name |
+—-+—————+——–+——–+———-+
|  1 | before insert |    15 |      5 | xiaofang |
|  2 | after insert  |    15 |      5 | xiaofang |
+—-+—————+——–+——–+———-+

可以看到，在插入没有重复冲突的行只触发了 before insert 和 after insert 触发器。没有触发 update 触发器。

再插入一条有重复冲突的记录。

TRUNCATE audit;
INSERT INTO emp VALUES(3,1,’xiaofang’) ON DUPLICATE KEY UPDATE emp_name=’xiaofang’;

查看 audit 表：

MariaDB [test]> select * from audit;
+—-+————————+——–+——–+———-+
| id | note                  | emp_no | mgr_no | emp_name |
+—-+————————+——–+——–+———-+
|  1 | before insert          |      3 |      1 | xiaofang |
|  2 | before update from new |      3 |      1 | xiaofang |
|  3 | before update from old |      3 |      1 | Tommy    |
|  4 | after update from new  |      3 |      1 | xiaofang |
|  5 | after update from old  |      3 |      1 | Tommy    |
+—-+————————+——–+——–+———-+

可以看到，这里触发了 3 个触发器：before insert/before update/after update，为什么前面只触发了两个 insert 触发器而这里触发了 3 个触发器。其实根据下面的图很好分析。

在 insert into... on duplicate key update 语句中，插入没有重复值冲突的记录时，首先判断是否存在 before insert 触发器，有就触发，触发之后检查约束，发现没有重复值冲突，然后直接触发 after insert 触发器。所以这种情况下只触发了 before insert 和 after insert 触发器。

而插入有重复值冲突的记录时，首先触发了 before insert 触发器，然后检查约束发现存在重复值冲突，所以改 insert 操作为 update 操作，update 操作再次回到事务的顶端，先触发 before update 再检查约束，这时候已经不再重复值冲突，所以后面触发 after update 触发器。

6.replace to 算法验证

插入新记录时，对于重复值冲突的记录，使用 replace to 语句代替 insert into 是另一种方法。这种方法实现方式和 on duplicate key update 方式不一样。

replace to 算法说明如下：

1. 尝试插入新行。
2. 存在重复值冲突时，从表中删除重复行。
3. 将新行插入到表中。

也就是说，存在重复值冲突时，如果使用触发器的话，将先触发 before insert，再触发 delete 操作，先是 before delete 再是 after delete，最后触发 after insert。

以下是验证过程和结果：首先清空 audit 表，再插入重复冲突的记录。

TRUNCATE audit;
REPLACE INTO emp VALUES(3,1,’gaoxiaofang’);

查看 audit 表：

MariaDB [test]> select * from audit;
+—-+—————+——–+——–+————-+
| id | note          | emp_no | mgr_no | emp_name    |
+—-+—————+——–+——–+————-+
|  1 | before insert |      3 |      1 | gaoxiaofang |
|  2 | before delete |      0 |  NULL | NULL        |
|  3 | after delete  |      0 |  NULL | NULL        |
|  4 | after insert  |      3 |      1 | gaoxiaofang |
+—-+—————+——–+——–+————-+

显然，和算法说明的结果是对应的。

7. 查看、删除触发器

mysql> SHOW CREATE TRIGGER trig_demo5\G
*************************** 1. row ***************************
              Trigger: trig_demo5
              sql_mode:
SQL Original Statement: CREATE DEFINER=`root`@`192.168.100.%` TRIGGER `test`.`trig_demo5` BEFORE UPDATE ON `test`.`emp`
    FOR EACH ROW BEGIN
        INSERT INTO audit VALUES(NULL,’before update from new’,new.emp_no,new.mgr_no,new.emp_name);
        INSERT INTO audit VALUES(NULL,’before update from old’,old.emp_no,old.mgr_no,old.emp_name);
    END
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci
mysql> show triggers;
mysql> show trigger like ‘pattern’;
mysql> show trigger where ‘expression’;

但是要注意，这个 like 的模式是对表名进行匹配的，而不是触发器名。例如触发器 trig_demo1 是基于 emp 表创建的，则使用 like ’emp’ 而不能使用 like ‘trig_demo1’。

在 information_schema 中有 TRIGGERS 元数据表：

例如：

mysql> select * from information_schema.triggers where trigger_name=’trig_demo1’\G
*************************** 1. row ***************************
          TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: trig_demo1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
      EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: emp
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
        INSERT INTO audit VALUES(null,’before insert’,NEW.emp_no,new.mgr_no,new.emp_name);
    END
        ACTION_ORIENTATION: ROW
            ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                  CREATED: NULL
                  SQL_MODE:
                  DEFINER: root@192.168.100.%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

删除触发器的时候，需要使用 drop 语句指定数据库名，而不是指定表名称。例如：

DROP TRIGGER [IF EXISTS] test.example_trigger;