SQL Server Audit tables the easy way

One of the more common tasks I perform from project to project is the creation of audit tables. The easiest way I know how to do this is to create a copy of the table I want to audit, and then add a trigger to the parent table. For my example I’ll use my ever-so-popular MLB database and my TEAMS table. The schema for TEAMS looks like:

CREATE TABLE [dbo].[TEAMS](
    [TEAM_ID] [int] IDENTITY(1,1) NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL
) ON [PRIMARY]

GO

The audit table for TEAMS will be an exact copy, with a couple of modifications. First, TEAM_ID needs to be a copy, so it’s no longer an identity column, The second change I made was to add an AUDIT_DT column to capture when the change occurred :

CREATE TABLE [dbo].[TEAMS_AUDIT](
    [TEAM_ID] [int] NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL,
    [AUDIT_DT] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

I then add a trigger to the TEAMS table to capture updates and deletes (I only want to capture when the original column changes, not when it’s added) :

CREATE TRIGGER TRG_TEAMS
   ON  TEAMS
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [MLB].[dbo].[TEAMS_AUDIT]
           ([TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE])
     SELECT [TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE]
    FROM DELETED
END
GO

That’s it! A couple of things you may want to consider when creating an audit table is :

• Remember that the audit table gets an insert for each update/delete to the parent table. This can have some performance ramifications. One of the things I do is not put any indexes on the audit table.
• You’ll want to purge the data from this table from time to time (or back it up).

Tags: SQL Server