At 04:08 PM 12/06/2004 +0800, you wrote:

> >
>Thank you Helen. Iam sorry I didn't mention clearly that what I mean by
>exception raise is the exceptions I create in my Database. So under such
>kind of exception does the transaction finish or not? If yes then I
>don't have to do anything right? and If No either I rollback or try to
>fix the problem.

It's no different whether your exception is a custom one created with
CREATE EXCEPTION, or a pre-defined one (gdscode or sqlcode) thrown by the
engine itself. Inside your stored procedure, if you throw an exception and
don't handle it inside the procedure (or trigger) it is just an
exception. The SP (or trigger) gets undone and the whole thing fails.

Now, whether it fails or succeeds, it is still NOT committed until your
application commits the transaction that executed the procedure...so, if
your SP goes and does a whole lot of things and then your app rolls the
transaction back, the SP work is all gone, too.

On the other hand, inside a trigger you can raise your custom exception (or
the engine can throw an exception) and you can handle it inside the SP
code, using a WHEN block. If you fix the problem in some way inside your
WHEN block, only the work that happened in that block (or, in the case of a
loop, in that iteration of the block) will be undone. Your program can
resume after handling the error and can complete successfully - still
needing to be committed when control returns to the client application.

/heLen