MySQL触发器详解：自动执行、审计与数据同步的工程实践

开发这么多年，触发器一直是个让人又爱又恨的东西。MySQL 的触发器说白了就是数据库里的一段“自动反应程序”，你往表里插入一条数据、改一条数据、删一条数据，只要设定了对应的事件，它就会自己跑起来，不用你写业务代码去反复调用。今天把我这些年用 MySQL 触发器的经验整理出来，从概念、语法到实际建表、踩坑，一次性讲清楚。这篇内容适合刚上手 MySQL 的初学者，也适合那些想把触发器用到生产环境但心里没底的开发者。

1. 触发器到底是个什么东西

1.1 触发器的本质：数据库里的“事件监听器”

很多同学第一次接触触发器时，容易把它和存储过程搞混。存储过程是你主动调用它，它才会执行；触发器不一样，它是被动的，只要表上发生了你预先定义好的增删改操作，它就自动触发，像寄居蟹一样附着在表上面。它就像一个门口装好的感应器，人一进门灯就亮，你不需要手动去按开关。

MySQL 的触发器从 5.0 版本开始就支持了，到现在依然是基于行的触发器，也就是FOR EACH ROW。这个设计决定了它和 SQL Server 或 Oracle 的语句级触发器有本质区别——MySQL 的触发器是“每一行受影响都会触发一次”。比如你用一条 UPDATE 语句同时改了 100 行数据，那这 100 行会依次触发 100 次触发器。理解这一点非常重要，因为很多性能问题都源于此。

触发器本身是存储在数据字典中的命名对象，它不属于应用层，也不属于某个具体的客户端会话，而是属于某个表。也就是说，不管你是用 Navicat 操作、用 JDBC 操作，还是在命令行里敲 SQL，只要对这张表做了符合条件的变更，触发器都会生效。这也是它的一大优势：逻辑收敛在数据库端，不依赖业务代码的调用路径。

1.2 触发器能解决什么问题：从审计到同步

触发器最典型的应用场景是审计日志。比如说一张订单表，业务上要求记录每一次金额修改的操作人、修改前金额、修改后金额、修改时间，如果靠应用层去写，你得在每个修改接口里都加上日志逻辑，很容易漏。用触发器的话，直接在订单表上挂一个 AFTER UPDATE 触发器，任何渠道进来的修改都会被记录，一条都跑不掉。

第二个常用场景是冗余字段或统计数据的维护。比如用户表和订单统计表，每当订单表插入一条数据，就自动更新用户表的订单数和累计消费金额。这种逻辑放在触发器里，能保证统计数据的实时性和一致性，不用等定时任务去跑。

第三个场景是跨表数据同步。比如一个商城系统，商品表在核心库里，搜索库需要一份精简的商品副本，你可以用触发器在商品表发生变更时自动更新搜索库的表。不过这里有个前提：触发器和目标表必须在同一个实例上。跨数据库实例的同步，触发器做不了，得用 Canal、Flink 这类工具，这也是大家在设计架构时要注意的边界。

2. 语法细节与设计要点

2.1 创建触发器的基础语法

先看 MySQL 8.0 创建触发器的标准语法：

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW trigger_body;

这里的trigger_body可以是一条单独的 SQL 语句，也可以是以BEGIN ... END包裹的复合语句块。由于多数实际场景都需要多条语句或多条件判断，几乎都是写成复合语句块。

我来写一个完整的例子。假设我们有两张表：一张商品库存表product，一张订单明细表order_item。每次往订单明细表插入一条记录时，希望自动扣减对应商品的库存。触发器的写法如下：

DELIMITER // CREATE TRIGGER trg_order_item_after_insert AFTER INSERT ON order_item FOR EACH ROW BEGIN UPDATE product SET stock = stock - NEW.quantity WHERE id = NEW.product_id; END// DELIMITER ;

