欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MySQL/MariaDB触发器

程序员文章站 2022-03-14 18:58:26
本文目录：1.创建触发器2.insert触发器3.delete触发器4.update触发器5.通过on duplicate key update分析触发器触发原理6.replace to算法验证7.查看、删除触发器 触发器用来实现在永久表上进行某些操作时触发启动另一操作。 1.创建触发器 以下是Ma ......

本文目录：
1.创建触发器
2.insert触发器
3.delete触发器
4.update触发器
5.通过on duplicate key update分析触发器触发原理
6.replace to算法验证
7.查看、删除触发器

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

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/MariaDB触发器

在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');

查看该表数据。

MySQL/MariaDB触发器

再创建一个极其简单的审核表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;

MySQL/MariaDB触发器

此处删除新建的这个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个触发器。其实根据下面的图很好分析。

MySQL/MariaDB触发器

在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;

 

回到Linux系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处：http://www.cnblogs.com/f-ck-need-u/p/8870446.html

注：若您觉得这篇文章还不错请点击右下角推荐，您的支持能激发作者更大的写作热情，非常感谢！

上一篇： 一篇文章带你入门python之推导式

下一篇： CIO爱大数据 但并非在云中

推荐阅读