Log record changes in SQL server in an audit table

This is the code with two bug fixes. The first bug fix was mentioned by Royi Namir in the comment on the accepted answer to this question. The bug is described on StackOverflow at Bug in Trigger Code. The second one was found by @Fandango68 and fixes columns with multiples words for their names.

ALTER TRIGGER [dbo].[TR_person_AUDIT]
ON [dbo].[person]
FOR UPDATE
AS
           DECLARE @bit            INT,
                   @field          INT,
                   @maxfield       INT,
                   @char           INT,
                   @fieldname      VARCHAR(128),
                   @TableName      VARCHAR(128),
                   @PKCols         VARCHAR(1000),
                   @sql            VARCHAR(2000),
                   @UpdateDate     VARCHAR(21),
                   @UserName       VARCHAR(128),
                   @Type           CHAR(1),
                   @PKSelect       VARCHAR(1000)


           --You will need to change @TableName to match the table to be audited.
           -- Here we made GUESTS for your example.
           SELECT @TableName = 'PERSON'

           SELECT @UserName = SYSTEM_USER,
                  @UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126)

           -- Action
           IF EXISTS (
                  SELECT *
                  FROM   INSERTED
              )
               IF EXISTS (
                      SELECT *
                      FROM   DELETED
                  )
                   SELECT @Type = 'U'
               ELSE
                   SELECT @Type = 'I'
           ELSE
               SELECT @Type = 'D'

           -- get list of columns
           SELECT * INTO #ins
           FROM   INSERTED

           SELECT * INTO #del
           FROM   DELETED

           -- 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 @PKSelect = COALESCE(@PKSelect + '+', '') 
                  + '''<[' + COLUMN_NAME 
                  + ']=''+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

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

               RETURN
           END

           SELECT @field = 0,
                  -- @maxfield = MAX(COLUMN_NAME) 
                  @maxfield = -- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName


                  MAX(
                      COLUMNPROPERTY(
                          OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                          COLUMN_NAME,
                          'ColumnID'
                      )
                  )
           FROM   INFORMATION_SCHEMA.COLUMNS
           WHERE  TABLE_NAME = @TableName






           WHILE @field < @maxfield
           BEGIN
               SELECT @field = MIN(
                          COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                              COLUMN_NAME,
                              'ColumnID'
                          )
                      )
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = @TableName
                      AND COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                              COLUMN_NAME,
                              'ColumnID'
                          ) > @field

               SELECT @bit = (@field - 1)% 8 + 1

               SELECT @bit = POWER(2, @bit - 1)

               SELECT @char = ((@field - 1) / 8) + 1





               IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
                  OR @Type IN ('I', 'D')
               BEGIN
                   SELECT @fieldname = COLUMN_NAME
                   FROM   INFORMATION_SCHEMA.COLUMNS
                   WHERE  TABLE_NAME = @TableName
                          AND COLUMNPROPERTY(
                                  OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                                  COLUMN_NAME,
                                  'ColumnID'
                              ) = @field



                   SELECT @sql = 
                          '
           insert into Audit (    Type, 
           TableName, 
           PK, 
           FieldName, 
           OldValue, 
           NewValue, 
           UpdateDate, 
           UserName)
           select ''' + @Type + ''',''' 
                          + @TableName + ''',' + @PKSelect
                          + ',''' + @fieldname + ''''
                          + ',convert(varchar(1000),d.' + @fieldname + ')'
                          + ',convert(varchar(1000),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

Take a look at this article on Simple-talk.com by Pop Rivett. It walks you through creating a generic trigger that will log the OLDVALUE and the NEWVALUE for all updated columns. The code is very generic and you can apply it to any table you want to audit, also for any CRUD operation i.e. INSERT, UPDATE and DELETE. The only requirement is that your table to be audited should have a PRIMARY KEY (which most well designed tables should have anyway).

Here's the code relevant for your GUESTS Table.

  1. Create AUDIT Table.
    IF NOT EXISTS
          (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]') 
                   AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
           CREATE TABLE Audit 
                   (Type CHAR(1), 
                   TableName VARCHAR(128), 
                   PK VARCHAR(1000), 
                   FieldName VARCHAR(128), 
                   OldValue VARCHAR(1000), 
                   NewValue VARCHAR(1000), 
                   UpdateDate datetime, 
                   UserName VARCHAR(128))
    GO
  1. CREATE an UPDATE Trigger on the GUESTS Table as follows.
    CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
    AS
    
    DECLARE @bit INT ,
           @field INT ,
           @maxfield INT ,
           @char INT ,
           @fieldname VARCHAR(128) ,
           @TableName VARCHAR(128) ,
           @PKCols VARCHAR(1000) ,
           @sql VARCHAR(2000), 
           @UpdateDate VARCHAR(21) ,
           @UserName VARCHAR(128) ,
           @Type CHAR(1) ,
           @PKSelect VARCHAR(1000)
           
    
    --You will need to change @TableName to match the table to be audited. 
    -- Here we made GUESTS for your example.
    SELECT @TableName = 'GUESTS'
    
    -- date and user
    SELECT         @UserName = SYSTEM_USER ,
           @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)
    
    -- Action
    IF EXISTS (SELECT * FROM inserted)
           IF EXISTS (SELECT * FROM deleted)
                   SELECT @Type = 'U'
           ELSE
                   SELECT @Type = 'I'
    ELSE
           SELECT @Type = 'D'
    
    -- get list of columns
    SELECT * INTO #ins FROM inserted
    SELECT * INTO #del FROM deleted
    
    -- 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 @PKSelect = COALESCE(@PKSelect+'+','') 
           + '''<' + COLUMN_NAME 
           + '=''+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
    
    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(COLUMNS_UPDATED(),@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, 
                   PK, 
                   FieldName, 
                   OldValue, 
                   NewValue, 
                   UpdateDate, 
                   UserName)
    select ''' + @Type + ''',''' 
           + @TableName + ''',' + @PKSelect
           + ',''' + @fieldname + ''''
           + ',convert(varchar(1000),d.' + @fieldname + ')'
           + ',convert(varchar(1000),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

I know this is old, but maybe this will help someone else.

Do not log "new" values. Your existing table, GUESTS, has the new values. You'll have double entry of data, plus your DB size will grow way too fast that way.

I cleaned this up and minimized it for this example, but here is the tables you'd need for logging off changes:

CREATE TABLE GUESTS (
      GuestID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

CREATE TABLE GUESTS_LOG (
      GuestLogID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestID INT, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

When a value changes in the GUESTS table (ex: Guest name), simply log off that entire row of data, as-is, to your Log/Audit table using the Trigger. Your GUESTS table has current data, the Log/Audit table has the old data.

Then use a select statement to get data from both tables:

SELECT 0 AS 'GuestLogID', GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS] WHERE GuestID = 1
UNION
SELECT GuestLogID, GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS_LOG] WHERE GuestID = 1
ORDER BY ModifiedOn ASC

Your data will come out with what the table looked like, from Oldest to Newest, with the first row being what was created & the last row being the current data. You can see exactly what changed, who changed it, and when they changed it.

Optionally, I used to have a function that looped through the RecordSet (in Classic ASP), and only displayed what values had changed on the web page. It made for a GREAT audit trail so that users could see what had changed over time.

Tags:

Sql

Sql Server