-------------------------------------------------------------------
-----code:start
-------------------------------------------------------------------
DECLARE
@ERR NVARCHAR(100)
,@ERR1 NVARCHAR(100)
,@ERR2 NVARCHAR(100)
-----Check Availability Table Temporary
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.tables
WHERE table_name ='Temp_Tran_1'
)
DROP TABLE Temp_Tran_1
-----Create Temporary Table Sample
CREATE TABLE Temp_Tran_1
( Cola INT PRIMARY KEY
,Colb CHAR(3)
)
BEGIN TRANSACTION OuterTran
PRINT '-----Run : OuterTran-----'
INSERT INTO Temp_Tran_1 VALUES (1, 'a')
SELECT @ERR = @@ERROR
PRINT '------------------------------------------------------------------'
BEGIN TRANSACTION Inner1
PRINT '-----Run : Inner1-----'
INSERT INTO Temp_Tran_1 VALUES (2, 'bbBBBBb')
SELECT @ERR1 = @@ERROR
PRINT '------------------------------------------------------------------'
BEGIN TRANSACTION Inner2
PRINT '-----Run : Inner2-----'
INSERT INTO Temp_Tran_1 VALUES (3, 'cccFSFSDFSD')
SELECT @ERR2 = @@ERROR
PRINT '------------------------------------------------------------------'
PRINT '------------------------------------------------------------------'
PRINT '-----Checking : For Commit Transaction-----'
IF @ERR = 0 AND @ERR1=0 AND @ERR2=0
BEGIN
COMMIT TRANSACTION Inner2
PRINT '------------------------------------------------------------------'
COMMIT TRANSACTION Inner1
PRINT '------------------------------------------------------------------'
COMMIT TRANSACTION OuterTran
PRINT '------------------------------------------------------------------'
END
ELSE
BEGIN
IF @ERR<>0
PRINT 'ERROR DURING INSERT At OuterTran'
IF @ERR1<>0
PRINT 'ERROR DURING INSERT At Inner1'
IF @ERR2<>0
PRINT 'ERROR DURING INSERT At Inner2'
ROLLBACK TRANSACTION
END
PRINT '------------------------------------------------------------------'
PRINT '------------------------------------------------------------------'
PRINT '-----View The Results-----'
SELECT * FROM Temp_Tran_1
-------------------------------------------------------------------
-----code:end
-------------------------------------------------------------------
Example Result Messages:
-----------------------------------------------------
-----Run : OuterTran-----
(1 row(s) affected)
------------------------------------------------------------------
-----Run : Inner1-----
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
------------------------------------------------------------------
-----Run : Inner2-----
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
------------------------------------------------------------------
------------------------------------------------------------------
-----Checking : For Commit Transaction-----
ERROR DURING INSERT At Inner1
ERROR DURING INSERT At Inner2
------------------------------------------------------------------
------------------------------------------------------------------
-----View The Results-----
Cola Colb
----------- ----
(0 row(s) affected)
Note:
The results in Table : Temp_Tran_1, still empty
= because there still an error when commit at transaction Inner1 and Inner2.
If there is no an error, in 3 transaction, example:
OuterTran, Inner1, Inner2
so process inserted to Table : Temp_Tran_1, will successfully.