>>>> 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;
>>>>
>>>> If I do
>>>>
>>>> new.invoiceno=new.invoiceno;
>>>> WHEN SQLCODE -803 DO new.invoiceno=NULL;
>>>>
>>>> it works, but I still get the unique value exception instead of
> getting
>>>> the null value assigned.
>
> 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

> Are you sure you are waiting for a 803 rather than some other code?
>
>>>> I finally catch the unique constrained error within the
> application and
>>>> nullify the value there but i wonder if is it possible to do it
> in the
>>>> trigger.
>
> Wouldn't it be easier to automatically allocate an invoice number,
> and allow the user to override that in a separate process?

My initial business logic was to set the value only after printing to
ensure that the value corresponds to the one on the paper and to ensure
that two users don't get the same values.
The problem is to handle the duplicate value and set it to null for this
case, not then assignment.
As Ivan said, it can't be done since it WHEN is used inside B/E block.

Thank you all
Radu