Want to retreive Update row in table

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

Want to retreive Update row in table

Author

Topic

krishnarao.kr
Starting Member

2 Posts

Posted - 2007-05-10 : 02:38:36

i want to insert a row from t1 to t2 if any of the row is updated in the t1. say if Nth row is updated then the values of the row should be inserted into t2. inside the trigger i need to retrieve the row which is updated, how to do it? any specific global variable like @@identity is there for updation?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 02:42:58

You can make use of COLUMNS_UPDATED() bitmap to determine whether any column was updated.

This example is taken directly from BOL:


CREATE TABLE my_table
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 03:55:36

Inside your trigger.


IF UPDATE(T1)
  UPDATE YOURTABLE SET T2 =T1 FROM INSERTED WHERE INSERTED.PK= YOURTABLE.PK

Are T1, T2 are the columns of the same table???

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 04:01:23

Chirag,

T1 and T2 are table names, not column names.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 04:08:59

shissssssss.. oooooo.. I am still in hangover of yesterdays taqila :(

Chirag

http://chirikworld.blogspot.com/

krishnarao.kr
Starting Member

2 Posts

Posted - 2007-05-10 : 04:39:48

T1, T2 are different table names, the code below works for INSERT, but my prob is when i update a row in T1(say n) it should insert a row in T2. i.e. want to write a UPDATE trigger in T1,in such a case how to identify the the row which is updated?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 04:42:06

I Guess you are looking for the Audit Trails.. here are some of the links.. which will get you going..

http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources