Atomic Transaction

Hi all,

If I have 2 database operations
1. Update ...
2. Delete ...

I want these 2 operations to execute atomically, thus I make use of

BEGIN Transaction ...
Update ...
Delete ...
COMMIT Transaction ...

But if one of these operation fails, it locks up the database. Does anyone have a resolution for this? How do I detect sql Exceptions to release the transaction so that it doesn't lock up the database?

Thanx

James :)I assume this is written in a stored procedure.

In that case

After your first UPDATE you need to chek

@.@.ERROR

If its Zero continue otherwise you need to ROLLBACK the transaction as the update failed. The same is true for your Delete statement. You should only COMMIT when both succeed. You should report or log the error somehow of course.

Have a look in Books Online under the topic

Using @.@.ERROR

its under Accessing and Changing relational Data :: Advanced Query Concepts :: Error Handling

regards

Steve|||Thanx, I will give that a try.
I love dbforums, people here's always got the answer.

James :D