MySQL触发器之：`触发器`的`多触发器`执行顺序：一个表上的多个`触发器`的执行顺序。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始。

今天我们要深入探讨MySQL触发器的一个重要方面：当一个表上存在多个触发器时，它们的执行顺序。理解这个顺序对于编写可靠且可预测的数据库逻辑至关重要。

触发器基础回顾

首先，简单回顾一下触发器的基本概念。触发器是在满足特定条件（例如，插入、更新或删除）时自动执行的存储过程。MySQL支持六种类型的触发器，分别对应于BEFORE和AFTER两种时机以及INSERT、UPDATE和DELETE三种操作。

触发器类型	说明
`BEFORE INSERT`	在新行插入到表之前触发。
`AFTER INSERT`	在新行插入到表之后触发。
`BEFORE UPDATE`	在现有行更新之前触发。
`AFTER UPDATE`	在现有行更新之后触发。
`BEFORE DELETE`	在现有行删除之前触发。
`AFTER DELETE`	在现有行删除之后触发。

多触发器执行顺序的确定性

在MySQL 5.7.2 及更早版本中，同一事件（例如，BEFORE INSERT）的多个触发器的执行顺序是未定义的。这意味着你不能依赖于特定的执行顺序。如果多个触发器依赖于彼此，或者它们的行为相互影响，那么结果可能是不可预测的。

从 MySQL 5.7.2 开始，引入了显式控制触发器执行顺序的机制。现在，你可以通过在创建触发器时指定 FOLLOWS 或 PRECEDES 子句来明确定义触发器的执行顺序。

FOLLOWS 和 PRECEDES 子句

FOLLOWS trigger_name: 表示新创建的触发器将在名为 trigger_name 的触发器之后执行。
PRECEDES trigger_name: 表示新创建的触发器将在名为 trigger_name 的触发器之前执行。

实验环境准备

为了演示多触发器的执行顺序，我们需要创建一个测试表和一个存储过程，用于记录触发器的执行顺序。

-- 创建测试表
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(255)
);

-- 创建一个存储过程来记录触发器执行顺序
CREATE TABLE trigger_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    trigger_name VARCHAR(255),
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE PROCEDURE log_trigger_execution(IN trigger_name VARCHAR(255))
BEGIN
    INSERT INTO trigger_log (trigger_name) VALUES (trigger_name);
END //

DELIMITER ;

实验一：未定义顺序 (MySQL 5.7.2 之前或未指定FOLLOWS/PRECEDES)

首先，我们创建两个BEFORE INSERT触发器，但不使用FOLLOWS或PRECEDES子句。

DELIMITER //

CREATE TRIGGER trigger_before_insert_1
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_1');
END //

CREATE TRIGGER trigger_before_insert_2
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_2');
END //

DELIMITER ;

现在，我们插入一条数据，并查看trigger_log表中的记录。

INSERT INTO test_table (value) VALUES ('test_value');

SELECT * FROM trigger_log;

在 MySQL 5.7.2 之前的版本或未指定FOLLOWS 或 PRECEDES 子句时，你可能会看到 trigger_before_insert_1 先执行，也可能看到 trigger_before_insert_2 先执行。执行顺序是不确定的。多次执行插入操作，结果可能不一致。

实验二：使用 FOLLOWS 指定顺序

接下来，我们删除之前的触发器，并使用FOLLOWS子句来明确指定执行顺序。

DROP TRIGGER IF EXISTS trigger_before_insert_1;
DROP TRIGGER IF EXISTS trigger_before_insert_2;

DELIMITER //

CREATE TRIGGER trigger_before_insert_1
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_1');
END //

CREATE TRIGGER trigger_before_insert_2
BEFORE INSERT ON test_table
FOLLOWS trigger_before_insert_1
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_2');
END //

DELIMITER ;

在这个例子中，我们指定trigger_before_insert_2 必须在 trigger_before_insert_1 之后执行。

再次插入一条数据并查看trigger_log表。

INSERT INTO test_table (value) VALUES ('test_value');

SELECT * FROM trigger_log;

现在，你应该总是看到 trigger_before_insert_1 的记录出现在 trigger_before_insert_2 之前。

实验三：使用 PRECEDES 指定顺序

我们可以使用PRECEDES子句达到相同的效果，但顺序相反。

DROP TRIGGER IF EXISTS trigger_before_insert_1;
DROP TRIGGER IF EXISTS trigger_before_insert_2;

DELIMITER //

CREATE TRIGGER trigger_before_insert_2
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_2');
END //

CREATE TRIGGER trigger_before_insert_1
BEFORE INSERT ON test_table
PRECEDES trigger_before_insert_2
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_1');
END //

DELIMITER ;

注意，这里我们先创建了trigger_before_insert_2，然后创建了trigger_before_insert_1，并指定它在trigger_before_insert_2之前执行。

再次插入数据并查看trigger_log，你会发现结果与使用FOLLOWS相同：trigger_before_insert_1 总是在 trigger_before_insert_2 之前执行。

实验四：多个触发器的复杂依赖

我们可以创建更复杂的依赖关系。例如，创建三个触发器，并定义它们的执行顺序。

DROP TRIGGER IF EXISTS trigger_before_insert_1;
DROP TRIGGER IF EXISTS trigger_before_insert_2;
DROP TRIGGER IF EXISTS trigger_before_insert_3;

DELIMITER //

CREATE TRIGGER trigger_before_insert_1
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_1');
END //

