At 01:36 PM 28/04/2005 -0400, you wrote:

>I intentionally forced a foreign key violation by inserting one bad row
>into SXC_CLAIM.
>The first error message was expected. The second was not.
>Why would a firebird error occur immediately after an FK error?

The handlers that are in the scope of the exception are executed
one-by-one, starting with WHEN blocks immediately following the exception
and working its way outward through any enclosing blocks, until any
possible handler for the exception(s) that occurred is found.

Unless one of the preceding handlers re-raises the exception (by calling
EXCEPTION (Fb 1.5 only) or EXCEPTION aCustomException) the execution of
handlers will continue until there is no applicable handler left. When an
exception is handled, the nesting level at which the last handling occurs
determines where execution returns to afterwards.

>If it was raised, why was I able to catch it?

It wasn't re-raised. The explicit GDSCODE was handled by the first
handler. The second handler (WHEN ANY) catches literally ANY exception
that remains after the first one executed. It would not have executed if
the first one had been re-raised.

>I'd expect problems that
>happened inside an error handler would not be caught.

If a problem happened inside an error handler (not the case here), that
exception would have caused execution to pass to the end of the block at
the next level up. In the case of your trigger, execution would terminate
because there is no outer level handler preceding the final END statement.
From your description, one assumes that isn't what happened.

>CREATE TRIGGER SXCCLAIM_2_PBMCLAIM FOR SXC_CLAIM ACTIVE AFTER INSERT
>POSITION 0 AS
>/* insert the data into PBM_CLAIM and set our FK to same row */
>DECLARE VARIABLE CLAIM_ID BIGINT;
>begin
> /* only import paid or reversed claims, not rejects */
> IF (NEW.CLAIMSTS IN ('P','X')) THEN
> BEGIN
> EXECUTE PROCEDURE
> P_PBM_CLAIM_I(
> NEW.PRODUCTID,
> NEW.ACCOUNTID,
> NEW.SRVPROVID,
> NEW.DECIMALQTY,
> NEW.CALINGRCST,
> NEW.GROUPID,
> NEW.RSPPATPAY,
> NEW.RSPDUEAMT,
> NEW.MULTSRCCDE,
> NEW.DATESBM)
> RETURNING_VALUES
> CLAIM_ID; <--- this shouldn't compile. The syntax should be

RETURNING_VALUES(:CLAIM_ID);

> UPDATE
> SXC_CLAIM /* AAAAAARRRRRGH!! */
> SET
> PBM_CLAIM = :CLAIM_ID
> WHERE
> RXCLAIMNBR = NEW.RXCLAIMNBR AND
> CLMSEQNBR = NEW.CLMSEQNBR AND
> CLAIMSTS = NEW.CLAIMSTS;
> END
> WHEN GDSCODE foreign_key DO
> BEGIN
> EXECUTE PROCEDURE
> P_EXCEPTION_LOG_I(
> 'E_BAD_CLAIM',
> 'Unable to import claim due to missing dependancies',
> GDSCODE,NULL);
> END

OK, the first handler picks up and handles *a* FK violation of some sort.

Now, execution moves on to the WHEN ANY handler, which swallows any other
exception, in this case the illegal update statement.

> WHEN ANY DO
> BEGIN
> EXECUTE PROCEDURE
> P_EXCEPTION_LOG_I(
> 'E_BAD_CLAIM',
> 'Unable to import claim or create fee',
> NULL,SQLCODE);
> END
>End

By hitting the same table with an update statement in an AFTER INSERT
trigger, you're trying to interfere with trigger execution. By the time an
AFTER trigger executes, all changes are complete. The update will cause
your update triggers to fire and trigger off a cascade of
inconsistency. Hence the SQLCODE -999, indicating that the engine has
detected the internal inconsistency but doesn't have an explicit error code
for it.

You should use a WHEN ANY handler with great care. By all means use it to
log the problems, but don't make it swallow exceptions that ought to
terminate the operation in order to prevent an inconsistency.

You have always had the ability to raise a custom exception from within an
error handler; from 1.5, you can also re-raise an internal exception,
using a bare EXCEPTION statement following your logging code. See the v.1.5
release notes for details.

>XCEPT MSG GDS_CODE
>SQL_CODE CUR_USER CUR_ROLE TX_TS ACTUAL_TS
>------------------------------------------------------------------------
>------------------------------------------------------------------------
>------------------------------------------------------------------------
>------------------------------------------------------------------------
>---------------
>E_BAD_CLAIM Unable to import claim due to missing dependancies
>335544466 0 SYSDBA NONE 4/28/2005 1:24:05 PM 4/28/2005 1:24:05 PM
>E_BAD_CLAIM Unable to import claim or create fee 0
>-999 SYSDBA NONE 4/28/2005 1:24:05 PM 4/28/2005 1:24:05 PM

Now, back to the principles involved in this trigger. 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.

Also, don't forget that, if you are logging to a database table, rather
than to an external table file, all your log records will disappear when
the trigger finally fails.

./heLen