MariaDB UPDATE触发器误更新account_audit无关记录问题求解

问题根因 #

你编写的UPDATE触发器WHERE条件逻辑错误：OLD.acct_name = NEW.acct_name 是对比更新前后account表同一条记录的账户名，只要你没有修改账户名字段，这个条件永远为真，最终会匹配到account_audit表中所有valid_to = '2038-01-19'的记录，才会导致所有账户的审计记录都被更新。

你之前尝试将条件改为OLD.acct_id = NEW.acct_id无效的原因也相同：只要你没有修改acct_id主键字段，这个条件也永远为真，还是会匹配所有有效审计记录。

修正方案 #

只需要将UPDATE语句的WHERE条件改为关联account_audit表的账户字段，匹配当前被修改的账户即可，修正后的触发器代码如下：

DELIMITER //
CREATE TRIGGER testtrigger_upd 
AFTER UPDATE ON account
  FOR EACH ROW BEGIN
    -- 匹配审计表中当前被修改账户的有效记录
    UPDATE account_audit SET valid_to = NOW() 
    WHERE valid_to = '2038-01-19' AND acct_id = OLD.acct_id;
    INSERT INTO 
      account_audit (acct_id, acct_name, acct_balance, tax_rate) 
      VALUES 
      (
        OLD.acct_id, 
        OLD.acct_name, 
        NEW.acct_balance, 
        OLD.tax_rate
      );
END//
DELIMITER ;

补充说明 #

• 这里使用acct_id = OLD.acct_id作为匹配条件，acct_id是唯一主键，匹配精度比acct_name更高，不会出现重名误改的问题。
• 如果你的业务场景允许修改acct_name，也可以直接沿用这个条件，不需要调整，因为OLD.acct_id永远和被修改的账户唯一对应。
• 触发器保持AFTER UPDATE即可，不需要调整为BEFORE，避免出现事务时序问题。

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