Adam wrote:

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

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

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