Create an Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create an Update Trigger

Author

Topic

lisateg
Starting Member

10 Posts

Posted - 2012-02-17 : 18:16:33

What is the correct syntax to write a trigger to update a CUST_BALANCE column in a CUSTOMER table when a new invoice record is entered?

I have the command if using Oracle:

CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE
AFTER INSERT ON INVOICE
FOR EACH ROW
BEGIN
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT
WHERE CUST_NUM = :NEW.CUST_NUM;
END;

But this isn't helpful for SQL Server. Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 18:36:58

I believe this is DB2, right?

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0000931.htm

Then there is dbforums

http://www.dbforums.com/db2/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lisateg
Starting Member

10 Posts

Posted - 2012-02-17 : 20:20:37

Not sure what DB2 is. I'm VERY new to this. I'm in an online class and our text uses Oracle to explain commands, but we're all working with SQL Server. I've got a question in to the intructor and his TA's and every other student in the class, but not a single person has responded with anything helpful for triggers. I'd like it spelled out so I can learn how to create this trigger and a few others.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 20:31:37

the query you posted wont work for Sql Server. If you want SQL Server related trigger syntax check this

http://msdn.microsoft.com/en-us/library/ms189799.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lisateg
Starting Member

10 Posts

Posted - 2012-02-17 : 21:08:34

Thanks for trying to help. I've seen the microsoft site, but I only started learning sql last week, so all the syntax it gives still leaves me scratching my head. I'm not sure what HAS to be included in the trigger and what's optional. I'll keep at it though and hope the instructor gets on the board and answers the question soon.

lisateg
Starting Member

10 Posts

Posted - 2012-02-17 : 23:52:59

Okay, I think I have the syntax for the trigger to work, but now I'm running into a problem with my SQL statement. Is there SQL Server syntax that does the same thing as :New in Oracle? If I want to use the statement:
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT
WHERE CUST_NUM = :NEW.CUST_NUM;
SQL Server doesn't recognize the :New portion. Does that make sense? I want a new balance to be calculated with the addition of a new invoice for a given customer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-18 : 15:25:39

in sql server the corresponding statement would be


UPDATE	c
SET	c.CUST_BALANCE = c.CUST_BALANCE + i.INV_AMOUNT
FROM CUSTOMER c
INNER JOIN INSERTED i
ON	i.CUST_NUM = c.CUST_NUM;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-18 : 16:29:51

In SQL Server triggers you have the inserted table which contain the new values (inserted rows or updated rows) and the deleted table which contains the old values (deleted rows or previous values for updated rows), and just like any other table you'd join them in to get the rows that were affected by the operation that fired the trigger.

--
Gail Shaw
SQL Server MVP

lisateg
Starting Member

10 Posts

Posted - 2012-02-18 : 16:58:03

Beautiful! Thanks visakh16! That was what I was looking for. And Gail, thanks for the additional explanation. That makes sense now!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-18 : 18:05:04

quote:
Originally posted by lisateg

Not sure what DB2 is. I'm VERY new to this. I'm in an online class and our text uses Oracle to explain commands, but we're all working with SQL Server. I've got a question in to the intructor and his TA's and every other student in the class, but not a single person has responded with anything helpful for triggers. I'd like it spelled out so I can learn how to create this trigger and a few others.

Ask for your money back

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lisateg
Starting Member

10 Posts

Posted - 2012-02-18 : 18:48:19

No kidding.

While I'm at it, I created another trigger, this time to update the quantity on hand for each product sold after a new line is added to my LINE table. Here's my trigger:

CREATE TRIGGER TRG_LINE_PROD
ON LINE
AFTER INSERT, UPDATE
AS
UPDATE P
SET P.P_QOH=(P.P_QOH - L.LINE_UNITS)
FROM PRODUCT P, LINE L
WHERE P.P_CODE=L.P_CODE;

But when I check it, it subtracts twice the amount!? Is there something wrong with my command somewhere that anyone can see?

lisateg
Starting Member

10 Posts

Posted - 2012-02-18 : 20:39:52

Okay, figured it out.

CREATE TRIGGER TRG_LINE_PROD
ON LINE
AFTER INSERT
AS
UPDATE P
SET P.P_QOH=(P.P_QOH - I.LINE_UNITS)
FROM PRODUCT P
INNER JOIN INSERTED I
ON I.P_CODE=P.P_CODE;

Worked for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 12:00:23

quote:
Originally posted by lisateg

Beautiful! Thanks visakh16! That was what I was looking for. And Gail, thanks for the additional explanation. That makes sense now!

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources