上一篇我们学习了如何利用存储过程和函数在数据库中实现业务逻辑。
今天我们介绍一种特殊的存储过程或者函数：触发器（Trigger）。

触发器概述

数据库触发器是一种特殊的存储过程或者函数，触发器不能被直接调用，而是当某个事件发生时自动触发并执行预定义的操作。常见的触发事件包括修改数据的 DML 语句、定义数据库对象的 DDL 语句以及系统级别的事件，例如用户的登录操作。以下是 DML 语句触发器的示意图：
image.png
触发器的常见用途包括：

  • 记录并审核用户对表中数据的修改操作，实现审计功能；
  • 实现比检查约束更复杂复杂的完整性约束，例如禁止非业务时间的数据操作；
  • 实现某种业务逻辑，例如增加或删除员工时自动更新部门中的人数；
  • 使用触发器生成序列号的值，为字段提供默认的数据；
  • 同步实时地复制表中的数据。

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

  • 触发器会增加数据库结构的复杂度；
  • 触发器需要占用更多的数据库资源；
  • 触发器也是一种存储过程，所以不同数据库之间的可移植性比较差；
  • 触发器不能接收参数，只能基于当前的触发对象进行操作。

适当使用触发器可以为我们的业务实现带来便利；但是不要过渡依赖触发器，避免会造成数据库的性能下降和维护困难。

触发器分类

我们可以根据触发的事件和时间，将触发器分为不同类型：

  • DML 触发器和 DDL 触发器。DML 触发器在发生数据修改操作时触发，可以按照 INSERT、UPDATE 以及 DELETE 语句进一步细分；DDL 触发器由数据定义语句触发，例如 CREATE、DROP 等。
  • BEFORE 触发器和 AFTER 触发器。BEFORE 触发器在触发事件之前执行，AFTER 触发器在触发事件之后执行。另外，还有一种类型称为 INSTEAD OF 触发器。它可以用于替代针对表或视图的 INSERT、UPDATE 或 DELETE 语句并执行其他的语句。
  • 行级触发器（row-level trigger）和语句级触发器（statement-level trigger）。行级触发器对于受影响的每一行数据执行一次，语句级触发器针对每条语句执行一次。例如，一个语句更新了 100 行数据，行级触发器执行 100 次，语句级触发器执行 1 次。

按照这些分类条件组合，可以创建各种触发器；例如，行级 BEFORE INSERT 触发器。以下是 4 种主流数据库对于常用触发器的支持情况：

触发器分类 Oracle MySQL SQL Server PostgreSQL
行级触发器 支持 支持 不支持 支持
语句级触发器 支持 不支持 支持 支持
BEFORE 触发器 支持 支持 不支持 支持
AFTER 触发器 支持 支持 支持 支持
INSTEAD OF 触发器 支持 不支持 支持 支持
DDL 触发器 支持 不支持 支持 事件触发器

MySQL 只有行级触发器，SQL Server 只有语句级触发器。接下来我们看看如何创建和管理触发器。

创建触发器

创建触发器的基本语法如下：
CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} triggering_event ON table_name [FOR EACH ROW] | [FOR EACH STATEMENT] trigger_body; 复制
其中：

  • CREATE TRIGGER 表示创建一个触发器，trigger_name 是触发的名称；
  • BEFORE、AFTER、分别表示触发的时机，INSTEAD OF 表示替代触发器；
  • triggering_event 定义了触发事件，例如 INSERT、UPDATE、DELETE 等；
  • table_name 表示与触发事件相关的表；
  • FOR EACH ROW 表示行级触发器，FOR EACH STATEMENT 表示语句级触发器；
  • trigger_body 定义了触发器执行的操作，具体的实现与存储过程或函数类似。

我们来看一个具体的例子。由于员工的薪水属于重要的隐私信息，所以需要记录薪水的修改历史。首先，创建一个审计表 salaryaudit：
CREATE TABLE salary_audit ( audit_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, — Oracle 和 PostgreSQL — auditid INTEGER NOT NULL AUTO_INCREMENT, — MySQL — audit_id INTEGER NOT NULL IDENTITY, — SQL Server empid INTEGER NOT NULL, old_salary NUMERIC(8,2) NULL, new_salary NUMERIC(8,2) NULL, change_date TIMESTAMP NOT NULL, — changedate DATETIME2 NOT NULL, — SQL Server changeby VARCHAR(50) NOT NULL, CONSTRAINT pk_salary_audit PRIMARY KEY (audit_id) ); 复制
其中，audit_id 是一个自增主键；emp_id 是员工编号；old_salary 和 new_salary 分别用于存储修改前和修改后的月薪；change_date 记录了修改时间；change_by 记录了执行修改操作的用户。注意，如果使用 MySQL 或者 SQL Server 需要修改某些字段的类型。
然后创建一个触发器 tri_audit_salary，用于记录员工月薪的修改历史。先来看一下 Oracle 中的 PL/SQL 实现：
— Oracle 实现 CREATE OR REPLACE TRIGGER tri_audit_salary AFTER UPDATE ON employee FOR EACH ROW DECLARE 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; 复制
其中，AFTER UPDATE ON employee 表示当员工表发生数据修改时触发该触发器；:NEW 和 :OLD 是 Oracle 触发器中的特殊变量，包含了修改后和修改前的记录；IF 语句表示月薪发生变化时插入一条审计记录；CURRENT_TIMESTAMP 和 USER 都是 Oracle 系统函数，返回当前时间和登录的用户。
再来看看如何在 MySQL 中实现相同的功能：
— MySQL 实现 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 语句的结束符，我们在介绍存储过程时已经有所了解；NEW 和 OLD 是 MySQL 触发器中的特殊变量，包含了修改后和修改前的数据；CURRENT_TIMESTAMP 和 USER() 都是 MySQL 系统函数，返回当前时间和登录的用户。
SQL Server 和 PostgreSQL 中的实现略有不同；为了不占用过多篇幅，我们在 GitHub 上提供了链接下载。
接下来我们执行一些数据修改的操作，验证该触发器的效果：
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 中包含一条数据，记录了月薪变化前后的情况。

管理触发器

对于触发器常见的管理操作包括查看和启用/禁用。常用的数据库管理和开发工具通常都会提供图形化的操作方式，我们在此介绍一些相关的 SQL 语句命令。

查看触发器

Oracle 提供了系统视图 usertriggers，可以用于查看触发器的各种信息：
SELECT trigger_name, trigger_type, triggering_event,table_name, status FROM user_triggers; TRIGGER_NAME |TRIGGER_TYPE |TRIGGERING_EVENT|TABLE_NAME|STATUS | ————————|———————|————————|—————|———-| TRI_AUDIT_SALARY|AFTER EACH ROW|UPDATE |EMPLOYEE |ENABLED| 复制
MySQL 和 PostgreSQL 提供了系统视图 information_schema.triggers：
SELECT trigger_name, event_manipulation, event_object_table, action_orientation, action_timing FROM information_schema.triggers WHERE event_object_table = ‘employee’; TRIGGER_NAME |EVENT_MANIPULATION|EVENT_OBJECT_TABLE|ACTION_ORIENTATION|ACTION_TIMING| ————————|—————————|—————————|—————————|——————-| tri_audit_salary|UPDATE |employee |ROW |AFTER | 复制
MySQL 中也可以使用 SHOW TRIGGERS; 命令查看触发器。
SQL Server 提供了系统视图 sys.triggers：
SELECT name, is_disabled, is_instead_of_trigger FROM sys.triggers; name |is_disabled|is_instead_of_trigger| ————————|—————-|——————————-|_ tri_audit_salary| 0| 0| 复制
默认创建的触发器处于启用状态，我们也可以将其禁用；此时触发器仍然存在，但是不会被触发。

启用/禁用触发器

在 Oracle 中，可以使用 ALTER TRIGGER 命令修改触发器的状态。以下语句可以禁用触发器 triaudit_salary：
— Oracle 实现 ALTER TRIGGER tri_audit_salary DISABLE; 复制
如果将 DISABLE 换成 ENABLE，可以再次启用该触发器。
SQL Server 中实现相同功能的命令如下：
— SQL Server 实现 DISABLE TRIGGER tri_audit_salary ON employee; 复制
如果将 DISABLE 换成 ENABLE，可以再次启用该触发器。
PostgreSQL 中实现相同功能的命令如下：
— PostgreSQL 实现_ ALTER TABLE employee DISABLE TRIGGER tri_audit_salary; 复制
如果将 DISABLE 换成 ENABLE，可以再次启用该触发器。
MySQL 不支持禁用触发器，只能将其删除；一旦创建就表示启用。

删除触发器

删除触发器的语句为 DROP TRIGGER，以下示例用于删除员工表上的触发器：
— Oracle、MySQL 以及 SQL Server 实现 DROP TRIGGER triaudit_salary; 复制
对于 PostgreSQL，删除触发器需要指定表名：
— PostgreSQL 实现_ DROP TRIGGER tri_audit_salary ON employee; 复制

小结

触发器是一种由特定事件自动触发的存储过程和函数，合理利用触发器可以帮助我们实现特定的数据处理和业务需求。
练习题：禁用触发器 tri_audit_salary，或者在 MySQL 中删除该触发器；然后将“孙乾”的月薪改回 4700。再次查看审计表 salary_audit，是否还会生成一条记录？