MySQL 触发器

前段时间在工作中踩到了关于触发器的两个坑，所以将触发器的相关知识撸了一遍，固有了此文。

问题回顾

命令行创建触发器报语法错误

CREATE TRIGGER order_update AFTER UPDATE ON `order` 
FOR EACH ROW
BEGIN
	IF old.state = 0 and new.state = 1 THEN
		update t1...;
		insert into t2 ...;
		insert into t3 ...;
	END IF;
END;

当时创建的触发器类似上面的，是一个有多条语句的触发器。使用命令行执行时始终报语法错误，几番尝试无果，最终用 Navicat 的设计表才创建成功。事后重学触发器的时候才发现问题所在，以及解决办法，官方文档 23.3.1 Trigger Syntax and Examples 中提到：

By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value must be checked before it is used to update the row:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

可使用 BEGIN... END 定于执行多个语句的触发器，但是用命令行执行 CREATE TRIGGER 前，要重新定于语句的分隔符，因为默认的分隔符时分号 ;，而我们的创建语句中有用到了分号，这样 MySQL 就蒙圈了。所以正确的语句如下，写重新定于语句分隔符为 // （这里定于为其他的也可以，比如 |），然后执行创建语句，然后恢复语句分隔符为 ;。

delimiter //
CREATE TRIGGER order_update AFTER UPDATE ON `order` 
FOR EACH ROW
BEGIN
	IF old.state = 0 and new.state = 1 THEN
		update t1...;
		insert into t2 ...;
		insert into t3 ...;
	END IF;
END;
delimiter ;

TRIGGER command denied to user ‘zhaobingshuang’@’%’ for table ‘order’

TRIGGER command denied to user 'zhaobingshuang'@'%' for table 'order'\n### The error may exist in class path resource [mapper/OrderMapper.xml]\n### The error may involve com.xxx.order.dao.OrderMapper.updateByPrimaryKeySelective-Inline\n### The error occurred while setting parameters\n### SQL: update order SET ...

我的账号是 zhaobingshuang，假设线上环境的账号是 prd_mysql。当天我用 Navicat 创建了上面的触发器 order_update，然后进行了线上测试，没有问题，就让运维同学把我账号对线上环境的权限收回了，毕竟谁都不想手里握着个定时炸弹。但过了几分钟就出现了上面的日志报警。

此时马上意识到是因为我的权限被收回导致的问题，因为日志里的账号 zhaobingshuang 是我的账号，又先紧急把我的权限加上了，暂时先解决线上问题了。但我的账号一直有线上环境的权限也比较危险，于是又用线上环境的账号 prd_mysql 重建了一下触发器，然后回收了我的线上权限，至此问题彻底解决了。

时候我终于在官方文档中找到了问题的原因：触发器是我创建的，所以 DEFINER = zhaobingshuang@%，但触发器被激活时，MySQL 会校验 DEFINER 是否还用于本表的 TRIGGER 权限。我的账号显然没有此权限了，因为权限被收回了，所以执行报错，出现上述的错误。除了 TRIGGER 权限之外还会校验其他的权限，先下文创建触发器的 DEFINER 部分。

创建触发器 CREATE TRIGGER

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 } other_trigger_name

DEFINER

CREATE TRIGGER 语句需要与触发器关联的表的 TRIGGER 权限。如果该DEFINER 子句存在，则所需的权限取决于该 *user*值，如 Section 23.6, “Stored Object Access Control” 所述。如果启用了二进制日志记录，则 CREATE TRIGGER 可能需要SUPER特权，Section 23.7, “Stored Program Binary Logging”。

在触发器被激活时，需要检查此用户的访问权限。如果 DEFINER 子句存在，该 user 值应被指定为一个 MySQL 帐户（user_name@host_name、CURRENT_USER、CURRENT_USER()），允许的值取决于您拥有的权限。如有关触发器安全性的其他信息，请参见 Section 23.6, “Stored Object Access Control”。

如果省略 DEFINER 子句，则默认定义者是执行 CREATE TRIGGER 语句的用户。这与显式指定 DEFINER = CURRENT_USER 相同 。

MySQL 在检查触发器权限时考虑到的 DEFINER 用户如下:

• 在执行 CREATE TRIGGER 时，用户必须有 TRIGGER 权限。
• 在触发器被激活时，会检查 DEFINER 用户的权限 。此用户必须具有以下权限：
  • 主题表的 TRIGGER 权限。
  • 如果在触发器主题中使用了 NEW.col_name 或 OLD.col_name ，则还需要主题表的 SELECT 权限。
  • 如果存在 SET NEW.col_name = value 语句，需要主题表的 UPDATE 权限。
  • 触发器执行的语句通常需要任何其他特权。

