MySQL - AFTER DELETE 触发器

MySQL - AFTER DELETE 触发器

在 MySQL 中，触发器是一种特殊类型的存储程序，它响应特定表上的某些事件而自动执行或“触发”。这些事件是 INSERT、UPDATE 或 DELETE 操作。触发器通常用于维护数据完整性、强制执行复杂的业务规则或创建审计日志。

触发器可以设置为在数据修改事件发生 BEFORE（之前）或 AFTER（之后）触发。

AFTER DELETE 触发器

AFTER DELETE 触发器是行级触发器，它在表中每删除一行时自动执行。由于它在删除之后触发，你不能修改被删除的行（因为它已经不存在了），但你可以访问其原始值来执行其他操作，例如将删除操作记录到归档表。

在 DELETE 触发器内部，你可以使用 OLD 关键字来访问刚被删除行中列的值。例如，OLD.ID 指的是被删除行的 ID 列的值。

语法

CREATE TRIGGER trigger_name
    AFTER DELETE ON table_name
    FOR EACH ROW
BEGIN
    -- 触发器体：一个或多个 SQL 语句
    -- 使用 OLD.column_name 访问已删除行中的值。
END;

实际示例：创建审计跟踪

AFTER DELETE 触发器的一个常见用途是维护审计跟踪。当主表中的一条记录被删除时，我们可以自动将该记录的副本插入到归档或日志表中。这确保了数据永不真正丢失。

步骤 1：创建主表和归档表

首先，让我们创建主表 CUSTOMERS 和一个 OLD_CUSTOMERS 表来存储已删除的记录。它们的结构应该相同。

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00);

CREATE TABLE OLD_CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤 2：创建 AFTER DELETE 触发器

现在，创建触发器。它将在 CUSTOMERS 表执行 DELETE 操作后触发，并将 OLD 行数据插入到 OLD_CUSTOMERS 中。

DELIMITER //

CREATE TRIGGER trg_customer_archive
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //

DELIMITER ;

注意：DELIMITER // 将语句分隔符从 ; 更改为 //，这允许触发器的 BEGIN...END 块（其中包含分号）被解析为单个语句。

验证触发器

让我们删除一位客户，看看我们的触发器是否有效。

DELETE FROM CUSTOMERS WHERE ID = 3;

现在，检查两个表的内容。

SELECT * FROM CUSTOMERS;

ID	姓名	年龄	地址	薪水
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00

SELECT * FROM OLD_CUSTOMERS;

ID	姓名	年龄	地址	薪水	删除时间
3	Kaushik	23	Kota	2000.00	YYYY-MM-DD HH:MM:SS

成功！‘Kaushik’ 的记录已从 CUSTOMERS 中移除，并且一个相同的副本已插入到 OLD_CUSTOMERS 中，有效地归档了删除操作。

考量和最佳实践

• 性能： 触发器会增加 DML 操作的开销。保持触发器逻辑尽可能简单高效。
• 复杂性： 触发器隐式执行（“神奇”行为），这会使应用程序逻辑更难调试和推理。对于复杂的业务规则，通常最好在应用程序代码或存储过程中处理逻辑。
• 替代方案： 在使用触发器之前，请考虑任务是否可以在应用程序中处理。权衡在于数据库中保证的执行（触发器）与应用程序中更易于维护和测试的代码之间。
• 级联效应： 警惕导致其他触发器触发的触发器。这可能导致复杂且难以预测的行为。

使用客户端代码管理触发器

你可以作为应用程序部署或迁移脚本的一部分，以编程方式创建、检查和删除触发器。

PHP
NodeJS
Java
Python



// 假设一个已连接的 $mysqli 对象
$triggerSql = "
    CREATE TRIGGER trg_customer_archive
    AFTER DELETE ON CUSTOMERS
    FOR EACH ROW
    BEGIN
        INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
        VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
    END
";

// 对于包含分号的语句使用 multi_query
if ($mysqli->multi_query($triggerSql)) {
    echo "Trigger created successfully.";
} else {
    echo "Error creating trigger: " . $mysqli->error;
}

// 不要忘记清除 multi_query 的结果
while ($mysqli->next_result()) {;}



// 假设一个来自 mysql2/promise 的已连接 'connection' 对象
async function createTrigger(connection) {
    const triggerSql = `
        CREATE TRIGGER trg_customer_archive
        AFTER DELETE ON CUSTOMERS
        FOR EACH ROW
        BEGIN
            INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
            VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
        END
    `;
    try {
        // 驱动程序正确处理多语句查询
        await connection.query(triggerSql);
        console.log('Trigger created successfully.');
    } catch (err) {
        console.error('Failed to create trigger:', err);
    }
}



// 假设一个已连接的 'conn' 对象 (java.sql.Connection)
String triggerSql = ""
    + "CREATE TRIGGER trg_customer_archive "
    + "AFTER DELETE ON CUSTOMERS "
    + "FOR EACH ROW "
    + "BEGIN "
    + "    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) "
    + "    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); "
    + "END";

try (Statement stmt = conn.createStatement()) {
    stmt.execute(triggerSql);
    System.out.println("Trigger created successfully.");
} catch (SQLException e) {
    e.printStackTrace();
}



# 假设一个来自 mysql-connector-python 的 'connection' 对象和 'cursor' 对象
# 使用多语句创建触发器可能很棘手。通常更容易
# 逐个运行或从脚本文件中运行它们。

trigger_definition = (""
    "CREATE TRIGGER trg_customer_archive "
    "AFTER DELETE ON CUSTOMERS "
    "FOR EACH ROW "
    "BEGIN "
    "    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) "
    "    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); "
    "END")

try:
    # 如果启用，MySQL 连接器可以处理多语句块
    # 但直接执行简单的 CREATE TRIGGER 语句即可。
    cursor.execute(trigger_definition)
    print("Trigger created successfully.")
except mysql.connector.Error as err:
    # 检查触发器是否已存在
    if err.errno == 1359:
         print("Trigger already exists.")
    else:
        print(f"Error: {err}")