首页 › MySQL › 第 06 章 › 讲义 › 4. 触发器：自动执行的特殊程序

4. 触发器：自动执行的特殊程序

触发器（Trigger）是绑定在表上的特殊存储程序，当表发生 INSERT/UPDATE/DELETE 时自动激活，无需手动调用。

四要素：

  • ① 触发时机（BEFORE / AFTER）——决定触发器在事件发生之前还是发生之后执行：BEFORE 可提前拦截或修改数据，AFTER 适合事后记录。
  • ② 触发事件（INSERT / UPDATE / DELETE）——指定由哪种数据操作激活触发器，三者选其一。
  • ③ 触发对象（表）——触发器绑定在哪张表上（ON 表名），只能针对某一张具体的表。
  • ④ 行级（FOR EACH ROW）——表示受影响的每一行都会执行一次触发逻辑，而不是整条语句只执行一次。

标准语法（注意 DELIMITER 改结束符）：

sql
DELIMITER //
CREATE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW
BEGIN
  -- 触发逻辑，可用 NEW / OLD
END //
DELIMITER ;
⚠️ BEFORE 可在数据写入前修改 NEW 值甚至拦截（用 SIGNAL 报错）；AFTER 适合写日志、更新统计。严禁在触发器里对本表再 INSERT/UPDATE/DELETE，会造成循环触发！

4.1 使用限制

  • 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
  • 不能对本表执行 INSERT / UPDATE / DELETE，避免循环触发。
  • 触发器执行失败，主 SQL 也会回滚。

4.2 BEFORE 与 AFTER 选择判断指南

一、核心本质区别

1. BEFORE：在 insert / update / delete 执行之前触发

  • 数据还未写入 / 更新 / 删除到表中
  • 可以修改 NEW 的值（即将要写入的数据）
  • 可以拦截、终止当前操作

2. AFTER：在 insert / update / delete 执行之后触发

  • 数据已经真实写入 / 更新 / 删除
  • 不能修改 NEW 值，修改无效
  • 适合做日志、统计更新、关联表同步

二、通用判断规则

  1. 需要校验数据、修改即将写入的值、拦截非法操作 → 用 BEFORE
  2. 需要根据最新数据做统计、写日志、更新其他表 → 用 AFTER
  3. 涉及人数、平均值、总数等统计计算 → 必须用 AFTER

三、按操作类型细分

操作类型BEFORE（操作前）AFTER（操作后）
INSERT 数据校验（年龄不能为负、格式检查）
自动填充字段（默认值、创建时间）
拦截非法插入		 记录插入日志
更新关联表统计（部门人数、平均年龄）
UPDATE 校验修改是否合法
修改即将更新的字段值（格式化、修正）		 记录修改日志
更新关联统计数据
DELETE 校验是否允许删除
备份删除前的数据到历史表		 更新关联表统计数据

四、SIGNAL SQLSTATE的语法

在 MySQL 中，SIGNAL SQLSTATE 用于在存储过程、函数或触发器中抛出自定义错误。它允许你定义特定的错误代码和错误信息，从而更好地处理和管理异常情况。以下是其详细语法及介绍：

基本语法：

SIGNAL SQLSTATE 'sqlstate_value'   SET MESSAGE_TEXT = 'message';

语法要素解释

1、SQLSTATE 'sqlstate_value'：SQLSTATE 是一个标准的 5 字符代码，用于标识 SQL 错误类型。不同的数据库系统遵循这个标准，尽管具体的错误代码可能有所不同。例如，'45000' 常用于表示用户定义的异常。在存储过程、函数或触发器中，当你想要抛出自定义的错误时，可以使用这个 SQLSTATE 值，并通过 SET MESSAGE_TEXT 来设置具体的错误信息。你可以选择 MySQL 预定义的 SQLSTATE 值，也可以自定义一个值，但建议遵循标准规范以确保可移植性和一致性。

2、SET MESSAGE_TEXT ='message'：这部分用于设置与该错误关联的详细错误信息。message 是一个字符串，可以包含任何有助于诊断问题的文本。例如，SET MESSAGE_TEXT = '操作错误'。

📝 本节例题

以下例题均基于 教学实例 库（student / course / sc / teacher / grades / dept / emp），开始前先执行 USE 教学实例;。建议先自己写一遍，再展开答案对照。

例 1
创建 BEFORE INSERT 触发器：往 emp 插入员工时，若薪资为空则自动填 3000。
查看参考答案（可复制）
sql
DROP TRIGGER IF EXISTS trg_emp_salary;
DELIMITER //
CREATE TRIGGER trg_emp_salary
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
  IF NEW.salary IS NULL THEN
    SET NEW.salary = 3000;
  END IF;
END//
DELIMITER ;

INSERT INTO emp(name, age, job, entrydate, dept_id) VALUES('新同事', 22, '开发', '2025-09-01', 1);
SELECT name, salary FROM emp WHERE name = '新同事';
💡 BEFORE 触发器里可以修改 NEW 的值，用来做默认值兜底或数据清洗。
例 2
创建 AFTER INSERT 触发器：新增学生时，把学号、姓名和插入时间写入日志表 stu_log。
查看参考答案（可复制）
sql
CREATE TABLE IF NOT EXISTS stu_log (
  Sno varchar(10), Sname varchar(10), optime datetime
);

DROP TRIGGER IF EXISTS trg_stu_ins;
CREATE TRIGGER trg_stu_ins
AFTER INSERT ON student FOR EACH ROW
INSERT INTO stu_log VALUES(NEW.Sno, NEW.Sname, NOW());

INSERT INTO student(Sno, Sname, Ssex, sage, class) VALUES('022', '周测', '女', 16, '01');
SELECT * FROM stu_log;
💡 AFTER 触发器适合做审计留痕；NEW 代表新行，OLD 在 INSERT 时不可用。
例 3
创建 BEFORE UPDATE 触发器：修改成绩时，若新值不在 0~100 之间，用 SIGNAL 抛错阻止修改。
查看参考答案（可复制）
sql
DROP TRIGGER IF EXISTS trg_sc_check;
DELIMITER //
CREATE TRIGGER trg_sc_check
BEFORE UPDATE ON sc 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 sc SET score = 120 WHERE Sno = '001' AND Cno = '01';  -- 报错被拦截
💡 SIGNAL SQLSTATE '45000' 表示自定义业务异常，错误信息写在 MESSAGE_TEXT 里。
←
上一节 3. 视图：虚拟的专用窗口
下一节 5. 触发器实战（教学实例库）
→