这里有个特别关键的操作：DELIMITER //。因为触发器主体内部有分号，而 MySQL 默认以分号作为 SQL 语句的结束符，如果不临时把分隔符改成//，MySQL 会在第一个分号处就结束整个创建语句，导致语法报错。每次创建完，记得用DELIMITER ;改回来。很多新手第一次写触发器就卡在这一步，在 Navicat 里直接写也不行，就是因为分隔符问题。

2.2 BEFORE 和 AFTER 该怎么选

BEFORE 和 AFTER 的区别，从字面看是触发时机，但实际设计时要考虑的是：你希望在数据变更生效之前拦截它，还是在变更生效之后响应它。

BEFORE 触发器的典型用途是数据校验和默认值填充。举个例子，订单表里要求订单金额不能为负数，如果插入一个负数，业务代码没有拦住，那么 BEFORE INSERT 触发器可以主动抛错，让这条插入失败。另一个常见场景是用触发器自动填充某些字段，比如创建时间，虽然现在 MySQL 已经支持 DEFAULT CURRENT_TIMESTAMP，但一些老的表结构没建好，可以用 BEFORE INSERT 触发器补上。

AFTER 触发器的典型用途是审计日志、同步、统计更新。此时数据已经写入或修改完成，触发器里做任何操作都不会影响主表当前行的写入结果。比如记录“谁在什么时候把价格从 100 改成了 120”，用 AFTER UPDATE 就是标准做法。

再强调一个容易忽略的点：BEFORE 触发器里你可以修改 NEW 字段的值，但 AFTER 触发器里修改 NEW 字段的值没有任何意义，因为行写入已经完成，改动不会回写。如果你需要在触发器里修正数据，必须在 BEFORE 阶段做。

2.3 NEW 和 OLD 关键字的使用

NEW 和 OLD 是触发器里的两个虚拟行，代表变更前和变更后的数据。

• INSERT 触发器：只有 NEW，没有 OLD，NEW 就是要插入的那一行。
• DELETE 触发器：只有 OLD，没有 NEW，OLD 就是要被删除的那一行。
• UPDATE 触发器：NEW 是更新后的行，OLD 是更新前的行，两个都有。

使用场景非常直观。比如更新价格时要记录旧价格和新价格，就是OLD.price和NEW.price。需要注意的是，NEW 里的字段可以直接赋值修改，比如SET NEW.status = 'PAID'，这在 BEFORE INSERT 和 BEFORE UPDATE 触发器里都合法。而 OLD 字段是只读的，不能修改。

DELIMITER // CREATE TRIGGER trg_order_before_insert BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单金额不能为负数'; END IF; IF NEW.created_at IS NULL THEN SET NEW.created_at = NOW(); END IF; END// DELIMITER ;

这里用了SIGNAL SQLSTATE '45000'，这是 MySQL 5.5 以后在触发器里主动抛异常的标准做法。45000 是用户自定义异常的标准状态码，抛出去之后，这条 INSERT 语句会直接失败，错误信息就是你设置的 MESSAGE_TEXT。这个技巧在数据保护场景里非常实用。

2.4 触发器的组合与局限性

一个表上可以定义多个触发器，同一类事件和时机也可以有多个触发器。比如既可以有一个 BEFORE INSERT 触发器做字段校验，也可以有一个 AFTER INSERT 触发器做日志记录，它们互不冲突。在 MySQL 5.7 及之前，相同时机和事件的多个触发器执行顺序是不确定的，从 MySQL 5.7.2 开始，可以用FOLLOWS和PRECEDES关键字控制多个相同类型触发器的顺序。

CREATE TRIGGER trg_a AFTER INSERT ON t FOR EACH ROW ...; CREATE TRIGGER trg_b AFTER INSERT ON t FOR EACH ROW FOLLOWS trg_a;

如果你想看的更细，MySQL 官网也有关于触发器限制的说明：触发器不能直接在 MySQL 的临时表上创建，也不能在存储过程和函数里显式地调用或管理触发器。不过最让人头疼的限制是，触发器内部对同表不能再做触发器的触发操作，否则会陷入递归调用，MySQL 默认通过限制来阻断这种情况，但跨表递归这类“间接递归”仍然可能出现。

3. 实操：从零实现订单库存扣减和审计日志

3.1 准备工作：建表与初始化数据

这一节我带你完整走一遍流程，从建表到验证，你能直接照着抄。

先创建商品表和订单表，商品表包含库存字段，订单表包含购买数量字段。我故意把字段设计得简单一点，重点是演示触发器的逻辑。

CREATE DATABASE IF NOT EXISTS demo_trigger; USE demo_trigger; CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, stock INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB; CREATE TABLE order_item ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, quantity INT NOT NULL, created_at DATETIME NOT NULL ) ENGINE=InnoDB; INSERT INTO product (name, stock) VALUES ('iPhone 15', 100), ('MacBook Pro', 50);

然后再建一张审计日志表，用来记录订单表的每一次插入：

CREATE TABLE order_item_log ( id INT PRIMARY KEY AUTO_INCREMENT, log_time DATETIME NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, action VARCHAR(10) NOT NULL ) ENGINE=InnoDB;

3.2 创建扣库存触发器

我们要保证一个核心逻辑：插入订单明细后，商品库存自动减少相应数量。这里用了 AFTER INSERT，因为如果插入失败，不应该扣库存；插入成功之后再扣，即使扣库存的语句意外出错，订单本身还是立得住。

DELIMITER // CREATE TRIGGER trg_order_item_after_insert AFTER INSERT ON order_item FOR EACH ROW BEGIN UPDATE product SET stock = stock - NEW.quantity WHERE id = NEW.product_id; END// DELIMITER ;

是否加入库存充足校验，取决于业务需要。如果库存不足时希望订单直接插入失败，那就应该把检查放在 BEFORE INSERT 里，或者像下面这样做成“插入前校验库存 + 插入后扣减”的组合。我个人更建议在 BEFORE 阶段校验库存不足就抛异常，否则数据会被写进去，然后触发器又把库存扣成负数，语义上很难看。

3.3 创建带校验的入库触发器

把上面的逻辑升级一下：插入订单时先判断库存是否够用，不够就直接抛错，够用再扣库存。注意校验必须放在 BEFORE INSERT，因为这样才能阻止本次插入生效。

DELIMITER // CREATE TRIGGER trg_order_item_before_insert BEFORE INSERT ON order_item FOR EACH ROW BEGIN DECLARE current_stock INT; SELECT stock INTO current_stock FROM product WHERE id = NEW.product_id; IF NEW.quantity > current_stock THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足，无法下单'; END IF; END// DELIMITER ;

这里有一个实战细节要提醒：BEFORE 触发器里查商品表的当前库存，和 AFTER 触发器里扣减库存，其实是两个独立的操作，在并发场景下可能存在时间窗口。比如两个事务同时读取到库存为 10，各自都通过了校验，然后同时扣减，最终库存变成负数。要彻底解决这个问题，应该使用事务、锁或者把校验和扣减放到同一个带锁的查询里。生产环境如果涉及并发下单，最好还是把库存扣减做成原子操作，比如UPDATE product SET stock = stock - 1 WHERE id = ? AND stock >= 1，而不是依赖触发器里先查后改。这点后面我在坑的部分还会细讲。

3.4 创建审计日志触发器

接下来给 order_item 表加上 AFTER INSERT 日志触发器，记录每一次插入的数据来源和操作时间。这个触发器能完美演示 NEW 关键字的作用：

DELIMITER // CREATE TRIGGER trg_order_item_after_insert_log AFTER INSERT ON order_item FOR EACH ROW BEGIN INSERT INTO order_item_log (log_time, product_id, quantity, action) VALUES (NOW(), NEW.product_id, NEW.quantity, 'INSERT'); END// DELIMITER ;

有同学会问：同一张表既有扣库存的 AFTER INSERT 触发器，又有记录日志的 AFTER INSERT 触发器，两个会不会互相干扰？不会。它们都在同一事件后执行，但各干各的，互不依赖。唯一要注意的是它们的执行顺序，如果日志里还想包含扣减后的库存，那就要确保日志触发器在扣库存触发器之后执行。MySQL 5.7.2 之后可以用FOLLOWS指定顺序，5.7 之前的版本顺序不可控，所以设计上最好不要让触发器之间有顺序依赖。

3.5 验证触发器是否生效

创建完触发器之后，一定要实际验证，不能写完就以为万事大吉。先插入一条正常订单：

INSERT INTO order_item (product_id, quantity, created_at) VALUES (1, 3, NOW()); SELECT id, name, stock FROM product; SELECT * FROM order_item_log;

按照预期，商品 1 的库存应该从 100 变成 97，日志表里应该有一条记录。再测试库存不足的情况，把商品 2 的现有库存改为 2，然后插入数量为 5 的订单，应该报错：

UPDATE product SET stock = 2 WHERE id = 2; INSERT INTO order_item (product_id, quantity, created_at) VALUES (2, 5, NOW());

这条语句会触发 BEFORE INSERT 触发器，抛出的错误信息应该是“库存不足，无法下单”。如果它没有报错，说明触发器没有被正确创建或没有被启用，这时需要用下面的命令排查：

SHOW TRIGGERS LIKE 'order_item'; SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, STATUS FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE = 'order_item';

第一条命令能看到数据库里所有触发器，第二条命令可以从系统表中查询触发器的元数据信息。设置成STATUS为 ACTIVE 才是正常启用的状态。如果触发器没有执行，优先检查表名拼写、事件类型和触发器是否属于当前数据库，这些我在下一章细讲。

3.6 触发器的删除与修改

触发器的修改比较特殊，MySQL 没有提供类似ALTER TRIGGER的语句，你必须先 DROP 再重新 CREATE。如果只是测试阶段，可以用下面的命令删除：

DROP TRIGGER IF EXISTS trg_order_item_before_insert;

也可以直接查出触发器并用动态 SQL 拼出来。不过在 Navicat 里更简单，选中表对象下的触发器节点，右键就能查看或者删除。需要注意的是，DROP 触发器需要具备对应表的 TRIGGER 权限，生产环境里别给普通应用账号配这个权限，否则应用被入侵后攻击者可以改动触发器，进而获得更大的操作空间。

4. 我在实战中踩过的坑与排查方法

4.1 触发器没生效，先查这几处

触发器不触发，是后台开发最常见的问题。第一，确认触发器是建在正确的库和正确的表上。如果数据库连接里用了demo_trigger，但触发器建在了别的库，当然不会触发。第二，确认你触发的事件类型是否和触发器的定义一致。很多人创建的是 BEFORE INSERT 触发器，却用 UPDATE 语句去测，自然没反应。第三，确认当前会话是否启用了触发器。MySQL 没有全局的触发器开关，但有一种极容易忽略的情况：如果表是通过只读账号访问的，触发器内部更新其他表时没有足够权限，那么整条 SQL 会报错而不是静默失败。这种报错往往会被上层应用吞掉，看起来就像触发器没执行。

我在排查时习惯先跑一遍SHOW TRIGGERS，逐个检查触发器的定义语句。如果发现数据库里根本没有这个触发器，多半是当时创建时出了语法错误，又被 Navicat 的弹窗给吞了。建议创建时直接用命令行执行，报错信息更直观。

4.2 递归调用和交叉触发：死循环怎么排查

触发器里最阴间的坑，就是递归和交叉触发。比如表 A 的触发器更新表 B，表 B 的触发器又更新表 A，这样两边就会你触发我、我触发你，直到 MySQL 检测到递归深度超限，报出ERROR 1442: Can't update table 'xxx' in stored function/trigger because it is already used by statement which invoked this stored function/trigger才停。

出现这个错误时，第一反应先别去优化 SQL，而是看看是不是触发器链路里出现了环。MySQL 默认不允许触发器在修改当前表的同时再次修改当前表，比如在 order_item 的 AFTER INSERT 触发器里再次 INSERT 或 UPDATE order_item，就会直接报错。但跨表的间接循环，MySQL 有时候检测不到，会变成无限循环，直到把连接拖垮。

我的建议是：触发器里只写简单的、单向的数据变更，尽量避免触发器再去操作第三张带有复杂业务逻辑的表。如果确实需要维护多张表的联动，优先把逻辑挪到应用层或事务里，用显式事务保证一致性，而不是让触发器互相咬。

4.3 性能问题：行级触发器的隐形炸弹

任何触发器都不能忽略性能问题。MySQL 的触发器是 FOR EACH ROW，一次 UPDATE 影响一万行，就会执行一万次触发器体。如果触发器里还有子查询、UPDATE 其他大表，那这个操作的耗时不是线性增长，而是几何级增长。我见过一个极端案例：一条影响 2000 行的 UPDATE 语句，因为触发器里执行了 3 次带全表扫描的查询，跑了将近 40 秒才结束。业务方一开始还以为是 MySQL 慢，后来一查，全是触发器拖的。

要避免这个问题，有几个要点：

• 触发器体内尽量只使用主键或索引字段来定位目标行，避免全表扫描。
• 触发器体内不要再写复杂的业务判断，能拆的尽量拆出去。
• 高写入并发的核心表，比如订单表、消息表，不建议直接挂重量级触发器，审计可以考虑使用 binlog 或消息队列来做。
• 可以用EXPLAIN单独分析触发器内的 SQL，确认走了索引。

4.4 主从复制和触发器一起用会出事

主从复制环境下，触发器的行为要格外小心。假设主库的订单表有个 AFTER INSERT 触发器，它负责更新统计表。这个触发器在主库执行了，产生了一条对统计表的 UPDATE 修改，而这条修改又会进入 binlog。当从库回放 binlog 时，会再次执行订单表 INSERT 对应的操作。如果从库上也存在同名触发器，那么从库的触发器会再次触发，对统计表再更新一次，最终导致主从数据不一致。

解决思路有两种：第一种是只在主库上保留触发器，从库上不建触发器。第二种是把触发器逻辑统一迁移到应用层，让所有实例都执行同样的逻辑。更严谨的做法是，在从库上关闭二进制日志记录，也就是启用log_replica_updates参数并合理配置，但这属于运维层面的策略，需要结合具体架构来定。这里只是提醒你，触发器不是单纯“写在数据库里”就完事，它和复制拓扑是强相关的。

4.5 常见问题速查表

4.6 一点额外的建议：让触发器替你把关数据，而不是把触发器当成业务主力

最后想认真说一句：触发器是个好东西，但它更像“最后一道防线”和“自动化小助手”，不应该变成业务逻辑的主力承载者。比如做主数据校验，应用层做一遍，BEFORE 触发器再做一遍；做审计日志，应用层可能漏，AFTER 触发器兜底。这种组合打法才是合理的。如果把所有业务规则都塞进触发器，后期维护成本极高，调试一个线上问题要同时看应用代码和数据库对象，排错链路长到让人崩溃。

从我个人的实操经验看，触发器设计越简单越安全。一个触发器只干一件事，命名能说明用途，比如trg_表名_事件_用途这种格式，半年后你再回来看这些代码，还能一眼看懂。另外一定记得做好版本管理，触发器的创建脚本要放进代码仓库，和表结构变更一起走流程，不能只在某个同事的 Navicat 里存着。数据库结构有变更时，顺手查一下关联的触发器是否受影响，比如表字段改名了，触发器里的 NEW.字段名和 OLD.字段名没有同步更新，那就会出现运行时报错。用information_schema.TRIGGERS定期导出触发器定义备份，是个成本极低但非常管用的习惯。

如果后续你的项目进入微服务架构，表已经按服务拆分到不同库，或者数据量上来了需要做分库分表，那时候触发器能发挥的余地会越来越小，同步和校验的任务更多会交给 Canal、Flink CDC 或者消息队列去处理。但这不代表触发器该被丢掉，在单体应用、中小规模系统、强审计需求等场景下，它依然是最直接、最省事的方案之一。理解它的底层机制和边界，你就能在合适的场景里放心用它，在它不该出现的地方果断绕开。