MySQL触发器机制详解与实战
触发器（Trigger）是数据库中一种自动执行的程序单元，当对表执行INSERT、UPDATE或DELETE操作时，数据库引擎会自动调用相应的触发器逻辑。这种机制使得业务规则能够在数据层得到强制执行，无需依赖应用程序代码。
触发器的核心价值
触发器在数据治理中扮演着关键角色，主要优势体现在：
- 数据完整性保障：在持久化之前执行校验逻辑，拦截不符合规则的数据
- 操作原子性：触发器执行失败时，整个事务回滚，确保数据一致性
- 审计追踪：自动记录数据变更历史，满足合规要求
- 级联计算：跨表同步更新，维护派生数据
- 指令重写：通过INSTEAD OF触发器替换原始操作逻辑
环境准备
首先建立演示所需的库表结构：
mysql> CREATE DATABASE demo_db;
Query OK, 1 row affected (0.01 sec)
mysql> USE demo_db;
Database changed
mysql> CREATE TABLE staff (
-> staff_id INT PRIMARY KEY AUTO_INCREMENT,
-> staff_name VARCHAR(30) NOT NULL,
-> department VARCHAR(20),
-> hire_date DATE
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE staff_archive (
-> archive_id INT PRIMARY KEY AUTO_INCREMENT,
-> ref_staff_id INT,
-> archive_content VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.03 sec)触发器语法结构
CREATE TRIGGER 触发器名称
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 目标表名
FOR EACH ROW
[触发器主体：单条SQL或BEGIN...END块]关键要素说明：
- 触发时机：BEFORE（操作前）或 AFTER（操作后）
- 监听事件：INSERT、UPDATE、DELETE三种数据操作
- 行级触发：FOR EACH ROW表示每行受影响记录都会激活触发器
创建触发器实例
以下示例实现：员工表新增记录后，自动向归档表写入一条日志。
mysql> DELIMITER //
mysql> CREATE TRIGGER trg_staff_insert_log
-> AFTER INSERT ON staff
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO staff_archive (ref_staff_id, archive_content)
-> VALUES (NEW.staff_id, CONCAT('新员工入职: ', NEW.staff_name));
-> END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;执行数据插入验证触发效果：
mysql> INSERT INTO staff (staff_name, department, hire_date)
-> VALUES ('李明', '研发部', '2024-03-15');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM staff;
+----------+------------+------------+------------+
| staff_id | staff_name | department | hire_date |
+----------+------------+------------+------------+
| 1 | 李明 | 研发部 | 2024-03-15 |
+----------+------------+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM staff_archive;
+------------+--------------+-------------------------+---------------------+
| archive_id | ref_staff_id | archive_content | created_at |
+------------+--------------+-------------------------+---------------------+
| 1 | 1 | 新员工入职: 李明 | 2024-03-15 09:30:22 |
+------------+--------------+-------------------------+---------------------+
1 row in set (0.00 sec)重要提示：触发器不能对自身所在的表进行递归操作，否则会触发错误码1442。
NEW与OLD伪记录
在触发器体内，可通过NEW和OLD引用正在处理的记录：
| 触发事件 | 可用引用 | 含义说明 |
|---|---|---|
| INSERT | NEW | 即将插入的新记录值 |
| UPDATE | NEW、OLD | NEW为更新后值，OLD为更新前值 |
| DELETE | OLD | 即将被删除的旧记录值 |
触发器信息查询
获取特定触发器的定义语句：
mysql> SHOW CREATE TRIGGER trg_staff_insert_log\G
*************************** 1. row ***************************
Trigger: trg_staff_insert_log
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,...
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER trg_staff_insert_log
AFTER INSERT ON staff
FOR EACH ROW
BEGIN
INSERT INTO staff_archive (ref_staff_id, archive_content)
VALUES (NEW.staff_id, CONCAT('新员工入职: ', NEW.staff_name));
END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
Created: 2024-03-15 09:28:17.45
1 row in set (0.00 sec)查看当前库所有触发器清单：
mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
Trigger: trg_staff_insert_log
Event: INSERT
Table: staff
Statement: BEGIN
INSERT INTO staff_archive (ref_staff_id, archive_content)
VALUES (NEW.staff_id, CONCAT('新员工入职: ', NEW.staff_name));
END
Timing: AFTER
Created: 2024-03-15 09:28:17.45
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,...
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.01 sec)移除触发器
mysql> DROP TRIGGER IF EXISTS trg_staff_insert_log;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TRIGGERS;
Empty set (0.00 sec)复杂场景：BEFORE触发器实现数据校验
利用BEFORE INSERT触发器在数据入库前进行拦截校验：
mysql> DELIMITER //
mysql> CREATE TRIGGER trg_validate_staff
-> BEFORE INSERT ON staff
-> FOR EACH ROW
-> BEGIN
-> IF NEW.hire_date > CURDATE() THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT = '错误：入职日期不能晚于今天';
-> END IF;
-> IF LENGTH(NEW.staff_name) < 2 THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT = '错误：员工姓名至少2个字符';
-> END IF;
-> END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;测试校验逻辑：
mysql> INSERT INTO staff (staff_name, department, hire_date)
-> VALUES ('王', '市场部', '2025-01-01');
ERROR 1644 (45000): 错误：员工姓名至少2个字符
mysql> INSERT INTO staff (staff_name, department, hire_date)
-> VALUES ('王强', '市场部', '2025-01-01');
ERROR 1644 (45000): 错误：入职日期不能晚于今天