I do not confess to being a database expert, nor a guru in T-SQL, which is why I have only just found out how crap transactions in T-SQL on SQL Server 2000 can be.  Take a look at the following piece of T-SQL:

CREATE TABLE #Foo
(
PK int NOT NULL
)

CREATE TABLE #Foo2
(
PK int NOT NULL
)

BEGIN TRANSACTION

INSERT INTO #Foo (PK) VALUES (1)
INSERT INTO #Foo2 (PK) VALUES (NULL)

COMMIT TRANSACTION

SELECT * FROM #Foo

DROP TABLE #Foo2
DROP TABLE #Foo

The above script creates two temporary tables, attempts to insert a value in the first, and then a NULL in the second table.  Since #Foo2.PK does not allow NULL values, the second insert fails.  I naively assumed that because I have wrapped the insert statements in a transaction, the commit will only complete if no error occurs.  This is not the case.  The commit statement commits the first insert but not the second – not exactly what I had in mind.  

The problem experienced above, is partially due to bad coding on my part because I did not initially understand the intricacies of how SQL Server 2000 manages transactions – the user is responsible for managing the roll back of a transaction after a statement error. The correct approach would have been to check the @@ERROR value for non-zero after each statement in the transaction block to see if a roll back was required. This can be a real chore if a transaction has many statements, and especially annoying if the error number of any one statement be reported, and the transaction aborted upon an error (typical behavior).  Fortunately, SQL Server 2005 comes to the rescue with try-catch – similar to that of how ADO.NET deals with transactions.  The following code will produce the desired results in SQL Server 2005:

CREATE TABLE #Foo
(
PK int NOT NULL
)

CREATE TABLE #Foo2
(
PK int NOT NULL
)

BEGIN TRANSACTION

BEGIN TRY

INSERT INTO #Foo (PK) VALUES (1)
INSERT INTO #Foo2 (PK) VALUES (NULL)

COMMIT TRANSACTION

END TRY
BEGIN CATCH

ROLLBACK TRANSACTION
PRINT ‘It broke’

END CATCH

SELECT * FROM #Foo

DROP TABLE #Foo2
DROP TABLE #Foo