Understanding Transactions - Beginning C# 2008 Databases: From Novice to Professional - page 147

Error handling is important at all times in SQL Server, and it's never more so than
inside transactional code. When you execute any T-SQL statement, there's always the
possibility that it may not succeed. The T-SQL @@ERROR function returns the error number
for the last T-SQL statement executed. If no error occurred, @@ERROR returns zero.
@@ERROR is reset after every T-SQL statement (even SET and IF ) is executed, so if you
want to save an error number for a particular statement, you must store it before the next
statement executes. That's why you declare the local variables @inserr and @delerr and
@maxerr .
If @@ERROR returns any value other than 0 , an error has occurred, and you want to roll
back the transaction. You also include PRINT statements to report whether a rollback or
commit has occurred.
-- If an error occurred, roll back
if @maxerr <> 0
begin
rollback
print 'Transaction rolled back'
end
else
begin
commit
print 'Transaction committed'
end
n Tip T-SQL (and standard SQL) supports various alternative forms for keywords and phrases. You've used
just ROLLBACK and COMMIT here.
Then you add some more instrumentation, so you could see what error numbers are
encountered during the transaction.
print 'INSERT error number:' + cast(@inserr as nvarchar(8))
print 'DELETE error number:' + cast(@delerr as nvarchar(8))
return @maxerr
Now let's look at what happens when you execute the stored procedure. You run it
twice, first by adding customer “a” and next by adding customer “aa”, but you also enter
the same nonexistent customer to delete each time. If all statements in a transaction are
supposed to succeed or fail as one unit, why does the INSERT succeed when the DELETE
doesn't delete anything?