How I create history or audit tables.

OK – not too complex this but quick and easy.

 

Firstly I create a history table – to do this I get SQL management studio to generate a create script for the table I want to audit.  I add the postscript “History” to the table name, remove the constraints and indexes and add in 2 extra columns, one for the type of update and one for the date/time e.g.

 

CREATE TABLE [dbo].[tblProductCodeHistory]
(
    chrChangeType nchar(1) NOT NULL,
    dteChangeTime DateTime NOT NULL,
    [intProductId] [int]  NOT NULL,
    [intSalesAccountId] [int] NOT NULL,
    [intSalesTaxCodeId] [int] NOT NULL,
    [vcrDescription] [nvarchar](100) NULL,
    [intBusinessId] [int] NOT NULL,
    [decNetPrice] [money] NULL,
    [vcrProductCode] [nvarchar](100) NOT NULL,
    [decCost] [money] NULL,
    [intSupplierAccountId] [int] NULL,
    [intProductTypeId] [int] NOT NULL,
    [intQuantity] [decimal](10, 3) NOT NULL ,
    [intUserId] [int] NULL,
    [dteUpdated] [datetime] NULL,
    [vcrUnit] [dbo].[ShortString] NULL DEFAULT (NULL),
) ON [PRIMARY]
GO

Then I create a couple of triggers on the original table as follows -

CREATE TRIGGER [dbo].[ProductCode_Delete]
   ON  [dbo].[tblProductCode]
   AFTER DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    INSERT INTO tblProductCodeHistory
SELECT 'D',GetDate(),* FROM deleted
    -- Insert statements for trigger here

END

GO
/****** Object:  Trigger [dbo].[Transaction_Update]    Script Date: 08/11/2009 16:54:16 ******/
CREATE TRIGGER [dbo].[ProductCode_Update]
   ON  [dbo].[tblProductCode]
   AFTER UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    INSERT INTO tblProductCodeHistory
SELECT 'U',GetDate(),* FROM deleted
    -- Insert statements for trigger here

END

 

Once you’ve done this once then just do a search and replace to create the next set of trigger e.g. replace ProductCode in the above script with MyTable.

One thing to bear in mind is if you have a username in the original table then you might want to update this as part of the delete stored proc so the last username gets in the history table.

A colleague of mine has been promising to create a script to create this off the sysobjects – but I’m not holding my breath!