Database audit trails

[MielieSpoor]

What is the best way to keep an audit trail of the data in you database? I have used triggers and mirror tables to keep audit trails but don't know if it really is the best and most efficient way to do it.

What are you using?

 

[Necuno]

similair to triggers without triggers its build into each transaction that is going through the database as what is being done, parameters, table and effected record ID.

i would like to know about some other methods too.

 

[dequadin]

The only transaction tables I've worked on were on an AS/400 DB2 database.

We had audit columns on each table (User, Date etc) and mirror tables. We didn't use triggers we did the audit trails in the code. We also had Mimix which keeps a record of every (insert/update) transaction to any table on the database. It's called journaling in the AS/400 world.

 

[guest2013-1]

I create a separate table and then store each field change in there.

So I have a layout similar to this:

ID-TableName-FieldName-oldValue-NewValue-UserID-CreatedDate

And then I have triggers on all the tables that a user can edit (we store UserID modified etc on those tables, but sometimes need to trace who exactly changed what to what)

I use sqlvariants for the values. So I don't have to worry about data types. So the triggers are just a copy/paste each time essentially.

Once it's setup I can monitor the little sons of bitches like there's no tomorrow.

"Who made that deal live?"
"Not us, we didn't do nothing?"
"Really? Your supervisor was the last one to edit it"
Supervisor: "No I didn't do anything to it"

*Audit logs*

"Really? Cause it says here Ms Data Capturer #1 changed the record to x and then to y and when it's y it locks for normal Data Capturers and needs a Supervisor to unlock it but it seems you forgot to take it off the approved deal system"

"Oh, yes, noooooow I remember..." <-- ahu

 

[greggpb]

Been thru this a couple of times with different methodogies

From shadow tables with no primary key to triggers..

there all have proc's and con's.. for trigger make sure you get everything from the guy in mssql manager to the guy using an app but then dataloads as well..

I have found a good compromiseis to implement in the stored proc data layer.. therfore choosing what you wish to audit.. and audit the data into a basic xml

Table name, Field name,username, dateChanged,XMLField
the XMLFiled can tell you type, prev value, application, stored proc... depending how complex you get.

Then lock down the DB so that only stored procs can be executed.. but this doesn't stop the DBAdmin from f'ing stuff up unaudited