Delta Tracing with Triggers

Question

I'm trying to create a Delta tracing for several tables based on triggers. Im using sql server 2012. To simplify my questions, I would like to look only at the after update trigger.

So here is the trace table:

CREATE TABLE [dbo].[History](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_History_Id]  DEFAULT (newsequentialid()),
    [ObjectId] [uniqueidentifier] NOT NULL,
    [Timestamp] [datetime] NOT NULL CONSTRAINT [DF_History_Timestamp]  DEFAULT (getdate()),
    [ChangingUser] [varchar](max) NOT NULL CONSTRAINT [DF_History_ChangingUser]  DEFAULT (suser_sname()),
    [Column] [varchar](max) NOT NULL,
    [OldValue] [nvarchar](max) NULL,
    [NewValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I would create a Trigger on a Table, which I would like to trace:

CREATE TRIGGER [dbo].[T1_TraceUpdate]
   on [dbo].[T1]
   AFTER update
AS 
BEGIN
set nocount on;
-- pseudo insert!
insert into History select * from inserted;
END

Except for the layout not matching the History Table, I have issues determinating the values, which have been changed and whose untouched. Is there a way to do it in a smarter way than just use dozends of if(updated(col_name))? Also I have to keep in mind, that there might be multiple rows affected. Is there a better and more resuable solution (possibly even without cursors?)...

Edit #1: Sample Data for T1 and History

T1 Design:

CREATE TABLE [dbo].[T1](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_T1_Id]  DEFAULT (newsequentialid()),
    [Title] [text] NULL,
    [Amount] [int] NULL,
    [Price] [decimal](18, 7) NULL,
    [Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample Data could be like this:

1d118497-bf69-e611-9e7d-40a8f04d1095    Abc            3    37,2500000  
9cf095a8-bf69-e611-9e7d-40a8f04d1095    NULL           1    27,3000000  
cc38386d-fe69-e611-9e7d-40a8f04d1095    Storm Catcher  10   NULL    

Lets fire a silly update:

update T1 set price = isnull(Price,100)*0.7 where Amount > 2

History should now look like this:

4848D80B-4E73-E611-BD43-40A8F04D1095    1D118497-BF69-E611-9E7D-40A8F04D1095    2016-09-05 11:49:33.473 sa  Price   37,2500000  26.0750000
E80EAB18-4E73-E611-BD43-40A8F04D1095    CC38386D-FE69-E611-9E7D-40A8F04D1095    2016-09-05 11:49:33.473 sa  Price   NULL        70

Answer 1

Triggers have inserted and deleted tables, which can be used to

Find the difference between the state of a table before and after a data modification and take actions based on that difference.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

Unpivot these tables to have one row per column, compare them and insert into the History table only rows where old and new values differ.

CREATE TRIGGER [dbo].[T1_TraceUpdate]
    on [dbo].[T1]
    AFTER update
AS 
BEGIN
    set nocount on;

    WITH
    CTE_Inserted
    AS
    (
        SELECT ID, ColumnName, ColumnValue
        FROM
            inserted
            CROSS APPLY
            (VALUES
                ('Title', Title),
                ('Amount', CAST(Amount AS nvarchar(max))),
                ('Price', CAST(Price AS nvarchar(max)))
            ) AS V(ColumnName, ColumnValue)
    )
    ,CTE_Deleted
    AS
    (
        SELECT ID, ColumnName, ColumnValue
        FROM
            deleted
            CROSS APPLY
            (VALUES
                ('Title', Title),
                ('Amount', CAST(Amount AS nvarchar(max))),
                ('Price', CAST(Price AS nvarchar(max)))
            ) AS V(ColumnName, ColumnValue)
    )
    INSERT INTO dbo.History([ObjectId], [Column], [OldValue], [NewValue])
    SELECT
        CTE_Inserted.Id AS [ObjectId]
        ,CTE_Inserted.ColumnName
        ,CTE_Deleted.ColumnValue AS [OldValue]
        ,CTE_Inserted.ColumnValue AS [NewValue]
    FROM
        CTE_Inserted
        INNER JOIN CTE_Deleted
            ON  CTE_Deleted.Id = CTE_Inserted.Id
            AND CTE_Deleted.ColumnName = CTE_Inserted.ColumnName
    WHERE
        ISNULL(CTE_Inserted.ColumnValue, N'') <> ISNULL(CTE_Deleted.ColumnValue, N'')
        OR (CTE_Inserted.ColumnValue IS NULL AND CTE_Deleted.ColumnValue IS NOT NULL) 
        OR (CTE_Inserted.ColumnValue IS NOT NULL AND CTE_Deleted.ColumnValue IS NULL) 
    ;

END

You'll have to use nvarchar(max) type instead of text in your T1 table. And you'll have to convert/cast all values to nvarchar(max) (or have several columns in the History table for different types).

Answer 2

Jaster, Finally i got a solution for your query.

Its a dynamic script ,where you can mention the name of required tables. The script will manage the data change on the mentioned tables table across all columns (except columns with datatype text,ntext and image) by creating dynamic triggers which will get fired on INSERT/UPDATE/DELETE actions on these tables. You can change the actions as per your requirement.I also included TableName and ModifiedDate in your HISTORY table for an easy look up.

So here are the steps to your solution.

Creating sample test environment using the provided Information

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'History')
CREATE TABLE [dbo].[History](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_History_Id]  DEFAULT (newsequentialid()),
    [Tablename] VARCHAR(150),
    [ObjectId] VARCHAR(1500) NOT NULL,
    [Timestamp] [datetime] NOT NULL CONSTRAINT [DF_History_Timestamp]  DEFAULT (getdate()),
    [ChangingUser] [varchar](max) NOT NULL CONSTRAINT [DF_History_ChangingUser]  DEFAULT (suser_sname()),
    [Column] [varchar](max) NOT NULL,
    [OldValue] [nvarchar](max) NULL,
    [NewValue] [nvarchar](max) NULL,
       [ModifiedDate] Datetime NULL
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'T1')

BEGIN
CREATE TABLE [dbo].[T1](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_T1_Id]  DEFAULT (newsequentialid()),
    [Title] text NULL,
    [Amount] [int] NULL,
    [Price] [decimal](18, 7) NULL,
    [Version] [datetime] NOT NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 

INSERT INTO [T1] ([Title],[Amount],[Price],[Version]) VALUES ('Abc',3,372500000 ,'2016-09-01 11:49:33.473'),
                                         (NULL,1,273000000  ,'2016-09-02 11:49:33.473'),
                                         ('Storm Catcher',10,NULL,'2016-09-01 11:49:33.473')
END

In the below temporary table ,you have to insert the table names for which wanted to track the data changes.

IF OBJECT_ID('tempdb..#FiltedTableList') IS NOT NULL
DROP TABLE #FiltedTableList
GO

CREATE TABLE #FiltedTableList
( ID INT IDENTITY(1,1),
  TableName VARCHAR(150))
INSERT INTO #FiltedTableList (TableName)
SELECT 'T1' --here we have only one table to track the changes

Here comes the final step. execute the below dynamic query.

DECLARE @TableName sysname,@Counter INT,@Dynamsql varchar(MAX) 
SELECT @Counter=MAX(ID) FROM #FiltedTableList

SET NOCOUNT ON

WHILE (@Counter !=0)

