It turns out that if you naively (although it makes sense) try to do:
BEGIN TRANSACTION insert(1)... insert(2)... -- Error here! insert(3)... COMMIT TRANSACTION
contrary to intuition, although insert(2) fails, and you would think the transaction gets aborted at this point, insert(3) still goes through. in order to rollback inserts 1 through 3, you would typically need to check for @@ERROR after each of the inserts, and do some sort of GOTO, which will let you jump to some error handling code section, which will perform ROLLBACK TRANSACTION.
Obviously this gets to be pretty, if not excruciatingly painful, to insert the check for @@ERROR after every single statement!
Thankfully there is a much cleaner way of doing this: TRY..CATCH
BEGIN TRANSACTION BEGIN TRY insert(1)... insert(2)... -- Error here! insert(3)... -- This never happens. insert(4)... etc... -- If we got here there were no errors above. print 'Committing transaction...' COMMIT TRANSACTION END TRY BEGIN CATCH -- On insert(2) we drop through here. Nice! print 'Rolling back transaction...' ROLLBACK TRANSACTION END CATCH;
Hope you find this useful... sure better than copy/pasting IF @@ERROR <> 0 a hundred times!