触发器对目标表特定行效果翻倍的原因排查

触发器代码 #

CREATE TRIGGER Go_System
AFTER INSERT ON Half_Trail
FOR EACH ROW
UPDATE Accounts
SET Balance = CASE WHEN NEW.Post_Roll_Position IN (3,5,7,9,11,13,15) THEN Balance
                    WHEN NEW.Pre_Roll_Position > NEW.Post_Roll_Position THEN Balance + 200
                    WHEN NEW.Pre_Roll_Position < NEW.Post_Roll_Position THEN Balance
                    END
WHERE Player_id = NEW.Player_id;

场景与问题 #

当前遇到的问题是：部分行触发后余额被翻倍。插入指定数据后：

初始表状态

Accounts表

Half_Trail表（插入后）

按照规则：

• 若Post_Roll_Position在(3,5,7,9,11,13,15)中，余额不变；
• 若Pre_Roll_Position > Post_Roll_Position，余额加200；
• 若Pre_Roll_Position < Post_Roll_Position，余额不变。

预期Player_id=3的余额应为150+200=350，但实际结果是550，相当于被加了两次200。

问题原因 #

核心是触发器被重复触发了两次，常见诱因包括：

1. 递归触发器开启：多数数据库（如SQL Server、MySQL 8.0+默认）允许递归触发，如果Accounts表的更新操作间接触发了Half_Trail表的插入/修改（哪怕是其他关联触发器导致），会形成循环触发，同一插入事件会多次执行触发器逻辑。
2. 重复执行插入操作：可能无意中执行了两次插入语句但未察觉；不过根据问题描述，更倾向于触发器递归的问题。
3. 数据库引擎对AFTER触发器的特殊处理：部分引擎在特定场景下（如事务嵌套）可能会重复触发AFTER类型触发器。

解决方案 #

1. 禁用递归触发器

• SQL Server：执行语句关闭当前数据库的递归触发开关

  ALTER DATABASE YourDatabaseName SET RECURSIVE_TRIGGERS OFF;
  

• MySQL 8.0.16+：创建触发器时添加NOT RECURSIVE关键字

  CREATE TRIGGER Go_System
  AFTER INSERT ON Half_Trail
  NOT RECURSIVE
  FOR EACH ROW
  UPDATE Accounts
  SET Balance = CASE 
                    WHEN NEW.Post_Roll_Position IN (3,5,7,9,11,13,15) THEN Balance
                    WHEN NEW.Pre_Roll_Position > NEW.Post_Roll_Position THEN Balance + 200
                    ELSE Balance -- 覆盖等于的情况，逻辑更严谨
                END
  WHERE Player_id = NEW.Player_id;
  

2. 排查循环触发链路

检查是否存在其他触发器，比如Accounts表的UPDATE触发器是否会向Half_Trail表插入数据，形成触发闭环。

3. 优化触发器逻辑

将CASE语句中的最后一个WHEN改为ELSE，覆盖Pre_Roll_Position = Post_Roll_Position的场景，避免逻辑遗漏。

内容的提问来源于stack exchange，提问作者Abubakar Popoola