I need to know the data changes in some table on a row level

Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row

simply i need to do the following in a trigger:

For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue

I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help

Thanks and have a great day

Hi

It is very simply.

From Books Online:

"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."

More , look for "inserted tables" in Books Online

Gigi,

www.sqlserver.ro

|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||

On the triggers use the following statements

Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted

Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted

|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:

UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The concepts (the deleted table and the inserted table) was in SQL 2000 too.

Gigi Ciubuc

www.sqlserver.ro