I am using two tables. The first is the invoice table the second is the payments.

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

After creating these two tables I was wanting a trigger that would be able to check to see if invoices "due_date" had passed at the time of the payments "payment_date". If so it would then add invoices "overdue_fee" to invoices "amt_due_left" and set invoices "overdue_fee" equal to zero. this is so if you enter another late payment it dose not double up the invoices "overdue_fee"

so far this trigger works but I cannot figure out the if statement to asses only the rows with payments "payment_date" passed invoices "due_date".

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 
    new.payment_date > invoice.due_date;
END $$ 
DELIMITER ;

This was my best attempt for over 5 hours. If another more enlightened would help me that would be much appreciated. also, I know the need for a shorthand in my example is not necessary and I intend on going back and redoing all attribute names at the end of the project.

DELIMITER $$
CREATE TRIGGER Late_payment after insert ON payments FOR EACH ROW
BEGIN
   UPDATE invoice
   If new.payment_date > invoice.due_date;
    set
    amt_due_left = amt_due_left   overdue_fee,
    overdue_fee = 0 
  
    where 
    new.payment_date > invoice.due_date;
    
END 
DELIMITER ;

CodePudding user response：

You need to inlcude the invoice_id to get the correct row

i change invoice number 3 to show you thatot works

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

fiddle