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

Answer 3

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.

Answer 4

From SQL Server 2016 and onwards there's built-in support for this: use System-Versioned Temporal Tables

Answer 5

In both Shiva's and dwilli's answer, instead of defining the table name statically (i.e. SELECT @TableName = 'PERSON'), here is a way to get it dynamically:

SELECT @TableName = OBJECT_NAME(parent_object_id) FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

Clarification: This is not my code, i have found it somewhere on the internet, it was quite a while ago though, so i can't remember where. Also, since i can't comment here, i'm posting this as an answer.

Answer 6

There are some great answers here to turn the source code into a better experience for the community. If any of you are like me and are looking for the full set of code with test tables ready for copy, paste (RAW on GitHub), and execute I've assembled it below:

/*this is a journey... 
    Source links:

    https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/pop-rivetts-sql-server-faq-no-5-pop-on-the-audit-trail/ 
    https://stackoverflow.com/questions/19737723/log-record-changes-in-sql-server-in-an-audit-table
    https://chat.stackexchange.com/transcript/message/34774768#34774768 
    http://jsbin.com/lafayiluri/1/edit?html,output 
*/

-- #region | Set up the tables
/*Firstly, we create the audit table.
There will only need to be one of these in a database*/
DROP TABLE IF EXISTS dbo.audit_trigger; 
CREATE TABLE dbo.audit_trigger (
    Type CHAR(1),
    TableName VARCHAR(128),
    PK VARCHAR(1000),
    FieldName VARCHAR(128),
    OldValue VARCHAR(1000),
    NewValue VARCHAR(1000),
    UpdateDate datetime,
    UserName VARCHAR(128)
);
GO
/*now we will illustrate the use of this tool
by creating a dummy test table called TrigTest.*/
DROP TABLE IF EXISTS dbo.trigtest;
CREATE TABLE trigtest (
    i INT NOT NULL,
    j INT NOT NULL,
    s VARCHAR(10),
    t VARCHAR(10)
);
GO

/*note that for this system to work there must be a primary key
to the table but then a table without a primary key
isn’t really a table is it?*/
ALTER TABLE trigtest ADD CONSTRAINT pk PRIMARY KEY (i, j);
GO
-- #endregion 

/*and now create the trigger itself. This has to be created for every
table you want to monitor*/

-- #region | trigger with bug fix
DROP TRIGGER IF EXISTS dbo.tr_audit_trigtest;
GO
CREATE TRIGGER tr_audit_trigtest ON dbo.trigtest 
    FOR  
        /*uncomment INSERT if you want. The insert data is on the source table
            but sometimes your end users wanna see ALL the data in the audit table
            and hey, storage is cheap-ish now /shrug    
        */
        -- INSERT, 
        UPDATE, 
        DELETE 
AS
SET NOCOUNT ON;
/*declare all the variables*/
DECLARE @bit INT;
DECLARE @field INT;
DECLARE @maxfield INT;
DECLARE @char INT;
DECLARE @fieldname VARCHAR(128);
DECLARE @TableName VARCHAR(128);
DECLARE @PKCols VARCHAR(1000);
DECLARE @sql VARCHAR(2000);
DECLARE @UpdateDate VARCHAR(21);
DECLARE @UserName VARCHAR(128);
DECLARE @Type CHAR(1);
DECLARE @PKSelect VARCHAR(1000);

/*now set some of these variables*/
SET @TableName = (
    SELECT 
        OBJECT_NAME(parent_object_id) 
    FROM sys.objects
    WHERE 
        sys.objects.name = OBJECT_NAME(@@PROCID)
);
SET @UserName = SYSTEM_USER;
SET @UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126);

/*Action*/
IF EXISTS (SELECT * FROM INSERTED)
    IF EXISTS (SELECT * FROM DELETED)
         SET @Type = 'U'
    ELSE SET @Type = 'I'
    ELSE SET @Type = 'D'
;
/*get list of columns*/
SELECT * 
INTO #ins
FROM INSERTED;

SELECT * 
INTO #del
FROM DELETED;

/*set @PKCols and @PKSelect via SELECT statement.*/
SELECT @PKCols = /*Get primary key columns for full outer join*/
        COALESCE(@PKCols + ' and', ' on') 
        + ' i.[' + c.COLUMN_NAME + '] = d.[' + c.COLUMN_NAME + ']'
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pk
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c ON (
            c.TABLE_NAME = pk.TABLE_NAME
            AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        )
    WHERE pk.TABLE_NAME = @TableName
        AND CONSTRAINT_TYPE = 'PRIMARY KEY'
;
SELECT @PKSelect = /*Get primary key select for insert*/
        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

SET @field = 0;
SET @maxfield = (
    SELECT 
        MAX(
            COLUMNPROPERTY(
                OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                COLUMN_NAME,
                'ColumnID'
            )
        )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE  
        TABLE_NAME = @TableName
);

WHILE @field < @maxfield
BEGIN
    SET @field = (
        SELECT 
            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
    );
    SET @bit = (@field - 1)% 8 + 1;
    SET @bit = POWER(2, @bit - 1);
    SET @char = ((@field - 1) / 8) + 1;

    IF (
        SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
        OR @Type IN ('I', 'D')
    )
    BEGIN
        SET @fieldname = (
            SELECT 
                COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE  
                TABLE_NAME = @TableName
                AND COLUMNPROPERTY(
                        OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                        COLUMN_NAME,
                        'ColumnID'
                    ) = @field
        );
        SET @sql = ('
            INSERT INTO audit_trigger (    
                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 AS i FULL OUTER JOIN #del AS 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
SET NOCOUNT OFF;
GO 
-- #endregion 


-- #region | now we can test the trigger out
INSERT trigtest SELECT 1,1,'hi', 'bye';
INSERT trigtest SELECT 2,2,'hi', 'bye';
INSERT trigtest SELECT 3,3,'hi', 'bye';
SELECT * FROM dbo.audit_trigger;
SELECT * FROM trigtest;
UPDATE trigtest SET s = 'hibye' WHERE i <> 1;
UPDATE trigtest SET s = 'bye' WHERE i = 1;
UPDATE trigtest SET s = 'bye' WHERE i = 1;
UPDATE trigtest SET t = 'hi' WHERE i = 1;
SELECT * FROM dbo.audit_trigger;
SELECT * FROM dbo.trigtest;
DELETE dbo.trigtest;
SELECT * FROM dbo.audit_trigger;
SELECT * FROM dbo.trigtest;
GO

DROP TABLE dbo.audit_trigger;
GO
DROP TABLE dbo.trigtest ;
GO
-- #endregion

Answer 7

Hey It's very simple see this

@OLD_GUEST_NAME = d.GUEST_NAME from deleted d;

this variable will store your old deleted value and then you can insert it where you want.

for example-

Create trigger testupdate on test for update, delete
  as
declare @tableid varchar(50);
declare @testid varchar(50);
declare @newdata varchar(50);
declare @olddata varchar(50);


select @tableid = count(*)+1 from audit_test
select @testid=d.tableid from inserted d;
select @olddata = d.data from deleted d;
select @newdata = i.data from inserted i;

insert into audit_test (tableid, testid, olddata, newdata) values (@tableid, @testid, @olddata, @newdata)

go