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
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)
ANDDATA_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 AUTONow 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"> <!-- 1) If exists drop table 2) If exists Drop Trigger --> <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"> <!-- 1) If exists drop table 2) Create Table 3) Create PK 4) If exists Drop Trigger 5) CreateTrigger --> <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 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 <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 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
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.