在触发器主体内，CURRENT_USER 函数返回 DEFINER用户，而不是其操作导致触发器被激活的用户。有关触发器内的用户审计的信息，请参阅 Section 6.2.18, “SQL-Based Account Activity Auditing”。

当触发器被激活时，触发器的执行需要定义此触发器的用户仍然拥有此表的 TRIGGER 权限。

如果使用 LOCK TABLES 锁定具有触发器的表，那么触发器中使用的表也会被锁定，如LOCK TABLES and Triggers。

trigger_name

触发器的名字。触发器名称存在于 schema namespace 中，这意味着所有触发器在 schema 中都必须具有唯一的名称。不同 schema 中的触发器可以具有相同的名称。

trigger_time

触发动作的时机。可以是 BEFORE 或 AFTER 表示触发器在要修改的每一行之前或之后执行。

trigger_event

激活触发器的事件。可以有以下值：

• INSERT：向表中插入新行时，比如 INSERT、LOAD DATA、REPLACE。
• UPDATE：修改行时，比如 UPDATE 或者 INSERT INTO ... ON DUPLICATE KEY UPDATE ... 存在重复键时。
• DELETE：删除行时，比如 DELETE、REPLACE。DROP TABLE 和 TRUNCATE TABLE 不会激活触发器，因为这两个语句没有执行 DELETE 行。删除分区也不会激活。

INSERT INTO ... ON DUPLICATE KEY UPDATE ... 语句比较令人困惑，首先 BEFORE INSERT 触发器始终会激活，如果没有重复键，紧接着执行一个 AFTER INSERT触发器；如果有重复键，紧接着执行 BEFORE UPDATE 和 AFTER UPDATE 两种触发器。

tbl_name

与触发器关联的表名。该表必须是一个永久表，不能是 TEMPORARY 表或视图 。

trigger_order

当表上有相同 trigger_time、trigger_event 的触发器时，触发器的执行顺序。使用FOLLOWS，新触发器在现有触发器之后激活。使用 PRECEDES，新触发器在现有触发器之前激活。

trigger_body

触发器激活时要执行的语句。简单的例子：

新建了一张表 account。然后创建了一个触发器 ins_sum，它会将插入表中的值累加到用户变量 @sum 上。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

要使用触发器，要将累加器变量设置为零，执行一条 INSERT 语句，然后查看变量的值：

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

上面只是一个很简单的例子，在触发器正文中，还可以使用别名 OLD 和 NEW 来引用主题表（与触发器关联的表）中的列，OLD 和 NEW是的 MySQL 的扩展，它们不区分大小写。OLD.col_name 指的是在更新或删除之前的列的值。 NEW.col_name 指的是要插入或更新后的列的值。OLD 是只读的，可以引用它，但不能修改它。在 BEFORE 触发器中，可以更改即将被插入或更新的列值（ SET NEW.col_name = value ）。（这样的语句在 AFTER 触发器中不起作用，因为行的更改已经发生。）

触发器不能使用 NEW.col_name 或 OLD.col_name 用于 引用 generated columns。有关生成列的信息，请参阅 Section 13.1.18.7, “CREATE TABLE and Generated Columns”。

在一个BEFORE触发器中，NEW 对于一个值 AUTO_INCREMENT 列的值是 0，而不是实际插入新行时自动生成的序列号。

在 MySQL 5.7.2 之前，表不能有多个具有相同 trigger_time 和 trigger_event 的触发器。例如，一个表不能有两个 BEFORE UPDATE 触发器。要解决此问题，可以使用 BEGIN ... END 定义执行多个语句的触发器。

在 BEGIN 块内，还可以使用存储过程中允许的其他语法，例如条件和循环。但和存储过程一样，==如果使用命令行定义一个执行多条语句的触发器，则需要重新定义语句分隔符，以便在触发器定义中使用 ; 语句分隔符。==以下示例说明了这些要点。它定义了一个UPDATE 触发器检查用于更新每一行的新值，并将该值修改为 0 到 100 范围内的值。这必须是BEFORE触发器，因为必须在更新之前检查列的值：

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

单独定义存储过程，然后使用简单 CALL 语句从触发器中调用会更简单 。如果想从多个触发器中复用相同的代码，这样做更好。

触发器执行的语句有以下限制：

• 触发器不能使用 CALL 语句调用向客户端返回数据或使用动态SQL的存储过程。(存储过程可以通过 OUT 或 INOUT 参数向触发器返回数据。)
• 触发器不能使用显式或隐式开始或结束事务的语句，如 START TRANSACTION，COMMIT或ROLLBACK(ROLLBACK to SAVEPOINT是允许的，因为它不会结束交易)。其他限制参见 Section 23.8, “Restrictions on Stored Programs”。

从 MySQL 5.7.2 开始，可以为表添加多个具有相同 trigger_time 和 trigger_event 的触发器。默认情况下，它们会按照被创建的顺序激活。要影响触发器顺序，可使用 trigger_order 中提到的语法。使用FOLLOWS，新触发器在现有触发器之后激活。使用 PRECEDES，新触发器在现有触发器之前激活。

例如，以下的触发器定义为account表定义了另一个 BEFORE INSERT 触发器 ：

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)

此触发器有一个PRECEDES 子句使其在 ins_sum 之前激活；如果没有那个子句，它会在 ins_sum 之后激活，因为它是在 ins_sum 之后创建的。

创建触发器时被记录的系统变量

• sql_mode：创建触发器时生效的 SQL 模式，触发器将始终在该模式下执行（忽略触发器被激活时服务器的 SQL 模式）。

• character_set_client：创建触发器时系统变量 character_set_client 的会话值 。

• collation_connection：创建触发器时系统变量 collation_connection 的会话值 。

• Database Collation：与触发器关联的数据库的排序规则。

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

schema（database）名称是可选的。如果 schema 被省略，触发器将从默认 schema 中删除。DROP TRIGGER 需要与触发器关联的表的 TRIGGER 权限。

使用 IF EXISTS 以防止删除不存在的触发器时发生错误。当使用 IF EXISTS 时，将为不存在的触发器生成一个 NOTE。参见 Section 13.7.5.40, “SHOW WARNINGS Statement”。

如果删除表，表的触发器也会被删除。

SHOW CREATE TRIGGER Statement

SHOW CREATE TRIGGER trigger_name

查询触发器的创建语句。执行此语句需要拥有与触发器关联的表的 TRIGGER 权限。

mysql> SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************
               Trigger: ins_sum
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                        NO_ZERO_IN_DATE,NO_ZERO_DATE,
                        ERROR_FOR_DIVISION_BY_ZERO,
                        NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum
                        BEFORE INSERT ON account
                        FOR EACH ROW SET @sum = @sum + NEW.amount
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci
               Created: 2018-08-08 10:10:07.90

输出有这些列：

• Trigger: 触发器名称。
• sql_mode：触发器执行时生效的 SQL 模式。
• SQL Original Statement：定义触发器的 CREATE TRIGGER 语句。
• character_set_client：触发器创建时系统变量 character_set_client 的会话值。
• collation_connection：触发器创建时系统变量 collation_connection 的会话值。
• Database Collation：与触发器关联的数据库的排序规则。
• Created：触发器创建的日期和时间。

触发器信息也可从 INFORMATION_SCHEMA TRIGGERS表中获得。见 Section 24.3.29, “The INFORMATION_SCHEMA TRIGGERS Table”.。

SHOW TRIGGERS Statement

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

列出当前数据库表上的触发器。此语句仅返回您拥有 TRIGGER 权限的数据库和表的结果。LIKE 子句用于过滤表名（不是触发器名称）。WHERE子句可以用于使用更一般的条件选择行，如 Section 24.8, “Extensions to SHOW Statements”。

对于触发器 ins_sum，其输出如下所示：

mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2018-08-08 10:10:12.61
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: me@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

输出有这些列：

• Trigger

  触发器的名称。

• Event

  触发事件。这是触发器为其激活的关联表上的操作类型。值为INSERT（插入一行）、 DELETE（删除一行）或 UPDATE（修改一行）。

• Table

  为其定义触发器的表。

• Statement

  触发器激活时执行的语句。

• Timing

  触发器是在触发事件之前还是之后激活。值为BEFORE或 AFTER。

• Created

  创建触发器的日期和时间。

• sql_mode

  创建触发器时生效的 SQL 模式，触发器将在该模式下执行。

• Definer

  创建触发器的用户，采用 格式。 user_name@host_name

• character_set_client

  创建触发器时系统变量 character_set_client 的会话值 。

• collation_connection

  创建触发器时系统变量 collation_connection 的会话值 。

• Database Collation

  与触发器关联的数据库的排序规则。

触发器信息也可从 INFORMATION_SCHEMA TRIGGERS表中获得。见 Section 24.3.29, “The INFORMATION_SCHEMA TRIGGERS Table”。

参考

• 23.3 Using Triggers,
• 23.3.1 Trigger Syntax and Examples
• 23.3.2 Trigger Metadata
• 13.7.5.11 SHOW CREATE TRIGGER Statement
• 13.7.5.38 SHOW TRIGGERS Statement
• TRIGGER 权限
• Section A.5, “MySQL 5.7 FAQ: Triggers”
• 24.3.29 The INFORMATION_SCHEMA TRIGGERS Table