Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Auditing Solution with XML And XSL

By David McKinney,

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.

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

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

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: 4989 | Views in the last 30 days: 0
 
Related Articles
FORUM

compatability level

compatability level

FORUM

compatibility level

compatibility level

FORUM

compatibility level

compatibility level

FORUM

Upgrading compatibility level is not backwards compatible?

Upgrading compatibility level is not backwards compatible?

FORUM

SQL log COMPATIBILITY_LEVEL

SQL log COMPATIBILITY_LEVEL

Tags
development    
miscellaneous    
security    
sql server 2005    
strategies    
xml    
xsl    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones