Hi,

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

The question is: why are you creating your own ID in
the first insert. Without it, there is no spoon. Sorry: problem.

If you want a generator to generate IDs, then do so. Always.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com