MySQL - 触发器

MySQL 触发器：自动化数据库操作

MySQL 中的触发器是与表关联的命名数据库对象，当表的特定事件发生时会自动激活。这些事件通常是 INSERT、UPDATE 或 DELETE 操作。触发器是强制执行业务规则、维护数据完整性和创建审计跟踪的强大工具。

注意：要创建触发器，您需要对关联表具有 TRIGGER 权限。默认情况下，SUPER 权限也允许创建触发器。

理解触发器激活时间和事件

MySQL 触发器由两个主要特性定义：激活时间和事件。

激活时间：这指定触发器应该在数据修改之前还是之后触发。选项有 BEFORE 和 AFTER。
事件：这是导致触发器触发的特定数据操作语言 (DML) 操作。选项有 INSERT、UPDATE 和 DELETE。

结合这些，我们可以得到任何给定表的六种可能的触发器类型：

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

所有 MySQL 触发器都是行级触发器，这意味着它们对受触发语句影响的每一行执行一次。MySQL 不支持语句级触发器，后者无论修改多少行，都只对每个语句执行一次。

在触发器内部访问数据

在触发器主体内部，您可以使用特殊别名访问受影响行的列值：

OLD：指在 UPDATE 或 DELETE 之前的现有行。它是只读的。
NEW：指将要插入的新行或行的更新版本。您可以在 BEFORE 和 AFTER 触发器中读取它，并在 BEFORE 触发器中修改其值（例如，清理输入）。

触发器事件	OLD	NEW
INSERT	不适用	将要插入的新行
UPDATE	更新前的行	更新后的行
DELETE	正在被删除的行	不适用

实际示例：创建审计跟踪

触发器最常见的用途之一是创建审计日志。假设我们有一个 employees 表，并且我们想将所有薪资更改记录到 salary_audits 表中。

首先，定义 employees 表：

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

接下来，创建 salary_audits 表来存储历史记录：

CREATE TABLE salary_audits (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    changed_by VARCHAR(100),
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

现在，让我们在 employees 表上创建一个 AFTER UPDATE 触发器。只要记录被更新，这个触发器就会触发。我们使用 DELIMITER 是因为触发器主体包含分号。

DELIMITER //

CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 检查薪资是否实际发生了变化
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audits(employee_id, old_salary, new_salary, changed_by)
        VALUES(OLD.employee_id, OLD.salary, NEW.salary, USER());
    END IF;
END//

DELIMITER ;

我们来测试一下。首先，插入一个新员工：

INSERT INTO employees (first_name, last_name, salary) VALUES ('Alice', 'Williams', 60000.00);

现在，更新 Alice 的薪资。此操作将触发触发器。

UPDATE employees SET salary = 65000.00 WHERE employee_id = 1;

如果您检查 salary_audits 表，您将看到一条新记录：

SELECT * FROM salary_audits;

-- 输出：
+----------+-------------+------------+------------+----------------+---------------------+
| audit_id | employee_id | old_salary | new_salary | changed_by     | changed_at          |
+----------+-------------+------------+------------+----------------+---------------------+
|        1 |           1 |   60000.00 |   65000.00 | user@localhost | 2023-10-27 10:30:00 |
+----------+-------------+------------+------------+----------------+---------------------+

触发器的优点

数据完整性：触发器可以强制执行标准约束（如 CHECK 或 FOREIGN KEY）无法处理的复杂业务规则和完整性约束。
自动化：它们自动运行，确保应用程序层不会遗漏关键操作（如审计或更新相关数据）。
集中式逻辑：业务逻辑存储并在数据库内部执行，为所有访问数据的应用程序提供单一的事实来源和一致性。
审计：非常适合创建详细的数据修改日志，这对于安全和合规性至关重要。

缺点和注意事项

隐藏逻辑：由于触发器在后台运行，它们可能会使数据库行为难以理解，对于不了解触发器的开发人员来说，也难以调试。
性能开销：触发器会增加 DML 操作的开销。编写不当或过于复杂的触发器会显著降低数据库性能。
级联效应：触发器可以激活其他触发器（级联效应），这可能导致复杂且难以追踪的执行路径。
调试：触发器排障比调试应用程序代码更具挑战性，因为它们不向客户端应用程序提供直接反馈。

现代 MySQL 触发器特性和限制

以下是现代 MySQL（8.0+ 版本）中关于触发器的一些要点：

多个触发器：从 MySQL 5.7.2 开始，您可以为同一个表定义多个具有相同激活时间和事件的触发器。您可以使用 CREATE TRIGGER 语句中的 FOLLOWS 或 PRECEDES 子句指定执行顺序。
禁止的语句：触发器不能包含显式或隐式提交或回滚事务的语句（例如，COMMIT、ROLLBACK、START TRANSACTION）。它们也不能使用 CALL 语句来调用向客户端返回数据或使用动态 SQL 的存储过程。
没有 RETURN 语句：触发器不返回值，因此不允许使用 RETURN 语句。
外键操作：触发器不会被级联外键操作（ON DELETE CASCADE、ON UPDATE CASCADE）激活。
视图和临时表：您不能在视图或 TEMPORARY 表上创建触发器。