MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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-02',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));


Records: 4  Duplicates: 0  Warnings: 0


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 
    new.payment_date > invoice.due_date
  AND new.invoice_id = invoice.invoice_id;
END
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' );

Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM invoice


invoice_id
invoice_date
due_date
overdue_fee
amt_due_left


1
2020-11-02
2020-11-05
15.00
120.24

2
2020-11-02
2020-11-05
35.00
200.00

3
2020-11-02
2020-11-02
0.00
1450.00

4
2020-11-02
2020-11-05
120.00
1200.00

invoice_id	invoice_date	due_date	overdue_fee	amt_due_left
1	2020-11-02	2020-11-05	15.00	120.24
2	2020-11-02	2020-11-05	35.00	200.00
3	2020-11-02	2020-11-02	0.00	1450.00
4	2020-11-02	2020-11-05	120.00	1200.00