On Tue, 2004-02-10 at 18:43, h_urlaf wrote:

> Hi all,
>
> Coming from Access and MySQL (yeah yeah, boo hiss), I'm used to
> defining columns as auto increment to automatically generate unique
> primary keys. I know the answer in Firebird is 'use generators', but
> generators don't seem to handle the following case:
>
> create table TEST ( ID INTEGER NOT NULL, DATA INTEGER, primary
> key(ID));
>
> create generator g1;
>
> set term ^;
> create trigger TBTEST for TEST active before insert as
> begin
> if (new.ID is null) then new.ID = gen_id(g1, 1);
> end ^
> set term ;^
>
> insert into TEST (ID) VALUES (1);
> insert into TEST (DATA) VALUES (1);
>
> The second insert yielded "violation of PRIMARY or UNIQUE KEY", which
> is pretty much what I had anticipated. Have people find ways to deal
> with this reliably? Should the app doing the insert or the trigger
> loop with a NOT EXISTS?
>
> Thanks,
> Emiliano

The question is, what do YOU want the application to do? Trying to
insert a duplicate entry into a unique index is not generator's fault.
What would a NOT EXISTS bring? You'd still end up with a message stating
that you can't insert such data due to referential integrity. Maybe I
misunderstood the point but I simply can't see any. Simply inform the
user the key is already used and that if he doesn't fill in the
appropriate field, one will be generated automatically (although i would
personally stick with generators)

Jakub Hegenbart