An Auditing Solution with XML And XSL

By David McKinney, 2007/08/20

Untitled Page An Auditing Solution with XML and XSL

In my previous article, I've got the XML, Now what?, I invited you to add XML and XSL to your arsenal of possible solutions to common problems. Specifically I showed how XML and XSL could be used to generate simple database documentation. In this article, I'd like to apply the same techniques to address a requirement of most database applications - Audit Tables.

If you're having difficulty following some of the techniques outlined below then I recommend you first read the previous article, which introduces some of the XML / XSL related stuff.

Design of the Auditing Solution

I've been using the same technique for audit tables for several years now, and am quite happy with it. Essentially, for each table to be audited, there is a corresponding audit table, which is populated by a trigger. The audit table has a primary key made of the primary key field(s) of the base table together with the 2 additional primary key columns described below. It's not going to work well if you have tables without primary keys - I make no apologies for this!

UpdateType has one of the following values 'Insertion', 'Deletion', 'Modification Before', 'Modification After'
AuditId is unique for each time a trigger is run. (e.g. if a trigger updates 12 records, all 24 records in the audit table will have the same AuditId). It is of uniqueidentifier datatype (GUID).

Figure 1 shows a couple of tables from Northwind with their associated audit tables.

Figure 1

Below is the trigger that would be used to populate the [Order Details] table.

CREATE TRIGGER [dbo].[trgAuditOrder Details] ON [dbo].[Order Details]
FOR INSERT, UPDATE, DELETE 
AS

DECLARE @GUID uniqueidentifier
DECLARE @RowsInserted numeric (18,0)
DECLARE @RowsDeleted numeric (18,0)
DECLARE @UpdateTypeDeletion varchar(20)
DECLARE @UpdateTypeInsertion varchar(20)

SET @GUID=newid()

SELECT
	@RowsInserted=count(*) 
FROM
	Inserted

SELECT
	@RowsDeleted=count(*) 
FROM
	Deleted

IF @RowsInserted != 0 and @RowsDeleted != 0
	BEGIN
		SET @UpdateTypeDeletion='Modification Before'
		SET @UpdateTypeInsertion='Modification After'
	END
ELSE IF @RowsInserted != 0
	SET @UpdateTypeInsertion='Insertion'
ELSE IF @RowsDeleted != 0
	SET @UpdateTypeDeletion='Deletion'

