MySQL 触发器

核心目标： 学习如何创建和使用触发器，以便在数据库表中的特定事件（INSERT, UPDATE, DELETE）发生时自动执行预定义的 SQL 语句。

什么是触发器？
触发器是与特定表相关联的命名数据库对象。当该表发生指定的 DML 事件（数据操作：插入、更新、删除）时，触发器会被自动激活并执行其包含的 SQL 代码。

触发器的用途：

• 数据验证和约束（比 CHECK 约束更复杂）。
• 数据审计和日志记录（记录数据变更历史）。
• 数据同步或衍生数据计算（如更新汇总表）。
• 强制执行复杂的业务规则。

触发器的构成要素：

1. 触发器名称 (Trigger Name): 唯一的名称，用于标识触发器。
2. 触发器时间 (Trigger Time): BEFORE 或 AFTER。
   • BEFORE: 在触发事件（INSERT, UPDATE, DELETE）实际执行 之前 触发。常用于数据验证或修改将要插入/更新的数据。
   • AFTER: 在触发事件实际执行 之后 触发。常用于审计日志、数据同步等。
3. 触发器事件 (Trigger Event): INSERT, UPDATE, 或 DELETE。指定哪种 DML 操作会激活触发器。
4. 目标表 (Table Name): 触发器所关联的表。
5. 触发器主体 (Trigger Body): 包含一个或多个 SQL 语句的 BEGIN ... END; 代码块。当触发器被激活时，这些语句将被执行。

特殊别名：NEW 和 OLD
在触发器主体内部，可以使用特殊的别名 NEW 和 OLD 来引用发生变化的行中的数据：

触发器类型	NEW 可用性	OLD 可用性	说明
INSERT	✅ 可用，表示将要插入的新行，可修改（BEFORE 中）	❌ 不可用	NEW 用于访问或修改即将插入的行
UPDATE	✅ 可用，表示更新后的新行，可修改（BEFORE 中）	✅ 可用，表示更新前的旧行，仅可读	可同时访问修改前后数据
DELETE	❌ 不可用	✅ 可用，表示将被删除的旧行，仅可读	仅能读取将被删除的数据

1. 创建触发器 (CREATE TRIGGER)

语法：

DELIMITER // -- 临时改变语句分隔符，以便在触发器主体中使用分号 ;
CREATE TRIGGER trigger_name
trigger_time trigger_event ON table_name
FOR EACH ROW -- 表示对受事件影响的每一行都执行触发器主体
trigger_body; -- 触发器主体，通常是 BEGIN...END 块
//
DELIMITER ; -- 恢复默认的分隔符

说明：

• DELIMITER // 和 DELIMITER ;: 因为触发器主体内部可能包含分号 ;，所以需要临时改变语句结束符，// 是常用的选择，最后再改回来。
• FOR EACH ROW: 这是 MySQL 触发器的标准，表示行级触发器，即事件影响多少行，触发器就执行多少次。

示例 1: 在插入新员工前检查工资是否有效 (BEFORE INSERT)

DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees -- 在 employees 表插入之前
FOR EACH ROW
BEGIN
    -- 检查工资是否小于 0
    IF NEW.salary < 0 THEN
        -- 如果无效，则抛出错误，阻止插入 (需要 MySQL 5.5+)
        SIGNAL SQLSTATE '45000' -- 自定义错误状态码
        SET MESSAGE_TEXT = 'Salary cannot be negative.';
        -- 对于旧版本 MySQL，可以尝试将值设为无效值或记录日志，但不能直接阻止
        -- SET NEW.salary = NULL; -- 例如，设为 NULL (如果列允许)
    END IF;
END;
//
DELIMITER ;

示例 2: 员工工资更新后记录日志 (AFTER UPDATE)

-- 假设有一个 employee_salary_log 表: log_id, emp_id, old_salary, new_salary, change_time
DELIMITER //
CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees -- 在 employees 表更新之后
FOR EACH ROW
BEGIN
    -- 检查工资列是否真的被更新了
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO employee_salary_log (emp_id, old_salary, new_salary, change_time)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;
END;
//
DELIMITER ;

示例 3: 删除订单时，自动更新产品库存 (AFTER DELETE)

