MySQL trigger fails but union works whats up?-CodePudding

so this is the invoice table spick and span works fine

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

so this is the payments table spick and span works fine

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


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

now for the fun part this is the union

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;

this union dose exactly whats its designed to and the results are this for the invoice table
'1','2020-11-02','2020-11-05','15.00','0.00'
'2','2020-11-02','2020-11-05','35.00','0.00'
'3','2020-11-02','2020-11-05','150.00','100.00'
'4','2020-11-02','2020-11-05','120.00','0.00'

now this is the established trigger I removed the amount due left > 0 so it shows the true results

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 ;

these are the results from the trigger
'1','2020-11-02','2020-11-05','15.00','-360.72'
'2','2020-11-02','2020-11-05','35.00','-400.00'
'3','2020-11-02','2020-11-05','150.00','-1100.00'
'4','2020-11-02','2020-11-05','120.00','0.00'

I was expecting the two to be the same I've gone through hundreds of iterations and it will always throw this error, I've used NEW. and changes, and still nothing. I just want the Union to do the same thing as the trigger any help?

CodePudding user response：

Since you trigger runs for each row inserted to payments, you don't need an additional reference to the payments table in the trigger, it is enough to simply reference NEW.column within the statement, e.g.

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;
END

I will however reiterate what I said in a comment, and storing values that can be calculated is generally not a good idea, unless you have a very good reason to such as it taking too long to run the calculations when needed - but even then I would argue that is what an OLAP environment is for, and you could keep your OLTP environment clean. As it stands your trigger might work for inserts, but you've not considered updates, or deletes. You're overwriting your existing data so once a payment is made the original invoice amount is lost forever. Triggers can also be disabled, and as soon as you start to get out of sync with this approach you have a reconciliation nightmare on your hands. I would really, really, really strongly advise to use a view or similar to calculate the amount due as and when it is needed, since it relies on only the underlying data there is no chance the amount due can ever by out of sync with what is in the invoice/payment table:

CREATE VIEW InvoicePayment
AS
SELECT  i.invoice_id,
        i.invoice_date,
        i.due_date,
        i.amt_due_left AS OriginalAmount,
        p.amount AS AmountPaid,
        i.amt_due_left - p.amount AS amt_due_left
FROM    invoice AS i
        LEFT JOIN 
        (  SELECT invoice_id, SUM(amnt_recived) AS amount
           FROM payments
           GROUP BY invoice_id
        ) AS p
          ON p.invoice_id = i.invoice_id