Thank this author by sharing:
By David McKinney,
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.
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!
Figure 1 shows a couple of tables from Northwind with their associated audit tables.
Below is the trigger that would be used to populate the [Order Details] table.
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)
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.
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 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.
Upgrading compatibility level is not backwards compatible?
SQL log COMPATIBILITY_LEVEL