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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-19 : 08:00:50
|
| Christopher writes "I have an audit table for a bunch of tables. As such, the audit table is different than most of the audit examples I have seen on SQLTEAM.COM and elsewhere. I decided to make my audit table look like this: audit_id column (int identity) record_id int table_name varchar column_name varchar new_value varchar old_value varcharThis way, if I want to say "What happened to record id 12 from table xyz" I can find it.What I need to do now is to create an update trigger that scans through all the records being updated (there could be more than one at once), and for each of those scans all the columns, finds out which change, and inserts an audit record into the above.To accomplish this, I am first creating two cursors:DECLARE @t_old CURSOR FOR SELECT * FROM [deleted]DECLARE @t_new CURSOR FOR SELECT * FROM [inserted]Then I use a while loop and FETCH NEXT to look at each row.What I need to do now is to have an inner loop that looks through all of the available columns and does something like IF UPDATE() to see which ones were changed, and log the audit. I figure out the columns by sayingset @columnNames = ( SELECT [column_name] from information_schema.columns where table_name='xyz' )I'm not sure how to construct the inner loop. Do I need to create a cursor on the result set for columnNames?Is this a really bad approach?" |
|
|
Kristen
Test
22859 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-19 : 18:29:28
|
| >> "Is this a really bad approach?"I'll go a step further than Kristen, and just say don't do it, because you are setting yourself up for a nightmare of bad performance, waits, and blocks. You are talking about having one table that will have multiple rows inserted in every transaction in your database. Imagine the impact of that when you have a lock on that table.If you need audit tables, just create one per table with the same schema, and some additional columns for datetime, application, login, host, etc. Keep it simple. With not too much effort, you can write a script to generate an audit table trigger from your table schema.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-19 : 22:03:47
|
| Oh I'm right behind you MVJ!In the post I linked to I said "I have only once implimented an audit system which stored table / column / OldData / NewData in a single auditing table and it was a nightmare."Kristen |
|
|
|
|
|
|
|
|
Audit trigger that stores all column changes 
It came up in discussion recently: