Handling Transaction

hi friends,

I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.

When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.

Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.

I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...

JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,

thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.

But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?

Jake

Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.