SQL Server Table Audit

I wanted to find a simple solution to do a audit trail on tables, I found a solution that came closest to what I wanted to achieve. I want to give full credit to Pop Rivett's SQL Server on the Audit Trail, check it out here.

I have modified it to make it even more easier to enable auditing on any database and made a couple of changes here and there.

We start by creating a table to store changed data.

CREATE TABLE [dbo].[Audit](
  [Type] [char](1) NULL,
  [TableName] [varchar](50) NULL,
  [PKFields] [varchar](100) NULL,
  [PKValues] [varchar](100) NULL,
  [FieldName] [varchar](128) NULL,
  [OldValue] [varchar](3000) NULL,
  [NewValue] [varchar](3000) NULL,
  [UpdateDate] [datetime] NULL,
  [UserName] [varchar](128) NULL
) ON [PRIMARY]

GO

There are a few changes here, I have increased the oldvalue and newvalue fields so that large textual field changes are logged, I have also reduced the primary key field size.

I have separated Pop Rivett's trigger into a stored procedure and a trigger, mainly to reduce redundancy and making it easier to fine tune the audit process.

IF EXISTS (
  SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
   WHERE SPECIFIC_NAME = N'PRC_Audit_INS'
)
   DROP PROCEDURE PRC_Audit_INS
GO

CREATE PROCEDURE PRC_Audit_INS
(
  @TableName VARCHAR(128),
  @UpdateColumns VARBINARY(100)
)
AS
SET NOCOUNT ON

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000),
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKFields VARCHAR(1000) ,
       @PKValues VARCHAR(1000)
       

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
               + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action
IF EXISTS (SELECT * FROM #ins)
       IF EXISTS (SELECT * FROM #del)
               SELECT @Type = 'U'
       ELSE
               return --SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'


-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKFields = COALESCE(@PKFields+',','')
       + COLUMN_NAME,
       @PKValues = COALESCE(@PKValues+'+'',''+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))'
       
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
       ORDER BY c.ORDINAL_POSITION

IF @PKCols IS NULL
BEGIN
       RAISERROR('no PK on table %s', 16, -1, @TableName)
       RETURN
END

SELECT         @field = 0,
       @maxfield = MAX(ORDINAL_POSITION)
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION)
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(@UpdateColumns, @char, 1) & @bit > 0
                                       OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = @TableName
                       AND ORDINAL_POSITION = @field
               SELECT @sql = ' insert Audit (    Type,                 TableName,                 PKFields,                 PKValues,                FieldName,                 OldValue,                 NewValue,                 UpdateDate,                 UserName) select ''' + @Type + ''','''
       + @TableName + ''',''' + @PKFields + ''',' + @PKValues
       + ',''' + @fieldname + ''''
       + ',convert(varchar(3000),d.' + @fieldname + ')'
       + ',convert(varchar(3000),i.' + @fieldname + ')'
       + ',''' + @UpdateDate + ''''
       + ',''' + @UserName + ''''
       + ' from #ins i full outer join #del d'
       + @PKCols
       + ' where i.' + @fieldname + ' <> d.' + @fieldname
       + ' or (i.' + @fieldname + ' is null and  d.'
                                + @fieldname
                                + ' is not null)'
       + ' or (i.' + @fieldname + ' is not null and  d.'
                                + @fieldname
                                + ' is null)'
               EXEC (@sql)
        END
END
GO

There are couple of things noteworty here, this procedure is designed to be called only from the trigger that will be created by the code below, also this procedure handles only updates and deletes. I figured logging inserts are not necessary as it would only make the audit table larger, secondly I added an 'ORDER BY c.ORDINAL_POSITION' in one of the select statements above, this is kind of important for me cause otherwise the primary key order is not truly reflected in the Audit log.

Here come the part 2 of the code which creates the trigger in every table which calls the stored procedure above to do the logging.

IF EXISTS (
  SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
   WHERE SPECIFIC_NAME = N'PRC_Tool_CreateAuditTriggers'
)
   DROP PROCEDURE PRC_Tool_CreateAuditTriggers
GO

CREATE PROCEDURE PRC_Tool_CreateAuditTriggers
(
  @Create bit  = null
)
AS
DECLARE @SqlDrop varchar(3000)
DECLARE @SqlCreate varchar(3000)
DECLARE @TableName varchar(100)

DECLARE C Cursor
FOR
SELECT 'IF OBJECT_ID (''TRG_' + [Name] + '_UPDDEL_Audit'',''TR'') IS NOT NULL ' + char(13) + char(10) +
' DROP TRIGGER TRG_' + [Name] + '_UPDDEL_Audit ' +char(13) + char(10),
'CREATE TRIGGER [dbo].[TRG_' + [Name] + '_UPDDEL_Audit] ' + char(13) + char(10) +
'ON [dbo].[' + [Name] + '] FOR UPDATE, DELETE ' + char(13) + char(10) +
'AS ' + char(13) + char(10) +
'SET NOCOUNT ON ' + char(13) + char(10) +
'SELECT * INTO #ins FROM inserted '  + char(13) + char(10) +
'SELECT * INTO #del FROM deleted ' + char(13) + char(10) +
'DECLARE @UpdatedColumns VARBINARY(100) ' + char(13) + char(10) +
'SET @UpdatedColumns = COLUMNS_UPDATED() ' + char(13) + char(10) +
'EXEC PRC_Audit_INS ''' + [Name] + ''', @UpdatedColumns ' + char(13) + char(10), [Name]
FROM sysobjects
WHERE xtype='u'
AND [name] NOT LIKE 'aspnet_%'  -- Using SQL membership so ignore those tables
AND [name] NOT IN ('Audit')  -- List of tables to ignore

OPEN C

FETCH NEXT FROM C
INTO @SqlDrop, @SqlCreate, @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Create is null
    Print @TableName
    ELSE
      BEGIN
          EXEC (@SqlDrop)
      IF @Create = 1
      BEGIN
        Print 'Creating Trigger for ' + @TableName
        EXEC (@SqlCreate)
      END
      ELSE
      Print 'Dropped Audit Triger for ' + @TableName
      END
      

  FETCH NEXT FROM C
  INTO @SqlDrop, @SqlCreate, @TableName
  END

CLOSE C

DEALLOCATE C
GO

   

So now to enable Auditing we just have to call:

PRC_Tool_CreateAuditTriggers 1

To Disable it:

PRC_Tool_CreateAuditTriggers 0

And to get a list of tables that are participating in the Audit.

PRC_Tool_CreateAuditTriggers