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))
DECLARE @iCount INT
BEGIN TRY
BEGIN
TRAN
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
COMMIT
TRAN
END TRY
BEGIN CATCH
INSERT
INTO @AuditLogVar (LogDate,LogText) VALUES (GetDate(),
'Process not completed, an
error has occurred : '+ERROR_MESSAGE())
-- An error has occurred, rollback the transaction
ROLLBACK TRAN
END CATCH
-- 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 :
