>>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.
If it's not broken, fix it until it is.
My Blog