mysql菜鸟教程
16.1 触发器概念与应用场景
一、什么是触发器？
触发器（Trigger） 是一种与表相关联的数据库对象，它会在表上发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行一段预先定义好的 SQL 语句或语句块。触发器不能像存储过程那样被显式调用，而是由数据库在事件发生时自动触发。
可以把触发器想象成数据库的“守卫”或“记录员”——当数据发生变化时，它自动执行一些额外的操作，而无需应用程序干预。
二、为什么需要触发器？
触发器的主要作用是在数据库层面实现复杂的业务规则、保证数据完整性以及自动化数据维护。具体来说，它可以：
自动完成数据校验：在插入或更新数据前检查数据的合法性，如果不符合条件则阻止操作。
记录数据变更日志：自动将数据的修改历史记录到日志表中，用于审计或追溯。
实现级联更新：当主表数据变化时，自动更新相关联的从表数据（比外键约束更灵活）。
维护派生数据：例如，当订单明细变化时，自动更新订单的总金额。
提供额外的安全性：限制某些操作，或者在不修改应用程序的情况下添加数据逻辑。
三、触发器的类型
根据触发时间和触发事件的不同，触发器可以分为多种类型：
1. 按触发时间分类
BEFORE 触发器：在事件发生之前执行。通常用于数据验证、修改即将插入或更新的值。
AFTER 触发器：在事件发生之后执行。通常用于记录日志、更新其他表等后续操作。
2. 按触发事件分类
INSERT 触发器：当向表中插入数据时触发。
UPDATE 触发器：当修改表中的数据时触发。
DELETE 触发器：当从表中删除数据时触发。
组合起来，共有 6 种触发器：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
四、触发器的基本语法
在 MySQL 中，创建触发器的语法如下：
CREATE TRIGGER 触发器名 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 FOR EACH ROW 触发器体;
触发器名：应具有描述性，通常包含表名、事件和时间，例如 before_insert_students。
BEFORE/AFTER：指定触发时间。
INSERT/UPDATE/DELETE：指定触发事件。
表名：触发器所依附的表。
FOR EACH ROW：表示触发器将对每一行受到影响的操作执行一次（而非整个语句一次）。
触发器体：可以包含一个或多个 SQL 语句，如果是多条语句，需用 BEGIN ... END 包裹，并注意修改分隔符。
在触发器体中，可以使用两个特殊的别名来访问正在被修改的行数据：
NEW：对于 INSERT 和 UPDATE 操作，NEW 代表新插入或更新后的行。可以读取和修改（仅 BEFORE 触发器可修改）NEW 的列值。
OLD：对于 UPDATE 和 DELETE 操作，OLD 代表修改前或删除前的行。只能读取，不能修改。
五、第一个触发器示例
假设我们有一个 students 表，我们希望每当插入新学生时，自动记录一条日志到 student_log 表中。
首先创建日志表：
CREATE TABLE student_log ( log_id INT PRIMARY KEY AUTO_INCREMENT, action VARCHAR(50), student_id INT, log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
然后创建触发器：
DELIMITER //
CREATE TRIGGER after_insert_student
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO student_log (action, student_id) VALUES ('INSERT', NEW.id);
END //
DELIMITER ;现在，当我们向 students 表插入一条记录时：
INSERT INTO students (name, age) VALUES ('张三', 20);触发器会自动执行，向 student_log 表中插入一条日志记录。
五、应用场景详解
1. 数据审计（记录变更历史）
通过触发器自动记录数据的变更，可以轻松实现审计功能。例如，为 employees 表创建三个触发器，分别记录插入、更新、删除操作到 employees_audit 表。
-- 审计表结构 CREATE TABLE employees_audit ( audit_id INT PRIMARY KEY AUTO_INCREMENT, emp_id INT, action VARCHAR(10), old_name VARCHAR(100), new_name VARCHAR(100), change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- AFTER UPDATE 触发器 DELIMITER // CREATE TRIGGER after_update_employees AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit (emp_id, action, old_name, new_name) VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name); END // DELIMITER ;
2. 数据验证与约束
虽然 MySQL 支持 CHECK 约束，但在某些复杂验证场景下，可以使用 BEFORE INSERT/UPDATE 触发器。例如，确保学生年龄在合理范围内：
DELIMITER // CREATE TRIGGER before_insert_students BEFORE INSERT ON students FOR EACH ROW BEGIN IF NEW.age < 0 OR NEW.age > 150 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到150之间'; END IF; END // DELIMITER ;
3. 自动计算派生列
当某列的值可以通过其他列计算得出时，可以使用触发器自动维护。例如，订单表中总价 = 单价 × 数量，可以在 INSERT 和 UPDATE 时自动计算：
DELIMITER // CREATE TRIGGER before_insert_order_items BEFORE INSERT ON order_items FOR EACH ROW BEGIN SET NEW.total_price = NEW.quantity * NEW.unit_price; END // DELIMITER ;
4. 级联操作（超越外键）
外键约束只能提供简单的级联删除或更新，但触发器可以实现更复杂的级联逻辑。例如，删除一个用户时，不仅删除其订单，还要记录一条日志：
DELIMITER //
CREATE TRIGGER after_delete_users
AFTER DELETE ON users
FOR EACH ROW
BEGIN
DELETE FROM orders WHERE user_id = OLD.id;
INSERT INTO delete_log (table_name, record_id) VALUES ('users', OLD.id);
END //
DELIMITER ;5. 数据同步
当某表数据变化时，自动更新另一个数据库中的表（通过跨库操作或使用 FEDERATED 引擎），或更新缓存表。
六、使用触发器的注意事项
性能影响：触发器是在操作表时自动执行的额外代码，会增加 DML 语句的开销。对于频繁操作的表，过多的触发器可能成为性能瓶颈。
递归触发：如果一个触发器的操作又触发了同一个表或其他表的触发器，可能形成无限递归。MySQL 默认允许最大递归深度为 0（即不允许递归），但可以通过 max_sp_recursion_depth 参数控制。
触发器不能返回结果集：不能在触发器中使用 SELECT ... INTO 返回结果集（但可以使用 SELECT ... INTO 变量 赋值）。
事务控制：在 MySQL 中，触发器是事务的一部分，触发器内的操作会随着外层事务一起提交或回滚。但触发器内不能使用 START TRANSACTION、COMMIT、ROLLBACK 等事务控制语句。
可见性与调试：触发器是隐式执行的，可能会给调试带来困难。应确保触发器逻辑正确，并记录足够的日志。
权限要求：创建触发器需要 TRIGGER 权限。
版本限制：MySQL 5.0.2 开始支持触发器。
七、小结
触发器 是一种特殊的存储程序，它会在表发生 INSERT、UPDATE、DELETE 事件时自动执行。
分为 BEFORE 和 AFTER 两种触发时间，以及三种触发事件，组合出六种触发器。
主要应用场景包括：审计、验证、自动计算、级联操作、数据同步等。
使用触发器时需注意性能、递归、事务等限制。
触发器是数据库自动化的重要工具，合理使用可以让数据层更加智能和可靠。下一节我们将学习如何实际创建和管理触发器，并通过更多示例加深理解。
发表评论
所有评论