> > This statement makes no sense. You say it works, yet you say it
> > returns an exception which indicates to me it did not work. If it
> > worked, then your application would not have received an exception.
>
> Mea culpa, I meant it compiles but it still returns the -803 code which
> I wanted to get it handled

That makes more sense. I think the problem is that the actions happen
like this:

1. BEFORE TRIGGERS
2. <action on table>
3. AFTER TRIGGERS

It is the <action on table> that raises the 803 (or whatever
exception), so the BEFORE TRIGGERS have no way of knowing that at some
later stage there is going to be a problem. You could of course run an
exists check in the BEFORE TRIGGERS but this check will be limited
your the isolation of your transaction (may be better than nothing
though).

Another idea, you could do what you need to do inside a stored procedure.

CREATE OR ALTER PROCEDURE FOO
(
INVOICENO BIGINT,
P1 BIGINT,
P2 BIGINT,
P3 BIGINT
)
AS
BEGIN
INSERT INTO BAR (INVOICENO P1, P2, P3) VALUES
(:INVOICENO, :P1, :P2, :P3);

WHEN SQLCODE -803 DO
BEGIN
INSERT INTO BAR (INVOICENO P1, P2, P3) VALUES
(NULL, :P1, :P2, :P3);
END
END
^

I think that should work.

Adam