INSERT INTO dbo.[auditOrder Details](AuditId, UpdateType , [OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
SELECT @GUID,@UpdateTypeDeletion, [OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]
FROM Deleted d

INSERT INTO dbo.[auditOrder Details] (AuditId, UpdateType, [OrderID], [ProductID], [UnitPrice], [Quantity], [Discount])
SELECT @GUID,@UpdateTypeInsertion, [OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]
FROM Inserted i

Creating of the Auditing Solution

I've implemented the above auditing solution in a number of databases, and it has met my auditing needs. However the creation of the audit tables and of the triggers is a fiddly and time consuming process.

The goal - in case you haven't guessed - is to use XML and XSL to completely automate this process.

The first step is to write the SQL query which returns the XML document. I used an XML authoring tool, XMLSpy, to design the structure of the XML document I wanted to produce (an XSD schema shown in figure 2), and then set about writing the SQL code to return this structure. (figure 3)

Figure 2 - graphical view of the XSD schema.

Figure 3 - SQL to generate XML document

SELECT  [name],
        compatibility_level,
        ( SELECT    dbtable.[name] AS 'dbtable/@name',
                    sch.name AS 'dbtable/@schema',
                    ( select    ind.name,
                                ind.type_desc,
                                ind.object_id,
								ind.is_primary_key,
                                ind.index_id,
			
                                ( select    index_column_id,
                                            column_id,
                                            key_ordinal,
                                            is_descending_key,
                                            is_included_column
                                  from      sys.index_columns index_cols
                                  where     index_id = ind.index_id
                                            and object_id = ind.object_id
                                for
                                  XML auto,
                                      type
                                )
                      FROM      sys.indexes [ind]
                      WHERE     ind.object_id = dbtable.object_id
                                and ind.name is not null and ind.is_primary_key=1
                    for
                      XML auto,
                          type
                    ) as 'dbtable/indexes',

( SELECT    ORDINAL_POSITION AS "dbcolumn/@column_id",
                                COLUMN_NAME AS "dbcolumn/@name",
                                IS_NULLABLE AS "dbcolumn/@is_nullable",
                                CHARACTER_MAXIMUM_LENGTH AS "dbcolumn/@max_length",
                                NUMERIC_PRECISION AS "dbcolumn/@precision",
                                NUMERIC_SCALE AS "dbcolumn/@scale",
                                COLLATION_NAME AS "dbcolumn/@collation_name",
                                DATA_TYPE AS "dbcolumn/@type"
                      FROM      INFORMATION_SCHEMA.COLUMNS isc
                      WHERE     isc.TABLE_NAME = OBJECT_NAME(dbtable.OBJECT_ID)
						AND		DATA_TYPE NOT IN ('text','ntext','image')
                    FOR
                      XML PATH(''),
                          type
                    ) AS 'dbtable/dbcolumns'
          FROM      sys.tables dbtable
                    INNER JOIN sys.schemas sch ON dbtable.schema_id = sch.schema_id
        for
          XML path(''),
              type
        ) AS dbtables
FROM    sys.databases AS [database]
WHERE   [name] = DB_NAME()
FOR     XML AUTO

Now I've got the XML, I wrote the XSL stylesheet to convert the XML into SQL! Although an authoring tool does make this job easier, there is no shortcut to XSL authoring (or if there is I haven't found it). It's quite different from most other coding - it takes time to learn and even once you're proficient it can be very frustrating.

The good news is that even with little knowledge of XSL, you should be able to modify the XSL document I've written to customise it to your own style (just tread carefully). So, for example, if you don't like my auditing technique, plug in your own.

Figure 4 - XSL to transform the XML into SQL

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
<xsl:output omit-xml-declaration="yes" method="text" name="mytext"/>

<xsl:template match="ROOT">
		<xsl:apply-templates select="database">		
		</xsl:apply-templates>
	</xsl:template>
	
	<xsl:template match="database">
	
		<xsl:apply-templates select="dbtables">
		</xsl:apply-templates>
	</xsl:template>

<xsl:template match="dbtables">

USE <xsl:value-of select="../@name"/>
		--GO
		<xsl:apply-templates select="dbtable" mode="Create"/>

--GO
		/* Rollback code
		USE <xsl:value-of select="../@name"/>
		--GO
		<xsl:apply-templates select="dbtable" mode="Rollback"/>	
		*/
	</xsl:template>

<xsl:template match="dbtable" mode="Rollback">
		
		
		
		<xsl:variable name="compatibility_level" select="../../@compatibility_level"/>
	
		<xsl:call-template name="IfExistsDropTable">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO
		<xsl:call-template name="IfExistsDropTrigger">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO
	</xsl:template>

<xsl:template match="dbtable" mode="Create">
		
			
		<xsl:variable name="compatibility_level" select="../../@compatibility_level"/>

<xsl:call-template name="IfExistsDropTable">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO
		<xsl:call-template name="CreateTable">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO	
		<xsl:call-template name="CreatePK">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO
		<xsl:call-template name="IfExistsDropTrigger">
			<xsl:with-param name="compatibility_level" select="$compatibility_level"/>
		</xsl:call-template>
		--GO
		<xsl:call-template name="CreateTrigger">
			<xsl:with-param name="compatibility_level"  select="$compatibility_level"/>
		</xsl:call-template>
		--GO	
	</xsl:template>

<xsl:template name="IfExistsDropTable">
<xsl:param name="compatibility_level"/>
<xsl:choose>
			<xsl:when test="$compatibility_level='80'">
			if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>]
			</xsl:when>
			<xsl:when test="$compatibility_level='90'">
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>]') AND type in (N'U'))
DROP TABLE [<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>]
			</xsl:when>
		</xsl:choose>
	</xsl:template>

<xsl:template name="CreateTable">
<xsl:param name="compatibility_level"/>
		CREATE TABLE [<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>] (
		
	[AuditId] [uniqueidentifier] NOT NULL ,
	[UpdateType] [varchar] (50) NOT NULL ,
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_audit<xsl:value-of select="@name"/>_UpdateDate] DEFAULT (getdate()),
	[UpdatedBy] [varchar] (255) NOT NULL CONSTRAINT [DF_audit<xsl:value-of select="@name"/>_UpdatedBy] DEFAULT (suser_sname())<xsl:apply-templates select="dbcolumns" mode="createtable"/>
		)
</xsl:template>

<xsl:template name="CreatePK">
<xsl:param name="compatibility_level"/>
ALTER TABLE [<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>] ADD 
	CONSTRAINT [PK_audit<xsl:value-of select="@name"/>] PRIMARY KEY  CLUSTERED 
	(
		[AuditId],
		[UpdateType] DESC
		<xsl:apply-templates select="indexes" mode="createpk"></xsl:apply-templates>
	)
</xsl:template>
	
<xsl:template name="IfExistsDropTrigger">
<xsl:param name="compatibility_level"/>
<xsl:choose>
			<xsl:when test="$compatibility_level='80'">
			if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="@schema"/>].[trgAudit<xsl:value-of select="@name"/>]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop table [<xsl:value-of select="@schema"/>].[audit<xsl:value-of select="@name"/>]
			</xsl:when>
			<xsl:when test="$compatibility_level='90'">
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[<xsl:value-of select="@schema"/>].[trgAudit<xsl:value-of select="@name"/>]'))
DROP TRIGGER [<xsl:value-of select="@schema"/>].[trgAudit<xsl:value-of select="@name"/>]	
			</xsl:when>
		</xsl:choose>
</xsl:template>

<xsl:template name="CreateTrigger">
<xsl:param name="compatibility_level"/>
CREATE TRIGGER [trgAudit<xsl:value-of select="@name"/>] ON [<xsl:value-of select="@schema"/>].[<xsl:value-of select="@name"/>]
FOR INSERT, UPDATE, DELETE 
AS

DECLARE @GUID uniqueidentifier
DECLARE @RowsInserted numeric (18,0)
DECLARE @RowsDeleted numeric (18,0)
DECLARE @UpdateTypeDeletion varchar(20)
DECLARE @UpdateTypeInsertion varchar(20)

SET @GUID=newid()

SELECT
	@RowsInserted=count(*) 
FROM
	Inserted

SELECT
	@RowsDeleted=count(*) 
FROM
	Deleted

INSERT INTO <xsl:value-of select="@schema"/>.[audit<xsl:value-of select="@name"/>](AuditId, UpdateType <xsl:apply-templates select="dbcolumns" mode="fieldlist"/>)
SELECT @GUID,@UpdateTypeDeletion<xsl:apply-templates select="dbcolumns" mode="fieldlist"/>
FROM Deleted d

INSERT INTO <xsl:value-of select="@schema"/>.[audit<xsl:value-of select="@name"/>] (AuditId, UpdateType<xsl:apply-templates select="dbcolumns" mode="fieldlist"/>)
SELECT @GUID,@UpdateTypeInsertion<xsl:apply-templates select="dbcolumns" mode="fieldlist"/>
FROM Inserted i
</xsl:template>

<xsl:template match="indexes" mode="createpk">
		<xsl:apply-templates select="ind" mode="createpk"/>
	</xsl:template>
	
	<xsl:template match="ind"  mode="createpk">
		<xsl:apply-templates select="index_cols"  mode="createpk"/>
	</xsl:template>
		
	<xsl:template match="index_cols"  mode="createpk">,<xsl:variable name="colid" select="@column_id"/>
	[<xsl:value-of select="../../../dbcolumns/dbcolumn[@column_id=$colid]/@name"/>]</xsl:template>

<xsl:template match="dbcolumns" mode="createtable">
		<xsl:apply-templates select="dbcolumn" mode="createtable"></xsl:apply-templates>
	</xsl:template>

<xsl:template match="dbcolumn" mode="createtable">
		<xsl:choose>
			<xsl:when test="@type='binary' or @type='varbinary'">,
				[<xsl:value-of select="@name"/>] [<xsl:value-of select="@type"/>] ( <xsl:choose>
					<xsl:when test="@max_length=-1">max</xsl:when>
					<xsl:otherwise><xsl:value-of select="@max_length"/></xsl:otherwise>
				</xsl:choose>) <xsl:call-template name="nullable"/></xsl:when>
						<xsl:when test="@type='char' or @type='nchar' or @type='varchar' or @type='nvarchar'">,
				[<xsl:value-of select="@name"/>] [<xsl:value-of select="@type"/>] ( <xsl:choose>
					<xsl:when test="@max_length=-1">max</xsl:when>
					<xsl:otherwise><xsl:value-of select="@max_length"/></xsl:otherwise>
				</xsl:choose>) COLLATE <xsl:value-of select="@collation_name"/> <xsl:call-template name="nullable"/></xsl:when>
			<xsl:when test="@type='int' or @type='tinyint' or @type='smallint' or @type='bigint' or @type='datetime' or @type='timestamp' or @type='image' or @type='text' or @type='uniqueidentifier' or @type='smalldatetime' or @type='real' or @type='money' or @type='float' or @type='SQL_variant' or @type='ntext' or @type='bit' or @type='smallmoney' or @type='XML'" >,
				[<xsl:value-of select="@name"/>] [<xsl:value-of select="@type"/>] <xsl:call-template name="nullable"/></xsl:when>
			<xsl:when test="@type='decimal' or @type='numeric'">,
				[<xsl:value-of select="@name"/>] [<xsl:value-of select="@type"/>] (<xsl:value-of select="@precision"/>,<xsl:value-of select="@scale"/>)<xsl:call-template name="nullable"/></xsl:when>
		</xsl:choose>
	</xsl:template>

<xsl:template name="nullable"><xsl:value-of select="' '"></xsl:value-of>
		<xsl:choose>
			<xsl:when test="@is_nullable='YES'">NULL</xsl:when>
			<xsl:when test="@is_nullable='NO'">NOT NULL</xsl:when>
		</xsl:choose>
	</xsl:template>

<xsl:template match="dbcolumns" mode="fieldlist">
	<xsl:apply-templates select="dbcolumn" mode="fieldlist"/>
</xsl:template>

<xsl:template match="dbcolumn" mode="fieldlist">, [<xsl:value-of select="@name"/>]</xsl:template>

</xsl:stylesheet>

Doing the actual transformation.
my previous article I showed how SSIS could be used to actually apply the XSL transformation. I used this technique as it's commonly available to SQL developers and easy to set up. If you want to get more adventurous, you could create a CLR assembly and use it to do your transformations. Mike Rorke's blog post gives such an example http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx And if you do have an XML authoring tool (e.g. Microsoft's free XMLNotepad) , you can get it to do the transformation for you.

The end result - or is it?

The end result is a (big) single script which generates audit tables for all user tables in the database, triggers for each table generated and finally a rollback script (in comments) should you change your mind. There are various ways to modify this to your own requirements. You could modify the SQL, if you only want certain tables to be generated. If you're willing to use a different XML parser (the XSL included has been written for the Microsoft XML Parsers 4-6), you can generate multiple output documents e.g. one for each table, or a separate rollback document. Hopefully Microsoft will soon make this available in their parsers.

I hope I've been able to provide a little more instruction into the murky world of XSL, and also provided you with a useful tool for building your auditing solution in seconds.

Total article views: 4955 | Views in the last 30 days: 1

FORUM

An Auditing Solution with XML And XSL

Design of the Auditing Solution

The end result - or is it?

compatability level

compatibility level

compatibility level

Upgrading compatibility level is not backwards compatible?

SQL log COMPATIBILITY_LEVEL

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

An Auditing Solution with XML And XSL

Design of the Auditing Solution

The end result - or is it?

compatability level

compatibility level

compatibility level

Upgrading compatibility level is not backwards compatible?

SQL log COMPATIBILITY_LEVEL

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: