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

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

By David McKinney,

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 3rd 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.            

ItemId ItemName Price
1 Chocolate bar 1.15
2 Soda 1.00
3 Chocolate milkshake 2.00
4 Mineral Water 1.30

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

RollforwardSQL RollbackSQL
UPDATE tblItem SET Price=3.30 WHERE ItemId=1 UPDATE tblItem SET Price=1.15 WHERE ItemId=1
UPDATE tblItem SET Price=4.00 WHERE ItemId=3 UPDATE tblItem SET Price=2.00 WHERE ItemId=3

An INSERT or a DELETE works in a similar way.

DELETE FROM tblItems WHERE ItemName=’Soda’

Should give the following in the audit table.

RollforwardSQL RollbackSQL
DELETE FROM tblItems WHERE ItemId=2

INSERT INTO tblItems (ItemId, Item, Price)

VALUES (2, ‘Soda’, 1.00)

(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!

Resources:

CreatingTheAuditDatabase.sql | CreateItemsTable.sql | CreatingTheAuditTrigger.sql
Total article views: 6018 | Views in the last 30 days: 2
 
Related Articles
FORUM
FORUM

Audit

Audit

FORUM

Audit

Triggers for audit

FORUM

auditing

column level auditing using profiler

ARTICLE

Audit Trails and Logging Part II

Continuing with Part II of his auditing series, Gsquared takes a look at active auditing techniques ...

Tags
auditing    
rollback    
 
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