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 into算法验证

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

replace into算法说明如下：

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;

本文原创地址在博客园：https://www.cnblogs.com/f-ck-need-u/p/8870446.html