SQL Server: How to audit rollback-ed data in a trigger?

Fri Apr 13, 2007 by Mladen Prajdić in sql-server

The most popular method of auditing data is with an

AFTER DELETE, INSERT, UPDATE trigger.

But what happens if you have a Rollback statement in your trigger if some condition is met?

You rollback your transaction but that also empties the inserted and deleted pseudo tables and all audit insert done in the trigger.

You get to the Catch 22 situation. So how to solve this?

It's acctually very simple. Use Table Variables.

Table variables live outside of the transaction and so their contents aren't affected by Commit or Rollback of the transaction.

Code below explaines this nicly:

CREATE TABLE MyPharmaceuticals
(
    Id INT IDENTITY PRIMARY KEY,
    DrugName VARCHAR(50)
)
GO
CREATE TABLE MyPharmaceuticalsAudit
(
    Id INT,
    DrugName VARCHAR(50)
)
GO
– CATCH 22 SITUATION –
SELECT 'Start CATCH 22'
GO
CREATE TRIGGER trgMyPharmaceuticalsAudit
ON MyPharmaceuticals
AFTER INSERT, UPDATE
AS
– handle insert and update
INSERT INTO MyPharmaceuticalsAudit
SELECT    Id, DrugName
FROM    inserted
<span class="rem">-- i don&#39;t want any batch that has Prozac in in</span>
<span class="rem">-- but i want to log the attempted insert of it</span>
<span class="kwrd">IF</span> (<span class="kwrd">EXISTS</span>(<span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> inserted <span class="kwrd">WHERE</span> DrugName = <span class="str">&#39;Prozac&#39;</span>) 
    <span class="kwrd">AND</span> <span class="preproc">@@TRANCOUNT</span> &gt; 0)
<span class="kwrd">BEGIN</span>
    <span class="kwrd">SELECT</span> @@TRANCOUNT, <span class="str">&#39;Prozac found. Rolling back CATCH 22.&#39;</span>
    <span class="rem">-- but this won&#39;t log it since the transaction </span>
    <span class="rem">-- and the upper insert will be rolled back</span>
    <span class="kwrd">ROLLBACK</span>
<span class="kwrd">END</span>

GO
– of course we should use the most common drugs in 
– use on the internet today first :)
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Viagra' UNION ALL
SELECT 'Cialis' UNION ALL
SELECT 'Aspirin' UNION ALL
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 1'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Prozac' UNION ALL
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 2'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Prozac'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 3'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 4'
COMMIT
END
GO
SELECT * FROM MyPharmaceuticals
SELECT * FROM MyPharmaceuticalsAudit
GO
DROP TRIGGER trgMyPharmaceuticalsAudit
GO
TRUNCATE TABLE MyPharmaceuticals
TRUNCATE TABLE MyPharmaceuticalsAudit
GO
– NO CATCH 22 SITUATION –
SELECT 'Start NO CATCH 22'
GO
CREATE TRIGGER trgMyPharmaceuticalsAudit
ON MyPharmaceuticals
AFTER INSERT, UPDATE
AS
DECLARE @inserted TABLE (Id INT, DrugName VARCHAR(50))
INSERT INTO @inserted
SELECT    Id, DrugName
FROM    inserted
<span class="rem">-- i don&#39;t want any Prozac in MyPharmaceuticals</span>
<span class="rem">-- but i want to log the attempted insert of it</span>
<span class="kwrd">IF</span> (<span class="kwrd">EXISTS</span>(<span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> @inserted <span class="kwrd">WHERE</span> DrugName = <span class="str">&#39;Prozac&#39;</span>) 
    <span class="kwrd">AND</span> <span class="preproc">@@TRANCOUNT</span> &gt; 0)
<span class="kwrd">BEGIN</span>
    <span class="kwrd">SELECT</span> @@TRANCOUNT, <span class="str">&#39;Prozac found. Rolling back NO CATCH 22.&#39;</span>
    <span class="kwrd">ROLLBACK</span>
<span class="kwrd">END</span>
<span class="rem">-- insert must be of course called after the rollback </span>
<span class="rem">-- otherwise we&#39;ll be rolling it back </span>
<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> MyPharmaceuticalsAudit
<span class="kwrd">SELECT</span>    Id, DrugName
<span class="kwrd">FROM</span>    @inserted

GO
– of course we should use the most common drugs in 
– use on the internet today first :)
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Viagra' UNION ALL
SELECT 'Cialis' UNION ALL
SELECT 'Aspirin' UNION ALL
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 1'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Prozac' UNION ALL
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 2'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Prozac'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 3'
COMMIT
END
GO
BEGIN TRAN
INSERT INTO MyPharmaceuticals (DrugName)
SELECT 'Penicilin'
IF @@TRANCOUNT > 0
BEGIN
SELECT @@TRANCOUNT, 'insert 4'
COMMIT
END
GO
SELECT * FROM MyPharmaceuticals
SELECT * FROM MyPharmaceuticalsAudit
GO
DROP TABLE MyPharmaceuticals
DROP TABLE MyPharmaceuticalsAudit