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?
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