Browse by Tag : logging (RSS)

Create a Dynamic Logging Trigger With XML

By Michael Coles in Pro SQL Server XML | 01-26-2008 8:47 PM | Categories: Filed under: XML, new features, programming, SQL Server, auditing, logging, trigger

Rating: |

Discuss | 6,390 Reads | 438 Reads in Last 30 Days |no comments

One of the more common uses of triggers is to create data manipulation language (DML) logging functionality. Essentially you can intercept and log inserts, updates, and deletes to tables. In the simplest case it's enough to know that one of these operations has occurred on a table, the date/time that it occurred, and some sort of identifying information for the user that performed the operation. At the other end of the requirements spectrum is the need to identify all information, including data indicating what exactly has changed.

What you usually find is that people who need to log DML operations will write one trigger and modify the same basic code for every other table that needs to be logged. I know one person who even wrote a client-side utility to automatically generate custom trigger code for hundreds of tables that he had to log. One problem with this method, apart from the sheer boredom of modifying the same trigger, again and again, is maintenance. If the underlying table structure changes your trigger is suddenly shot. In this post we're going to use FOR XML and SQL Server catalog views to create a dynamic trigger that will work on just about any table, and will automatically adjust it's output if the table structure changes.

To start with, we'll create a log table called dbo.AuditDml in the AdventureWorks database:

CREATE TABLE dbo.AuditDml (Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
SchemaName VARCHAR(100) NOT NULL,
TableName VARCHAR(100) NOT NULL,
TriggerName VARCHAR(100) NOT NULL,
LogTime DATETIME NOT NULL,
UserName VARCHAR(100) NULL,
SPID INT NOT NULL,
Changes XML);
GO

Next we’ll create a dynamic trigger. By dynamic I mean that you can run this script against any table and it will create a trigger that automatically detects its parent table and schema and logs all DML actions against the table properly, regardless of table structure. The example below creates the dynamic trigger on the AdventureWorks HumanResources.Shift table.

-- Change the schema and table name to match any
-- existing table in your database

CREATE TRIGGER HumanResources.AuditDml_Shift
ON HumanResources.Shift
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @Changes XML;
SELECT @Changes = COALESCE((
      SELECT *
    FROM deleted
    FOR XML AUTO), '') +
    COALESCE((
      SELECT *
      FROM inserted
      FOR XML AUTO), '')

  DECLARE @TriggerID INT;
DECLARE @TableID INT;
DECLARE @SchemaID INT;
DECLARE @SchemaName VARCHAR(100);

SET @TriggerID = @@PROCID;

SELECT @TableID = parent_id
FROM sys.triggers
WHERE object_id = @TriggerID;

SELECT @SchemaID = t.schema_id,
    @SchemaName = s.name
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE t.object_id = @TableID;

INSERT INTO dbo.AuditDml (SchemaName,
    TableName,
    TriggerName,
    LogTime,
    UserName,
    SPID,
    Changes)
SELECT @SchemaName,
    OBJECT_NAME(@TableID),
    OBJECT_NAME(@TriggerID),
    GETDATE(),
    USER_NAME(),
    @@SPID,
    @Changes;
END
GO

The dynamic trigger automatically determines it's schema and parent table name based on its own ID; so it doesn't matter what table this trigger is created on. It will always automatically detect this information with no special action on the part of the developer. The actual data changes are grabbed from the inserted and deleted virtual tables, which are dynamically structured using the FOR XML AUTO clause. FOR XML AUTO structures the XML data automatically based on the columns in the inserted and deleted virtual tables. Again, you don't have to do anything special, the trigger will automatically adjust its output based on the structure of the table - even if the table structure changes in the future. Now that we have a dynamic trigger created on the HumanResources.Shift table, we'll run a few DML statements to test it.

INSERT INTO HumanResources.Shift (Name, StartTime, EndTime)
VALUES ('Noon', '12:00:00', '20:00:00');

UPDATE HumanResources.Shift SET Name = 'Afternoon' WHERE Name = 'Noon';

DELETE FROM HumanResources.Shift WHERE ShiftId > 3;

These statements perform an insert, an update, and a subsequent delete of a new example shift I call the "Noon" or "Afternoon" shift. The actions are logged, as shown in the image below.

Each entry has information like the schema name, table name, date/time, user name, etc. The Changes column is an XML data type column with the contents of the inserted and deleted virtual tables in XML format. The image below shows the results of the sample UPDATE statement above.

xml changes log entry for update statement

This type of dynamic logging is especially useful when you have to log DML activity for several tables in a transactional system. A couple of caveats: This trigger may require some changes if your table contains LOB data type columns. Also always take care when using triggers on high-DML activity tables, as triggers of any kind can affect performance.

SQL Server Standard magazine will be publishing more of these tips for getting the most out of SQL Server XML in a future issue.