Audit Triggers: Use INSERTED or DELETED system tables

Question

Guys,

The topic of how to audit tables has recently sprung up in our discussions... so I like your opinion on whats the best way to approach this. We have a mix of both the approaches (which is not good) in our database, as each previous DBA did what he/she believed was the right way. So we need to change them to follow any one model.

CREATE TABLE dbo.Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
)

CREATE TABLE dbo.Audit_Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
Audit_Type VARCHAR(1)  NOT NULL
Audited_Created_On DATETIME
Audit_Created_By VARCHAR(50)
)

Approach 1: Store, in audit tables, only those records that are replaced/deleted from the main table ( using system table DELETED). So for each UPDATE and DELETE in the main table, the record that is being replaced is INSERTED into the audit table with 'Audit_Type' column as wither 'U' ( for UPDATE ) or 'D' ( for DELETE)

INSERTs are not Audited. For current version of any record you always query the main table. And for history you query audit table.

Pros: Seems intutive, to store the previous versions of records Cons: If you need to know the history of a particular record, you need to join audit table with main table.

Appraoch 2: Store, in audit table, every record that goes into main table ( using system table INSERTED).

Each record that is INSERTED/UPDATED/DELETED to main table is also stored in audit table. So when you insert a new record it is also inserted into audit table. When updated, the new version (from INSERTED) table is stored in Audit table. When deleted, old version (from DELETED) table is stored in audit table.

Pros: If you need to know the history of a particular record, you have everything in one location.

Though I did not list all of them here, each approach has its pros and cons?

thanks,
_UB

Answer 1

A third approach we use alot is to only audit the interesting columns, and save both 'new' and 'old' value on each row.

So if you have your "name" column, the audit table would have "name_old" and "name_new".

In INSERT trigger, "name_old" is set to blank/null depending on your preference and "name_new" is set from INSERTED. In UPDATE trigger, "name_old" is set from DELETED and "name_new" from INSERTED In DELETE trigger, "name_old" is set from DELETED and "new_name" to blank/null.

(or you use a FULL join and one trigger for all cases)

For VARCHAR fields, this might not look like such a good idea, but for INTEGER, DATETIME, etc it provides the benefit that it's very easy to see the difference of the update.

I.e. if you have a quantity-field in your real table and update it from 5 to 7, you'd have in audit table:

quantity_old  quantity_new
           5             7

Easily you can calculate that the quantity was increased by 2 on the specific time.

If you have separate rows in audit table, you will have to join one row with "the next" to calculate difference - which can be tricky in some cases...

Answer 2

I'd go with :

Appraoch 2: Store, in audit table, every record that goes into main table ( using system table INSERTED).

is one more row per item really going to kill the DB? This way you have the complete history together.

If you purge out rows (a range all older than X day) you can still tell if something has changed or not:

• if an audit row exists (not purged) you can see if the row in question changed.
• if no audit rows exist for the item (all were purged) nothing changed (since any change writes to the audit table, including completely new items)

if you go with Appraoch 1: and purge out a range, it will be hard (need to remember purge date) to tell new inserts vs. ones where all rows were purged.

Answer 3

Im using this tool, an audit trigger generator that implements "approach 3" : http://www.auditdatabase.com/AuditTools.html

It's free and work with SQL Server 2005 and SQL Server 2008