> I have a invoices table with an invoice number field with an unique
> constraint index.
> The business logic was, until recent, that these values were assigned by
> the system based on a generator and so on...
> The client came with a requirement that this number should also be
> allowed to be entered manually for some obscure reason.
> The problem is when the user enters an already existing value
> I thought in the beginning that I could capture the -803 sqlcode in the
> before insert/update trigger and change the value, eventually throw some
> custom exception
> First I can't compile this (the only code in the trigger)
>
> WHEN SQLCODE -803 DO new.invoiceno=NULL;

WHEN is part of BEGIN/END block, and it catches exceptions
that occur inside that block (but not inside WHEN) only.
If you trigger does not contain any other statement,
then of course no exception can be raised and handled.
The -803 exception you are seeing is generated after
your before-insert/before-update triggers fire.

You could use some IF (EXISTS ...) test, but it does not see
newly inserted but not yet committed records.

You could also try (inside trigger) to insert the value -
if it generates -803, you know it already exists,
if it does not generate exception, just raise exception
manually to remove that test-row. But there can be other problem-
the other transaction could rollback, which means you got "false"
confirmation that the value is really in table. It can be avoided
by using WAIT transaction.

Ivan