CREATE TRIGGER trigger_before_insert_2
BEFORE INSERT ON test_table
FOLLOWS trigger_before_insert_1
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_2');
END //

CREATE TRIGGER trigger_before_insert_3
BEFORE INSERT ON test_table
FOLLOWS trigger_before_insert_2
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_3');
END //

DELIMITER ;

在这个例子中，trigger_before_insert_1 第一个执行，然后是 trigger_before_insert_2，最后是 trigger_before_insert_3。

实验五：触发器之间的相互依赖（循环依赖）

MySQL不允许创建相互依赖的触发器。例如，以下代码会报错：

DROP TRIGGER IF EXISTS trigger_before_insert_1;
DROP TRIGGER IF EXISTS trigger_before_insert_2;

DELIMITER //

CREATE TRIGGER trigger_before_insert_1
BEFORE INSERT ON test_table
FOLLOWS trigger_before_insert_2
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_1');
END //

-- 这条语句将会报错，因为 trigger_before_insert_1 依赖于 trigger_before_insert_2
CREATE TRIGGER trigger_before_insert_2
BEFORE INSERT ON test_table
FOLLOWS trigger_before_insert_1
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert_2');
END //

DELIMITER ;

MySQL会抛出一个错误，指出存在循环依赖。

实验六：不同类型的触发器执行顺序

不同类型的触发器（BEFORE 和 AFTER）的执行顺序是固定的。BEFORE 触发器总是在 AFTER 触发器之前执行。例如：

DROP TRIGGER IF EXISTS trigger_before_insert;
DROP TRIGGER IF EXISTS trigger_after_insert;

DELIMITER //

CREATE TRIGGER trigger_before_insert
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_before_insert');
END //

CREATE TRIGGER trigger_after_insert
AFTER INSERT ON test_table
FOR EACH ROW
BEGIN
    CALL log_trigger_execution('trigger_after_insert');
END //

DELIMITER ;

INSERT INTO test_table (value) VALUES ('test_value');

SELECT * FROM trigger_log;

无论你如何创建这些触发器，trigger_before_insert 总是会在 trigger_after_insert 之前执行。在同一类型的触发器中，可以使用 FOLLOWS 或 PRECEDES 来指定顺序。

注意事项

错误处理: 如果触发器中发生错误，可能会导致事务回滚。确保你的触发器能够正确处理错误，避免数据不一致。
性能: 过多的触发器可能会影响数据库性能。只在必要时使用触发器，并尽量保持触发器的逻辑简单高效。
维护性: 复杂的触发器逻辑可能会难以维护。编写清晰、易于理解的触发器代码，并添加适当的注释。
OLD 和 NEW: 在 BEFORE 触发器中，你可以修改 NEW 值，影响插入或更新的数据。在 AFTER 触发器中，你只能访问 OLD 和 NEW 值，不能修改它们。
版本兼容性: FOLLOWS 和 PRECEDES 子句只在 MySQL 5.7.2 及更高版本中可用。如果你需要支持更早的版本，需要采取其他方法来控制触发器的执行顺序（例如，使用一个触发器调用其他存储过程，并控制存储过程的执行顺序）。但是这种方式复杂且不易维护，不推荐使用。

案例分析：审计日志

一个常见的触发器用例是创建审计日志。我们可以使用触发器来记录对表中数据的更改。

-- 创建审计表
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    record_id INT,
    column_name VARCHAR(255),
    old_value VARCHAR(255),
    new_value VARCHAR(255),
    modified_by VARCHAR(255),
    modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER audit_test_table_update
AFTER UPDATE ON test_table
FOR EACH ROW
BEGIN
    IF OLD.value <> NEW.value THEN
        INSERT INTO audit_log (table_name, record_id, column_name, old_value, new_value, modified_by)
        VALUES ('test_table', OLD.id, 'value', OLD.value, NEW.value, USER());
    END IF;
END //

DELIMITER ;

这个触发器会在每次更新 test_table 的 value 列时，将更改记录到 audit_log 表中。 modified_by 记录了执行更新操作的用户。

案例分析：数据验证

另一个常见的用例是数据验证。我们可以在 BEFORE INSERT 或 BEFORE UPDATE 触发器中检查数据的有效性，并防止无效数据插入到表中。

DELIMITER //

CREATE TRIGGER validate_test_table_insert
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
    IF LENGTH(NEW.value) > 100 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Value cannot be longer than 100 characters';
    END IF;
END //

DELIMITER ;

这个触发器会在插入数据之前检查 value 列的长度。如果长度超过 100 个字符，则会引发一个错误，阻止插入操作。

最佳实践

保持触发器简洁: 触发器应该执行简单的、定义明确的任务。避免在触发器中编写复杂的逻辑。
使用事务: 在触发器中使用事务来确保数据的一致性。
谨慎使用触发器: 不要过度使用触发器。在某些情况下，使用应用程序代码来处理数据更改可能更合适。
记录触发器: 为每个触发器添加清晰的注释，说明其用途和行为。
测试触发器: 彻底测试所有触发器，确保它们按预期工作。

总结一下今天的讲解

今天我们深入研究了MySQL触发器的多触发器执行顺序。从MySQL 5.7.2开始，可以使用FOLLOWS和PRECEDES子句来显式控制同一事件的多个触发器的执行顺序，这对于构建可靠的数据库逻辑至关重要。理解并合理利用触发器，可以帮助我们更好地维护数据的完整性和一致性。

发表回复 取消回复

发表回复取消回复