Creating a log table

Question

Creating a log table

aalianebhan

SSC Enthusiast

Points: 150
More actions
March 3, 2014 at 2:25 am

#290373

I want to create a (generic) trigger that will place into a logging table all adjustments to numerous (indeterminate) tables...
The log table appears generally like this:
TableName
TableKeyValue
ID identity
ChangeDate
ColumnName
OldValue
NewValue
Realized I'd just store the old and new principles as nvarchar instead of trying to deal with data forms. Any table reader would want to deal with re-changing any data types....
Anyway, I ca not determine how to do this in a trigger. The trigger can tell me which areas have changed, or I can just place all of the fields and then later remove any where oldvalue = newvalue.
I can find out the column names, and I do not even want to use them, since I can simply use ordinal figures, but once I build an insert statement, how do I do it?
Or is there a easier way, and I am just obsessing for no reason....
A little advice would be very much appreciated.
Thanks....
......................................
Livetecs
Time Tracking Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

aalianebhan (3/3/2014)
I want to create a (generic) trigger that will place into a logging table all adjustments to numerous (indeterminate) tables...
The log table appears generally like this:
TableName
TableKeyValue
ID identity
ChangeDate
ColumnName
OldValue
NewValue
Realized I'd just store the old and new principles as nvarchar instead of trying to deal with data forms. Any table reader would want to deal with re-changing any data types....
Anyway, I ca not determine how to do this in a trigger. The trigger can tell me which areas have changed, or I can just place all of the fields and then later remove any where oldvalue = newvalue.
I can find out the column names, and I do not even want to use them, since I can simply use ordinal figures, but once I build an insert statement, how do I do it?
Or is there a easier way, and I am just obsessing for no reason....
A little advice would be very much appreciated.
Thanks....
......................................
Livetecs
Time Tracking Software

"Generic" triggers, whether written in T-SQL or a CLR, will always be a huge performance problem especially on wider tables that are being audited at the "field" level, as you propose. I know this because I had to replace all such triggers in my production database because it was taking 4 minutes to update just 4 columns on 10,000 rows. When I got done with the replacement, it only took 800 milliseconds to do the same thing (equal to 40,000 inserts into the log/audit table).

The way I solved the problem was to write code that would generate the trigger code as good ol' fashioned T-SQL and then use that code to create the trigger. If the table changes, then just rerun the code to regen the trigger.

Also, I don't know what you're thinking of using for the OldValue and NewValue columns but I strongly recommend what I used... SQL_Variant. That way you don't have to store everything and make the conversion to VARCHAR(8000). It's a real performance saver not only on inserts to the log table, but also when you try to use the data from the table. You can even interrogate what the datatypes of the SQL_Variant are. See Books Online for more details there. If you don't know what "Books Online" actually is (a lot of people don't), please post back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)