>>I kind of like Nicholas idea, another alternative could be something like this:
>>
>>

>>BEGIN TRY
>>     BEGIN TRANSACTION
>>         update MyTable1 set Field1 = @SomeValue where Field2 = @SomeValue2
>>         IF @@ROWCOUNT > 0
>>         BEGIN
>>             update MyTable2 Set ....
>>
>>             insert into MyTable3 ....
>>         END
>>END TRY
>>
>>BEGIN CATCH
>>      IF @@TRANCOUNT > 0
>>          ROLLBACK
>>END CATCH
>>
>>IF @@TRANCOUNT > 0
>>          COMMIT
>>

>
>Thank you. Could you, please, clarify one thing. In your code, after CATCH/END CATCH, does the ROLLBACK automatically sets the @@TRANCOUNT to 0 (zero)? So that after ROLLBACK, COMMIT does not fire?

Yes, if the ROLLBACK has been done, the @@TRANCOUNT variable will be decremented and if it was 1 before ROLLBACK, the COMMIT will not fire.