第 33 篇触发器

imodouAbout 6 minMySQL

《MySQL 入门教程》第 33 篇触发器

33.1 触发器概述

MySQL 触发器（trigger）是一种存储程序，它和一个指定的表相关联，当该表中的数据发生变化（增加、更新、删除）时自动执行。这些修改数据行的操作被称为触发器事件，例如 INSERT 或者 LOAD DATA 等插入数据的语句可以激活插入触发器。

按照 SQL 标准，触发器可以分为行级触发器（row-level trigger）和语句级触发器（ statement-level trigger）。行级触发器对于修改的每一行数据都会激活一次，如果一个语句插入了 100 行数据，将会调用触发器 100 次；语句级触发器针对每个语句激活一次，一个插入 100 行数据的语句只会调用一次触发器。MySQL 只支持行级触发器，不支持预语句级触发器。

不同事件可以激活不同类型的触发器。INSERT 事件触发器用于插入数据的操作，包括 INSERT、LOAD DATA、REPLACE 语句等；UPDATE 事件触发器用于更新操作，例如 UPDATE 语句；DELETE 事件触发器用于删除操作，例如 DELETE 和 REPLACE 语句等，DROP TABLE 和 TRUNCATE TABLE 语句不会激活删除触发器。

另外，MySQL 触发器可以在触发事件之前或者之后执行，分别称为 BEFORE 触发器和 AFTER 触发器。这两种触发时机可以和不同的触发事件进行组合，例如 BEFORE INSERT 触发器或者 AFTER UPDATE 触发器。

MySQL 触发器的优点包括：

记录并审核用户对表中数据的修改操作，实现审计功能；
实现比检查约束更复杂的完整性约束，例如禁止非业务时间的数据操作；
实现某种业务逻辑，例如增加或删除员工时自动更新部门中的人数；
同步实时地复制表中的数据。

虽然触发器功能强大，但是它也存在一些缺点：

触发器会增加数据库结构的复杂度，而且触发器对应用程序不可见，难以调试；
触发器需要占用更多的数据库服务器资源，尽量使用数据库提供的非空、唯一、检查约束等；
触发器不能接收参数，只能基于当前的触发对象进行操作。

针对特殊场景使用触发器可以带来一定的便利性；但不要过渡依赖触发器，避免造成数据库的性能下降和维护困难。接下来我们介绍触发器的管理操作。

33.2 创建触发器

MySQL 使用CREATE TRIGGRT语句创建触发器，基本语法如下：

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

其中，trigger_name 是触发器的名称；BEFORE 和 AFTER 用于指定触发器的触发时机；INSERT、UPDATE 和 DELETE 用于定义触发事件的类型；table_name 是触发器关联的表名，不能是临时表或者视图；FOR EACH ROW 表明这是一个行级触发器；trigger_body 是触发器执行的具体语句。

举例来说，由于员工的薪水属于重要信息，所以需要记录薪水的修改历史。首先，我们创建一个审计表：

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

其中，audit_id 是自增主键；emp_id 是员工编号；old_salary 和 new_salary 分别用于存储修改前和修改后的月薪；change_date 记录了修改时间；change_by 记录了执行修改操作的用户。

然后创建一个触发器 tri_audit_salary，用于记录员工月薪的修改记录：

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

其中，DELIMITER 用于修改 SQL 语句的结束符，我们在前文介绍存储过程时已经有所了解；AFTER 表示在修改数据之后执行触发器；UPDATE 表示只针对更新操作记录数据变化；触发器主体中的 NEW 和 OLD 是 MySQL 触发器中的特殊变量，包含了修改后和修改前的记录，对于 INSERT 触发器而言没有 OLD 变量，对于 DELETE 触发器而言没有 NEW 变量；CURRENT_TIMESTAMP 和 USER() 都是 MySQL 系统函数，返回当前时间和登录的用户。

创建触发器之后，我们执行一些数据修改的操作，验证该触发器的效果：

UPDATE employee
SET email = 'sunqian@shuguo.net'
WHERE emp_name = '孙乾';
 
UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = '孙乾';
 
SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

第一个 UPDATE 语句只修改了“孙乾”的电子邮箱，所以不会触发 tri_audit_salary；第二个 UPDATE 语句修改了他的月薪，触发了 tri_audit_salary。因此审计表 salary_audit 中包含一条数据，记录了月薪变化前后的情况。

如果想要同时审计新增员工和删除员工的操作，可以再创建一个 INSERT 触发器和 DELETE 触发器。

除此之外，MySQL 支持针对相同的触发时机和相同的事件定义多个触发器，同时指定它们的执行顺序：

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

其中，FOLLOWS 表示该触发器在触发器 other_trigger 之后执行；PRECEDES 表示该触发器在 other_trigger 之前执行；如果没有指定任何选项，默认情况下按照触发器的创建顺序执行。

33.3 查看触发器

使用SHOW TRIGGERS语句可以查看数据库中的触发器列表：

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

其中，db_name 用于查看指定数据库中的触发器，默认为当前数据库；LIKE 用于匹配存储过程的名称，WHERE 可以指定更多的过滤条件。例如，以下语句返回了当前数据库中的触发器：

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

另外，MySQL 系统表 INFORMATION_SCHEMA.TRIGGERS 中包含了更详细的触发器信息。

如果想要获取创建某个触发器的 DDL 语句，可以SHOW CREATE TRIGGER语句。例如：

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

33.4 删除触发器

MySQL 没有提供修改触发器的语句，只能通过DROP TRIGGER语句删除并再次创建触发器。例如，以下语句可以用于删除触发器 tri_audit_salary：

DROP TRIGGER IF EXISTS tri_audit_salary;

IF EXISTS 可以避免触发器 tri_audit_salary 不存在时产生错误。

第 33 篇 触发器

《MySQL 入门教程》第 33 篇 触发器