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.

> this shouldn't compile. The syntax should be
> RETURNING_VALUES(:CLAIM_ID);

It compiles, but I'll change it.

>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.

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?

>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.

>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.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Friday, April 29, 2005 7:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQLCODE -999 after catching FK exception

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







Yahoo! Groups Links