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?
If it was raised, why was I able to catch it? I'd expect problems that
happened inside an error handler would not be caught.

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;

UPDATE
SXC_CLAIM
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
WHEN ANY DO
BEGIN
EXECUTE PROCEDURE
P_EXCEPTION_LOG_I(
'E_BAD_CLAIM',
'Unable to import claim or create fee',
NULL,SQLCODE);
END
End

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