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.

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

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

1. Fourth, depending on the use case, you may wish to remove the trigger from firing for inserts (new records) or deletes (deleted records).

1. Fifth, this trigger required SQL 2008 or higher.

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

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

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

table_change_diagram.png_width400

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.

code_formatsql

– 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]

;

code

=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

code_formatsql

–

 ON  

 AFTER INSERT, DELETE, UPDATE

SET NOCOUNT ON;

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

	);

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')

;

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

;

set @SummaryID = (SELECT SCOPE_IDENTITY());

insert into TableChangeLogDetail (SummaryID, ColumnName, OldValue, NewValue)

	select @SummaryID, T.RowName, T.Value1, T.Value2

	from @t T

;

Source

See Also