MySQL - BEFORE DELETE 触发器

MySQL BEFORE DELETE 触发器：现代指南

在现代数据库管理中，触发器（trigger）是自动化操作的强大工具。触发器是一个命名的数据库对象，它与表关联，并在表上发生特定事件（如 INSERT、UPDATE 或 DELETE）时被激活。本教程重点介绍 BEFORE DELETE 触发器（即“删除前”触发器），它是审计、归档和强制执行复杂业务规则等任务的关键组成部分。

理解触发器：核心概念

触发器本质上是事件驱动的存储过程。它们自动运行，这意味着您无需直接从应用程序中调用它们。它们根据执行时机（事件 BEFORE 或 AFTER）和事件本身（INSERT、UPDATE、DELETE）进行分类。

• BEFORE 触发器：在数据修改（例如 DELETE）发生之前执行。它们非常适合在主操作提交之前验证数据或准备相关数据。
• AFTER 触发器：在数据修改完成之后执行。它们通常用于记录操作或根据更改更新其他表。

MySQL BEFORE DELETE 触发器

一个 BEFORE DELETE 触发器会针对表中即将被删除的每一行触发。在此触发器中，您可以使用 OLD 别名（alias）访问正在被删除行的数据。例如，OLD.column_name 指的是该行在被删除之前 column_name 列的值。这对于捕获数据的最终快照非常有用。

实际用例：归档。当用户从主 users 表中删除时，BEFORE DELETE 触发器可以自动将其信息复制到 archived_users 表中，确保数据不会永久丢失。这是维护历史记录和遵守合规性要求的常见模式。

语法

创建 BEFORE DELETE 触发器的基本语法如下：

CREATE TRIGGER trigger_name
    BEFORE DELETE ON table_name
    FOR EACH ROW
BEGIN
    -- 您的 SQL 语句在此处。
    -- 您可以使用 OLD 别名访问将被删除的行。
    -- 示例：INSERT INTO archive_table (id, data) VALUES (OLD.id, OLD.data);
END;

注意：在 SQL 客户端中，DELIMITER 命令通常用于将语句分隔符从 ; 更改为其他字符（如 //），这允许将可能包含分号的触发器主体解析为单个语句。

综合示例：归档已删除客户

让我们构建一个实际示例。我们将有一个用于活跃客户的 customers 表，以及一个用于存储已删除客户数据的 archived_customers 表。

步骤 1：创建表。请注意归档表中的 archived_at 列，用于标记删除的时间戳。

-- 活跃客户主表
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    signup_date DATE NOT NULL
);

-- 用于归档已删除客户的表
CREATE TABLE archived_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    signup_date DATE NOT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤 2：使用一些示例数据填充 customers 表。

INSERT INTO customers (name, email, signup_date) VALUES
('Alice Johnson', 'alice@example.com', '2023-01-15'),
('Bob Williams', 'bob@example.com', '2023-02-20'),
('Charlie Brown', 'charlie@example.com', '2023-03-10');

步骤 3：创建 BEFORE DELETE 触发器以处理归档过程。

DELIMITER //

CREATE TRIGGER archive_customer_before_delete
    BEFORE DELETE ON customers
    FOR EACH ROW
BEGIN
    -- 将被删除的记录插入到归档表中。
    INSERT INTO archived_customers (id, name, email, signup_date)
    VALUES (OLD.id, OLD.name, OLD.email, OLD.signup_date);
END//

DELIMITER ;

步骤 4：执行 DELETE 语句以测试触发器。

DELETE FROM customers WHERE email = 'bob@example.com';

验证

现在，让我们通过查询两个表来验证触发器是否按预期工作。

查询 customers 表显示 Bob 已被删除：

SELECT * FROM customers;

ID	姓名	邮箱	注册日期
1	Alice Johnson	alice@example.com	2023-01-15
3	Charlie Brown	charlie@example.com	2023-03-10

查询 archived_customers 表显示 Bob 的数据已成功归档：

SELECT id, name, email, signup_date FROM archived_customers;

ID	姓名	邮箱	注册日期
2	Bob Williams	bob@example.com	2023-02-20

从应用程序代码管理触发器

尽管触发器是在数据库中定义的，但您的应用程序代码才是触发这些事件的源头。触发器的执行对应用程序是透明的；您只需运行一个 DELETE 语句，数据库就会处理其余部分。下面是使用各种语言执行 DELETE 语句的现代且健壮的示例，前提是我们在上面定义的触发器已存在于数据库中。

客户端代码的最佳实践

• 使用预处理语句（Prepared Statements）：始终使用参数化查询来防止 SQL 注入漏洞。
• 连接管理：确保数据库连接安全打开并可靠关闭，通常使用 try-catch-finally 或语言特定的结构（如 try-with-resources）。
• 配置管理：将数据库凭据存储在环境变量或安全的配置文件中，而不是源代码中。

Python
Node.js
Java
PHP



此 Python 示例使用 `mysql-connector-python` 库，并演示了使用环境变量进行凭据管理和适当错误处理等最佳实践。

```python
import mysql.connector
import os

def delete_customer_by_email(email):
    """删除客户并依赖触发器进行归档。"""
    connection = None
    try:
        # 最佳实践：使用环境变量进行配置
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=os.getenv('DB_NAME', 'TUTORIALS')
        )
        cursor = connection.cursor()

        # 使用预处理语句防止 SQL 注入
        delete_query = "DELETE FROM customers WHERE email = %s"

        cursor.execute(delete_query, (email,))
        connection.commit() # 重要提示：提交事务

        if cursor.rowcount > 0:
            print(f"Successfully deleted customer with email: {email}")
            print("The BEFORE DELETE trigger should have archived the record.")
        else:
            print(f"No customer found with email: {email}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed.")

# --- 运行此示例 ---
# 1. 确保 'customers' 表和触发器存在。
# 2. 运行函数：
delete_customer_by_email('charlie@example.com')

此 Node.js 示例使用现代的 mysql2/promise 库，结合 async/await 以实现更简洁、更具可读性的异步代码。

const mysql = require('mysql2/promise');

// 最佳实践：使用环境变量进行配置
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    database: process.env.DB_NAME || 'TUTORIALS'
};

async function deleteCustomerByEmail(email) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);

        // 使用预处理语句防止 SQL 注入
        const deleteQuery = 'DELETE FROM customers WHERE email = ?';

        const [result] = await connection.execute(deleteQuery, [email]);

        if (result.affectedRows > 0) {
            console.log(`Successfully deleted customer with email: ${email}`);
            console.log('The BEFORE DELETE trigger should have archived the record.');
        } else {
            console.log(`No customer found with email: ${email}`);
        }

    } catch (error) {
        console.error(`Database error: ${error.message}`);
    } finally {
        if (connection) {
            await connection.end();
            console.log('MySQL connection closed.');
        }
    }
}

// --- 运行此示例 ---
// 1. 运行 `npm install mysql2`
// 2. 确保 'customers' 表和触发器存在。
// 3. 运行函数：
deleteCustomerByEmail('alice@example.com');

此 Java 示例采用现代 JDBC 实践，包括使用 try-with-resources 进行自动连接管理，以及 PreparedStatement 以确保安全性。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TriggerExample {

    // 最佳实践：使用属性文件或环境变量存储凭据
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void deleteCustomerByEmail(String email) {
        String deleteSQL = "DELETE FROM customers WHERE email = ?";

        // try-with-resources 确保连接和语句被关闭
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {

            pstmt.setString(1, email);
            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                System.out.println("Successfully deleted customer with email: " + email);
                System.out.println("The BEFORE DELETE trigger should have archived the record.");
            } else {
                System.out.println("No customer found with email: " + email);
            }

        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // 假设 'customers' 表和触发器存在。
        // 注意：您需要在类路径中包含 MySQL JDBC 驱动程序。
        deleteCustomerByEmail("alice@example.com");
    }
}

此 PHP 示例使用 mysqli 扩展，采用面向对象风格、预处理语句和适当的错误处理。

<?php
// 最佳实践：使用环境变量或安全的配置文件
$dbhost = getenv('DB_HOST') ?: 'localhost';
$dbuser = getenv('DB_USER') ?: 'root';
$dbpass = getenv('DB_PASSWORD') ?: 'password';
$dbname = getenv('DB_NAME') ?: 'TUTORIALS';

// 启用 mysqli 抛出异常以处理错误
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

    $emailToDelete = 'alice@example.com';

    // 使用预处理语句防止 SQL 注入
    $stmt = $mysqli->prepare("DELETE FROM customers WHERE email = ?");
    $stmt->bind_param("s", $emailToDelete);
    $stmt->execute();

    if ($stmt->affected_rows > 0) {
        echo "Successfully deleted customer with email: $emailToDelete\n";
        echo "The BEFORE DELETE trigger should have archived the record.\n";
    } else {
        echo "No customer found with email: $emailToDelete\n";
    }

    $stmt->close();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Database Error: " . $e->getMessage() . "\n";
    exit();
}

?>