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
You can use SET XACT_ABORT ON command, which causes transactions to fail when any runtime error occurs, not just fatal ones. In this case you don’t need to test each statement using @ERROR.
How can I do something similar to that in SQL Server 2000?