MySQL触发器执行时误将其他账户Balance设为NULL的问题排查

你的触发器存在两个核心问题：

1. 无过滤的全表更新：触发器里的UPDATE Accounts语句没有指定WHERE条件，会对Accounts表的所有行执行更新操作，而不是只针对被修改P_Location的用户。
2. CASE语句缺少ELSE分支：当账户对应的Person的P_Location不在3、7、15、1这四个值范围内时，CASE表达式没有匹配的分支，会返回NULL，直接覆盖这些账户原有的Balance值。

当你执行UPDATE Person SET P_Location = 3 WHERE id = 1;时：

• 对于Person_id=1的账户，CASE匹配到第一个分支，Balance被设为2000-150=1850，结果正确。
• 对于Person_id=2和3的账户，它们对应的P_Location分别是10和60，不在CASE的匹配条件里，CASE返回NULL，导致这两个账户的Balance被设为NULL。

CREATE TRIGGER Bonuses
AFTER UPDATE ON Person
FOR EACH ROW
UPDATE Accounts
SET Balance = CASE 
                   WHEN NEW.P_Location = 3 THEN Balance - 150
                   WHEN NEW.P_Location = 7 THEN Balance + 100
                   WHEN NEW.P_Location = 15 THEN Balance - 30
                   WHEN NEW.P_Location = 1 THEN Balance + 200
                   ELSE Balance -- 保持原余额不变
               END
WHERE Person_id = NEW.id; -- 仅更新当前被修改的用户账户

• 添加WHERE Person_id = NEW.id：确保触发器只更新刚刚修改了P_Location的用户对应的账户，不会影响其他无关账户。
• 使用NEW.P_Location替代子查询：直接引用触发器中更新后的P_Location值，不需要再去Person表查询，效率更高且避免歧义。
• 增加ELSE Balance：当P_Location不在目标值范围内时，保持原Balance不变，防止被设为NULL。

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