auto update table variable value after insertion on another table

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

SQL Server 2000 Forums

SQL Server Development (2000)

auto update table variable value after insertion on another table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-07 : 08:46:49

bearky writes "my problem is when i insert record to one table(eg.Inventory table is used to add in stock), i want it automatically update another table(eg.Availability)quantity in stock field...is there any code(sql command) can help me to do so? thanks a lot"

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 06:56:59

You can create a TRIGGER on the Inventory table. Then whenever a row is either Inserted, Updated or Deleted, in the Inventory table, the trigger will perform an action based on the row(s) that changed - e.g. UPDATE the Quantity column in the Availability table.

A TRIGGER has two pseudo tables available to it "inserted" and "deleted". These contain all the rows that were changed. You can join these to your Inventory table, or each other, to work out what changed.

e.g.

CREATE TRIGGER dbo.MyTrigger
ON dbo.Inventory
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
UPDATE A
SET Quantity = Quantity
- COALESCE(D.OnHandQty, 0)
+ COALESCE(I.OnHandQty, 0)
FROM dbo.Availability A
LEFT OUTER JOIN inserted I
ON I.PartNo = A.PartNo
LEFT OUTER JOIN deleted D
ON D.PartNo = A.PartNo
END
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources