Trigger : which line is updated

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

Other SQL Server 2008 Topics

Trigger : which line is updated

Author

Topic

cemte40
Starting Member

4 Posts

Posted - 2008-06-25 : 10:26:37

Hi,

I need to know which line of a table is updated when a trigger runs because a column of a line has changed.

Is there any functions or methods to do that?

Thanks in advance!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-25 : 12:53:15

Copies of the modified records are stored in a virtual table called "Inserted", which you can reference in your trigger and link to the original table via the primary key.

e4 d5 xd5 Nf6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 14:25:39

You can also use UPDATE(Column) to check if a column value has changed. Look into CREATE TRIGGER in books online

http://doc.ddart.net/mssql/sql70/create_8.htm

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-25 : 15:43:25

UPDATE() does not actually detect whether a column was changed. Merely whether it was included in the columnlist for an UPDATE statement.
The actual value may not have changed at all.

e4 d5 xd5 Nf6

cemte40
Starting Member

4 Posts

Posted - 2008-06-26 : 17:26:02

What is store exactly in the table update. The line which is updated????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 17:31:37

For inserts, the inserted table will contain all rows that were inserted.
For updates, the inserted table will contain the after image of the updated rows and the deleted table will contain the before image of the updated rows.
For deletes, the deleted table will contain the deleted rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cemte40
Starting Member

4 Posts

Posted - 2008-06-27 : 03:11:39

Thanks. Whith this query, I can find which row is affected by updates?

--> Select * from myTable where myId = Updated.myID

Is it correct or no?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 04:59:03

quote:
Originally posted by cemte40

Thanks. Whith this query, I can find which row is affected by updates?

--> Select * from myTable where myId = Updated.myID

Is it correct or no?

Select t.* from myTable t
inner join inserted i
on i.myID=t.myID
inner join deleted d
on d.myID=t.myID

this will give you updated rows of table. there's no table called updated. instead for updates, you will have values in both deleted & inserted tables with former having old values and latter new values.

cemte40
Starting Member

4 Posts

Posted - 2008-06-27 : 07:28:04

Thanks.
I am going to try it.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-27 : 11:18:48

I suggest you read about triggers and the inserted/deleted tables in Books Online before you go any further. You still seem very unclear on both the concept and the syntax.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources