how to get updated field after update? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to get updated field after update?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-03-10 : 13:17:15

Hi there,

table1 has cols:
tbID | Name | ..
111 | John | ..

Now I need to fire a trigger to get updated field Name after the UPDATE operation. How can I find the row with ID 111?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 13:34:34

you can use INSERTED table for that. It will have all rows that were involved in update operation

so trigger will be like


CREATE TRIGGER Trg_table1
ON table1
AFTER UPDATE
AS
BEGIN
...some code here

-- the below will give you updated rows information
SELECT name,...
FROM INSERTED 
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2012-03-10 : 17:05:40

thanks visakh16. I noticed that inserted table holds the data after update, but I need data before update.
I have table2 to hold Name as foreign key. Say, if Name in table1 changed to Smith, for example, then all the Name in table2 must be changed accordingly. I need to run:

update table2
set Name = @newName
where Name = @oldName

Here how can I get @oldName? Thanks again

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-10 : 18:04:38

The deleted table holds the old values.

--
Gail Shaw
SQL Server MVP

allan8964
Posting Yak Master

249 Posts

Posted - 2012-03-10 : 18:14:10

I tested, for update operation, deleted and inserted tables return same values. Thanks.

allan8964
Posting Yak Master

249 Posts

Posted - 2012-03-10 : 18:17:19

Sorry, I tested in a wrong way .... You're so right, deleted return old value and inserted returns new one. Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:12:50

quote:
Originally posted by allan8964

Sorry, I tested in a wrong way .... You're so right, deleted return old value and inserted returns new one. Thanks!!!

This is what happens

In Insert operation
----------------------
INSERTED table contains new values which are inserted
DELETED table will not have any rows

In Update operation
----------------------
INSERTED table will have new values which got updated (or were a part of the update)
DELETED table will have old values before the update

In Delete Operation
----------------------
INSERTED table will not have any rows
DELETED table will have values of row which got deleted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources