Table Auditing with XML

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.

	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.

	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.

Insert 1 row
PreviousRowXmlValues: NULL
CurrentRowXmlValues:

			<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />

Update 1 row
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:
			<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.




	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.

You can leave a response, or trackback from your own site.

2 Responses to “Table Auditing with XML”

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

  2. [...] 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. [...]

Leave a Reply