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

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

I need help with what appears to be a botched install.

Okay, i'm learning VB.NET (and having quite a bit of fun with it). Well, I had the old beta version. Anyway, went away for a week and found I could no longer use it. Why?

"Beta period is over.".

Yeah, it sucks.

Well, I uninstalled it via the uninstaller. I upgraded my .NET framework, etc, and redownloaded VB.NET. Everything installed okay, save for the SQL server. It repeatedly fails.

I have hunted down and shot each component quite ruthlessly, but I don't know what beta component remains. I can't get it to install because it says I have beta components left.

And VB.NET works. Barely. I had to disable the hosting process, otherwise it gives me a message about an invalid binding handle. Not often that a Beta product works better than the production one.

The uninstall tool won't work for some reason. I'm this close to just reformatting.

Please help, I'd like to code again.

Thanks.

bump|||Hi,

if you also had SQL Server beta installed you should also deinstall SQL Server as in the Beta period the SQL Server framework version and the version of the framework VS uses had to be in line.So installing VS / SQL Server / Framework version, reboot, installing SQL Server / VS (Framework is installed with one of the two) should make your system running :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

I need help with what appears to be a botched install.

Okay, i'm learning VB.NET (and having quite a bit of fun with it). Well, I had the old beta version. Anyway, went away for a week and found I could no longer use it. Why?

"Beta period is over.".

Yeah, it sucks.

Well, I uninstalled it via the uninstaller. I upgraded my .NET framework, etc, and redownloaded VB.NET. Everything installed okay, save for the SQL server. It repeatedly fails.

I have hunted down and shot each component quite ruthlessly, but I don't know what beta component remains. I can't get it to install because it says I have beta components left.

And VB.NET works. Barely. I had to disable the hosting process, otherwise it gives me a message about an invalid binding handle. Not often that a Beta product works better than the production one.

The uninstall tool won't work for some reason. I'm this close to just reformatting.

Please help, I'd like to code again.

Thanks.

bump|||Hi,

if you also had SQL Server beta installed you should also deinstall SQL Server as in the Beta period the SQL Server framework version and the version of the framework VS uses had to be in line.So installing VS / SQL Server / Framework version, reboot, installing SQL Server / VS (Framework is installed with one of the two) should make your system running :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de