BEGIN

       SELECT @TableName= TableName
       FROM #FiltedTableList
       WHERE ID=@Counter

        DECLARE  @ColumnFilter VARCHAR (MAX)

        SELECT  @ColumnFilter=Stuff((   Select ', ' + C.COLUMN_NAME 
        From INFORMATION_SCHEMA.COLUMNS As C  Where  C.TABLE_NAME = @TableName
               AND c.DATA_TYPE NOT IN ('text','ntext','image')
                Order By C.ORDINAL_POSITION   For Xml Path('')  ), 1, 2, '')


        IF OBJECT_ID('tempdb..##MagInserted') IS NOT NULL
        DROP TABLE ##MagInserted

        IF OBJECT_ID('tempdb..##MagDeleted') IS NOT NULL
        DROP TABLE ##MagDeleted


EXEC('IF OBJECT_ID (''' + @TableName+ '_LogTracker'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TableName+ '_LogTracker')
SELECT @Dynamsql =
 'CREATE TRIGGER ' + @TableName+ '_LogTracker ON ' + @TableName+ ' FOR INSERT, UPDATE, DELETE
  AS
  DECLARE   @column int , @maxColumn int , @char int,@columnname varchar(128) , @TableName varchar(128) , @KeyColumn varchar(1000) , @Dynamsql varchar(2000) ,@dataquery VARCHAR(MAX), 
            @ModifiedDate varchar(21), @UserName varchar(128) , @key int , @Type char(1) , @PKFieldSelect varchar(1000),@PKValueSelect varchar(1000)
  SELECT @TableName = ''' + @TableName+ ''';
  SELECT @UserName = system_user , @ModifiedDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114);
  SELECT '+ @ColumnFilter+' INTO ##MagInserted FROM inserted;SELECT '+ @ColumnFilter+' INTO ##MagDeleted FROM deleted;
   SELECT @KeyColumn = COALESCE(@KeyColumn + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS kc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = kc.TABLE_NAME and c.CONSTRAINT_NAME = kc.CONSTRAINT_NAME
  WHERE kc.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = ''PRIMARY KEY''
  SELECT @PKFieldSelect = COALESCE(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS kc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = kc.TABLE_NAME and c.CONSTRAINT_NAME = kc.CONSTRAINT_NAME
  WHERE kc.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = ''PRIMARY KEY''
  SELECT @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS kc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = kc.TABLE_NAME  and c.CONSTRAINT_NAME = kc.CONSTRAINT_NAME
  WHERE  kc.TABLE_NAME = @TableName  AND CONSTRAINT_TYPE = ''PRIMARY KEY''  
  SELECT @column = 0 , @maxColumn = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName 
              WHILE @column < @maxColumn
              BEGIN
                     SELECT @column = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @column
                     SELECT @key = (@column - 1 )% 8 + 1; SELECT @key = power(2,@key - 1);SELECT @char = ((@column - 1) / 8) + 1
                   IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @key > 0 OR @Type IN (''I'',''D'')
                           BEGIN
                           SELECT @columnname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @column
                           SELECT @Dynamsql = ''insert History (TableName,[ObjectId], [Column], OldValue, NewValue, ModifiedDate, ChangingUser)''
                           SELECT @Dynamsql = @Dynamsql + '' select '''''' + @TableName + '''''''' 
                           SELECT @Dynamsql = @Dynamsql +  '','' + @PKValueSelect
                           SELECT @Dynamsql = @Dynamsql + '','''''' + @columnname + ''''''''
                           SELECT @Dynamsql = @Dynamsql + '',convert(varchar(1000),d.'' + @columnname + '')''
                           SELECT @Dynamsql = @Dynamsql + '',convert(varchar(1000),i.'' + @columnname + '')''
                           SELECT @Dynamsql = @Dynamsql + '','''''' + @ModifiedDate + ''''''''
                           SELECT @Dynamsql = @Dynamsql + '','''''' + @UserName + ''''''''
                           SELECT @Dynamsql = @Dynamsql + '' from ##MagInserted i full outer join ##MagDeleted d''
                           SELECT @Dynamsql = @Dynamsql + @KeyColumn
                           SELECT @Dynamsql = @Dynamsql + '' where i.'' + @columnname + '' <> d.'' + @columnname
                           SELECT @Dynamsql = @Dynamsql + '' or (i.'' + @columnname + '' is null and  d.'' + @columnname + '' is not null)''
                           SELECT @Dynamsql = @Dynamsql + '' or (i.'' + @columnname + '' is not null and  d.'' + @columnname + '' is null)''
                           EXEC (@Dynamsql)
                           END    END   '
SELECT @Dynamsql
EXEC(@Dynamsql)

SET @Counter=@Counter-1

END

So here is the test case and output :

Answer 3

Join inserted, selected and check columns. Note NULL safe non-equality comparison, setting not-NULL value to NULL and vise versa is counted as changed data.

CREATE TRIGGER [dbo].[T1_TraceUpdate]
    on [dbo].[T1]
    AFTER update
AS 
BEGIN
    set nocount on;
    -- Changing Id breaks the logic of the trigger
    IF UPDATE(ID) 
    BEGIN 
        RAISERROR ('ID cannot change.', 16, 1);
        --
    END     
    INSERT INTO dbo.History(ObjectId, [Column], OldValue, NewValue)
    SELECT inserted.ID, ColumnName, OldValue, NewValue
    FROM inserted 
    INNER JOIN Deleted ON  Deleted.Id = Inserted.Id
    CROSS APPLY
       (SELECT ColumnName='Title', OldValue=deleted.Title, NewValue=inserted.Title 
          WHERE isnull(nullif(inserted.Title,deleted.Title), nullif(deleted.Title,inserted.Title)) IS NOT NULL 
        UNION ALL
        SELECT 'Amount', CAST(deleted.Amount AS nvarchar(max)), CAST(inserted.Amount AS nvarchar(max))
          WHERE isnull(nullif(inserted.Amount,deleted.Amount), nullif(deleted.Amount,inserted.Amount)) IS NOT NULL  
        UNION ALL
        SELECT 'Price', CAST(deleted.Price AS nvarchar(max)), CAST(inserted.Price AS nvarchar(max))
          WHERE isnull(nullif(inserted.Price, deleted.Price), nullif(deleted.Price,inserted.Price)) IS NOT NULL
        UNION ALL
        -- you may want to skip version (`timestamp` type) as it's always updated by server
        SELECT 'Version', CONVERT(varchar(max),CONVERT(VARBINARY,deleted.Version),1), CONVERT(varchar(max),CONVERT(VARBINARY,inserted.Version),1)
          WHERE isnull(nullif(inserted.Version, deleted.Version), nullif(deleted.Version,inserted.Version)) IS NOT NULL
     ) t;
END;  

Answer 4

I can't give you our proprietary solution, but I can suggest an approach. What we do is we have a script that looks up the columns for a specified table in the systems tables and then generates the trigger code using dynamic SQl with a loop to grab all the columns one at a time.

We run this script every time a change to the structure of the table is made or a new table we want to audit is created. So the actual trigger looks somewhat similar to the other solutions posted, but it is created through a script that uses dynamic SQL to go through all the columns and add them to the script to create the trigger.

This is faster and less open to mistakes and missing something than generating each trigger by hand. This approach has been working flawlessly for over 15 years on our legacy database. To keep things easier to understand, we have one trigger for inserts, one for deletes and one for updates for each table.

Answer 5

You can use also CDC (Change Data Capture)

1. Enable DB for CDC
2. Enable table/columns for CDC
3. Read CDC tables to read the changes (each actual change has old and new row, + $updatemask where you can find updated columns. It automatically excludes fake updates)

All that stuff can be done dynamically for each table.

The triggers slow down the OLTP system. Transaction is not committed till the trigger finishes. CDC works after transaction is committed and when you want - CDC scanner reads directly transactional log, like in transactional replication. The advantage of CDC is that you read CDC tables, not the base tables. You can delay the process and read / populate history tables during quiet times, e.g. on weekends or overnight.