Imagine an audit table that allows you to rollback the changes you just made to the data.  Or imagine that you’ve had to restore a database from last nights backup, and you want to roll forward this morning’s transactions – for all tables except one.  Imagine an audit table that will give you the sql insert script for the file you’ve just loaded using SSIS.  Imagine all this without using a 3^rd party tool, and without going near the transaction log.

The code in this article is going to give you all of this, and more – sort of.

Enough imagining - now for the reality.  The basic idea is an audit trigger that, instead of just recording old and new values, will actually generate the sql for you to enable you to reproduce the exact same insert, update or delete.  In addition, it will also generate the sql to rollback the statement.

Note: The code below was written for SQL 2005.

Auditing the Items table.            

Let’s say we want to double the price of items containing chocolate.

You might use an update statement like the following

UPDATE tblItems 
 SET Price=Price*2 
 WHERE lower(Item) like '%chocolate%'

The audit table should record the following

An INSERT or a DELETE works in a similar way.

DELETE FROM tblItems WHERE ItemName=’Soda’

Should give the following in the audit table.

(Also if ItemId is an identity column, the INSERT statement must be surrounded by SET IDENTITY_INSERT ON / OFF.)

Creating the Audit database

I’m creating a specific database to store the audit table.  (There is only one audit table.)  If you’d rather not create a new database, then you can create the audit table in an existing database – but you’ll have to make a couple of simple modifications to the code.

CREATE DATABASE Audit
GO
USE Audit
GO
CREATE TABLE [dbo].[BigAudit](
[Autonumber] [bigint] IDENTITY(1,1) NOT NULL,
[Catalog] [nvarchar](128) NOT NULL,
[AuditedTable] [nvarchar](255) NOT NULL,
[RollForwardSQL] [nvarchar](4000) NOT NULL,
[RollBackSQL] [nvarchar](4000) NOT NULL,
[TStamp] [datetime] NOT NULL CONSTRAINT [DF_BigAudit_TStamp]  DEFAULT (getdate()),
 CONSTRAINT [PK_BigAudit] PRIMARY KEY CLUSTERED 
(
[Autonumber] ASC
)
) 

Creating the Items Table

For the purposes of this example, let’s create the items table in the Audit database (something that wouldn’t normally happen – and it’ll work fine if you create it somewhere else.)  The script below will create the table and populate it with 4 rows as above.

CREATE TABLE [dbo].[tblItem](
[ItemId] [int] NOT NULL,
[Item] [nvarchar](100) NOT NULL,
[Price] [numeric](18, 2) NOT NULL,
 CONSTRAINT [PK_tblItem] PRIMARY KEY CLUSTERED 
(
[ItemId] ASC
)
) 

insert into [dbo].[tblItem]   (  [ItemId],  [Item],  [Price])   select 1,'Chocolate bar',1.15
insert into [dbo].[tblItem]   (  [ItemId],  [Item],  [Price])   select 2,'Soda',1.00
insert into [dbo].[tblItem]   (  [ItemId],  [Item],  [Price])   select 3,'Chocolate milkshake',2.00
insert into [dbo].[tblItem]   (  [ItemId],  [Item],  [Price])   select 4,'Mineral Water',1.30

Creating the Audit Trigger

Auditing is done by a regular trigger, which first examines the inserted and deleted tables to establish whether we’re dealing with an insert, delete or update.  In then tackles each case differently – constructing the sql by selecting from the inserted and deleted tables.

The script below will create the trigger on the Items table.  Once this is created, you can start playing about, by inserting updating and deleting rows from the Items table.  The changes – and the sql to undo the changes – will be scripted for you in the table BigAudit.

IF EXISTS ( SELECT  *
            FROM    sys.triggers
            WHERE   object_id = OBJECT_ID(N'[dbo].[trgAudittblItem]') ) 
    DROP TRIGGER [dbo].[trgAudittblItem]
GO

CREATE TRIGGER [dbo].[trgAudittblItem] ON [dbo].[tblItem]
    FOR INSERT, UPDATE, DELETE
AS
    DECLARE @IsInsert BIT
    DECLARE @IsDelete BIT
    DECLARE @IsUpdate BIT
    DECLARE @RowsInserted INT
    DECLARE @RowsDeleted INT
    DECLARE @TStamp DATETIME
    SELECT  @RowsInserted = COUNT(*)
    FROM    Inserted
    SELECT  @RowsDeleted = COUNT(*)
    FROM    Deleted
    IF @RowsInserted != 0
        AND @RowsDeleted != 0 
        SET @IsUpdate = 1
    ELSE 
        IF @RowsInserted != 0 
            SET @IsInsert = 1
        ELSE 
            IF @RowsDeleted != 0 
                SET @IsDelete = 1
    SELECT  @TStamp = GETDATE()

    DECLARE @GUID UNIQUEIDENTIFIER
    SET @GUID = NEWID()
    IF @IsInsert = 1 
        BEGIN
            INSERT  INTO Audit.dbo.BigAudit
                    (
                      Catalog,
                      AuditedTable,
                      RollForwardSQL,
                      RollBackSQL,
                      TStamp
                    )
                    SELECT  'YourDatabase' AS Catalog,
                            '[dbo].[tblItem]' AS AuditedTable,
                            'insert into [dbo].[tblItem]
(
[ItemId],
[Item],
[Price])
' + 'select ' + CAST(i.[ItemId] AS VARCHAR) + ',' + QUOTENAME(i.[Item], '''')
                            + ',' + CAST(i.[Price] AS VARCHAR) AS RollForwardSQL,
                            'DELETE FROM [dbo].[tblItem] WHERE 
[ItemId]= ' + CAST(i.[ItemId] AS VARCHAR) AS RollBackSQL,
                            @TStamp
                    FROM    Inserted i
        END
    IF @IsDelete = 1 
        BEGIN
            INSERT  INTO Audit.dbo.BigAudit
                    (
                      Catalog,
                      AuditedTable,
                      RollForwardSQL,
                      RollBackSQL,
                      TStamp
                    )
                    SELECT  'YourDatabase' AS Catalog,
                            '[dbo].[tblItem]' AS AuditedTable,
                            'DELETE FROM [dbo].[tblItem] WHERE 
[ItemId]= ' + CAST(i.[ItemId] AS VARCHAR) AS RollForwardSQL,
                            'insert into [dbo].[tblItem]
(
[ItemId],
[Item],
[Price])
' + 'select ' + CAST(i.[ItemId] AS VARCHAR) + ',' + QUOTENAME(i.[Item], '''')
                            + ',' + CAST(i.[Price] AS VARCHAR) AS RollBackSQL,
                            @TStamp
                    FROM    deleted i
        END

    IF @IsUpdate = 1 
        BEGIN
            INSERT  INTO Audit.dbo.BigAudit
                    (
                      Catalog,
                      AuditedTable,
                      RollForwardSQL,
                      RollBackSQL,
                      TStamp
                    )
                    SELECT  'YourDatabase' AS Catalog,
                            '[dbo].[tblItem]' AS AuditedTable,
                            'UPDATE [dbo].[tblItem] 
SET 
[Item]=' + QUOTENAME(t1.[Item], '''') + ', 
[Price]=' + CAST(t1.[Price] AS VARCHAR) + ' WHERE 

[ItemId]=' + CAST(t1.[ItemId] AS VARCHAR) AS RollForwardSQL,
                            'UPDATE [dbo].[tblItem] 
SET 
[Item]=' + QUOTENAME(t2.[Item], '''') + ', 
[Price]=' + CAST(t2.[Price] AS VARCHAR) + ' WHERE 

[ItemId]=' + CAST(t2.[ItemId] AS VARCHAR) AS RollBackSQL,
                            @TStamp
                    FROM    Inserted t1
                            INNER JOIN Deleted t2 ON t1.[ItemId] = t2.[ItemId]


        END
GO

Now try executing the update and delete statements we saw earlier against the item table. Then take a look at the BigAudit table in the fields RollBackSQL and RollForwardSQL.

Try with your own SQL statements - an insert for example.

If you want to rollback the changes just select the script from the BigAudit table

SELECT RollBackSQL FROM dbo.BigAudit ORDER BY Autonumber DESC

Copy the results into a query window and execute to rollback the changes. (By the way, this will also get recorded to the audit table.)

Note the descending sort so that the changes get undone in the right order. This means, for example, that you won't get problems with foreign key constraints.

At this stage you may well be thinking, well that’s quite cool, but I’ve got lots of tables, with lots of fields, and it’s going to take me forever to write such triggers for them.  The good news is that you don’t have to.

In Part II, I’ll be showing you how you can generate these triggers for all tables in your database, in a matter of minutes. 

There are a couple of conditions – it won’t generate triggers for tables that don’t have primary keys, and it won’t correctly audit changes when changes are made to primary key fields.  Hopefully, this won’t be an issue for most of us.  I’m a firm believer that every table without exception should have a primary key, and it’s generally considered bad practice to change the value of a primary key field.

A little clue about how it's done - XML is heavily involved!