我见过很多开发同事第一次听到“触发器”这个词第一反应是数字电路里的D触发器。搞清楚数据库的trigger是另一回事之后下一个问题通常是这不就是数据库里的“回调函数”吗还真有点像但它比应用层回调更“霸道”——不需要任何业务代码去调用只要你往表里INSERT了数据、UPDATE了字段、DELETE了行数据库自己就把对应的逻辑跑完了。这篇文章把SQL触发器trigger从原理、语法、实战到踩坑从头捋一遍MySQL和SQL Server两侧的写法都给了示例适合刚接触触发器、以及用过但不太确定为什么这么用的同学。1. 触发器到底解决什么问题一个“忘了补日志”引发的数据事故1.1 业务代码里的规则为什么不可靠先讲一个我自己带项目时碰到的真实场景。早期我们做了一个订单系统订单表orders上调整金额时业务上要求同步往审计表写一条变更记录。第一次开发的同学把逻辑写在应用层Service里看起来没毛病先执行UPDATE orders再执行INSERT INTO orders_log。问题出在哪呢半年后需求迭代原本写这个逻辑的同事离职了新来的同事加了一个“改订单金额”的新接口只记得改orders表忘了补日志逻辑。两个月后财务对账发现一笔异常金额查了半天查不到是谁改的最后只能翻数据库的binlog。一顿排查下来责任在谁其实都不重要了重要的是这种“靠人守规矩”的方式天生就会漏。这事的教训就是操作可以分散在多个接口、多个系统、多个开发手里但规则如果只靠人去执行一定会有人忘记。所以后来我们定了一个原则审计留痕这类强制规则能下沉到数据库就下沉到数据库不依赖应用层自觉。这就是触发器最核心的价值——它把“规则”和“操作”解耦规则跟着表走谁操作这张表规则就自动生效。1.2 触发器的本质数据库里那个“自动站岗的守卫”严格一点说触发器trigger是数据库里的一段与特定表绑定的逻辑代码。它不像存储过程那样需要你显式地CALL或EXECUTE去调用而是由表上的DML操作自动触发执行。它的触发时机一共有六种组合时机事件典型用途BEFOREINSERT插入前校验、补默认值、拒绝非法数据AFTERINSERT插入后写日志、同步数据、刷新统计BEFOREUPDATE更新前校验、保护关键字段AFTERUPDATE更新后审计、记录变更前后值BEFOREDELETE删除前拦截、归档AFTERDELETE删除后清理关联数据、写删除日志用生活里的话说触发器就像你给办公室门装的感应灯有人进门灯自动亮不需要专门安排一个保安站在门口拉开关。你想改灯的逻辑只需要换掉那个感应器不需要让所有进门的人都改一套动作。这个类比还能帮你理解第二个关键点触发器是事务的一部分。感应灯亮了不会影响人进门但触发器里如果出了异常整个事务都会回滚——这个特性后面讲代码时会反复提到。1.3 触发器的适用边界什么事不该交给它既然触发器这么方便是不是能干的都扔给它不是。我带团队时给新人划了一条线适合做审计日志、变更留痕、简单的数据同步、轻量级校验、汇总字段自动维护。不适合做复杂业务校验、调用外部接口、发邮件、大批量ETL、重量级计算。因为触发器跑在数据库事务上下文里它太重会拖垮主业务流程而且出错会导致整个事务回滚影响面非常大。一个发邮件失败的触发器完全可能让一笔本该成功的订单插入也跟着失败。另外还得提醒一句很多人搜索里出现的“d触发器ff”其实是数字电路里的D触发器D flip-flop那是硬件时序逻辑靠时钟沿锁存数据跟数据库的trigger没有任何关系。数据库里的触发器关键词就是trigger本文全部基于后者来写。2. CREATE TRIGGER语法拆解与第一段可运行代码2.1 MySQL的CREATE TRIGGER语法逐项拆解先看MySQL的完整语法骨架CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW BEGIN -- 触发器逻辑 END;逐项拆开看trigger_name触发器名称在一个数据库schema内必须唯一。我习惯的前缀是trg_后面跟表名和动作比如trg_orders_update_log一眼能看出这个触发器是干嘛的。{BEFORE | AFTER}触发时机先于操作还是后于操作执行。{INSERT | UPDATE | DELETE}监听哪种DML事件。ON table_name绑定在哪张表上。FOR EACH ROW行级触发器。MySQL只支持行级触发器意思是每影响到一行就触发一次。这一点和SQL Server有本质区别后面专门做对比。BEGIN...END逻辑体里面可以写多条SQL语句。这里有一个初学者必踩的坑MySQL客户端里执行CREATE TRIGGER时BEGIN...END内部有自己的分号如果直接用默认分隔符;MySQL会在第一个分号处就把语句切断导致语法错乱。解决办法是先用DELIMITER $$把结束符临时换成$$创建完再换回来。2.2 NEW和OLD伪记录触发器怎么拿到“旧值”和“新值”行级触发器里有两张“虚拟行”可以直接用NEW和OLD。NEW代表当前操作产生的新行。INSERT时只有NEWUPDATE时有NEW更新后的行DELETE时没有NEW。OLD代表操作之前已经在表里的旧行。UPDATE时是更新前的行DELETE时是被删掉的行INSERT时没有OLD。两个重要细节在BEFORE触发器里你可以修改NEW的字段值。比如插入前发现某个字段没传值可以直接给NEW.status赋值这个值会随插入写进表。在AFTER触发器里行已经写进表了所以NEW字段不能再改。不管在BEFORE还是AFTEROLD都只能读不能改。这个“读写权限”的差异决定了你选择BEFORE还是AFTER时能做什么不能做什么。2.3 完整代码演示订单金额变更自动写日志业务需求是这样订单表orders的任何一行发生UPDATE只要金额或状态真变了就自动往orders_log里写一条记录记录变更前后的值和变更时间。建表CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, product_name VARCHAR(100) NOT NULL, quantity INT NOT NULL DEFAULT 1, amount DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders_log ( log_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, action_type VARCHAR(10) NOT NULL, old_amount DECIMAL(10, 2) NULL, new_amount DECIMAL(10, 2) NULL, old_status TINYINT NULL, new_status TINYINT NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );然后创建触发器DELIMITER $$ CREATE TRIGGER trg_orders_update_log AFTER UPDATE ON orders FOR EACH ROW BEGIN -- 只有金额或状态真正变化时才写日志 IF OLD.amount NEW.amount OR OLD.status NEW.status THEN INSERT INTO orders_log(order_id, action_type, old_amount, new_amount, old_status, new_status) VALUES(NEW.id, UPDATE, OLD.amount, NEW.amount, OLD.status, NEW.status); END IF; END$$ DELIMITER ;测试一下UPDATE orders SET amount 99.50, status 2 WHERE id 1; SELECT * FROM orders_log;只要执行了UPDATE日志表里就会自动多出一行应用层一行代码都没写。如果后面再多几个接口、再多几个系统去改orders表这个规则依然生效。2.4 为什么一定要加“IF OLD.xxx NEW.xxx”判断这个判断是我特别想强调的。不加它就算你的UPDATE语句根本没改任何字段实际值比如执行UPDATE orders SET amount 99.50 WHERE id 1而这一行原本的amount就是99.50触发器照样会执行一次INSERT产生一条无意义的日志。线上的教训是这样的我们有一个表后台任务每分钟会跑一次UPDATE ... SET last_access_time NOW()刷新最后访问时间。一开始写触发器的时候没加判断跑了两个月审计表多了300多万行垃圾数据磁盘直接报警。后来把判断条件加上日志量降到了原来的几十分之一。所以写触发器的时候凡是涉及UPDATE一定要先想清楚是不是只有值真正变化才需要触发这个习惯能帮你省下大量的存储空间和排查时间。3. 三个真正能用上的触发器场景含完整SQL3.1 场景一下单自动扣库存并发下也不超卖需求订单明细表order_items每插入一条自动扣减商品库存表product_stock里对应的库存。如果库存不足整个插入直接失败回滚。表结构CREATE TABLE product_stock ( product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, stock INT NOT NULL ); CREATE TABLE order_items ( item_id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL );触发器DELIMITER $$ CREATE TRIGGER trg_order_items_stock_deduct BEFORE INSERT ON order_items FOR EACH ROW BEGIN DECLARE current_stock INT; -- 锁住商品库存行防止并发下超卖 SELECT stock INTO current_stock FROM product_stock WHERE product_id NEW.product_id FOR UPDATE; IF current_stock IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 商品不存在无法下单; END IF; IF current_stock NEW.quantity THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足下单失败; END IF; UPDATE product_stock SET stock stock - NEW.quantity WHERE product_id NEW.product_id; END$$ DELIMITER ;这里有三个技术点值得展开说为什么用BEFORE不用AFTER用BEFORE校验失败发生在插入之前行连写都写不进去事务直接失败干净利落。如果用AFTER行已经插入成功了库存不够时再抛异常回滚虽然也能拦住但表里的自增ID会留下空洞而且多了一次“插进去又拆出来”的动作。为什么用SELECT ... FOR UPDATE这是行锁锁住商品库存这一行。两个用户同时下单时第二个用户会被阻塞在SELECT上等第一个事务提交后才继续往下走从而避免两个订单同时读到同一个库存数字、一起扣减造成超卖。没有这个锁并发一上来库存就成负数了。SIGNAL SQLSTATE 45000是主动抛异常的写法MySQL 5.6及以上版本可用。在旧版本里常用做法是在BEFORE触发器里故意写一个会报错的赋值语句来“制造”错误。如果你维护的还是老版本记得换成老写法。3.2 场景二核心表只许改不许删业务里总有那么几张“碰不得”的表比如工资表、合同表、上线配置表。运维跑批、脚本清理、甚至手滑都可能把核心数据DELETE掉。与其事后恢复不如在触发器层面直接拦住物理删除。假设有一个工资表CREATE TABLE employee_salary ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, salary DECIMAL(10, 2) NOT NULL );加一个保护性触发器DELIMITER $$ CREATE TRIGGER trg_employee_salary_protection BEFORE DELETE ON employee_salary FOR EACH ROW BEGIN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 工资记录不允许物理删除; END$$ DELIMITER ;效果很直观任何对employee_salary的DELETE都会直接报错提示“工资记录不允许物理删除”。为什么用BEFORE因为BEFORE触发时数据还没真删抛异常能拦得干干净净用AFTER的话数据删都删了再报错属于“事后追责”没意义。这种做法适合那种“原则上不该删、只做状态标记软删除”的表。如果业务确实需要删可以单独提供一套受控的删除存储过程在存储过程里临时绕过触发器、执行前记录操作人而不是让所有人都有销毁数据的路径。3.3 场景三汇总统计表随明细自动更新需求销售明细表sale_detail每插入一条新记录当天的销售总额和总件数自动累加到汇总表sale_summary。这个需求如果放应用层做每次插入都要先查汇总存不存在再决定INSERT还是UPDATE很繁琐。用触发器加一句INSERT ... ON DUPLICATE KEY UPDATE就能搞定。表结构CREATE TABLE sale_detail ( id INT PRIMARY KEY AUTO_INCREMENT, sale_date DATE NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, amount DECIMAL(10, 2) NOT NULL ); CREATE TABLE sale_summary ( sale_date DATE PRIMARY KEY, total_quantity INT NOT NULL DEFAULT 0, total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0 );触发器DELIMITER $$ CREATE TRIGGER trg_sale_detail_sync_summary AFTER INSERT ON sale_detail FOR EACH ROW BEGIN INSERT INTO sale_summary(sale_date, total_quantity, total_amount) VALUES (NEW.sale_date, NEW.quantity, NEW.amount) ON DUPLICATE KEY UPDATE total_quantity total_quantity NEW.quantity, total_amount total_amount NEW.amount; END$$ DELIMITER ;这个写法的妙处在于ON DUPLICATE KEY UPDATE当天汇总记录不存在就新建一行存在就在原值基础上累加一条SQL语句同时覆盖了“插入”和“更新”两种状态不需要先查一遍汇总表。这是MySQL特有的语法写触发器时非常常用。需要提醒的是这个方案适合汇总逻辑简单、实时性要求高的场景。如果明细表一天有几十万行、又跑了很多批任务AFTER INSERT逐行累加的性能压力会很大那时候更合适的方案是离线批次聚合或定时任务而不是触发器。4. 触发器最容易踩的坑和调试经验4.1 在触发器里操作同一张表MySQL直接报错新手写触发器最容易犯的错是想在表A的触发器里再更新表A。MySQL对这个问题是零容忍的会直接报错提示触发器调用的语句正在使用同一张表禁止嵌套触发同表操作。常见的错误写法是-- 错误示范AFTER UPDATE 触发器里再去 UPDATE 同一张表 CREATE TRIGGER trg_bad_example AFTER UPDATE ON orders FOR EACH ROW BEGIN UPDATE orders SET updated_at NOW() WHERE id NEW.id; -- 报错 END;正确做法是如果只是想更新“某个字段”放在BEFORE UPDATE里直接给NEW.updated_at赋值DELIMITER $$ CREATE TRIGGER trg_orders_updated_at BEFORE UPDATE ON orders FOR EACH ROW BEGIN SET NEW.updated_at NOW(); END$$ DELIMITER ;因为BEFORE阶段行还没写入磁盘改NEW字段是安全的不需要再发一条UPDATE。这个差别虽然小却是我见过的触发器“误用重灾区”很多人绕不过这个弯。4.2 大批量导入时行级触发器的性能损耗MySQL的FOR EACH ROW意味着每一行都要执行一遍触发器逻辑。如果你运行一条INSERT INTO ... SELECT ...一次插了1万行触发器就会被执行1万次。我踩过一个很实在的坑一次线上数据回填任务原本跑完只要两分钟结果加了个审计触发器之后跑了40分钟整个库的并发被拖得很惨。后来学乖了大批量任务开始前先DROP TRIGGER或者用开关变量让触发器空跑任务结束再重建。如果你有数据仓库的汇聚场景我也建议尽量不要用触发器改用定时任务或增量ETL方案。4.3 触发器调试的可用手段触发器没法像普通代码那样打断点这是它最大的痛点。实际排错时我一般按下面几步来日志调试在触发器里临时往一张debug_log表插入关键变量的值跑完查询这张表定位问题。注意调试完成一定要删掉这段临时代码。查元数据用SHOW TRIGGERS\G;查看当前库所有触发器用SHOW CREATE TRIGGER trg_orders_update_log;查看某个触发器的创建语句确认线上版本是不是你想要的那个。查系统表用SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA 你的库名;精确过滤某个库的触发器清单。另外还有一个排查思路触发器报错信息里通常会带上触发器和对应SQL语句的信息看到SIGNAL抛出来的自定义消息就能快速知道是哪条业务规则被拦住了。4.4 SQL Server和MySQL的触发器差异对照如果你不是只写MySQL下面这张表建议收藏。SQL Server的触发器和MySQL有不少本质区别对比项MySQLSQL Server触发时机BEFORE / AFTERAFTER默认/ INSTEAD OF触发器粒度FOR EACH ROW行级语句级一条DML语句只触发一次新旧数据访问NEW/OLDINSERTED/DELETED同表同类多个触发器5.7.2支持按创建时间顺序触发支持多个触发顺序不保证逻辑体语法基于MySQL存储过程语法基于T-SQLSQL Server里同样实现“订单金额变更写日志”代码长这样CREATE TRIGGER trg_orders_updatelog ON orders AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO orders_log(order_id, action_type, old_amount, new_amount, old_status, new_status) SELECT d.id, UPDATE, d.amount, i.amount, d.status, i.status FROM INSERTED i INNER JOIN DELETED d ON i.id d.id; END;在SQL Server里INSERTED保存的是新行DELETED保存的是旧行一条UPDATE语句即使影响了100行这两个虚拟表里也有100条对应记录所以用集合操作去关联处理而不是像MySQL那样逐行处理。MySQL没有INSTEAD OF触发器SQL Server的INSTEAD OF触发器可以在不真正执行DML的情况下先接管整条语句由触发器自己决定做什么。比如写一个INSTEAD OF INSERT触发器在里面检查数据合法性再决定是真正插入还是抛错。这相当于变相实现了“先校验后操作”的控制流。最后分享一点个人体会触发器不是那种需要天天用的技术但它确实是数据库里不可替代的能力。我自己用了这么多年最深的感受是用之前先问自己一句——这条规则是不是“必须跟着数据走”如果是比如审计、防删、同步这类伴随性逻辑用触发器非常顺手如果只是某个业务流程里的一段临时逻辑宁可写在应用层别让数据库替你扛。另外每建一个触发器都要考虑到它会影响所有经过这张表的DML包括批量导入、后台任务、别人手动改数据维护文档里一定要写清“这张表挂了哪些触发器、各自什么作用”否则半年后没人看得懂触发器为什么存在。希望这篇能帮你把触发器的边界和使用姿势理清楚少踩我踩过的那些坑。