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.
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.
We are using Firebird 2 RC3, I didn't know if is it 2.0 related or just
my logic flaw so I posted here first

TIA

Radu