I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

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

    -- i don't want any batch that has Prozac in in
    -- but i want to log the attempted insert of it
    IF (EXISTS(SELECT * FROM inserted WHERE DrugName = 'Prozac') 
        AND @@TRANCOUNT > 0)
    BEGIN
        SELECT @@TRANCOUNT, 'Prozac found. Rolling back CATCH 22.'
        -- but this won't log it since the transaction 
        -- and the upper insert will be rolled back
        ROLLBACK
    END
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
    
    -- i don't want any Prozac in MyPharmaceuticals
    -- but i want to log the attempted insert of it
    IF (EXISTS(SELECT * FROM @inserted WHERE DrugName = 'Prozac') 
        AND @@TRANCOUNT > 0)
    BEGIN
        SELECT @@TRANCOUNT, 'Prozac found. Rolling back NO CATCH 22.'
        ROLLBACK
    END
    -- insert must be of course called after the rollback 
    -- otherwise we'll be rolling it back 
    INSERT INTO MyPharmaceuticalsAudit
    SELECT    Id, DrugName
    FROM    @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
 

kick it on DotNetKicks.com

Print | posted on Friday, April 13, 2007 5:58 PM | Filed Under [ SQL Server ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET