触发器

导学

触发器（Trigger）是数据库的"自动反应系统"——当表上发生 INSERT、UPDATE 或 DELETE 时，MySQL 会自动执行你预定义的代码。它适合审计日志、数据同步、约束检查等场景。

定义

触发器（Trigger）：与表关联的数据库对象，当表上发生特定 DML 事件（INSERT、UPDATE、DELETE）时，自动执行预定义的 SQL 语句。

核心语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发逻辑
END;

关键字	说明
`BEFORE`	在原始操作执行前触发，可修改 `NEW` 值或阻止操作
`AFTER`	在原始操作成功后触发，常用于日志和联动更新
`INSERT`	新数据插入时触发，`NEW` 可用，`OLD` 为 NULL
`UPDATE`	数据更新时触发，`NEW` 和 `OLD` 均可用
`DELETE`	数据删除时触发，`OLD` 可用，`NEW` 为 NULL
`FOR EACH ROW`	行级触发器，MySQL 5.7 只支持行级触发

伪记录 NEW 和 OLD

触发事件	NEW	OLD
`INSERT`	新插入的行	不可用（NULL）
`UPDATE`	更新后的新值	更新前的旧值
`DELETE`	不可用（NULL）	被删除的行

NEW.列名 访问新值，OLD.列名 访问旧值。只能在触发器体内使用。

SQL 示例

场景一：INSERT 后记录日志（AFTER INSERT）

第一步：建立测试环境

-- 创建员工表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

-- 创建审计日志表
CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    action VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

当前数据状态（employees 表）：

SELECT * FROM employees;

结果：空表

当前数据状态（logs 表）：

SELECT * FROM logs;

结果：空表

第二步：创建 AFTER INSERT 触发器

DELIMITER //

CREATE TRIGGER trg_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO logs (emp_id, action)
    VALUES (NEW.emp_id, 'INSERT');
END //

DELIMITER ;

第三步：执行 INSERT 操作

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

第四步：查看触发后的数据状态

SELECT * FROM employees;

操作后的数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

SELECT * FROM logs;

操作后的数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00

结果解读：

向 employees 表插入 2 条记录后，触发器 trg_employee_insert 自动执行了 2 次（行级触发器）。
logs 表自动生成了 2 条审计日志，记录了新增员工的 ID。
NEW.emp_id 引用了插入的新数据主键。
这种机制无需应用层干预，数据库层面自动完成审计记录。

场景二：UPDATE 前检查数据（BEFORE UPDATE）

第一步：建立测试环境

基于前面已建立的 employees 表：

当前数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

第二步：创建 BEFORE UPDATE 触发器

DELIMITER //

CREATE TRIGGER trg_score_check
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '成绩必须在 0-100 之间';
    END IF;
END //

DELIMITER ;

第三步：尝试合法 UPDATE 操作

UPDATE employees SET score = 95.00 WHERE emp_id = 1;

操作后的数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	技术部	95.00
2	白歌	技术部	NULL

结果解读：

emp_id = 1 的 score 从 100 成功更新为 95.00，更新操作正常完成。

第四步：尝试非法 UPDATE 操作

UPDATE employees SET score = 150.00 WHERE emp_id = 1;

执行结果：

ERROR 1644 (45000): 成绩必须在 0-100 之间

操作后的数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	技术部	95.00
2	白歌	技术部	NULL

结果解读：

尝试将 emp_id = 1 的 score 更新为 150.00 时，触发器 trg_score_check 在 UPDATE 执行前拦截了该操作。
SIGNAL SQLSTATE '45000' 抛出错误，整个 UPDATE 语句被回滚，emp_id = 1 的 score 仍然是 95.00，未发生任何变化。
BEFORE 触发器的核心价值：在数据真正写入前进行校验，不通过则阻止原始操作。

场景三：UPDATE 后记录变更（AFTER UPDATE）

第一步：建立测试环境

基于前面已建立的 employees 表和 logs 表。

当前数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	技术部	95.00
2	白歌	技术部	NULL

当前数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00

第二步：创建 AFTER UPDATE 触发器

DELIMITER //

CREATE TRIGGER trg_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.dept <> NEW.dept THEN
        INSERT INTO logs (emp_id, action)
        VALUES (NEW.emp_id, 'DEPT_CHANGED');
    END IF;

    IF OLD.score <> NEW.score OR (OLD.score IS NULL) <> (NEW.score IS NULL) THEN
        INSERT INTO logs (emp_id, action)
        VALUES (NEW.emp_id, 'SCORE_CHANGED');
    END IF;
END //

DELIMITER ;

第三步：执行 UPDATE 操作（变更部门）

UPDATE employees SET dept = '产品部' WHERE emp_id = 1;

操作后的数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	产品部	95.00
2	白歌	技术部	NULL

SELECT * FROM logs;

操作后的数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00
3	1	DEPT_CHANGED	2026-06-10 15:02:00

第四步：执行 UPDATE 操作（变更成绩）

UPDATE employees SET score = 88 WHERE emp_id = 2;

操作后的数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	产品部	95.00
2	白歌	技术部	88

SELECT * FROM logs;

操作后的数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00
3	1	DEPT_CHANGED	2026-06-10 15:02:00
4	2	SCORE_CHANGED	2026-06-10 15:03:00

结果解读：

emp_id = 1 的部门变更触发了 DEPT_CHANGED 日志，旧值和新值分别通过 OLD.dept 和 NEW.dept 获取。
emp_id = 2 的成绩变更触发了 SCORE_CHANGED 日志。
触发器内部使用条件判断特定字段是否真的发生了变化，避免无意义的日志记录。
AFTER UPDATE 触发器在原操作成功提交后执行，适合记录历史变更轨迹。

场景四：DELETE 后备份数据（AFTER DELETE）

第一步：建立测试环境

-- 创建员工历史表（删除备份）
CREATE TABLE employees_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

当前数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	产品部	95.00
2	白歌	技术部	88

当前数据状态（employees_history 表）：

SELECT * FROM employees_history;

结果：空表

第二步：创建 AFTER DELETE 触发器

DELIMITER //

CREATE TRIGGER trg_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_history (emp_id, emp_name, dept, score)
    VALUES (OLD.emp_id, OLD.emp_name, OLD.dept, OLD.score);
END //

DELIMITER ;

第三步：执行 DELETE 操作

DELETE FROM employees WHERE emp_id = 2;

操作后的数据状态（employees 表）：

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	产品部	95.00

SELECT * FROM employees_history;

操作后的数据状态（employees_history 表）：

history_id	emp_id	emp_name	dept	score	deleted_at
1	2	白歌	技术部	88	2026-06-10 15:05:00

结果解读：

emp_id = 2 的记录从 employees 表中删除。
触发器 trg_employee_delete 自动将删除前的完整数据备份到 employees_history 表。
OLD.emp_id、OLD.emp_name 等引用了被删除行的旧值。DELETE 操作中 NEW 不可用。
这种"软删除备份"机制可以在误删后恢复数据，也可用于审计追踪。

场景五：INSERT 前自动填充数据（BEFORE INSERT）

第一步：建立测试环境

基于前面已建立的 employees 表（当前只剩 1 条记录）。

当前数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	产品部	95.00

第二步：创建 BEFORE INSERT 触发器

DELIMITER //

CREATE TRIGGER trg_employee_insert_before
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- 如果部门为空，默认设为 '技术部'
    IF NEW.dept IS NULL OR NEW.dept = '' THEN
        SET NEW.dept = '技术部';
    END IF;

    -- 如果成绩为空，默认设为 0
    IF NEW.score IS NULL THEN
        SET NEW.score = 0.00;
    END IF;
END //

DELIMITER ;

第三步：执行 INSERT 操作（部分字段为 NULL）

INSERT INTO employees (emp_name, dept, score) VALUES
('小崔', NULL, NULL);

操作后的数据状态（employees 表）：

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	产品部	95.00
2	小崔	技术部	0.00

结果解读：

emp_id = 2：dept 为 NULL，触发器将其自动修改为 技术部。
emp_id = 2：score 为 NULL，触发器将其自动修改为 0.00。
BEFORE INSERT 触发器可以在数据真正插入前修改 NEW 的值，实现默认值填充、数据格式化等功能。

场景六：触发器嵌套联动（UPDATE 触发另一个表的 INSERT）

第一步：建立测试环境

基于前面已建立的 employees 表和 logs 表。

当前数据状态（employees 表）：

emp_id	emp_name	dept	score
1	大翔	产品部	95.00
2	小崔	技术部	0.00

当前数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00
3	1	DEPT_CHANGED	2026-06-10 15:02:00
4	2	SCORE_CHANGED	2026-06-10 15:03:00

第二步：创建 AFTER UPDATE 触发器

DELIMITER //

CREATE TRIGGER trg_score_alert
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 成绩低于 60 时记录低分预警（且之前不低于 60 或为 NULL）
    IF NEW.score < 60 AND (OLD.score >= 60 OR OLD.score IS NULL) THEN
        INSERT INTO logs (emp_id, action)
        VALUES (NEW.emp_id, 'LOW_SCORE');
    END IF;
END //

DELIMITER ;

第三步：执行 UPDATE 操作（成绩变化）

-- 大翔成绩从 95 降到 50，触发低分预警
UPDATE employees SET score = 50 WHERE emp_id = 1;

操作后的数据状态（employees 表）：

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	产品部	50
2	小崔	技术部	0.00

SELECT * FROM logs;

操作后的数据状态（logs 表）：

log_id	emp_id	action	created_at
1	1	INSERT	2026-06-10 15:00:00
2	2	INSERT	2026-06-10 15:00:00
3	1	DEPT_CHANGED	2026-06-10 15:02:00
4	2	SCORE_CHANGED	2026-06-10 15:03:00
5	1	LOW_SCORE	2026-06-10 15:15:00

结果解读：

emp_id = 1（大翔）：成绩从 95 降到 50，跨越了 "低于 60" 的阈值，触发了 LOW_SCORE 预警。
emp_id = 2（小崔）：成绩已经是 0，再次更新到 0 不会触发预警（因为旧值也低于 60）。
触发器通过比较 OLD.score 和 NEW.score 的状态变化，避免了重复预警。

场景七：查看和删除触发器

查看当前数据库所有触发器：

SHOW TRIGGERS;

结果示例：

Trigger	Event	Table	Statement	Timing	Created
trg_employee_insert	INSERT	employees	BEGIN...	AFTER	2026-06-10 15:00:00
trg_score_check	UPDATE	employees	BEGIN...	BEFORE	2026-06-10 15:00:00
trg_employee_update	UPDATE	employees	BEGIN...	AFTER	2026-06-10 15:02:00
trg_employee_delete	DELETE	employees	BEGIN...	AFTER	2026-06-10 15:05:00
trg_employee_insert_before	INSERT	employees	BEGIN...	BEFORE	2026-06-10 15:10:00
trg_score_alert	UPDATE	employees	BEGIN...	AFTER	2026-06-10 15:12:00

查看特定触发器的创建语句：

SHOW CREATE TRIGGER trg_employee_insert;

结果示例：

Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection
trg_employee_insert	...	CREATE TRIGGER `trg_employee_insert` ...	utf8mb4	utf8mb4_general_ci

从 information_schema 查看触发器详情：

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING,
    ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

删除触发器：

DROP TRIGGER IF EXISTS trg_employee_insert;

确认删除后：

SHOW TRIGGERS WHERE `Trigger` = 'trg_employee_insert';

结果：无记录返回，触发器已删除。

结果解读：

SHOW TRIGGERS 列出当前数据库中所有的触发器基本信息。
SHOW CREATE TRIGGER 可以查看触发器的完整创建语句，便于备份或迁移。
information_schema.TRIGGERS 提供了更结构化的查询方式，适合在程序中动态获取触发器信息。
DROP TRIGGER IF EXISTS 安全删除触发器，IF EXISTS 避免触发器不存在时报错。
删除触发器后，原表上的 DML 操作不再自动触发额外逻辑。

触发器的限制

限制	说明
同表同事件同时机只能有一个触发器	MySQL 5.7 中，一张表同一事件（如 BEFORE INSERT）只能有一个触发器
触发器中不能对本表做相同操作	AFTER INSERT 触发器中不能对同表执行 INSERT，会导致递归
不能显式调用	触发器由数据库自动触发，不能 `CALL`
错误处理复杂	触发器中的错误可能导致原始操作回滚

常见误区

误区	正解
触发器可以替代应用层逻辑	触发器隐藏了业务逻辑，调试困难。简单审计和约束可用，复杂逻辑应放在应用层。
触发器执行失败不影响原操作	`BEFORE` 触发器中抛出错误会阻止原操作；`AFTER` 触发器失败也可能导致回滚。
一张表可以有无限个触发器	MySQL 5.7 中，同一事件同一时机只能有一个触发器。

面试考点

Q：BEFORE 和 AFTER 触发器的区别？

BEFORE 在原操作之前执行，可以修改 NEW 值或阻止操作（抛异常）；AFTER 在原操作成功后执行，不能修改原操作的数据，常用于日志记录和联动更新。

Q：触发器和存储过程的区别？

存储过程需要显式 CALL 调用；触发器由数据库在特定事件发生时自动触发，不能手动调用。

Q：触发器有什么缺点？

隐藏逻辑，难以调试和追踪；2. 增加数据库负担；3. 可能引发级联触发（Trigger Cascade）；4. 主从复制中可能引发问题；5. 代码版本控制困难。

小结

触发器在 INSERT/UPDATE/DELETE 时自动执行
BEFORE 用于校验和修改数据，AFTER 用于日志和联动
使用 NEW 和 OLD 访问新值和旧值
一张表同一事件同一时机只能有一个触发器
触发器适合简单场景，复杂逻辑建议放在应用层