MySQL - AFTER UPDATE 触发器

MySQL：AFTER UPDATE 触发器

一个 AFTER UPDATE 触发器是一种特定类型的数据库触发器 (Database Trigger)，它在表上的 UPDATE 操作成功完成后自动执行。这对于记录更改、更新其他表中的聚合数据或在记录被修改后发送通知等任务非常有用。

请记住，MySQL 触发器是行级 (Row-level) 的，这意味着 AFTER UPDATE 触发器将为 UPDATE 语句成功更新的每一行触发一次。

AFTER UPDATE 触发器剖析

在 AFTER UPDATE 触发器中工作时，您可以访问受影响行的数据的旧值和新值。这是通过特殊关键字完成的：

• OLD.column_name：这表示更新发生之前列的值。
• NEW.column_name：这表示更新发生之后列的值。

这种双重访问使得 AFTER UPDATE 触发器在审计和比较任务中非常强大。

语法

CREATE TRIGGER trigger_name
    AFTER UPDATE ON table_name
    FOR EACH ROW
BEGIN
    -- 触发器逻辑在此处。您可以访问 OLD 和 NEW 值。
END;

示例：强制执行业务规则

虽然 BEFORE UPDATE 触发器通常用于验证，但 AFTER UPDATE 触发器可用于强制执行规则或对更改做出反应。让我们创建一个示例，该示例阻止用户年龄更新为负值。我们将使用 SIGNAL 语句向客户端返回自定义、清晰的错误消息。

首先，让我们创建一个 users 表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

插入一个示例用户：

INSERT INTO users (username, age) VALUES ('alex_jones', 35);

现在，创建 AFTER UPDATE 触发器。请注意，对于此特定验证，BEFORE UPDATE 触发器更高效，因为它可以完全阻止无效更新的发生。但是，我们在此处使用 AFTER UPDATE 来演示其语法和 SIGNAL 语句。

DELIMITER //

CREATE TRIGGER after_user_age_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN
        -- SQLSTATE '45000' 是一个通用的用户自定义错误代码。
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Validation Error: Age cannot be negative.';
    END IF;
END//

DELIMITER ;

让我们通过尝试一个无效的更新来测试触发器：

UPDATE users SET age = -5 WHERE username = 'alex_jones';

预期输出

数据库将拒绝更新并返回我们定义的自定义错误：

ERROR 1644 (45000): Validation Error: Age cannot be negative.

然而，一个有效的更新将成功执行，不会有任何错误：

UPDATE users SET age = 36 WHERE username = 'alex_jones';
-- 查询成功，1 行受影响

在客户端程序中处理触发器错误

一个编写良好的应用程序应该能够捕获由触发器引发的错误并优雅地处理它们。它不应该崩溃，而应该向用户显示有意义的消息。

语法概览

Node.js
Python
Java
PHP



Using `mysql2/promise` with a try...catch block to handle the error:

try {
    const [result] = await connection.execute(updateQuery);
} catch (error) {
    // 触发器产生的错误将在此处捕获。
    console.error(`Database Error: ${error.message}`);
}



Using `mysql-connector-python` and its specific error classes:

from mysql.connector import errors

try:
    cursor.execute(update_query)
    connection.commit()
except errors.DatabaseError as e:
    # 触发器的 SIGNAL 将引发 DatabaseError。
    print(f"Database Error: {e}")



Using JDBC with a try-catch block for `SQLException`:

try (Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(updateSql);
} catch (SQLException e) {
    // 如有需要，检查特定的 SQLSTATE。
    if ("45000".equals(e.getSQLState())) {
        System.err.println("Caught trigger validation error: " + e.getMessage());
    } else {
        e.printStackTrace();
    }
}



Using PDO and catching `PDOException`:

try {
    $pdo->exec($updateSql);
} catch (PDOException $e) {
    // 可以获取触发器的错误消息。
    echo "Database Error: " . $e->getMessage();
}

完整示例 (Node.js)

这个 Node.js 示例使用现代的 mysql2/promise 库来执行更新并正确捕获来自我们触发器的自定义错误。

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

// 最佳实践：使用环境变量存储凭据
const dbConfig = {
    host: 'localhost',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
};

async function attemptInvalidUpdate() {
    let connection;
    try {
        // 在实际应用中使用连接池
        connection = await mysql.createConnection(dbConfig);
        console.log('Connected to the database.');

        const invalidUpdateQuery = "UPDATE users SET age = -5 WHERE username = 'alex_jones';";
        console.log(`Attempting to execute: ${invalidUpdateQuery}`);

        await connection.execute(invalidUpdateQuery);

        console.log('Update was successful (this should not happen).');

    } catch (error) {
        // 捕获由触发器的 SIGNAL 抛出的错误
        if (error.sqlState === '45000') {
            console.error('Caught expected validation error from trigger:');
            console.error(`=> ${error.message}`);
        } else {
            console.error('An unexpected database error occurred:', error);
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

attemptInvalidUpdate();

/*
预期输出：

Connected to the database.
Attempting to execute: UPDATE users SET age = -5 WHERE username = 'alex_jones';
Caught expected validation error from trigger:
=> Validation Error: Age cannot be negative.
Connection closed.
*/