ROLLBACK TRAN –RollBack in case of Error
I think there is no need to explain the above code. It is self-explainatory, each line and each steps are crystal clear.
Anyways, Lets discuss it with a small detail:
In the above TRY-Catch block a transaction has been started, where i am performing two
UPDATE . If both gets completed successfully, the
COMMITwill be called and the transaction gets committed. If, however, either one produces error, control will be given to CATCH block where the transaction will be rolledback.
SQL Statements Reffered (From MSDN)
@@ERROR – Returns 0 if the previous Transact-SQL statement encountered no errors. Returns an error number if the previous statement encountered an error.
@@TRANCOUNT – The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTIONsavepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1