Preserving Logging Data When a Transaction Rollbacks

Category : Blogs Published : September 25, 2013 User Rating : 5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

A common practice when running SQL statements is to log each statement by inserting information into a table. This can be useful for auditing purposes and to log errors etc. However a problem occurs when using transactions and the transaction is rolled back (for instance if there is an error), because the inserts into the log table will also be rolled back !

However it’s fairly easy to get around this by taking advantage of the fact that table variables do not participate in transaction rollbacks. By storing the log data in a table variable and then copying this data into the ‘real’ table once the transaction is completed the log data is not lost.

By way of example here is some code to demonstrate this :

-- Create the log table
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[AuditLog]')
                AND type in (N'U'))

   CREATE TABLE AuditLog (LogDate DATETIME, LogText VARCHAR(1000))
-- Create table variable to temporarily store log entries during transaction
DECLARE @AuditLogVar TABLE (LogDate DATETIME, LogText VARCHAR(1000))
    INSERT INTO @AuditLogVar (LogDate,LogText) VALUES (GetDate(),'Starting process..')
    INSERT INTO @AuditLogVar (LogDate,LogText) VALUES (GetDate(),'Initialising variable')
      -- Force an error :
      SELECT @iCount = 1/0
    -- Process complete
    INSERT INTO @AuditLogVar (LogDate,LogText) VALUES (GetDate(),'Process complete' )
    -- All OK, commit the transaction
    INSERT INTO @AuditLogVar (LogDate,LogText) VALUES (GetDate(),
            'Process not completed, an error has occurred : '+ERROR_MESSAGE())

      -- An error has occurred, rollback the transaction
-- Transaction is complete, copy logged data into table
INSERT INTO AuditLog (LogDate,LogText) SELECT LogDate,LogText FROM @AuditLogVar
Here I’ve created a table variable to temporarily store logging data. I’ve then started a transaction and forced a rollback of the transction by causing a divide by zero error, which will roll back any changes made to data (though in this simplistic example I’ve not actually made any changes to data). However the logging information in the table variable is not rolled back. This information is then copied to the ‘real’ table once the transaction is complete.

I can check this by running the query :

SELECT LogDate,LogText FROM AuditLog ORDER BY LogDate DESC

Which gave me the following result :
Preserving Logging Data When a Transaction Rollbacks

Related Articles

The following articles may also be of interest :

Link back to this article : Logging Data When a Transaction Rollbacks.aspx


SQL, blogs


Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered