Another reason you might need the "if(new.id is null)..." part is that you
might need a stored procedure that adds a record to this table and need to
use and/or return the new id of the record just created. You can create a
local variable, call the generator, and pass in the new id in the INSERT
statement instead of leaving it NULL.

For you situation, I'd be tempted to leave this field strictly to
"auto-numbering" and create a second field for your sometimes-specific ID.


David Cornelius
CorneliusConcepts.com

________________________________

From: h_urlaf [mailto:h_urlaf@...]
Sent: Tuesday, February 10, 2004 10:03 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Auto-increment and generators


--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

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

Because we have a situation where the IDs can usually be 'random' as
long as they're unique, but in some instances we want a specific ID.

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

Then what is the point of things like

if (new.ID is null) then new.ID = gen_id(g1, 1);

(which Helen pointed out as being a Good Thing). If what you're saying
is right, I ought to omit the "if (new.ID is null) THEN" part, and add
an update trigger that does
new.ID = old.ID
to make sure people don't update these IDs. Right?

Thanks,
Emiliano