Log record changes in SQL server in an audit table

Question

The table :

    CREATE TABLE GUESTS (       GUEST_ID int IDENTITY(1,1) PRIMARY KEY,        GUEST_NAME VARCHAR(50),        GUEST_SURNAME VARCHAR(50),        ADRESS VARCHAR(100),        CITY VARCHAR(50),        CITY_CODE VARCHAR(10),        COUNTRY VARCHAR(50),        STATUS VARCHAR(20),        COMMENT nvarchar(max); 

For the logging :

CREATE TABLE AUDIT_GUESTS (   ID int IDENTITY(1,1) PRIMARY KEY,    GUEST_ID int,   OLD_GUEST_NAME VARCHAR(50),    NEW_GUEST_NAME VARCHAR(50),    OLD_GUEST_SURNAME VARCHAR(50),    NEW_GUEST_SURNAME VARCHAR(50),   OLD_ADRESS VARCHAR(100),    NEW_ADRESS VARCHAR(100),   OLD_CITY VARCHAR(50),    NEW_CITY VARCHAR(50),   OLD_CITY_CODE VARCHAR(10),    NEW_CITY_CODE VARCHAR(10),    OLD_COUNTRY VARCHAR(50),    NEW_COUNTRY VARCHAR(50),    OLD_STATUS VARCHAR(20),    NEW_STATUS VARCHAR(20),    OLD_COMMENT nvarchar(max),    NEW_COMMENT nvarchar(max),    AUDIT_ACTION varchar(100),   AUDIT_TIMESTAMP datetime); 

I would like to create a trigger on my GUESTS table to log all changes in my AUDIT_GUESTS table. How can I do that in SQL Server 2014 Express ?

I tried :

create TRIGGER trgAfterUpdate ON [dbo].[GUESTS]  FOR UPDATE AS     declare @GUEST_ID int;     declare @GUEST_NAME varchar(50);     declare @GUEST_SURNAME VARCHAR(50);     declare @ADRESS VARCHAR(100);      declare @CITY VARCHAR(50);     declare @CITY_CODE VARCHAR(10);      declare @COUNTRY VARCHAR(50);     declare @STATUS VARCHAR(20);     declare @COMMENT nvarchar(max);     declare @AUDIT_ACTION varchar(100);     declare @AUDIT_TIMESTAMP datetime;      select @GUEST_ID=i.GUEST_ID from inserted i;                 select @GUEST_NAME=i.GUEST_NAME from inserted i;         select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;     select @ADRESS=i.ADRESS from inserted i;     select @CITY=i.CITY from inserted i;     select @CITY_CODE=i.CITY_CODE from inserted i;     select @COUNTRY=i.COUNTRY from inserted i;     select @STATUS=i.STATUS from inserted i;     select @COMMENT=i.COMMENT from inserted i;          if update(GUEST_NAME)         set @audit_action='Updated Record -- After Update Trigger.';          if update(GUEST_SURNAME)         set @audit_action='Updated Record -- After Update Trigger.';          if update(ADRESS)         set @audit_action='Updated Record -- After Update Trigger.';          if update(CITY)         set @audit_action='Updated Record -- After Update Trigger.';          if update(CITY_CODE)         set @audit_action='Updated Record -- After Update Trigger.';          if update(COUNTRY)         set @audit_action='Updated Record -- After Update Trigger.';          if update(STATUS)         set @audit_action='Updated Record -- After Update Trigger.';          if update(COMMENT)         set @audit_action='Updated Record -- After Update Trigger.';          insert into AUDIT_GUESTS            (GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP)      values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());     GO 

Works kind of ok but I would like to see old-new values.

In SQLite I had :

CREATE TRIGGER [LOG_UPDATE] AFTER UPDATE OF [GUEST_NAME], [GUEST_SURNAME], [ADRESS], [CITY], [CITY_CODE], [COUNTRY], [STATUS], [COMMENT] ON [GUESTS] BEGIN INSERT INTO GUESTS_LOG  ( GUEST_ID,    NAME_OLD,NAME_NEW,    SURNAME_OLD,SURNAME_NEW,    ADRESS_OLD,ADRESS_NEW,    CITY_OLD,CITY_NEW,    CITY_CODE_OLD,CITY_CODE_NEW,    COUNTRY_OLD,COUNTRY_NEW,    STATUS_OLD,STATUS_NEW,       COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)        VALUES      (OLD.GUEST_ID,   OLD.GUEST_NAME,NEW.GUEST_NAME,    OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,   OLD.ADRESS,NEW.ADRESS,   OLD.CITY,NEW.CITY,   OLD.CITY_CODE,NEW.CITY_CODE,   OLD.COUNTRY,NEW.COUNTRY,     OLD.STATUS,NEW.STATUS,   OLD.COMMENT,NEW.COMMENT,'record changed',datetime('now','localtime'));    END 

and it worked OK. Just dont know how to pass this to SQL server. Just begun learning it.

Answer 1

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 

2. 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 

Answer 2

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