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


 


 

Comments (2)

  1. you’d certainly want to do this if you wanted to cripple the performance of your database. I’ve seen this tried a few times with oltp databases – with the expected result – halved performance to taking the application down.

    Just beware of the implications of taking this route.

  2. segaa says:

    Anyways, in spite of performance issues, sometime you need it, especially because of SOX stuff etc.

Skip to main content