I am trying to write a trigger that updates a column called CUST_BALANCE in a table called CUSTOMER when new data is entered into another table called INVOICE. What I want to do is when new data is inserted into INVOICE it will take the value in column INV_AMOUNT and add that to CUST_BALNCE in the CUSTOMER TABLE. Here is what I thought of
CREATE TRIGGER TRG_UPDATECUSTBALANCE//create the trigger ON INVOICE //on the invoice table AFTER INSERT//after ned data it inserted UPDATE C//update customer table SET C.CUST_BALANCE= C.CUST_BALANCE+ I.INV_AMOUNT//take cust_balance and add in INV_AMOUNT FROM CUSTOMER C, INSERTED I //from inserted and customer tables WHERE C.CUST_NUMBER= I.CUST_NUMBER//Where customer number is the same
The error I am getting is that Update is being used in the wrong syntax. What am I doing wrong and how do I fix it?