If you haven’t read Part I, now might be a good time to do so and get some background on this series.

My specialist area seems to be XML and XSL with SQL Server.  It is in fact a very specialist area – as far as I can gather there are only a handful of us.  I’m sure by now most of you have a fair idea about what XML is, and know that SQL Server can return data in XML format.  For those who don’t know what XSL is, though, it’s a stylesheet language used to transform XML into other formats.

The goal of this article is to generate audit triggers for all tables in a database in a single operation.  What's more, the audit table will record SQL to rollback and rollforward changes. To do this, I’ll be using XML and XSL, and here’s how.  The audit trigger needs to know about the fields in the table being audited - the datatypes, and which fields form the primary key etc..  This type of information is contained in various system tables / views.  The sql below queries the system tables to return XML which describes all tables in the database against which it is executed. 

WITH    pkcolumns
          AS ( SELECT   si.name,
                        sic.column_id,
                        si.object_id
               FROM     sys.indexes si
                        INNER JOIN sys.index_columns sic ON si.index_id = sic.index_id
                                                            AND si.object_id = sic.object_id
               WHERE    si.is_primary_key = 1
             ) ,
        tables
          AS ( SELECT   t.name AS tableName,
                        t.object_id AS objectId,
                        s.name AS schemaName
               FROM     sys.tables t
                        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
               WHERE    t.type = 'U'
             ) ,
        columns
          AS ( SELECT   OBJECT_NAME(col.object_id) AS objectName,
                        col.object_id AS objectId,
                        col.column_id AS columnId,
                        col.name AS columnName,
                        col.is_identity AS isIdentity,
                        pk.name AS pkName,
                        typ.name AS typeName,
                        CASE WHEN typ.name LIKE '%datetime%' THEN 1
                             WHEN typ.precision = 0 THEN 1
                             ELSE 0
                        END AS 'takesQuotes',
                        CASE WHEN typ.NAME LIKE '%datetime%' THEN 1
                             ELSE 0
                        END AS isDateTime,
                        col.is_nullable AS isNullable
               FROM     sys.columns col
                        LEFT JOIN pkcolumns pk ON col.object_id = pk.object_id
                                                  AND col.column_id = pk.column_id
                        INNER JOIN sys.types typ ON col.user_type_id = typ.user_type_id
                                                    AND col.system_type_id = typ.system_type_id
--order by object_name(col.object_id), column_id
             )
    SELECT  [name],
            compatibility_level,
            ( SELECT    t.tableName,
                        t.schemaName,
                        t.objectId,
                        ( SELECT    c.isIdentity "@isIdentity",
                                    c.isDateTime "@isDateTime",
                                    c.takesQuotes "@takesQuotes",
                                    c.isNullable "@isNullable",
                                    c.columnName,
                                    c.pkName,
                                    c.typeName
                          FROM      columns c
                          WHERE     c.objectId = t.objectId
                          ORDER BY  objectName,
                                    columnId
                        FOR
                          XML PATH('column'),
                              TYPE
                        )
              FROM      tables t
            FOR
              XML PATH('table'),
                  TYPE
            )
    FROM    sys.databases AS [database]
    WHERE   DB_NAME() = [database].name
FOR     XML AUTO

If you look closely, you'll see I've used Common Table Expressions to simplify the XML generation. Each CTE represents a different level in the XML hierarchy.

The structure of the XML document thus created is shown by the schema diagram below (generated with XML Spy.)

Figure 1 Schema describing the XML

The next stage is to write the XSL document which will transform this XML into the audit triggers, as seen in Part I.  I’m not going to go into to any detail about how the XSL sheet is structured, except to say that I found writing it very challenging, as the XSL document was a strange mixture of XML, SQL of the trigger and SQL generated by the trigger.  The final result isn’t the most beautiful XSL sheet I’ve ever written, but it does the job it’s supposed to do.

A variety of methods may be used to carry out the actual XSL transformation.

I outlined one such method using SSIS in a previous article I've got the XML - Now What? .  You can also use a tool such as XML Spy or a CLR function. There is even a command line utility available as a free download from Microsoft.

The result of the transformation is a SQL script to generate the audit triggers.  All being well, you should simply be able to run it against your database.  It will try to audit to a table called BigAudit in a database called Audit.  The scripts to generate the audit database and audit table are included in Part I.

I hope you’ve managed to keep up, and are now happily playing with your new auditing solution.  Of course, there are various reasons why in practice you probably aren’t going to want to audit all tables in your database (or on your SQL Server instance) to a single audit table.  (I wrote this principally to see if it could be done, rather than to meet a requirement.)  However I do believe there are many potential practical applications for such a solution, and I look forward to hearing your views on this.