Trigger update - 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

Trigger update

Author

Topic

bobred
Starting Member

14 Posts

Posted - 2011-06-18 : 06:31:38

Hi

I am new to sql and have tried a few things to no avail.
I have 2 tables, what I need is when an update occurs on table2 it gets an int value from table1.col1 and adds an int from table2.col1 together, then I want the added value to update table1.col1 where the 2 tables share a common value, any help would be appreciated.
Thanks, James.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-18 : 08:02:15

Please give table structure, sample data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

bobred
Starting Member

14 Posts

Posted - 2011-06-21 : 14:08:45

Hi

I have come up with this

ALTER TRIGGER Trigger6
ON dbo.ReceiveOrder
FOR UPDATE
AS
	UPDATE    Consumables
SET              Quantity = Consumables.Quantity + ReceiveOrder.Quantity_Received
FROM         Consumables INNER JOIN
                      ReceiveOrder ON Consumables.Product = ReceiveOrder.Product

This almost does what I want. I have Quantity_Received=25, Quantity=100 but when it triggers I get the new Quantity=150 any ideas.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-22 : 02:07:25

Instead of Consumables you should use INSERTED table which has at runtime of the trigger the actual changed data.
Read about trigger in BOL.

No, you're never too old to Yak'n'Roll if you're too young to die.

bobred
Starting Member

14 Posts

Posted - 2011-06-22 : 04:09:22

I will have a look, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources