SYSK 347: How-To Keep Historical/Audit Data Accurate

I’m sure you’ve heard this saying many times before – “the hard disk space is cheap now – save everything”… With that, many companies create paired (or buddy) tables for all transactional data, i.e. for every transactional table (i.e. not lookups, a.k.a. domain values) you’d create another one with same columns plus (usually) four more – AuditRecordID, Action (insert, update, delete), AuditTimeStamp, and AuditSQLUser (see SQL script below).

So, to make sure that anytime you insert, update or delete a record, a corresponding record is inserted into the “buddy” table, I prefer to use triggers. For security reason, I also have a trigger on the audit (“buddy”) table that prevents users from deleting or modifying data in it.

Below are such triggers for the insert and update actions (delete would be virtually same as insert):

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE trigger [Table1_InsertTrigger]

on [dbo].[Table1] for insert

as

begin

            insert into dbo.Table1_Audit

            select 'i', getdate(), suser_name(), inserted.*

            from inserted;

end

CREATE trigger [Table1_UpdateTrigger]

on [dbo].[Table1] for update

as

begin

-- NOTE: the commented out output clause below is not usable due to

-- having triggers on the audit table:

-- Msg 331, Level 16, State 1, Procedure Table1_UpdateTrigger, Line 7

-- The target table 'dbo.Table1_Audit' of the OUTPUT INTO clause cannot have any enabled triggers.

            -- Make sure the timestamp is correct

            update dbo.Table1

            set LastUpdatedTimeStamp = getdate()

            where pk_id in (select pk_id from inserted)

            -- output 'u', getdate(), suser_name(), inserted.* into dbo.Table1_Audit;

            -- Workaround for the output clause

            insert into Table1_Audit

            select 'u', getdate(), suser_name(), inserted.*

            from inserted

end

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE trigger [Table1_Audit_Trigger]

on [dbo].[Table1_Audit] instead of update, delete

as

begin

            raiserror (N'Audit tables are insert-only', 16, 1);

end

You could go even further and create a DDL trigger that prevents users from dropping your audit tables – the example is provided in SQL Server 2005 Books Online (just look for DDL triggers).

Script to create Table1:

USE [YourDatabase]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Table1](

            [pk_id] [bigint] IDENTITY(1,1) NOT NULL,

            [Data1] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [LastUpdatedTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_Table1_LastUpdatedTimeStamp] DEFAULT (getdate()),

 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

            [pk_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Script to create Table1_Audit:

USE [YourDatabase]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Table1_Audit](

            [AuditRecordID] [bigint] IDENTITY(1,1) NOT NULL,

            [Action] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            [AuditTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_Table1_Audit_AuditTimeStamp] DEFAULT (getdate()),

            [AuditSQLUser] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Table1_Audit_AuditSQLUser] DEFAULT (suser_name()),

            [pk_id] [bigint] NOT NULL,

            [Data1] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [LastUpdatedTimeStamp] [datetime] NOT NULL,

 CONSTRAINT [PK_Table1_Audit] PRIMARY KEY CLUSTERED

(

            [AuditRecordID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF