32

One simple method I've used in the past is basically just creating a second table whose structure mirrors the one I want to audit, and then create an update/delete trigger on the main table. Before a record is updated/deleted, the current state is saved to the audit table via the trigger.

While effective, the data in the audit table is not the most useful or simple to report off of. I'm wondering if anyone has a better method for auditing data changes?

There shouldn't be too many updates of these records, but it is highly sensitive information, so it is important to the customer that all changes are audited and easily reported on.

Mark Harrison
  • 267,774
  • 112
  • 308
  • 434
Brandon Wood
  • 5,257
  • 4
  • 34
  • 31
  • 2
    SQL Server 2008 has some built-in support with the new Change Data Capture feature – andyp Aug 29 '08 at 17:42
  • 1
    I should point out that this feature is only available in the Enterprise edition. – user17060 Feb 09 '09 at 18:38
  • The Change Data Capture feature also appears to be more for ensuring applications objects are kept in line with the database rather than a auditing solution. (It only stores 2 days worth of changes for example.) You'd still need to poll for changes and write those to a table, so you might as well just create and use triggers to write to a central audit table as below. Oh, and the URL above is broken, use this [link](http://msdn.microsoft.com/en-us/library/bb933874.aspx). – BJury Oct 01 '13 at 10:06

6 Answers6

19

How much writing vs. reading of this table(s) do you expect?

I've used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime - generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were sparse enough that they could be run at low-use periods of the day.

Added: If the amount of data vs. reporting is a concern, the audit table could be replicated to a read-only database server, allowing you to run reports whenever necessary without bogging down the master server from doing their work.

Greg Hurlman
  • 17,247
  • 6
  • 50
  • 84
6

We are using two table design for this.

One table is holding data about transaction (database, table name, schema, column, application that triggered transaction, host name for login that started transaction, date, number of affected rows and couple more).

Second table is only used to store data changes so that we can undo changes if needed and report on old/new values.

Another option is to use a third party tool for this such as ApexSQL Audit or Change Data Capture feature in SQL Server.

John Emeres
  • 473
  • 5
  • 6
2

I have found these two links useful:

Using CLR and single audit table.
Creating a generic audit trigger with SQL 2005 CLR

Using triggers and separate audit table for each table being audited.
How do I audit changes to SQL Server data?

Community
  • 1
  • 1
HadleyHope
  • 1,173
  • 1
  • 10
  • 19
1

Are there any built-in audit packages? Oracle has a nice package, which will even send audit changes off to a separate server outside the access of any bad guy who is modifying the SQL.

Their example is awesome... it shows how to alert on anybody modifying the audit tables.

Mark Harrison
  • 267,774
  • 112
  • 308
  • 434
1

OmniAudit might be a good solution for you need. I've never used it before because I'm quite happy writing my own audit routines, but it sounds good.

GateKiller
  • 68,419
  • 71
  • 167
  • 203
1

I use the approach described by Greg in his answer and populate the audit table with a stored procedure called from the table triggers.

Community
  • 1
  • 1
Chris Miller
  • 4,489
  • 3
  • 30
  • 45