MySQL触发器执行异常但关联更新正常，求解决方法

问题场景 #

现有invoice（发票）和payments（支付）表结构及数据均正常，手动执行以下关联UPDATE语句时，能正确更新invoice表的amt_due_left（剩余应缴金额）字段，得到预期结果：

正确的手动更新SQL #

UPDATE invoice
    INNER JOIN
    payments ON invoice.invoice_id = payments.invoice_id
    set
    amt_due_left = amt_due_left - amnt_recived
    where 
    invoice.invoice_id = payments.invoice_id AND amt_due_left > 0;

预期正确结果 #

• 发票1: '1','2020-11-02','2020-11-05','15.00','0.00'
• 发票2: '2','2020-11-02','2020-11-05','35.00','0.00'
• 发票3: '3','2020-11-02','2020-11-05','150.00','100.00'
• 发票4: '4','2020-11-02','2020-11-05','120.00','0.00'

但创建以下AFTER INSERT触发器后，invoice表的amt_due_left出现负数异常：

错误的触发器SQL #

DELIMITER $$

CREATE TRIGGER trigger_rmv_payment after insert ON payments FOR EACH ROW
BEGIN
   UPDATE invoice
    inner JOIN
    payments ON invoice.invoice_id = payments.invoice_id
    set
    amt_due_left = amt_due_left - amnt_recived
    where 
    invoice.invoice_id = payments.invoice_id ;
END$$    

DELIMITER ;

执行触发器后的异常结果 #

• 发票1: '1','2020-11-02','2020-11-05','15.00','-360.72'
• 发票2: '2','2020-11-02','2020-11-05','35.00','-400.00'
• 发票3: '3','2020-11-02','2020-11-05','150.00','-1100.00'
• 发票4: '4','2020-11-02','2020-11-05','120.00','0.00'

错误原因 #

原触发器的问题在于：每次插入新支付记录时，它会关联整个payments表更新所有匹配的发票余额，相当于把该发票的所有历史支付金额重复减去，导致余额被多次扣除后变成负数。而手动执行的UPDATE是一次性计算所有支付的总和来更新，但触发器是行级触发，每次只需要处理当前插入的那笔支付。

修复后的触发器 #

使用NEW关键字获取当前插入的支付记录的invoice_id和amnt_recived，仅更新对应发票的余额，同时保留amt_due_left > 0的条件避免出现负数：

DELIMITER $$

CREATE TRIGGER trigger_rmv_payment AFTER INSERT ON payments FOR EACH ROW
BEGIN
    UPDATE invoice
    SET amt_due_left = amt_due_left - NEW.amnt_recived
    WHERE invoice.invoice_id = NEW.invoice_id AND amt_due_left > 0;
END$$

DELIMITER ;

该触发器每次只针对新插入的单笔支付操作，找到对应发票并减去本次收到的金额，和手动执行的关联UPDATE效果完全一致，不会重复扣除历史支付金额。

内容的提问来源于stack exchange，提问作者John Patrick Box