Re: COMMIT after an ERROR?

On Fri, 12 Oct 2001, J C Lawrence wrote:

>
> Does an error during a transaction invalidate the transaction?
>
> eg
>
>   BEGIN
>   SomeQueryThatSucceeds
>   SomeQueryThatFails
>   SomeOtherQueryThatSucceeds
>   COMMIT
>
> Will the transaction successfully COMMIT, or will the COMMIT fail?
>
> Translation: Do I need to monitor for error conditions during a
> transaction and manually do the ROLLBACK in those cases?
>
>

Hey JC...

What will happen is:

BEGIN WORK;
--> BEGIN
SELECT * FROM sometable;
--> (select results)
INSERT INTO sometable VALUES (blah);
--> insert fails, gives error
UPDATE sometable SET somefield=somedata WHERE somecondition=somepattern;
--> NOTICE: transaction is in error, will not continue

If you COMMIT WORK or ABORT WORK, either way it will close the transaction
which is in an errored state and automatically rollback the changes.

--Nathan