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

Update Trigger

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 23:02:01

Hi, please may i check with you how do I retrieve the last updated row for a trigger?

For example, when you want to retrieve the lasted inserted row, you select from inserted. When you want to retrieve the last deleted row, you select from deleted.

Is it the same when you want to select from updated row?

For example, I want to create a trigger that will update another table with the information when the student table is updated.

CREATE Trigger Trig_UpdateWarningLetter
on Student
AFTER Update
AS
UPDATE [WarningLetter]
SET
[Address] = select address from ???
where
StudentID = select studentId from ???
END
GO

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:18:59

for update actions, the new values can be obtained from inserted and old values from deleted. so it should be:-


CREATE Trigger Trig_UpdateWarningLetter
on Student
AFTER Update 
AS
UPDATE w
SET 
w.[Address] = i.address,
w.StudentID = i.studentId 
FROM [WarningLetter] w
JOIN inserted i
ON i.linkingcol=w.linkingcol
END 
GO

linkingcol is column by which updated table and warningletter are related

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 23:22:03

Thank you very much, visakh16

I was an idiot as I could not get from updated table :(

I thought there is a table called updated as well, where I select the values from updated. and as you guess, that did not work.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:25:26

No problem...you're welcome

you can look into explanation of triggers in books online which explains these concepts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources