1. 触发器到底是什么为什么我劝你别轻易用很多写SQL的朋友第一次接触触发器是在面试题或者公司的老项目里看到一大堆CREATE TRIGGER的脚本然后被吓一跳。说实话我刚开始干活那会儿也觉得触发器是个神秘玩意儿后来自己动手写过几个才慢慢摸清楚它的脾气。简单说触发器是一种特殊的存储过程它不像普通存储过程那样需要你手动CALL一遍而是自动附加在表上的、由数据变更事件触发的逻辑。你往表里INSERT一条记录UPDATE一行数据或者DELETE某些行触发器就会自动执行一串预定义好的SQL。它能解决什么问题最典型的场景就是审计日志。你不想让业务代码每次写库的时候都额外插一条日志表因为万一业务代码忘了写日志就丢了而且业务逻辑会变得很啰嗦。触发器可以让日志记录这件事变成“数据库层面的惯性动作”只要表里有数据变动日志就一定被记录谁都没法跳过。还有数据一致性维护、自动更新汇总表、级联修改等场景触发器都能派上用场。但为什么我又说“别轻易用”呢因为触发器最大的问题是隐式执行。它像是藏在数据库里的定时炸弹你在业务代码里根本看不到它排查问题的时候特别容易被忽略。我刚入行时接手过一个系统莫名其妙的字段总被改动查了很久才发现有个老触发器在背后偷偷UPDATE。所以我的态度是触发器不是不能用而是用之前必须想清楚它的执行时机、性能开销和调试成本。今天这篇就带你把触发器的原理、语法、实战代码和坑一次性讲透。适合谁看正在学SQL的大学生、刚写完增删改查准备进阶的初级开发以及需要在数据库里做审计或者数据一致性维护的运维和DBA。如果你已经是个老手也可以看看后面我踩过的坑和排查技巧有些细节不一定写进官方文档里。2. 触发器的工作机制与核心语法2.1 触发器的三个关键要素事件、时机、粒度理解触发器先记住三个维度事件、时机和粒度。事件指的是哪种数据变更会激活触发器。SQL标准里主要有INSERT、UPDATE、DELETE三种。有些数据库还支持TRUNCATE、MERGE这类操作但并不是所有数据库都支持比如MySQL的触发器就不响应TRUNCATE这是一个非常经典的坑。时机分两种AFTER也叫FOR EACH ROW之后执行和INSTEAD OF替代原操作执行。AFTER意味着原始SQL已经改完了数据你再去做后续处理INSTEAD OF则是你拦截住这次操作用自己的逻辑替代掉原来的INSERT/UPDATE/DELETE常用于视图上实现可更新视图因为视图本身没有物理数据你需要告诉数据库“这个更新到底该怎么落到基表上”。粒度是行级还是语句级。行级触发器FOR EACH ROW表示每一行被改动都会执行一次触发器适合做逐行审计、逐行校验语句级触发器FOR EACH STATEMENT表示无论这条语句改了多少行触发器只执行一次适合做批量操作后的统计、释放资源等。举个生活化的例子行级触发器就像每个快递包裹都要过一遍安检仪语句级触发器就像整批包裹到了之后你只做一次总数登记。2.2 创建触发器的标准SQL骨架不同数据库的触发器方言差异不小但核心语法骨架基本一致。以MySQL为例最基础的触发器长这样DELIMITER $$ CREATE TRIGGER trigger_name AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(operation, record_id, changed_at) VALUES (INSERT, NEW.employee_id, NOW()); END$$ DELIMITER ;拆开看CREATE TRIGGER trigger_name给触发器起名命名规范建议是表名_事件_时机比如employees_ai表示employees表的AFTER INSERT触发器。我见过不少团队用trg_xxx其实效果差不多关键是团队内部统一方便后面排查。AFTER INSERT ON employees指定监听哪个表的哪个事件。FOR EACH ROW声明这是行级触发器。BEGIN...END里面是触发器要执行的SQL语句块。如果只有一条语句MySQL可以省略BEGIN END但我建议新手永远都写上因为后面很可能要加第二条语句。NEW和OLD这是触发器里的两个虚拟行。NEW代表新插入或更新后的值OLD代表更新或删除前的值。INSERT只有NEWDELETE只有OLDUPDATE两者都有。这个规则在任何数据库里几乎都是通用的只是写法略有差异比如SQL Server里用INSERTED和DELETED两个虚拟表Oracle里用NEW和OLD但在语句级触发器中有限制。2.3 修改与删除触发器修改触发器不能直接ALTER TRIGGER的正文通用的做法是先DROP再CREATE。MySQL 8.0之前尤其如此8.0之后也没有提供修改触发器定义的语法。如果你只想临时禁用触发器可以用ALTER TABLE employees DISABLE TRIGGER employees_ai; ALTER TABLE employees ENABLE TRIGGER employees_ai;这条语法是SQL Server的写法在MySQL里对应的是DISABLE TRIGGERMySQL 8.0也支持了但写法不同。删除触发器用DROP TRIGGER IF EXISTS employees_ai;我强烈建议在你的发布脚本里都加上IF EXISTS否则在重复执行的CI脚本里会直接报错。3. 实战代码演示从审计日志到数据校验3.1 场景一自动审计日志AFTER INSERT/UPDATE/DELETE这是触发器最经典的用途。假设我们有一张员工表employees需要记录所有变更历史要求包括谁在什么时间把哪个字段从什么值改成了什么值。如果完全靠业务代码做每个写入的地方都要加日志逻辑不仅重复而且容易漏。用触发器可以一劳永逸。先建一张审计表CREATE TABLE employees_audit ( audit_id INT AUTO_INCREMENT PRIMARY KEY, emp_id INT NOT NULL, operation VARCHAR(10) NOT NULL, old_value TEXT, new_value TEXT, changed_by VARCHAR(64) NOT NULL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );然后创建三个触发器分别对应增、删、改DELIMITER $$ CREATE TRIGGER employees_after_insert AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (NEW.employee_id, INSERT, NULL, CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), CURRENT_USER()); END$$ CREATE TRIGGER employees_after_delete AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (OLD.employee_id, DELETE, CONCAT_WS(,, OLD.name, OLD.department, OLD.salary), NULL, CURRENT_USER()); END$$ CREATE TRIGGER employees_after_update AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (NEW.employee_id, UPDATE, CONCAT_WS(,, OLD.name, OLD.department, OLD.salary), CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), CURRENT_USER()); END$$ DELIMITER ;这里有几个细节值得说CONCAT_WS(,, ...)用来把多个字段拼成一个字符串方便在一条日志里看到完整的变更前后快照。如果你需要精确到字段级别的变更对比建议改成单独记录字段名的设计比如field_name、old_value、new_value每行一个字段否则后面做分析会很痛苦。CURRENT_USER()可以用来记录操作人但如果是通过应用服务器连接数据库的这个值通常是应用配置的数据库账号并不是真正的业务用户。想记录真实业务用户需要靠应用在连接时执行SET app_user xxx然后触发器里读取app_user否则审计日志里的用户维度就是个假的我在实际项目里踩过这个坑。3.2 场景二防止脏数据BEFORE INSERT/UPDATE触发器可以充当最后一道数据防线。比如工资字段salary不能为负数如果业务层校验漏掉了数据库触发器可以兜底DELIMITER $$ CREATE TRIGGER employees_before_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT salary cannot be negative; END IF; END$$ DELIMITER ;SIGNAL语句是MySQL 5.5提供的抛异常机制执行后这条INSERT会被拒绝并且错误信息会直接返回给调用方。SQLSTATE 45000是用户自定义异常的通用状态码也可以用其他未占用的状态码。BEFORE触发器的好处是在数据真正落盘之前做判断可以节省一次无效写入。而且BEFORE触发器可以修改NEW的值。比如你想自动去除字符串两端的空格、统一邮箱小写CREATE TRIGGER employees_before_insert_clean BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.email LOWER(TRIM(NEW.email)); SET NEW.name TRIM(NEW.name); END;注意在AFTER触发器里修改NEW的值是无效的因为数据已经写进去了。这一点特别容易被人忽略。你可以在AFTER触发器里更新其他表但改不动当前这张表的当前行。3.3 场景三级联更新汇总表AFTER INSERT/UPDATE/DELETE假设有个订单表orders需要维护一个每个客户的订单总数统计表customer_stats。方案之一是每次订单变化后更新客户的总订单数和总金额。触发器写起来很直白CREATE TRIGGER orders_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE customer_stats SET order_count order_count 1, total_amount total_amount NEW.amount WHERE customer_id NEW.customer_id; END;但这里有一个可怕的陷阱触发器里的UPDATE如果也触发了别的更新再反过来影响当前表就会形成递归触发。MySQL默认不允许递归触发器可以通过session.max_sp_recursion_depth等参数控制但像SQL Server里默认RECURSIVE_TRIGGERS是OFF的Oracle也有复杂的相互触发机制。我在项目里见过触发器A更新表B表B的触发器又更新表A最后导致栈溢出或者死锁。所以在设计级联触发器时一定要画清楚触发链路能拆到应用层就别塞进数据库。另外如果orders表频繁插入行级触发器会同一条INSERT上的其他操作一起在同一个事务里执行。假如customer_stats表很大UPDATE没有走索引性能会急剧下降。这就是为什么很多DBA强烈反对在核心交易表上使用过于复杂的触发器。4. SQL Server 与 MySQL 的触发器差异对照4.1 SQL Server 的 INSERTED 和 DELETED 虚拟表SQL Server的触发器在逻辑上和MySQL不太一样。它是基于语句级触发的虽然你可以在FOR EACH ROW的语义上写出逐行逻辑但SQL Server不提供FOR EACH ROW关键字。它通过两个虚拟表INSERTED和DELETED来保存被影响行的快照这两张表和原表结构相同里面装的是本次操作涉及的所有行。比如实现审计日志SQL Server的标准写法是CREATE TRIGGER employees_audit_trigger ON employees AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM INSERTED) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_at) SELECT e.employee_id, INSERT, NULL, CONCAT_WS(,, e.name, e.department, e.salary), GETDATE() FROM INSERTED e; END IF EXISTS (SELECT 1 FROM DELETED) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_at) SELECT e.employee_id, DELETE, CONCAT_WS(,, e.name, e.department, e.salary), NULL, GETDATE() FROM DELETED e; END END如果一个UPDATE语句同时改了5000行INSERTED和DELETED里都有5000行数据上面的触发器会批量插入效率比MySQL的行级触发器高不少——MySQL的行级触发器遇到5000行更新会执行5000次触发器主体。这也是为什么在MySQL里用触发器做批量更新审计时要格外小心性能而在SQL Server里这个问题相对没那么严重因为你可以写基于集合的批量逻辑。但SQL Server也有自己的坑如果触发器内部发生了错误会导致整个事务回滚而且错误信息可能直接抛给应用程序。SET NOCOUNT ON必须在触发器开头写否则会多返回影响行数一些ORM会因此判错。4.2 MySQL 8.0 与旧版语法差异MySQL从8.0开始支持窗口函数、CTE等更多现代特性但触发器语法本身变化不大。旧版的DELIMITER问题是新手永恒的烦恼如果你用Navicat或命令行直接贴触发器定义;会导致MySQL认为语句提前结束了所以必须用DELIMITER $$把分隔符临时换成别的。需要注意MySQL 8.0的SIGNAL在存储程序里可以带SET MESSAGE_TEXT在旧版5.5之前是没有这个能力的。如果公司还在用5.6、5.7请务必检查版本对SIGNAL的支持。另外MySQL触发器默认无法修改NEW之外的其他表其实是可以的只要不触发递归。官方文档里有一条限制触发器不能返回结果集也就是不能在触发器中执行SELECT查询直接把结果返回给客户端只能用SELECT INTO或者把结果用于赋值。我在一些老项目里见过有人试图在触发器中写SELECT * FROM xxx其实运行时MySQL会报错这点要留意。4.3 一个触发器多个事件 vs 多个触发器选哪个在SQL Server里你可以方便地写一个AFTER INSERT, UPDATE, DELETE的复合触发器。但MySQL不允许一个触发器监听多个事件你必须为INSERT、UPDATE、DELETE分别建触发器。如果要复用逻辑可以抽一个存储过程让三个触发器都去调用它。比如DELIMITER $$ CREATE PROCEDURE sp_audit_employee(IN op VARCHAR(10), IN old_row TEXT, IN new_row TEXT, IN emp_id INT) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (emp_id, op, old_row, new_row, CURRENT_USER()); END$$ CREATE TRIGGER employees_ai AFTER INSERT ON employees FOR EACH ROW BEGIN CALL sp_audit_employee(INSERT, NULL, CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), NEW.employee_id); END$$ -- 类似地创建 DELETE、UPDATE 触发器 DELIMITER ;这样三个触发器只写一次核心逻辑维护成本低很多。别小看这个习惯等需要把字段拼接方式从逗号改成JSON的时候你会感谢这个设计的。5. 触发器的高危用法与性能陷阱5.1 触发器执行顺序不可控一个表上可以定义多个同一类型的触发器比如两个AFTER INSERT触发器。在MySQL里同事件同时机的触发器按照创建时间先后执行但从MySQL 5.7开始推荐使用FOLLOWS和PRECEDES来显式指定顺序。如果不指定你可能会在DROP掉一个触发器后另一个触发器的行为发生变化因为顺序变了。在实际项目中我遇到过这样一个case同一个表上有两个触发器一个负责写审计日志一个负责更新汇总表。后来审计触发器因为需求被DROP掉结果汇总表开始出现数据不一致查了很久才发现原来之前汇总触发器是在审计触发器之后执行的审计触发器里的某些临时变量间接影响了汇总触发器的输入。虽然这种耦合很离谱但确实存在于老系统的“屎山”代码里。我的建议是如果一定要放多个触发器不要在触发器里依赖另一个触发器的副作用否则就是给自己埋雷。5.2 触发器内部写操作与外键约束的火花触发器是在SQL语句执行过程中触发的而外键约束的检查发生在语句执行期间。有些数据库里触发器里的操作会和外键约束产生冲突。比如你在BEFORE DELETE触发器里尝试删除父表记录而子表还有外键引用可能还没到外键检查阶段触发器就把数据改坏了。更常见的坑是级联删除。表A删一行触发器自动删表B关联行表B又有触发器删表C如果表C的外键指向表A整个删除链路可能把自己锁死或者产生意外的大量删除。我见过最严重的一次线上事故就是一条DELETE触发了三级触发器把三张业务表里的几万行数据全清了。当时应用层执行删除的工程师并不知道有这些触发器存在。所以一定要记住触发器写出来你欠的债是要还的排查链路时必须用文档把触发器的依赖关系画清楚。5.3 循环触发防护与死锁如果数据库允许递归触发器而且你恰好写了两个互相更新的触发器那么一条INSERT可能会无限调用下去直到数据库抛出递归深度过深错误。SQL Server默认禁用递归触发器但如果你手动打开了RECURSIVE_TRIGGERS就务必小心。还有一种情况是间接递归触发器里更新了另一张表另一张表的触发器更新了第三张表第三张表的触发器又回过来更新第一张表。这种链路查起来极其痛苦而且容易导致死锁。我在排查一次“数据库偶发死锁”问题时翻遍了所有存储过程和定时任务都没找到原因最后是用sys.dm_exec_trigger_stats和扩展事件跟踪到的是一个冷门触发器引起的表锁升级。建议所有用到触发器的系统在测试环境里跑一遍完整的增删改压测观察是否有锁等待异常别只在数据量小的功能测试上溜一眼。5.4 触发器与事务回滚的关系触发器默认和触发它的语句在同一个事务里。如果触发器内抛了异常整个事务回滚——这是AFTER触发器的特点。在SQL Server里如果触发器在AFTER阶段执行时出错当前语句会回滚包括已经插入的数据。在Oracle里触发器中产生的异常也会导致整个事务回滚除非你用自治事务PRAGMA AUTONOMOUS_TRANSACTION把日志写入独立出去。这里有个很精妙的分寸拿捏。比如你想做一个“操作失败也要记录失败日志”的触发器如果使用普通事务触发器报错回滚后你试图插入的失败日志也会被回滚日志就没了。这时需要自治事务来写日志。MySQL里没有自治事务这个词但可以通过SIGNAL之后的异常处理流程来做一些补救或者干脆让业务代码来捕获这个错误并记录别全靠触发器。6. 常见问题与排查技巧实录6.1 怎么查看一张表上已有哪些触发器MySQL下一条命令就能看到表相关的触发器SHOW TRIGGERS LIKE employees%;也可以从系统表查SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE employees;SQL Server里则是SELECT t.name AS trigger_name, OBJECT_NAME(t.parent_id) AS table_name, te.type_desc AS event_type FROM sys.triggers t JOIN sys.trigger_events te ON t.object_id te.object_id;老项目排查第一件事就是把所有历史触发器列出来。我甚至建议把触发器清单纳入数据库对象的资产台账里谁建的、建在哪些表上、作用是什么都要有据可查不然半年后没人知道这些触发器为什么会存在。6.2 触发器不执行的可能原因我总结了几个高频原因表名和触发器监听的事件写错了。比如你监听的是AFTER UPDATE但实际代码执行的是INSERT ... ON DUPLICATE KEY UPDATE这种语句在插入冲突时走的路径很复杂MySQL里它既不是纯INSERT也不是纯UPDATE而是两者混合触发器可能只触发一个或者都不触发。这个坑很阴需要专门测试。权限问题。如果触发器的创建者账号对表没有足够权限或者触发器调用的存储过程没权限会静默失败或直接报错。尤其是迁移数据库实例之后账号映射变了触发器还在但执行时报错系统看起来像是“没触发”。BEFORE触发器里抛了异常但没有被业务代码感知。有些框架吞掉了数据库异常你会观察到这条INSERT没有生效但应用日志里没有任何报错。这时候去查SHOW WARNINGS或数据库错误日志才有线索。你改的是视图而不是基表。视图上的INSTEAD OF触发器没建或者建了没匹配上事件操作就被数据库直接拒绝了。6.3 临时禁用触发器做数据修复数据修复时不想让触发器捣乱可以临时禁用。MySQL方案-- 8.0 支持禁用触发器 ALTER TABLE employees DISABLE TRIGGER employees_after_insert; -- 或者使用 SET 会话变量在触发器中做开关 SET disable_audit_trigger 1;然后在触发器开头判断IF disable_audit_trigger IS NULL THEN -- 正常执行审计 END IF;第二种方式的好处是不需要改表结构特别适合帮你临时修复存量数据又不希望产生审计噪音的场景。SQL Server里可以DISABLE TRIGGER employees_after_insert ON employees; ENABLE TRIGGER employees_after_insert ON employees;注意SQL Server禁用触发器之后通过ALTER TABLE、TRUNCATE TABLE等操作仍然不触发触发器这是官方行为不用惊讶。6.4 触发器里更新同一张表会报错吗在MySQL里如果AFTER INSERT触发器里直接UPDATE当前表会报“Cant update table employees in stored function/trigger because it is already used by statement which invoked this stored function/trigger.”这个错误。也就是说你没法在AFTER触发器里再改当前表当前行因为这条记录已经被锁定了。BEFORE触发器里可以SET NEW.xxx但不能UPDATE整表。想绕过这个限制得用独立事务或改设计但大多数情况下说明你的逻辑设计有问题建议换个思路。SQL Server同样限制触发器内修改被触发表但表达方式略有不同而且SQL Server对嵌套深度有NESTED_TRIGGERS选项控制。如果你真的需要“UPDATE后马上修正另一个字段”优先考虑在BEFORE阶段修改NEW不建议用AFTER再改表。6.5 触发器导致主从复制中断这是我最想提醒的一个坑。在MySQL主从架构里如果主库上触发器里用了CURRENT_USER()、UUID()、RAND()等非确定性函数或者触发器里操作了其他表可能导致从库执行时产生不同的结果进而中断复制。尤其是基于STATEMENT的复制模式binlog_formatSTATEMENT一个不谨慎的触发器就能让整条复制链路卡死。我实际遇到过一次主库上某表INSERT后触发器调用了存储过程存储过程里用LAST_INSERT_ID()来生成业务单号。在主库执行正常但到了从库重放的时候LAST_INSERT_ID()上下文已经变了导致从库数据错位最后花了很久才定位到是触发器的问题。解决思路是在主从环境下尽量让触发器里的操作是确定性的或者binlog_format改用ROW模式让从库重放的是实际数据行而不是SQL逻辑。7. 用触发器之前的几条实战经验写了这么多年的SQL我个人的体会是触发器是一门“小刀”技术它锋利、便捷但也很容易划伤自己。别把复杂的业务规则全塞进触发器里记住三个原则第一触发器只做绝对必须由数据库兜底的事。比如审计、防负数、防NULL、强制唯一性的辅助判断。凡是应用层能清晰处理的优先放在应用层处理因为应用层有完善的日志、监控、链路追踪数据库触发器只有干巴巴的错误信息。第二触发器实现必须配套文档。每个触发器都要在注释里写明建它的人、日期、背景、依赖的表、预期行为、失效时会有什么影响。我自己习惯在触发器定义的BEGIN前面写一长段历史注释七八个月后回来维护时真的能救命。第三变更前先在测试环境验证触发链路。尤其是有批量UPDATE/DELETE的数据清洗任务先小规模跑一遍观察受影响行数确认触发器的性能可控再上生产。千万别在生产上直接执行一个10万行的UPDATE如果触发器里有个慢查询整个表会被锁到怀疑人生。最后再分享一个小技巧排查触发器相关问题时先跑一遍SHOW TRIGGERS看看有没有“野触发器”再用数据库自带的审计工具或者扩展事件SQL Server的Extended Events、MySQL的Performance Schema跟踪一下被触发的动作。很多时候问题不在SQL语句本身而是背后那只看不见的触发器之手。 1. 触发器到底是什么为什么我劝你别轻易用很多写SQL的朋友第一次接触触发器是在面试题或者公司的老项目里看到一大堆CREATE TRIGGER的脚本然后被吓一跳。说实话我刚开始干活那会儿也觉得触发器是个神秘玩意儿后来自己动手写过几个才慢慢摸清楚它的脾气。简单说触发器是一种特殊的存储过程它不像普通存储过程那样需要你手动CALL一遍而是自动附加在表上的、由数据变更事件触发的逻辑。你往表里INSERT一条记录UPDATE一行数据或者DELETE某些行触发器就会自动执行一串预定义好的SQL。它能解决什么问题最典型的场景就是审计日志。你不想让业务代码每次写库的时候都额外插一条日志表因为万一业务代码忘了写日志就丢了而且业务逻辑会变得很啰嗦。触发器可以让日志记录这件事变成“数据库层面的惯性动作”只要表里有数据变动日志就一定被记录谁都没法跳过。还有数据一致性维护、自动更新汇总表、级联修改等场景触发器都能派上用场。但为什么我又说“别轻易用”呢因为触发器最大的问题是隐式执行。它像是藏在数据库里的定时炸弹你在业务代码里根本看不到它排查问题的时候特别容易被忽略。我刚入行时接手过一个系统莫名其妙的字段总被改动查了很久才发现有个老触发器在背后偷偷UPDATE。所以我的态度是触发器不是不能用而是用之前必须想清楚它的执行时机、性能开销和调试成本。今天这篇就带你把触发器的原理、语法、实战代码和坑一次性讲透。适合谁看正在学SQL的大学生、刚写完增删改查准备进阶的初级开发以及需要在数据库里做审计或者数据一致性维护的运维和DBA。如果你已经是个老手也可以看看后面我踩过的坑和排查技巧有些细节不一定写进官方文档里。2. 触发器的工作机制与核心语法2.1 触发器的三个关键要素事件、时机、粒度理解触发器先记住三个维度事件、时机和粒度。事件指的是哪种数据变更会激活触发器。SQL标准里主要有INSERT、UPDATE、DELETE三种。有些数据库还支持TRUNCATE、MERGE这类操作但并不是所有数据库都支持比如MySQL的触发器就不响应TRUNCATE这是一个非常经典的坑。时机分两种AFTER也叫FOR EACH ROW之后执行和INSTEAD OF替代原操作执行。AFTER意味着原始SQL已经改完了数据你再去做后续处理INSTEAD OF则是你拦截住这次操作用自己的逻辑替代掉原来的INSERT/UPDATE/DELETE常用于视图上实现可更新视图因为视图本身没有物理数据你需要告诉数据库“这个更新到底该怎么落到基表上”。粒度是行级还是语句级。行级触发器FOR EACH ROW表示每一行被改动都会执行一次触发器适合做逐行审计、逐行校验语句级触发器FOR EACH STATEMENT表示无论这条语句改了多少行触发器只执行一次适合做批量操作后的统计、释放资源等。举个生活化的例子行级触发器就像每个快递包裹都要过一遍安检仪语句级触发器就像整批包裹到了之后你只做一次总数登记。2.2 创建触发器的标准SQL骨架不同数据库的触发器方言差异不小但核心语法骨架基本一致。以MySQL为例最基础的触发器长这样DELIMITER $$ CREATE TRIGGER trigger_name AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(operation, record_id, changed_at) VALUES (INSERT, NEW.employee_id, NOW()); END$$ DELIMITER ;拆开看CREATE TRIGGER trigger_name给触发器起名命名规范建议是表名_事件_时机比如employees_ai表示employees表的AFTER INSERT触发器。我见过不少团队用trg_xxx其实效果差不多关键是团队内部统一方便后面排查。AFTER INSERT ON employees指定监听哪个表的哪个事件。FOR EACH ROW声明这是行级触发器。BEGIN...END里面是触发器要执行的SQL语句块。如果只有一条语句MySQL可以省略BEGIN END但我建议新手永远都写上因为后面很可能要加第二条语句。NEW和OLD这是触发器里的两个虚拟行。NEW代表新插入或更新后的值OLD代表更新或删除前的值。INSERT只有NEWDELETE只有OLDUPDATE两者都有。这个规则在任何数据库里几乎都是通用的只是写法略有差异比如SQL Server里用INSERTED和DELETED两个虚拟表Oracle里用NEW和OLD但在语句级触发器中有限制。2.3 修改与删除触发器修改触发器不能直接ALTER TRIGGER的正文通用的做法是先DROP再CREATE。MySQL 8.0之前尤其如此8.0之后也没有提供修改触发器定义的语法。如果你只想临时禁用触发器可以用ALTER TABLE employees DISABLE TRIGGER employees_ai; ALTER TABLE employees ENABLE TRIGGER employees_ai;这条语法是SQL Server的写法在MySQL里对应的是DISABLE TRIGGERMySQL 8.0也支持了但写法不同。删除触发器用DROP TRIGGER IF EXISTS employees_ai;我强烈建议在你的发布脚本里都加上IF EXISTS否则在重复执行的CI脚本里会直接报错。3. 实战代码演示从审计日志到数据校验3.1 场景一自动审计日志AFTER INSERT/UPDATE/DELETE这是触发器最经典的用途。假设我们有一张员工表employees需要记录所有变更历史要求包括谁在什么时间把哪个字段从什么值改成了什么值。如果完全靠业务代码做每个写入的地方都要加日志逻辑不仅重复而且容易漏。用触发器可以一劳永逸。先建一张审计表CREATE TABLE employees_audit ( audit_id INT AUTO_INCREMENT PRIMARY KEY, emp_id INT NOT NULL, operation VARCHAR(10) NOT NULL, old_value TEXT, new_value TEXT, changed_by VARCHAR(64) NOT NULL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );然后创建三个触发器分别对应增、删、改DELIMITER $$ CREATE TRIGGER employees_after_insert AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (NEW.employee_id, INSERT, NULL, CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), CURRENT_USER()); END$$ CREATE TRIGGER employees_after_delete AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (OLD.employee_id, DELETE, CONCAT_WS(,, OLD.name, OLD.department, OLD.salary), NULL, CURRENT_USER()); END$$ CREATE TRIGGER employees_after_update AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (NEW.employee_id, UPDATE, CONCAT_WS(,, OLD.name, OLD.department, OLD.salary), CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), CURRENT_USER()); END$$ DELIMITER ;这里有几个细节值得说CONCAT_WS(,, ...)用来把多个字段拼成一个字符串方便在一条日志里看到完整的变更前后快照。如果你需要精确到字段级别的变更对比建议改成单独记录字段名的设计比如field_name、old_value、new_value每行一个字段否则后面做分析会很痛苦。CURRENT_USER()可以用来记录操作人但如果是通过应用服务器连接数据库的这个值通常是应用配置的数据库账号并不是真正的业务用户。想记录真实业务用户需要靠应用在连接时执行SET app_user xxx然后触发器里读取app_user否则审计日志里的用户维度就是个假的我在实际项目里踩过这个坑。3.2 场景二防止脏数据BEFORE INSERT/UPDATE触发器可以充当最后一道数据防线。比如工资字段salary不能为负数如果业务层校验漏掉了数据库触发器可以兜底DELIMITER $$ CREATE TRIGGER employees_before_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT salary cannot be negative; END IF; END$$ DELIMITER ;SIGNAL语句是MySQL 5.5提供的抛异常机制执行后这条INSERT会被拒绝并且错误信息会直接返回给调用方。SQLSTATE 45000是用户自定义异常的通用状态码也可以用其他未占用的状态码。BEFORE触发器的好处是在数据真正落盘之前做判断可以节省一次无效写入。而且BEFORE触发器可以修改NEW的值。比如你想自动去除字符串两端的空格、统一邮箱小写CREATE TRIGGER employees_before_insert_clean BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.email LOWER(TRIM(NEW.email)); SET NEW.name TRIM(NEW.name); END;注意在AFTER触发器里修改NEW的值是无效的因为数据已经写进去了。这一点特别容易被人忽略。你可以在AFTER触发器里更新其他表但改不动当前这张表的当前行。3.3 场景三级联更新汇总表AFTER INSERT/UPDATE/DELETE假设有个订单表orders需要维护一个每个客户的订单总数统计表customer_stats。方案之一是每次订单变化后更新客户的总订单数和总金额。触发器写起来很直白CREATE TRIGGER orders_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE customer_stats SET order_count order_count 1, total_amount total_amount NEW.amount WHERE customer_id NEW.customer_id; END;但这里有一个可怕的陷阱触发器里的UPDATE如果也触发了别的更新再反过来影响当前表就会形成递归触发。MySQL默认不允许递归触发器可以通过session.max_sp_recursion_depth等参数控制但像SQL Server里默认RECURSIVE_TRIGGERS是OFF的Oracle也有复杂的相互触发机制。我在项目里见过触发器A更新表B表B的触发器又更新表A最后导致栈溢出或者死锁。所以在设计级联触发器时一定要画清楚触发链路能拆到应用层就别塞进数据库。另外如果orders表频繁插入行级触发器会同一条INSERT上的其他操作一起在同一个事务里执行。假如customer_stats表很大UPDATE没有走索引性能会急剧下降。这就是为什么很多DBA强烈反对在核心交易表上使用过于复杂的触发器。4. SQL Server 与 MySQL 的触发器差异对照4.1 SQL Server 的 INSERTED 和 DELETED 虚拟表SQL Server的触发器在逻辑上和MySQL不太一样。它是基于语句级触发的虽然你可以在FOR EACH ROW的语义上写出逐行逻辑但SQL Server不提供FOR EACH ROW关键字。它通过两个虚拟表INSERTED和DELETED来保存被影响行的快照这两张表和原表结构相同里面装的是本次操作涉及的所有行。比如实现审计日志SQL Server的标准写法是CREATE TRIGGER employees_audit_trigger ON employees AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM INSERTED) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_at) SELECT e.employee_id, INSERT, NULL, CONCAT_WS(,, e.name, e.department, e.salary), GETDATE() FROM INSERTED e; END IF EXISTS (SELECT 1 FROM DELETED) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_at) SELECT e.employee_id, DELETE, CONCAT_WS(,, e.name, e.department, e.salary), NULL, GETDATE() FROM DELETED e; END END如果一个UPDATE语句同时改了5000行INSERTED和DELETED里都有5000行数据上面的触发器会批量插入效率比MySQL的行级触发器高不少——MySQL的行级触发器遇到5000行更新会执行5000次触发器主体。这也是为什么在MySQL里用触发器做批量更新审计时要格外小心性能而在SQL Server里这个问题相对没那么严重因为你可以写基于集合的批量逻辑。但SQL Server也有自己的坑如果触发器内部发生了错误会导致整个事务回滚而且错误信息可能直接抛给应用程序。SET NOCOUNT ON必须在触发器开头写否则会多返回影响行数一些ORM会因此判错。4.2 MySQL 8.0 与旧版语法差异MySQL从8.0开始支持窗口函数、CTE等更多现代特性但触发器语法本身变化不大。旧版的DELIMITER问题是新手永恒的烦恼如果你用Navicat或命令行直接贴触发器定义;会导致MySQL认为语句提前结束了所以必须用DELIMITER $$把分隔符临时换成别的。需要注意MySQL 8.0的SIGNAL在存储程序里可以带SET MESSAGE_TEXT在旧版5.5之前是没有这个能力的。如果公司还在用5.6、5.7请务必检查版本对SIGNAL的支持。另外MySQL触发器默认无法修改NEW之外的其他表其实是可以的只要不触发递归。官方文档里有一条限制触发器不能返回结果集也就是不能在触发器中执行SELECT查询直接把结果返回给客户端只能用SELECT INTO或者把结果用于赋值。我在一些老项目里见过有人试图在触发器中写SELECT * FROM xxx其实运行时MySQL会报错这点要留意。4.3 一个触发器多个事件 vs 多个触发器选哪个在SQL Server里你可以方便地写一个AFTER INSERT, UPDATE, DELETE的复合触发器。但MySQL不允许一个触发器监听多个事件你必须为INSERT、UPDATE、DELETE分别建触发器。如果要复用逻辑可以抽一个存储过程让三个触发器都去调用它。比如DELIMITER $$ CREATE PROCEDURE sp_audit_employee(IN op VARCHAR(10), IN old_row TEXT, IN new_row TEXT, IN emp_id INT) BEGIN INSERT INTO employees_audit(emp_id, operation, old_value, new_value, changed_by) VALUES (emp_id, op, old_row, new_row, CURRENT_USER()); END$$ CREATE TRIGGER employees_ai AFTER INSERT ON employees FOR EACH ROW BEGIN CALL sp_audit_employee(INSERT, NULL, CONCAT_WS(,, NEW.name, NEW.department, NEW.salary), NEW.employee_id); END$$ -- 类似地创建 DELETE、UPDATE 触发器 DELIMITER ;这样三个触发器只写一次核心逻辑维护成本低很多。别小看这个习惯等需要把字段拼接方式从逗号改成JSON的时候你会感谢这个设计的。5. 触发器的高危用法与性能陷阱5.1 触发器执行顺序不可控一个表上可以定义多个同一类型的触发器比如两个AFTER INSERT触发器。在MySQL里同事件同时机的触发器按照创建时间先后执行但从MySQL 5.7开始推荐使用FOLLOWS和PRECEDES来显式指定顺序。如果不指定你可能会在DROP掉一个触发器后另一个触发器的行为发生变化因为顺序变了。在实际项目中我遇到过这样一个case同一个表上有两个触发器一个负责写审计日志一个负责更新汇总表。后来审计触发器因为需求被DROP掉结果汇总表开始出现数据不一致查了很久才发现原来之前汇总触发器是在审计触发器之后执行的审计触发器里的某些临时变量间接影响了汇总触发器的输入。虽然这种耦合很离谱但确实存在于老系统的“屎山”代码里。我的建议是如果一定要放多个触发器不要在触发器里依赖另一个触发器的副作用否则就是给自己埋雷。5.2 触发器内部写操作与外键约束的火花触发器是在SQL语句执行过程中触发的而外键约束的检查发生在语句执行期间。有些数据库里触发器里的操作会和外键约束产生冲突。比如你在BEFORE DELETE触发器里尝试删除父表记录而子表还有外键引用可能还没到外键检查阶段触发器就把数据改坏了。更常见的坑是级联删除。表A删一行触发器自动删表B关联行表B又有触发器删表C如果表C的外键指向表A整个删除链路可能把自己锁死或者产生意外的大量删除。我见过最严重的一次线上事故就是一条DELETE触发了三级触发器把三张业务表里的几万行数据全清了。当时应用层执行删除的工程师并不知道有这些触发器存在。所以一定要记住触发器写出来你欠的债是要还的排查链路时必须用文档把触发器的依赖关系画清楚。5.3 循环触发防护与死锁如果数据库允许递归触发器而且你恰好写了两个互相更新的触发器那么一条INSERT可能会无限调用下去直到数据库抛出递归深度过深错误。SQL Server默认禁用递归触发器但如果你手动打开了RECURSIVE_TRIGGERS就务必小心。还有一种情况是间接递归触发器里更新了另一张表另一张表的触发器更新了第三张表第三张表的触发器又回过来更新第一张表。这种链路查起来极其痛苦而且容易导致死锁。我在排查一次“数据库偶发死锁”问题时翻遍了所有存储过程和定时任务都没找到原因最后是用sys.dm_exec_trigger_stats和扩展事件跟踪到的是一个冷门触发器引起的表锁升级。建议所有用到触发器的系统在测试环境里跑一遍完整的增删改压测观察是否有锁等待异常别只在数据量小的功能测试上溜一眼。5.4 触发器与事务回滚的关系触发器默认和触发它的语句在同一个事务里。如果触发器内抛了异常整个事务回滚——这是AFTER触发器的特点。在SQL Server里如果触发器在AFTER阶段执行时出错当前语句会回滚包括已经插入的数据。在Oracle里触发器中产生的异常也会导致整个事务回滚除非你用自治事务PRAGMA AUTONOMOUS_TRANSACTION把日志写入独立出去。这里有个很精妙的分寸拿捏。比如你想做一个“操作失败也要记录失败日志”的触发器如果使用普通事务触发器报错回滚后你试图插入的失败日志也会被回滚日志就没了。这时需要自治事务来写日志。MySQL里没有自治事务这个词但可以通过SIGNAL之后的异常处理流程来做一些补救或者干脆让业务代码来捕获这个错误并记录别全靠触发器。6. 常见问题与排查技巧实录6.1 怎么查看一张表上已有哪些触发器MySQL下一条命令就能看到表相关的触发器SHOW TRIGGERS LIKE employees%;也可以从系统表查SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE employees;SQL Server里则是SELECT t.name AS trigger_name, OBJECT_NAME(t.parent_id) AS table_name, te.type_desc AS event_type FROM sys.triggers t JOIN sys.trigger_events te ON t.object_id te.object_id;老项目排查第一件事就是把所有历史触发器列出来。我甚至建议把触发器清单纳入数据库对象的资产台账里谁建的、建在哪些表上、作用是什么都要有据可查不然半年后没人知道这些触发器为什么会存在。6.2 触发器不执行的可能原因我总结了几个高频原因表名和触发器监听的事件写错了。比如你监听的是AFTER UPDATE但实际代码执行的是INSERT ... ON DUPLICATE KEY UPDATE这种语句在插入冲突时走的路径很复杂MySQL里它既不是纯INSERT也不是纯UPDATE而是两者混合触发器可能只触发一个或者都不触发。这个坑很阴需要专门测试。权限问题。如果触发器的创建者账号对表没有足够权限或者触发器调用的存储过程没权限会静默失败或直接报错。尤其是迁移数据库实例之后账号映射变了触发器还在但执行时报错系统看起来像是“没触发”。BEFORE触发器里抛了异常但没有被业务代码感知。有些框架吞掉了数据库异常你会观察到这条INSERT没有生效但应用日志里没有任何报错。这时候去查SHOW WARNINGS或数据库错误日志才有线索。你改的是视图而不是基表。视图上的INSTEAD OF触发器没建或者建了没匹配上事件操作就被数据库直接拒绝了。6.3 临时禁用触发器做数据修复数据修复时不想让触发器捣乱可以临时禁用。MySQL方案-- 8.0 支持禁用触发器 ALTER TABLE employees DISABLE TRIGGER employees_after_insert; -- 或者使用 SET 会话变量在触发器中做开关 SET disable_audit_trigger 1;然后在触发器开头判断IF disable_audit_trigger IS NULL THEN -- 正常执行审计 END IF;第二种方式的好处是不需要改表结构特别适合帮你临时修复存量数据又不希望产生审计噪音的场景。SQL Server里可以DISABLE TRIGGER employees_after_insert ON employees; ENABLE TRIGGER employees_after_insert ON employees;注意SQL Server禁用触发器之后通过ALTER TABLE、TRUNCATE TABLE等操作仍然不触发触发器这是官方行为不用惊讶。6.4 触发器里更新同一张表会报错吗在MySQL里如果AFTER INSERT触发器里直接UPDATE当前表会报“Cant update table employees in stored function/trigger because it is already used by statement which invoked this stored function/trigger.”这个错误。也就是说你没法在AFTER触发器里再改当前表当前行因为这条记录已经被锁定了。BEFORE触发器里可以SET NEW.xxx但不能UPDATE整表。想绕过这个限制得用独立事务或改设计但大多数情况下说明你的逻辑设计有问题建议换个思路。SQL Server同样限制触发器内修改被触发表但表达方式略有不同而且SQL Server对嵌套深度有NESTED_TRIGGERS选项控制。如果你真的需要“UPDATE后马上修正另一个字段”优先考虑在BEFORE阶段修改NEW不建议用AFTER再改表。6.5 触发器导致主从复制中断这是我最想提醒的一个坑。在MySQL主从架构里如果主库上触发器里用了CURRENT_USER()、UUID()、RAND()等非确定性函数或者触发器里操作了其他表可能导致从库执行时产生不同的结果进而中断复制。尤其是基于STATEMENT的复制模式binlog_formatSTATEMENT一个不谨慎的触发器就能让整条复制链路卡死。我实际遇到过一次主库上某表INSERT后触发器调用了存储过程存储过程里用LAST_INSERT_ID()来生成业务单号。在主库执行正常但到了从库重放的时候LAST_INSERT_ID()上下文已经变了导致从库数据错位最后花了很久才定位到是触发器的问题。解决思路是在主从环境下尽量让触发器里的操作是确定性的或者binlog_format改用ROW模式让从库重放的是实际数据行而不是SQL逻辑。7. 用触发器之前的几条实战经验写了这么多年的SQL我个人的体会是触发器是一门“小刀”技术它锋利、便捷但也很容易划伤自己。别把复杂的业务规则全塞进触发器里记住三个原则第一触发器只做绝对必须由数据库兜底的事。比如审计、防负数、防NULL、强制唯一性的辅助判断。凡是应用层能清晰处理的优先放在应用层处理因为应用层有完善的日志、监控、链路追踪数据库触发器只有干巴巴的错误信息。第二触发器实现必须配套文档。每个触发器都要在注释里写明建它的人、日期、背景、依赖的表、预期行为、失效时会有什么影响。我自己习惯在触发器定义的BEGIN前面写一长段历史注释七八个月后回来维护时真的能救命。第三变更前先在测试环境验证触发链路。尤其是有批量UPDATE/DELETE的数据清洗任务先小规模跑一遍观察受影响行数确认触发器的性能可控再上生产。千万别在生产上直接执行一个10万行的UPDATE如果触发器里有个慢查询整个表会被锁到怀疑人生。最后再分享一个小技巧排查触发器相关问题时先跑一遍SHOW TRIGGERS看看有没有“野触发器”再用数据库自带的审计工具或者扩展事件SQL Server的Extended Events、MySQL的Performance Schema跟踪一下被触发的动作。很多时候问题不在SQL语句本身而是背后那只看不见的触发器之手。