-- 假设 orders 表有 order_id, product_id, quantity 列
-- 假设 products 表有 product_id, stock_quantity 列
DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders -- 在 orders 表删除之后
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock_quantity = stock_quantity + OLD.quantity -- 将删除订单的数量加回库存
    WHERE product_id = OLD.product_id;
END;
//
DELIMITER ;

2. 查看触发器 (SHOW TRIGGERS)

作用：显示数据库中存在的触发器信息。
语法：

-- 显示当前数据库的所有触发器
SHOW TRIGGERS;

-- 显示指定数据库的触发器
SHOW TRIGGERS FROM database_name;

-- 使用 LIKE 过滤触发器名称
SHOW TRIGGERS LIKE 'pattern%';

-- 查看特定表的触发器 (通过 information_schema)
SELECT * FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE = 'table_name';

示例:

SHOW TRIGGERS;
SHOW TRIGGERS FROM mydatabase;
SHOW TRIGGERS LIKE 'after_%';

3. 删除触发器 (DROP TRIGGER)

作用：永久删除一个触发器。
语法：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

说明：

• IF EXISTS: 可选，如果触发器不存在，则不会报错。
• schema_name.: 可选，指定触发器所在的数据库名称，如果省略则默认为当前数据库。

示例:

-- 删除名为 after_employee_salary_update 的触发器
DROP TRIGGER IF EXISTS after_employee_salary_update;

-- 删除指定数据库的触发器
DROP TRIGGER IF EXISTS mydatabase.before_employee_insert;

4. 注意点与最佳实践

• 性能影响: 触发器会在每次相关的 DML 操作时执行，复杂的触发器逻辑会增加操作的开销，可能影响性能。
• 调试困难: 触发器是隐式执行的，出现问题时调试可能比应用程序代码更困难。
• 循环触发: 小心设计，避免触发器导致的操作又反过来触发自身或其他触发器，形成无限循环。例如，在一个表的 UPDATE 触发器中又去 UPDATE 同一个表。
• 事务: 触发器在触发它的语句所在的事务中运行。如果触发器失败（如遇到错误），整个语句（以及触发器做的所有更改）通常会回滚。触发器内部不应包含事务控制语句（如 COMMIT, ROLLBACK）。
• 业务逻辑位置: 考虑将复杂的业务逻辑放在应用程序层而不是触发器中，这样更易于维护、测试和理解。触发器更适合用于强制数据完整性、审计等数据库层面的任务。
• 保持简单: 尽量让触发器的逻辑简单明了，专注于单一任务。
• 文档化: 记录触发器的目的和逻辑，方便他人理解和维护。

---

练习题

假设有一个 products 表（包含 product_id, product_name, stock_quantity 列）和一个 product_audit_log 表（包含 log_id INT AUTO_INCREMENT PK, product_id INT, old_stock INT, new_stock INT, change_time TIMESTAMP）。

1. 创建一个触发器 before_product_update，在更新 products 表的 stock_quantity 之前，检查新的库存量 (NEW.stock_quantity) 是否小于 0。如果小于 0，则阻止更新并报错 “Stock quantity cannot be negative”。
   答案:

   DELIMITER //
   CREATE TRIGGER before_product_update
   BEFORE UPDATE ON products
   FOR EACH ROW
   BEGIN
       IF NEW.stock_quantity < 0 THEN
           SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'Stock quantity cannot be negative.';
       END IF;
   END;
   //
   DELIMITER ;
   

2. 创建一个触发器 after_product_stock_update，在 products 表的 stock_quantity 被更新 之后，如果新旧库存量不同，则向 product_audit_log 表插入一条日志记录，包含产品 ID、旧库存、新库存和变更时间。
   答案:

   DELIMITER //
   CREATE TRIGGER after_product_stock_update
   AFTER UPDATE ON products
   FOR EACH ROW
   BEGIN
       IF OLD.stock_quantity <> NEW.stock_quantity THEN
           INSERT INTO product_audit_log (product_id, old_stock, new_stock, change_time)
           VALUES (OLD.product_id, OLD.stock_quantity, NEW.stock_quantity, NOW());
       END IF;
   END;
   //
   DELIMITER ;
   

3. 显示当前数据库中的所有触发器。
   答案:

   SHOW TRIGGERS;
   

4. 假设不再需要库存小于 0 的检查，删除触发器 before_product_update。
   答案:

   DROP TRIGGER IF EXISTS before_product_update;
   

---