> No, it is not a PK, it is just a field with unique constraint index.
> InvoiceNO means the actual invoice number that is written on the

client

> papers.
> None of my PK have any meaning for any of the any applications we

write

> (except one or two fields but are not editable in ANY case, just
> tracking numbers etc... anyway this is a bit off topic)
>
> My question stands.
> It is possible to do the -803 exception handling or it must be done
> through IF (EXISTS....)

Your existence check will be limited to transaction isolation. In
other words it would become possible for two people to simultaneously
choose the same 'invoiceno'. You would need to have a mechanism in
place to serialise inserts. It may be different from country to
country, but I imagine where I am from there would be all sorts of
eyebrows raised if one could just issue random invoice numbers.

But back to your question.

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

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?

Adam