0

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?

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by MeSampath
0

Hi,

Modify the trigger like this way;

CREATE TRIGGER  TRG_UPDATECUSTBALANCE
ON INVOICE 
AFTER INSERT
[B]AS[/B] --newly added
UPDATE [B]Customer
SET CUST_BALANCE= CUST_BALANCE[/B] + I.INV_AMOUNT FROM CUSTOMER C, INSERTED I 
WHERE C.CUST_NUMBER= I.CUST_NUMBER

Changed codes are in green.

Good luck.

Thank you.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.