MySQL - BEFORE UPDATE 触发器

使用 BEFORE UPDATE 触发器进行数据验证

在数据库系统中，维护数据完整性至关重要。虽然 CHECK 约束对于简单的规则很有用，但 BEFORE UPDATE 触发器（即“更新前”触发器）提供了一种强大的机制，可以在行被修改之前强制执行复杂的业务逻辑和验证。这确保了无效数据永远不会进入您的表。

MySQL BEFORE UPDATE 触发器

一个 BEFORE UPDATE 触发器是一种行级触发器，它在执行 UPDATE 操作之前，对每一行自动执行。它的主要优势在于能够检查甚至修改即将写入表的数据。

关键概念：OLD vs. NEW。在 UPDATE 触发器内部，您可以访问两个特殊的别名：OLD 指的是更新之前的行值，而 NEW 指的是提议的新值。例如，OLD.salary 是当前薪水，NEW.salary 是提议的新薪水。您可以读取两者，但只能修改 NEW 值。

语法

创建 BEFORE UPDATE 触发器的语法很简单：

CREATE TRIGGER trigger_name
    BEFORE UPDATE ON table_name
    FOR EACH ROW
BEGIN
    -- 触发器逻辑在此处。使用 OLD.column 和 NEW.column 访问数据。
    -- 要拒绝更新，请使用 SIGNAL 语句。
END;

示例：强制执行业务规则

让我们考虑一个实际场景。我们有一个 employees 表，并且我们希望强制执行一条业务规则：员工的薪水永远不能降低。BEFORE UPDATE 触发器是实现这一目标的完美工具。

步骤 1：创建 employees 表。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0)
);

步骤 2：插入一些初始数据。

INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Software Engineer', 75000.00),
('Jane Smith', 'Project Manager', 90000.00);

步骤 3：创建 BEFORE UPDATE 触发器以防止薪水降低。

DELIMITER //

CREATE TRIGGER prevent_salary_decrease
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    -- 将提议的新薪水与旧薪水进行比较
    IF NEW.salary < OLD.salary THEN
        -- 如果新薪水较低，则使用自定义错误拒绝更新
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'A salary decrease is not permitted.';
    END IF;
END//

DELIMITER ;

理解 SIGNAL：SIGNAL 语句是您有意地从触发器或存储过程中引发错误的方式。SQLSTATE '45000' 是“未处理的用户定义异常”的通用状态码，非常适合自定义业务规则违规。

测试触发器

现在，让我们用两个 UPDATE 语句来测试我们的触发器。

尝试 1：有效的薪水增加。这应该会成功。

UPDATE employees SET salary = 80000.00 WHERE name = 'John Doe';

结果：Query OK, 1 row affected.（查询成功，1 行受影响。）更新成功。

尝试 2：无效的薪水降低。这应该被触发器阻止。

UPDATE employees SET salary = 85000.00 WHERE name = 'Jane Smith';

结果：数据库返回错误，更新被取消。

ERROR 1644 (45000): A salary decrease is not permitted.

在应用程序代码中处理触发器错误

使用验证触发器的一个关键部分是确保您的应用程序可以优雅地处理它们产生的错误。您的应用程序不应崩溃，而应捕获数据库错误并向用户提供有意义的反馈。

错误处理的最佳实践

• 使用 Try-Catch 块：始终将数据库操作包装在 try-catch（或等效）块中。
• 检查错误：在 catch 块中，检查错误对象。您可以检查特定的 SQLSTATE (‘45000’) 或错误消息文本来识别您的自定义业务规则违规。
• 提供用户反馈：将技术性的数据库错误转换为用户友好的消息，例如“错误：您不能降低员工的薪水。”

Python
Node.js
Java
PHP



此 Python 示例展示了如何捕获 `DatabaseError` 并检查其属性以提供特定的反馈。

