MySQL - 创建触发器

MySQL - CREATE TRIGGER 现代指南

在现代数据库系统中，触发器（triggers）是强大的工具，能够自动化响应特定事件的操作。可以将它们理解为数据库表的“事件监听器”（event listeners）。当表上发生 INSERT、UPDATE 或 DELETE 等操作时，触发器可以自动执行一组预定义的 SQL 语句。这对于维护数据完整性、创建审计跟踪或强制执行无法通过简单约束处理的复杂业务规则来说是无价的。

MySQL 触发器是与表关联的存储程序。它们是自动调用的，这意味着您不需要在应用程序代码中直接调用它们。它们响应数据操作语言（DML）事件而运行：

• INSERT：向表中添加新行。
• UPDATE：修改现有行。
• DELETE：从表中删除行。

理解 CREATE TRIGGER 语法

要创建触发器，您需要使用 CREATE TRIGGER 语句。下面我们来详细解析其现代语法和组成部分。

语法

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name FOR EACH ROW
BEGIN
    -- 触发器逻辑在这里
END;

关键组成部分：

• trigger_name：在模式（schema）中为触发器指定的唯一名称。
• BEFORE | AFTER：触发器执行时机。BEFORE 触发器在事件操作执行之前触发，允许您修改即将插入/更新的数据。AFTER 触发器在操作完成后触发，适用于日志记录或级联操作。
• INSERT | UPDATE | DELETE：激活触发器的 DML 事件。
• table_name：触发器关联的表的名称。
• FOR EACH ROW：此子句指定触发器主体将为受 DML 语句影响的每一行执行。
• BEGIN…END：此代码块包含构成触发器逻辑的 SQL 语句。对于简单的单语句触发器，此代码块是可选的。

在触发器主体内部，您可以使用特殊别名 NEW 和 OLD 来访问受影响行的列值。NEW 指向新的行数据（在 INSERT 和 UPDATE 中），而 OLD 指向更改前的现有行数据（在 UPDATE 和 DELETE 中）。

实战示例：数据验证

我们来创建一个实用触发器。我们将有一个 products 表，并希望确保任何产品都不能以负价格插入或更新。

首先，定义一个现代的 products 表：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在，我们创建一个 BEFORE INSERT 触发器来验证价格。我们使用 BEFORE 触发器，以便在数据保存之前对其进行纠正。

-- 注意：DELIMITER 命令在 MySQL 命令行等客户端中使用
-- 将语句分隔符从 ; 更改为 //，以便正确解释触发器主体中的 ;。
DELIMITER //

CREATE TRIGGER before_product_insert
BEFORE INSERT ON products FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        -- 作为业务规则，这里我们将价格设为 0，而不是让操作失败。
        -- 在其他场景中，您可能希望发出错误信号。
        SET NEW.price = 0;
    END IF;
END//

DELIMITER ;

验证

我们尝试插入一个负价格的产品，来测试我们的触发器。

INSERT INTO products (name, price) VALUES
    ('Laptop', 1200.00),
    ('Mouse', -25.00), -- 这个值应该被触发器纠正
    ('Keyboard', 75.00);

现在，查询 products 表以查看结果：

SELECT * FROM products;

输出将显示触发器拦截了负值并进行了纠正：

id	name	price	created_at
1	Laptop	1200.00	YYYY-MM-DD HH:MM:SS
2	Mouse	0.00	YYYY-MM-DD HH:MM:SS
3	Keyboard	75.00	YYYY-MM-DD HH:MM:SS

通过应用程序代码创建触发器

虽然触发器通常由数据库管理员（DBA）通过迁移脚本管理，但有时您可能需要以编程方式创建它们。这里将介绍如何使用现代、安全的实践在各种语言中实现这一点。一个关键原则是使用环境变量存储凭据和健壮的错误处理。

NodeJS
Python
Java
PHP



此示例使用 `mysql2/promise` 和 `async/await` 来编写简洁、异步的代码。请使用 `.env` 文件存储凭据（例如，`DB_HOST=localhost`）。

// main.js
const mysql = require('mysql2/promise');
require('dotenv').config(); // 使用 'npm install dotenv'

const triggerSQL = `
CREATE TRIGGER before_product_insert
BEFORE INSERT ON products FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END
`;

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME
        });

        console.log('如果触发器存在，则删除...');
        await connection.execute('DROP TRIGGER IF EXISTS before_product_insert');

        console.log('正在创建新触发器...');
        await connection.execute(triggerSQL);

        console.log('触发器创建成功！');
    } catch (error) {
        console.error('发生错误：', error.message);
    } finally {
        if (connection) {
            await connection.end();
            console.log('连接已关闭。');
        }
    }
}

main();



此 Python 示例使用 `mysql-connector-python` 和上下文管理器（`with`）进行自动连接和游标管理，这是一种最佳实践。

# main.py
import mysql.connector
import os # 用于环境变量

# 最佳实践：从环境变量加载凭据
config = {
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'password'),
    'host': os.getenv('DB_HOST', '127.0.0.1'),
    'database': os.getenv('DB_NAME', 'test_db'),
}

trigger_sql = """
CREATE TRIGGER before_product_insert
BEFORE INSERT ON products FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END
"""

try:
    with mysql.connector.connect(**config) as connection:
        print("已连接到 MySQL 数据库...")
        with connection.cursor() as cursor:
            print("如果触发器存在，则删除...")
            cursor.execute("DROP TRIGGER IF EXISTS before_product_insert")

            print("正在创建新触发器...")
            cursor.execute(trigger_sql)

            connection.commit()
            print("触发器 'before_product_insert' 创建成功。")

except mysql.connector.Error as err:
    print(f"错误: {err}")

finally:
    print("脚本执行完毕。")



此 Java 示例使用现代的 try-with-resources 语句进行自动资源管理，并从属性文件或环境变量中加载凭据以确保安全。

// TriggerManager.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class TriggerManager {
    // 安全地加载凭据，例如，从环境变量或配置文件中
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        String triggerSQL = "CREATE TRIGGER before_product_insert " +
                            "BEFORE INSERT ON products FOR EACH ROW " +
                            "BEGIN " +
                            "    IF NEW.price < 0 THEN " +
                            "        SET NEW.price = 0; " +
                            "    END IF; " +
                            "END";

        // try-with-resources 确保连接和语句自动关闭
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {

            System.out.println("已连接到数据库。");

            System.out.println("如果触发器存在，则删除...");
            stmt.execute("DROP TRIGGER IF EXISTS before_product_insert");

            System.out.println("正在创建新触发器...");
            stmt.execute(triggerSQL);

            System.out.println("触发器创建成功！");

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



此 PHP 示例使用现代的 `mysqli` 面向对象接口，并包含适当的错误检查。

<?php
// config.php
// 最佳实践：使用环境变量将凭据存储在 Web 根目录之外。
define('DB_HOST', getenv('DB_HOST') ?: 'localhost');
define('DB_USER', getenv('DB_USER') ?: 'root');
define('DB_PASS', getenv('DB_PASSWORD') ?: 'password');
define('DB_NAME', getenv('DB_NAME') ?: 'test_db');

// main.php
require_once 'config.php';

// 使用面向对象风格进行错误报告
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    echo "连接成功！\n";

    $dropTriggerSQL = "DROP TRIGGER IF EXISTS before_product_insert";
    $createTriggerSQL = "
        CREATE TRIGGER before_product_insert
        BEFORE INSERT ON products FOR EACH ROW
        BEGIN
            IF NEW.price < 0 THEN
                SET NEW.price = 0;
            END IF;
        END;
    ";

    echo "正在删除现有触发器（如果存在）...\n";
    $mysqli->query($dropTriggerSQL);

    echo "正在创建新触发器...\n";
    $mysqli->query($createTriggerSQL);

    echo "触发器创建成功！\n";

} catch (mysqli_sql_exception $e) {
    // 捕获连接或查询错误
    error_log("MySQL 错误: " . $e->getMessage());
    die("数据库操作失败。请检查服务器日志。");
} finally {
    if (isset($mysqli)) {
        $mysqli->close();
        echo "连接已关闭。\n";
    }
}