July 13th, 2011 
When working with meta data tables it can be very useful to track when people make changes to the data. How do you track any change against any number of columns for any table? I have implemented a fairly simple solution that uses triggers and stores all changes as XML into one change log table.
I know triggers are very expensive and should be avoided unless the are truly needed. There are several DBAs out there that abuse them and I am sent into help figure out why their performance is so bad. I feel that in this instance they can be quite handy. Again, this is intended for meta data table or smaller tables. If you are working on large tables, this can significantly slow down updates to the table.
The table
The logging table has the Table Name, Previous Records as XML, Current Records as XML, User, Time Stamp.
The logging table has the Table Name, Previous Records as XML, Current Records as XML, User, Time Stamp.
CREATE TABLE dbo.MetaDataChangeLog( MetaDataChangeLogId INT IDENTITY(1,1) NOT NULL, TableName VARCHAR(255) NOT NULL, PreviousRowXmlValues XML NULL, CurrentRowXmlValues XML NULL, RowCreateSource VARCHAR(255) NOT NULL, RowCreateDateTime DATETIME NOT NULL, CONSTRAINT PK_MetaDataChangeLogId PRIMARY KEY CLUSTERED (MetaDataChangeLogId ASC) ) ALTER TABLE dbo.MetaDataChangeLog ADD DEFAULT (GETDATE()) FOR RowCreateDateTime GO
The Trigger (I am going to use my SSIS Config table in this example).
The trigger is going to be set up for AFTER INSERT, UPDATE, DELETE. It then does a SELECT *, from the INSERT and DELETE data sets, FOR XML AUTO. Updates have both an insert and a delete. The insert will have a delete value of NULL and vice versa.
The trigger is going to be set up for AFTER INSERT, UPDATE, DELETE. It then does a SELECT *, from the INSERT and DELETE data sets, FOR XML AUTO. Updates have both an insert and a delete. The insert will have a delete value of NULL and vice versa.
CREATE TRIGGER ETL.trSSISConfigurationsChangeLog ON ETL.SSISConfigurations AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; DECLARE @previousRowXmlValues XML DECLARE @currentRowXmlValues XML DECLARE @rowCreateSource VARCHAR(100) --Get the previous value SET @previousRowXmlValues = (SELECT * FROM DELETED FOR XML AUTO) --Get the current value SET @currentRowXmlValues = (SELECT * FROM INSERTED FOR XML AUTO) --Gets the SQL User that has made the update SET @rowCreateSource = SYSTEM_USER -- Insert the changes into the Log table INSERT INTO MetaDataChangeLog( TableName, PreviousRowXmlValues, CurrentRowXmlValues, RowCreateSource) VALUES ( 'ETL.SSISConfigurations', @PreviousRowXmlValues, @CurrentRowXmlValues, @RowCreateSource) END
The Data
This is what some sample data looks like. I inserted a row, updated it, then deleted it. (If you affect more then 1 row at a time all rows will be captured in row in the same XML.
This is what some sample data looks like. I inserted a row, updated it, then deleted it. (If you affect more then 1 row at a time all rows will be captured in row in the same XML.
Insert 1 row
PreviousRowXmlValues: NULL
CurrentRowXmlValues:
<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
Update 1 row
PreviousRowXmlValues:
PreviousRowXmlValues:
<DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
CurrentRowXmlValues:
<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Updated Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
Delete 2 rows
PreviousRowXmlValues:
PreviousRowXmlValues:
<DELETED ConfigurationFilter="vTestVariable2" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" /> <DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
CurrentRowXmlValues: NULL
Data saved in Log Table
View the data
That’s great that we now have a log of each change to our ETL.SSISConfigurations. But clicking on each of the XML statements is not the cleanest way to view the changes. For this we can create a view on top of each table and use XQuery to display the data. I do a CROSS APPLY, instead of an inline statement, so that if more then 1 row was affected in the change, it will be shown in the results. You will see that 5195 has two records, one for the Delete (old value) and one for the Insert (new value). 5196 deleted 2 rows so 2 rows show up. Simon Incegives a good example of how to flatten XML in SQL.
That’s great that we now have a log of each change to our ETL.SSISConfigurations. But clicking on each of the XML statements is not the cleanest way to view the changes. For this we can create a view on top of each table and use XQuery to display the data. I do a CROSS APPLY, instead of an inline statement, so that if more then 1 row was affected in the change, it will be shown in the results. You will see that 5195 has two records, one for the Delete (old value) and one for the Insert (new value). 5196 deleted 2 rows so 2 rows show up. Simon Incegives a good example of how to flatten XML in SQL.
CREATE VIEW [Audit].[vSSISConfigurationsChangeLog] AS
SELECT MetaDataChangeLogId AS ChangeId,
'DELETED' AS ChangeType,
RowCreateSource AS ChangeSource,
RowCreateDateTime AS ChangeDateTime,
R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(20)') AS ConfiguredValueType
FROM MetaDataChangeLog MDC
CROSS APPLY MDC.PreviousRowXmlValues.nodes('/DELETED') AS R(Data)
WHERE TableName = 'ETL.SSISConfigurations'
UNION
SELECT MetaDataChangeLogId AS ChangeId,
'INSERTED' AS ChangeType,
RowCreateSource AS ChangeSource,
RowCreateDateTime AS ChangeDateTime,
R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(255)') AS ConfiguredValueType
FROM MetaDataChangeLog MDC
CROSS APPLY MDC.CurrentRowXmlValues.nodes('/INSERTED') AS R(Data)
WHERE TableName = 'ETL.SSISConfigurations'
Now we have a single table that will track all data changes. You can quickly query the table to view the data or you can write a XQuery against the XML and work with it as if it was a table.
Posted in SQLServerPedia Syndication, T-SQL 
Tags: Auditing, Microsoft, SQL, SQL 2008, SQLServer, Trigger, XML, XQuery
you might want to look into async triggers with Service Broker. this way you avoid having to wait for the log table to fill up with xml which can potentially be a long insert.
[...] Table Auditing with XML Posted in General « An alternate, perhaps better view of the SQL Saturday #89′s AWESOME schedule!!!… You can leave a response, or trackback from your own site. [...]