Triggers - 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

Triggers

Author

Topic

StevenK
Starting Member

2 Posts

Posted - 2010-07-25 : 17:13:00

I Want to update a column in one table when columns in another table are updated, inserted or deleted is this posible

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-25 : 17:22:16

Yes.

If you want more specific help than that, some details on the structure of the tables and what you want to update when would be useful.

--
Gail Shaw
SQL Server MVP

StevenK
Starting Member

2 Posts

Posted - 2010-07-25 : 17:33:26

Table 1 dbo.ItemsUsed
Columns
ScheduleID PK
ItemID PK
AmountAdded
AmountUsed

Table 2 dbo.Items
Columns
ItemID pk
ItemName
ItemDescription
ItemMiniumPerDay
ItemsOnHand

Table1 Is Related to Table2 Thourgh ItemID. Table1 is futher Related Thourgh ScheduleID but that is not important with this trigger.

What i want to do is update the itemsonhand column when the itemsAdded or ItemsUsed columns Are Altered

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 01:24:12

[code]
Create trigger 'TriggerName'
on table1 for update
as
Begin

If Update(itemsAdded)
Update t set t.itemsAdded=i.itemsAdded from inserted i
inner join table2 t on t.ItemID=iItemID

End
[/code]
Same way you can write it for ItemsUsed to.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 02:44:22

[code]
CREATE TRIGGER MyTrigger
ON dbo.ItemsUsed FOR UPDATE
AS
BEGIN

UPDATE U
SET ItemsOnHand = U.ItemsOnHand + I.AmountAdded - I.AmountUsed
FROM dbo.Items AS U
JOIN inserted AS I
ON I.ItemID = U.ItemID
JOIN deleted AS D
ON D.ItemID = U.ItemID
WHERE I.AmountAdded <> D.AmountAdded
OR I.AmountUsed <> D.AmountUsed
END
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources