Let’s assume we have an empty table in the TestDB database with the following structure:
USE TestDB
GO
CREATE TABLE TestTable
(
ID INT PRIMARY KEY,
Val INT
)
GO
CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable (Val)
GO
There are transactions in the code sections below that will fail because the duplicate values are inserted:Section 1USE TestDB
GO
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
INSERT INTO TestTable(ID, Val)
VALUES(2, 100)
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END
END CATCH
Section 2USE TestDB
GO
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
INSERT INTO TestTable(ID, Val)
VALUES(2, 100)
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() = 1
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END
END CATCH
Section 3USE TestDB
GO
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
INSERT INTO TestTable(ID, Val)
VALUES(2, 100)
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() > 0
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END
END CATCH
Section 4USE TestDB
GO
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
INSERT INTO TestTable(ID, Val)
VALUES(2, 100)
INSERT INTO TestTable(ID, Val)
VALUES(1, 100)
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
PRINT 'Transaction is rolled back'
END CATCH
In three of these sections of code, the situation is correctly handled, allowing the transactions to be rolled back successfully. Which three? |
