Trigger - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 08:51:25

Rajesh writes "Hi,

I am using SQL Server 7.0 on Win NT 4.0 Svr 5.0

I want to create a update trigger on a table which
logs any change of value in any of the columns.

The table in which i want to log the details is like this

TableName
FieldName
OldValue
NewValue
LogDateTime
UserName

So, is there any way to write a common trigger which logs the above details if any of the column's value gets changed."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 08:57:16

Yes and no. You can definitely write a trigger to do this, but you have to customize it to each table. Plus you have to handle data conversions in order to insert the old and new values; date and numeric values should be converted to varchar and stored. If it is even possible to write a generic trigger (and I'm pretty sure it isn't) it will be EXTREMELY inefficient and slow, you are better off customizing it to each table, and TESTING the performance.

motokevin
Starting Member

36 Posts

Posted - 2002-04-12 : 16:13:00

From the above question, it seems that you just want to log 1 table.

I recently wrote some triggers to do something similar.

This code should get you started:

Create Trigger tr_MyTable_Log
On MyTable
AFTER Update
As

Insert Into LogTable(TableName,FieldName,OldValue,NewValue,LogDateTime,UserName)
Select 'MyTable','MyColumn1',Convert(Varchar,Deleted.MyColumn1),Convert(Varchar,Inserted.MyColumn1),GetDate,USER_ID
From Inserted,Deleted
Where Inserted.MyColumn1=Deleted.MyColumn1

Insert Into LogTable(TableName,FieldName,OldValue,NewValue,LogDateTime,UserName)
Select 'MyTable','MyColumn2',Convert(Varchar,Deleted.MyColumn2),Convert(Varchar,Inserted.MyColumn2),GetDate,USER_ID
From Inserted,Deleted
Where Inserted.MyColumn2=Deleted.MyColumn2

etc......

etc......

GO

-----
When you do the trigger for Insert and Delete the code will be similar but you wont need to inner join the Inserted and Deleted tables. You also wont need to put a record for OldValue on inserted, or NewValue for deleted. Also you may have to address the issue of a multiple row query (the one I gave you is for a single row update).

To see whether it's a multiple row query or not, get @@RowCount at the beginning of the Trigger.

You can also do Insert/Update/Delete in the same trigger:
on an Insert, the Deleted table will have zero rows
on a delete, the Inserted table will have zero rows
on an update, the Inserted and Deleted tables will both have rows

(The Inserted and Deleted tables are created by SQL specifically to access them in a trigger)
--

rob

quote:
If it is even possible to write a generic trigger (and I'm pretty sure it isn't) it will be EXTREMELY inefficient and slow, you are better off customizing it to each table

I'm sure you could probably write a generic trigger, and I'm sure you're right that it would be EXTREMELY slow.

However, I recently wrote a generic SP to create logging triggers for all tables in my DB. That's when I ran into the VarChar Size Limitation Problem.

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14442

Hope the code is enough to get you going Rajesh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources