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

SQL Server 2008 Forums

Transact-SQL (2008)

Triggers

Author

Topic

uncledude10
Starting Member

2 Posts

Posted - 2009-05-22 : 15:18:00

I just want a simple Update trigger if anything gets changed in table a update table b

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-22 : 19:40:36

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 11:29:35

something like


CREATE TRIGGER YourTriggerName
ON Tablea
AFTER UPDATE
AS
BEGIN
UPDATE b
SET b...

FROM tableB b
JOIN INSERTED i
ON i...=b....
..
END

uncledude10
Starting Member

2 Posts

Posted - 2009-05-23 : 18:09:19

Thanks! for some reason I was struggling with this, I did figure it out though. In the past when I did triggers I always added(inserted) a new record. This time I just wanted to update an old record.

CREATE Trigger ChngAdditionalActions
ON
AdditionalActions
For UPDATE
as

DECLARE @IDN INT
DECLARE @LastChange dateTime

SELECT @IDN = (SELECT inquiry_ID FROM inserted)
SELECT @LastChange = getdate()

Update StatusTable
Set LastChange = @lastChange, Comments = @LastChange
where idn = @IDN

I don't do triggers very often so I was completely lost thanks again...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-23 : 19:22:17

follow the syntax that visakh provided. your trigger can only handle single record

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-24 : 01:18:05

quote:
Originally posted by uncledude10

Thanks! for some reason I was struggling with this, I did figure it out though. In the past when I did triggers I always added(inserted) a new record. This time I just wanted to update an old record.

CREATE Trigger ChngAdditionalActions
ON
AdditionalActions
For UPDATE
as

DECLARE @IDN INT
DECLARE @LastChange dateTime

SELECT @IDN = (SELECT inquiry_ID FROM inserted)
SELECT @LastChange = getdate()

Update StatusTable
Set LastChange = @lastChange, Comments = @LastChange
where idn = @IDN

I don't do triggers very often so I was completely lost thanks again...

See the line in blue. As Khtan suggested you're assigning value of inquiry_ID from inserted to a variable. While doing batch updates, inserted can contain more than 1 records. in such case you will get only a single value as you're storing it in variable. so to handle this, join on to inserted table as i showed you in my suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources