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.

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