At 04:09 PM 2/05/2005 -0400, you wrote:

>OK. I'm used to Java, where only one handler would catch an exception.
>What I'll do is add a local variable and use it to flag that the second
>exception block shouldn't do anything if the first block has executed
>and set the variable.

It's the same story with PSQL. You had two exceptions. The one that was
handled by the WHEN ANY handler gave SQLCODE -999.

>The update statement works, and there are no update triggers on
>SXC_CLAIM.
>What is the proper way to link to the new inserted row?

Through the New.variables - in a BEFORE trigger. The update statement on
the other table (if that's what the EXECUTE PROCEDURE call does) will work
there, too, and you can pick up the RETURNING_VALUE for comparison with the
NEW.value.

> >If you have to execute an operation on another table that will affect
>any NEW.value
> >in the row being addressed by the trigger, perform this operation in a
>BEFORE trigger.
>
>I put it in an AFTER trigger, because I wanted to make sure the insert
>into SXC_CLAIM was successful.

Then catch the exception when it is unsuccessful, and raise a custom
exception that tells the client what went wrong. By all means, let the
handler log it to an external file first, but make sure that the exception
actually gets raised and prevents the insert from being written with
inconsistent data.

> >but don't make it swallow exceptions that ought to terminate the
>operation in order to prevent an inconsistency.
>
>I'm very pedantic about that. In this case, all data that is valid must
>be loaded in to SXC_CLAIM, and any errors that occur during the trigger
>processing need human intervention.

Then don't swallow the exceptions!! In fact, make it an absolute rule not
to write handlers inside triggers that swallow exceptions UNLESS the error
conditions are of mere academic interest.

./heLen