mercredi 25 novembre 2015

update Trigger gives invalid statement

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