Member Avatar for aaronmk2

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
  • 112 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by MeSampath
Member Avatar for MeSampath

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.