[SOLVED] Audit Triggers: Use INSERTED or DELETED system tables

Audit Triggers: Use INSERTED or DELETED system tables

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?

Solution

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.