含关联与IF语句的SQL触发器问题：逾期付款时更新发票金额

需求 #

新增付款记录时，检查对应发票的due_date（到期日）是否早于付款的payment_date（付款日期）；若是，将发票的overdue_fee（逾期费）加到amt_due_left（剩余应付款）中，并将overdue_fee置为0，避免后续重复累加逾期费。

表结构与测试数据 #

-- 发票表
create table invoice(
invoice_id DECIMAL(3),
invoice_date DATE,
due_date DATE,
overdue_fee DECIMAL(10,2),
amt_due_left decimal(12,2),
PRIMARY KEY(invoice_id));

INSERT INTO invoice VALUES 
(1,'2020-11-02','2020-11-05',15,120.24),
(2,'2020-11-02','2020-11-05',35,200.00),
(3,'2020-11-02','2020-11-05',150,1300.00),
(4,'2020-11-02','2020-11-05',120,1200.00);

-- 付款表
create table payments(
payment_id int, 
invoice_id decimal(3),
payment_type varchar(40),
amnt_recived decimal(12,2),
payment_date Date,
primary key (payment_id),
CONSTRAINT fk_has_invoice_id
FOREIGN KEY(invoice_id)REFERENCES invoice(invoice_id));

insert into payments values
(1,1,"credit_card",120.24,'2020-11-03' ),
(2,2,"cash",200,'2020-11-03' ),
(3,3,"debit",1200.00,'2020-11-03' ),
(4,4,"cash",1200.00,'2020-11-03' );

原触发器的问题 #

你编写的两个触发器存在以下核心问题：

1. 第一个触发器：WHERE条件仅判断了日期，未关联当前付款对应的invoice_id，会错误更新所有逾期的发票，而非当前付款对应的那一张。
2. 第二个触发器：语法错误，在UPDATE语句中使用If的写法不符合MySQL语法规则，同时同样缺少invoice_id关联条件。

修正后的触发器代码 #

DELIMITER $$
CREATE TRIGGER Late_payment AFTER INSERT ON payments FOR EACH ROW
BEGIN
   UPDATE invoice
   SET
       amt_due_left = amt_due_left + overdue_fee,
       overdue_fee = 0 
   WHERE 
       invoice.invoice_id = NEW.invoice_id -- 关联当前付款对应的发票
       AND NEW.payment_date > invoice.due_date; -- 仅处理逾期付款的情况
END $$ 
DELIMITER ;

修正说明 #

• 新增invoice.invoice_id = NEW.invoice_id条件，确保仅更新当前付款对应的发票，避免批量修改错误数据。
• 保留日期判断条件，确保只有当付款日期晚于发票到期日时才执行逾期费累加和清零操作。
• 逻辑上先累加逾期费到剩余应付款，再将逾期费置0，避免后续付款时重复计算。

测试验证 #

插入一条逾期付款记录测试：

INSERT INTO payments VALUES (5, 1, "cash", 135.24, '2020-11-06');

执行后查看invoice表，发票ID=1的amt_due_left会从120.24变为135.24（120.24+15），同时overdue_fee变为0，符合需求。

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