021_autonomous_tx.sql 1.25 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
/*
	One method to simulate an autonomous transaction is with @TableVariables

*/

CREATE DATABASE TranDemo 
GO
USE TranDemo
GO

CREATE TABLE FooBar (ID int NOT NULL PRIMARY KEY CLUSTERED);
GO
CREATE TABLE Logger (ID int NOT NULL, Msg varchar(200));
GO

--@TableVariables do NOT participate in transaction management
DECLARE @Logger TABLE (ID int NOT NULL IDENTITY(1,1) , Msg varchar(200));


--start my batch processing
BEGIN TRY
BEGIN TRAN
	INSERT INTO @Logger (Msg) VALUES ('Starting my batch processing.');

	INSERT INTO @Logger (Msg) VALUES ('Inserting 1');
	INSERT INTO FooBar VALUES (1);
	INSERT INTO @Logger (Msg) VALUES ('Inserting 2');
	INSERT INTO FooBar VALUES (2);
	INSERT INTO @Logger (Msg) VALUES ('Inserting 3');
	INSERT INTO FooBar VALUES (3);
	INSERT INTO @Logger (Msg) VALUES ('Inserting 1...again');
	INSERT INTO FooBar VALUES (1);
	INSERT INTO @Logger (Msg) VALUES ('Completed my batch processing.');
COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	INSERT INTO @Logger (Msg) VALUES ('Error occurred during my batch processing');
END CATCH

--FINALLY...update dbo.Logger 
INSERT INTO Logger SELECT * FROM @Logger

--Let's look at Logger
SELECT * FROM Logger

--cleanup
USE master
GO
DROP DATABASE TranDemo;
GO