Audit Data

Hi all,
I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
Thanks,What do you need this for?|||One method is to use the Binary_Checksum() function across both the original record and the updated record. If the values returned are different, then the record has changed and you should copy the original into your archive table.|||Does that mean i need to check every single field? that will take a while isn't it? is there any better way? how do you guys do your audit?

The reason I need this for is to keep a log on who did what on when on selected tables in a given DB, where sometimes ppl will ask things like who delete this record, or who update that record etc, and i can't just keep the last update as they need to chase back who did updates, for example PersonA update 2 times and PersonB comes in and update 3 times then PersonD delete that record...i need to keep track of all these info.|||There are many off-the-shelf packages that will give you this functionality. Download the eval and see how it is done. Most likely it all bottles down to C2-based auditing trace.|||I ran across this the other day. The VB package builds a shadow audit table (per auditied table) and builds the triggers for insert, update and delete. Also has paramaters which build SP's to trim the audit records or restore from the audit table.

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=e94eb930-1f8d-4e9a-be65-38313ce31166|||I think you are going to have problems with audit table design:

"AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate"

I suspect you will find it very difficult to manage or retrieve usefull information from a single audit table that covers all the tables in your database, though at first glance that may appear to be the simplest solution. You will find that writing queries against this to track changes or restore lost data will be quite cumbersome.

A more common approach is to create a copy of each of the tables you want to archive, perhaps with "_HIST" appended to the name and with your Update time columns included as well. Then just have an update trigger on your production tables that copies the entire record to its history table.

Even though you are increasing the number of tables, you will find this method much easier to use.