如何通过触发器记录和审核用户的操作？

《SQL 从入门到精通》专栏目录

• 第 01 篇 和数据打交道的你，一定要学会 SQL
• 第 02 篇 在 SQL 的世界里一切都是关系
• 第 03 篇 使用 SELECT 语句初步探索数据库
• 第 04 篇 通过查询条件实现数据过滤
• 第 05 篇 如何使用 SQL 语句进行模糊查找？
• 第 06 篇 利用 ORDER BY 对数据进行排序显示
• 第 07 篇 如何实现排行榜和前端分页效果
• 第 08 篇 什么是函数？如何利用函数提高数值计算的效率？
• 第 09 篇 SQL 常见函数之文本数据处理
• 第 10 篇 日期和时间的存储与格式转换
• 第 11 篇 如何为 SQL 增加 IF-THEN-ELSE 逻辑，并且实现行列转换？
• 第 12 篇 轻松完成数据报表中的汇总分析
• 第 13 篇 按照部门或者职位进行分组统计
• 第 14 篇 实战案例：世界银行全球 GDP 数据分析
• 第 15 篇 小心 SQL 中的空值陷阱！
• 第 16 篇 如何同时查询多个表中的相关数据？
• 第 17 篇 子查询：多表查询的另一种方式
• 第 18 篇 你知道表也能进行加减运算吗？
• 第 19 篇 将表当作一个变量，实现递归调用和层次遍历
• 第 20 篇 销售数据的多维度交叉分析
• 第 21 篇 高级报表之移动分析和累计求和
• 第 22 篇 高级报表之分类排名和环比/同比分析
• 第 23 篇 分析股票 K 线图与检测可疑的银行转账
• 第 24 篇 什么是 ER 图，如何进行数据库规范化设计？
• 第 25 篇 SQL 支持哪些数据类型，使用时如何进行选择？
• 第 26 篇 使用 DDL 管理数据库中的对象
• 第 27 篇 使用 DML 执行数据的增删改合
• 第 28 篇 为什么数据库事务如此重要？
• 第 29 篇 索引一定能提高性能吗？
• 第 30 篇 视图有哪些优缺点，什么时候使用视图？
• 第 31 篇 该不该使用存储过程封装业务接口？
• 第 32 篇 如何通过触发器记录和审核用户的操作？
• 第 33 篇 什么是执行计划，如何查看执行计划？
• 第 34 篇 了解常见 SQL 查询优化技巧
• 第 35 篇 使用 SQL 处理 NoSQL 数据
• 第 36 篇 在 Python 中运行 SQL 进行数据分析
• 第 37 篇 在 Java 中使用 SQL 执行增删改查
• 第 38 篇 动态 SQL 语句与防止 SQL 注入
• 第 39 篇 结束语：SQL 编程的道与术

---

上一篇我们学习了如何利用存储过程和函数在数据库中实现业务逻辑。

今天我们介绍一种特殊的存储过程或者函数：触发器（Trigger）。

触发器概述

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

触发器的常见用途包括：

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

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

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

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

触发器分类

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

• 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 定义了触发器执行的操作，具体的实现与存储过程或函数类似。

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

CREATE TABLE salary_audit (
    audit_id    INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Oracle 和 PostgreSQL
    -- audit_id    INTEGER NOT NULL AUTO_INCREMENT, -- MySQL
    -- audit_id    INTEGER NOT NULL IDENTITY, -- SQL Server
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    -- change_date DATETIME2 NOT NULL, -- SQL Server
    change_by   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 提供了系统视图 user_triggers，可以用于查看触发器的各种信息：

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 命令修改触发器的状态。以下语句可以禁用触发器 tri_audit_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 tri_audit_salary;

对于 PostgreSQL，删除触发器需要指定表名：

-- PostgreSQL 实现
DROP TRIGGER tri_audit_salary ON employee;

小结

触发器是一种由特定事件自动触发的存储过程和函数，合理利用触发器可以帮助我们实现特定的数据处理和业务需求。

练习题：禁用触发器 tri_audit_salary，或者在 MySQL 中删除该触发器；然后将“孙乾”的月薪改回 4700。再次查看审计表 salary_audit，是否还会生成一条记录？