```python
import mysql.connector
import os

def update_employee_salary(name, new_salary):
    """尝试更新员工薪水并处理触发器错误。"""
    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='your_database'
        )
        cursor = connection.cursor()

        update_query = "UPDATE employees SET salary = %s WHERE name = %s"
        cursor.execute(update_query, (new_salary, name))
        connection.commit()
        print(f"Successfully updated salary for {name}.")

    except mysql.connector.Error as err:
        # 检查我们的自定义触发器错误
        if err.sqlstate == '45000':
            print(f"Business Rule Violation: {err.msg}")
        else:
            print(f"A database error occurred: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# --- 运行示例 ---
print("--- Attempting a valid update ---")
update_employee_salary('John Doe', 95000.00)

print("\n--- Attempting an invalid update ---")
update_employee_salary('Jane Smith', 80000.00)

此 Node.js 示例使用 try...catch 块中的 async/await 来干净地处理触发器产生的错误。

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

async function updateEmployeeSalary(name, newSalary) {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASSWORD || 'password',
            database: 'your_database'
        });

        const updateQuery = 'UPDATE employees SET salary = ? WHERE name = ?';
        await connection.execute(updateQuery, [newSalary, name]);
        console.log(`Successfully updated salary for ${name}.`);

    } catch (error) {
        // 检查我们的自定义触发器错误
        if (error.sqlState === '45000') {
            console.error(`Business Rule Violation: ${error.message}`);
        } else {
            console.error(`A database error occurred: ${error.message}`);
        }
    } finally {
        if (connection) await connection.end();
    }
}

// --- 运行示例 ---
(async () => {
    console.log('--- Attempting a valid update ---');
    await updateEmployeeSalary('John Doe', 96000.00);

    console.log('\n--- Attempting an invalid update ---');
    await updateEmployeeSalary('Jane Smith', 81000.00);
})();

此 Java 示例使用 try-catch 块并检查 SQLException 的 SQLState 来识别自定义错误。

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

public class UpdateTriggerExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void updateEmployeeSalary(String name, double newSalary) {
        String updateSQL = "UPDATE employees SET salary = ? WHERE name = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {

            pstmt.setDouble(1, newSalary);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
            System.out.println("Successfully updated salary for " + name);

        } catch (SQLException e) {
            // 检查我们的自定义触发器错误
            if ("45000".equals(e.getSQLState())) {
                System.err.println("Business Rule Violation: " + e.getMessage());
            } else {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    public static void main(String[] args) {
        System.out.println("--- Attempting a valid update ---");
        updateEmployeeSalary("John Doe", 97000.00);

        System.out.println("\n--- Attempting an invalid update ---");
        updateEmployeeSalary("Jane Smith", 82000.00);
    }
}

此现代 PHP 示例使用 mysqli 的面向对象风格，结合异常处理来捕获触发器的错误。

<?php
// 最佳实践：使用环境变量或安全的配置文件
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'your_database';

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

function updateEmployeeSalary(string $name, float $newSalary) {
    global $dbhost, $dbuser, $dbpass, $dbname;
    $mysqli = null;
    try {
        $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
        $stmt = $mysqli->prepare("UPDATE employees SET salary = ? WHERE name = ?");
        $stmt->bind_param("ds", $newSalary, $name);
        $stmt->execute();
        echo "Successfully updated salary for $name.\n";
    } catch (mysqli_sql_exception $e) {
        // 检查我们的自定义触发器错误
        if ($e->getCode() == 1644) { // 1644 是 SIGNAL 状态的错误号
            echo "Business Rule Violation: " . $e->getMessage() . "\n";
        } else {
            echo "Database Error: " . $e->getMessage() . "\n";
        }
    } finally {
        if ($mysqli) {
            $mysqli->close();
        }
    }
}

// --- 运行示例 ---
echo "--- Attempting a valid update ---\n";
updateEmployeeSalary('John Doe', 98000.00);

echo "\n--- Attempting an invalid update ---\n";
updateEmployeeSalary('Jane Smith', 83000.00);

?>