The WIKI article describes the process of using SQL Triggers and some advanced SQL XML functions to track all changes to a table. It can be used, for instance, to identify all changes made to a UDF table or any other table.

A few cautions and warnings are in order:

  1. There is nothing to DELETE the records created. So the change log table will grow to be tremendous - possibly 1/2 the whole database over a year or two depending on the table. You need a plan to clear our these entries before you put this in place.
  2. Second, this macro only applies to single inserts, deletes, and updates. If you are using SQL updated functions or inserting in groups, it won't capture the results.
  3. Third, there could be a slight delay in saves and updates (a few milliseconds). I doubt this will normally be noticeable unless SQL Server is already under a heavy load.
  4. Fourth, depending on the use case, you may wish to remove the trigger from firing for inserts (new records) or deletes (deleted records).
  5. Fifth, this trigger required SQL 2008 or higher.
  6. The foreign key link cascades deletes. This means if you delete a value out of the summary table, it automatically deletes the values out of the details table.
  7. This will detect changes in the first 100 characters of the value, but not beyond this. This can be adjusted, but at a performance cost.
  8. This routine will not work for large XML fields (e.g. ParamStr, PartUDFXML, etc.) and a modified approach should be used.

Concept

Two different tables are used for each record is changed, a summary and a detail table.

The TableChangeLogSummary is used to track the change events. One row is recorded for each insert, delete, or update.

  • SummaryID. This is the key field for this record. It is autogenerated and tracked by SQL Server.
  • ID. This is the changed record's ID.
  • ClassTypeID. This is the change record's ClassTypeID.
  • ModifiedByUser. This is the new ModifiedByUser value. It will be blank for deleted records.
  • ModifiedByDate. This is the new ModifiedByDate value. It will be blank for deleted records.
  • SeqID. This is the new SeqID value. It will be blank for deleted records.
  • LastModifiedDate. This is the ModifiedDate value of the pre-modified record. It will be blank for inserted records.
  • ChangeType. This is a single character that indicates if the record is for an Insert (I), Update (U) or Delete (D).
  • TableName. This is the name of the table that was modified.

The TableChangeLogDetail is used to track the actual data changes. One row is recorded for column that is changed, excluding system columns (StoreID, ModifiedByUser .. IsSystem).

  • DetailID. This is the key field for this record. It is autogenerated and tracked by SQL Server.
  • SummaryID. This is the link to the Summary Record's ID.
  • ColumnName. This is the column name that was modified.
  • OldValue. This is the text value of old value that was overwritten. It will be NULL for inserted records, but may also be NULL for previously existing records.
  • NewValue. This is the text value of the new value that was saved. It will be NULL for deleted records, but may also be NULL for values that were removed.

Cautions

Make sure you know what you are doing here! You can definitely lock up your system with a bad trigger. It won't corrupt your good data, but it can bring you to a crawl.

1. Create the Tracking Tables

Run the following SQL to create the two tracking tables identified, and the foreign key between them.

-- Create the Summary Table
CREATE TABLE [dbo].[TableChangeLogSummary](
    [SummaryID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ID] [INT] NOT NULL,
    [ClassTypeID] [INT] NULL,
    [ModifiedByUser] [VARCHAR](25) NULL,
    [ModifiedDate] [smalldatetime] NULL,
    [SeqID] [INT] NULL,
    [LastModifiedDate] [smalldatetime] NULL,
    [ChangeType] CHAR(1) NULL,
        [TableName] VARCHAR(25) NULL
)
;
CREATE NONCLUSTERED INDEX [IX_TableChangeSummary_ID] ON [TableChangeLogSummary] ( [ID] ASC )
;
CREATE NONCLUSTERED INDEX [IX_TableChangeSummary_TableDate] ON [TableChangeLogSummary] ( [TableName], [ModifiedDate] )
;
CREATE NONCLUSTERED INDEX [IX_TableChangeSummary_DateTable] ON [TableChangeLogSummary] ( [ModifiedDate], [TableName] )
;
-- Create Detail Table
CREATE TABLE [dbo].[TableChangeLogDetail](
    [DetailID] [BIGINT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [SummaryID] [INT] NOT NULL,
    [ColumnName] [VARCHAR](100) NOT NULL,
    [OldValue] [VARCHAR](100) NULL,
    [NewValue] [VARCHAR](100) NULL
)
;
CREATE NONCLUSTERED INDEX [IX_TableChangeDetail_Summary] ON [dbo].[TableChangeLogDetail] ( [SummaryID] ASC )
;
CREATE NONCLUSTERED INDEX [IX_TableChangeLogDetail_Column] ON [dbo].[TableChangeLogDetail] ( [ColumnName] ASC, [SummaryID] ASC )
;
-- Add a Foreign Key to link the tables
-- and auto-delete the detail when the summary is deleted
ALTER TABLE [dbo].[TableChangeLogDetail]  WITH CHECK ADD  CONSTRAINT [FK_TableChangeLogDetail_TableChangeLogSummary] FOREIGN KEY([SummaryID])
REFERENCES [dbo].[TableChangeLogSummary] ([SummaryID])
ON DELETE CASCADE
;
ALTER TABLE [dbo].[TableChangeLogDetail] CHECK CONSTRAINT [FK_TableChangeLogDetail_TableChangeLogSummary]
;

2. Create the Trigger on the Table

Run the following SQL to create the trigger on the desired table. You will need to update the following information:

  • . Put the actual Table Name
  • . Replace with the type of update desired. You can use multiples separate by commas. For example, the following are valid choices:
    • UPDATE
    • UPDATE, DELETE
    • INSERT, UPDATE, DELETE
-- =============================================
-- Author:        Cyrious Software
-- Create date: 2016-04
-- Description:    This Trigger will add a row to the ChangeTrackingLog tables
--   that contains a record for every record changed for
-- =============================================
CREATE TRIGGER ChangeTrackingTrigger_Table
   ON
   AFTER INSERT, DELETE, UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Get the table name of the current process
    DECLARE @TableName VARCHAR(25);
        SET @TableName = COALESCE((SELECT object_name(parent_id) FROM sys.triggers WHERE object_id = @@PROCID), 'Unknown');
        DECLARE @xmlOld xml;
    DECLARE @xmlNew xml;
    DECLARE @SummaryID INT;
    DECLARE @t TABLE (
            ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
            RowName VARCHAR(100),
            Value1 VARCHAR(100),
            Value2 VARCHAR(100)
            );
    SET @xmlOld =
        (
        SELECT TOP 1 *
        FROM [deleted] AS [TABLE]
        ORDER BY ID
        FOR XML AUTO, ELEMENTS
        );
    SET @xmlNew =
        (
        SELECT TOP 1 *
        FROM [inserted] AS [TABLE]
        ORDER BY ID
        FOR XML AUTO, ELEMENTS
        );
    -- now join the two XMLs using the ColumName and exclude any that are not different
    WITH
        XML1 AS
        (
            SELECT T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
                    T.N.value('.', 'nvarchar(100)') AS VALUE
            FROM @xmlOld.nodes('/TABLE/*') AS T(N)
        ),
        XML2 AS
        (
            SELECT T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
                    T.N.value('.', 'nvarchar(100)') AS VALUE
            FROM @xmlNew.nodes('/TABLE/*') AS T(N)
        )
        INSERT INTO @t (RowName, Value1, Value2)
            SELECT COALESCE(XML1.NodeName, XML2.NodeName) AS NodeName,
                   XML1.Value AS Value1,
                   XML2.Value AS Value2
            FROM XML1
              FULL OUTER JOIN XML2
                ON XML1.NodeName = XML2.NodeName
            WHERE COALESCE(XML1.Value, '')  COALESCE(XML2.Value, '')
              AND XML1.NodeName NOT IN ('StoreID', 'ModifiedByUser', 'ModifiedByComputer', 'SeqID')
    ;
    -- Now create the Summary record
    INSERT INTO TableChangeLogSummary (ID, ClassTypeID, ModifiedByUser, ModifiedDate, SeqID, LastModifiedDate, ChangeType, TableName)
        SELECT COALESCE(I.ID, D.ID),
            COALESCE(I.ClassTypeID, D.ClassTypeID),
            I.ModifiedByUser,
            I.ModifiedDate,
            I.SeqID,
            D.ModifiedDate,
            CASE WHEN (D.ID IS NULL) THEN 'I' WHEN (I.ID IS NULL) THEN 'D' ELSE 'U' END,
                        @TableName
        FROM [inserted] I
        FULL OUTER JOIN [deleted] D ON I.id = D.id
    ;
    -- Now capture the new SummaryID that was generated
    SET @SummaryID = (SELECT SCOPE_IDENTITY());
    -- Now create the detail records
    INSERT INTO TableChangeLogDetail (SummaryID, ColumnName, OldValue, NewValue)
        SELECT @SummaryID, T.RowName, T.Value1, T.Value2
        FROM @t T
    ;
END

3. Sample Output

  • Note: The TableName column was added to the table after this example. It would be displayed as the last column in the list.

Contributor: Cyrious Software

Date: 4/2016

Version: Control 5+

Requires: SQL 2008 or Higher

You could leave a comment if you were logged in.