I have this trigger in sqlite which is giving invalid statement error I am trying to update the table orders after a payment has been inserted. The trigger looks for all payment with the payments orderid, sums it, and update the total in the orders
payments table
Amount
ID
OrderID
Orders Table
ID
Paid
Bal
The trigger
CREATE TRIGGER [update_order_total]
AFTER INSERT
ON Payments
FOR EACH ROW
BEGIN
UPDATE Orders Set Orders.Paid =(Select SUM (Amount) From Payments Where OrderID = Orders.id ) WHere id = new.ID;
UPDATE Orders Set Orders.Bal = Orders.Paid - (Select SUM (Amount) From Payments Where OrderID = Orders.id ) WHere id = new.ID;
END
EDIT
I made edit to the trigger, the first statements now works, but the second update statement doesn't
CREATE TRIGGER [update_order_total]
AFTER INSERT
ON Payments
FOR EACH ROW
BEGIN
UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID = Orders.ID) WHere Orders.id = new.ID;
UPDATE Orders Set Bal = Paid - (Select SUM (Amount) From Payments AS p Where p.OrderID = Orders.ID) WHere Orders.id = new.ID;
END
Please what am I doing wrong?
Aucun commentaire:
Enregistrer un commentaire