--- In firebird-support@yahoogroups.com, Radu Sky <skysword76@...> wrote:

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

Well it was in hindsight not a wise choice for a PK.

Lesson:

Never use a field with business significance as your primary key.
Always create a surrogate key. To manage your relational structures
and give the monkeys their own field to play with that has nothing to
do with the primary key you allocate.

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