--- In firebird-support@yahoogroups.com, Jakub Hegenbart
<Kyosuke@s...> wrote:

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

I would want the trigger to insert the 'next available' ID.

> What would a NOT EXISTS bring?

I mean something like

DECLARE NEW_ID AS BIGINT;
....

if (new.ID IS NULL) THEN
BEGIN
NEW_ID = GEN_ID(G1);
WHILE (EXISTS(SELECT * FROM TBL WHERE ID = :NEW_ID)) NEW_ID =
GEN_ID(G1);
new.ID = NEW_ID;
END

> user the key is already used and that if he doesn't fill in the
> appropriate field, one will be generated automatically (although
> would personally stick with generators)

So would I, but here's the situation: we have a 'mailbox ID' that is
generally random (doesn't matter, as long as it's unique). Some
organizations, however, want to specifically set this ID at some point
(to phone numbers, personnel ID, what-have-you), and this change may
have to happen on a database that is already deployed.

What I might do is set the field to NULL, and offer the user to
auto-generate them later (just loop through a generator and retry on
unique constraint conflict until I've got them all). Not pretty, but
it could work.

Emiliano