Daniel L. Miller wrote:

Howdy all.

I understand how to use generators with before-insert triggers to
provide simple auto-increment fields:

NEW.ID = GEN_ID(G_CUSTOMER_ID, 1);

Taking it a step further, I can check for a null value before
incrementing the generator in the trigger - if for example I needed to
assign the generator's value prior to the insert:

IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(G_CUSTOMER_ID, 1);

My question now is how to handle "tinkering". Particularly when
developing a new database/application, it's typical to add or change
records without going through the "proper" procedures (for example, I
use ibwebadmin, which forces an entry in primary key fields without
checking the result of the before insert trigger). So the generator
can get "behind" in its numbering.

If I want to implement "generator protection" - where should I place
it? In the before-inserts, the after-updates, both, or elsewhere? Is
this something I should keep in my production environment as a form of
defensive programming? I'm envisioning something like (and I haven't
tried this yet):

DECLARE VARIABLE t_id INTEGER;
DECLARE VARIABLE t_gen INTEGER;
BEGIN
SELECT MAX(id) FROM jobs INTO :t_id;
t_gen = GEN_ID( g_job_id, 0 );
IF ( t_id > t_gen ) THEN
t_id = GEN_ID( g_job_id, ( t_id - t_gen ) );
END

--
Daniel