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