Hi Daniel,

As you were just advised don't do this, it is really bad practice.

You could use
Set Generator G_CUSTOMER_ID TO 100

As long as you are the only user and nothing else is running etc etc.

But another better option would be to create an after insert trigger.
You don't really need to look for the maximum, as it will be executed
any time you add a manual entry. To be safe, you should probably
convert it to a stored procedure, and call it from both after insert
and after update. To be safe, generators should never be decremented.


DECLARE VARIABLE t_id INTEGER;
DECLARE VARIABLE t_gen INTEGER;
BEGIN
t_gen = GEN_ID( g_job_id, 0 );

WHILE NEW.ID > t_gen DO
BEGIN
t_gen = GEN_ID( g_job_id, 1);
END
END

Looking at it again, you could replace the while loop with your if
condition to be more efficient. The main change is to not worry about
the maximum ID, just look at the current ID, and it should take care
of itself.

Personally, I wouldn't put this into a production system without
analysing any performance penalties. Only looking at the current ID
instead of searching for the maximum will help with the performance.
It is still probably possible to get a primary key duplication
exception, so I would keep it as a safeguard only, and always call
the generator manually.

I wouldn't mind putting some form of generator integrity protection
into our database, so let me know if you find any gotchas.

Adam


--- In firebird-support@yahoogroups.com, "Daniel L. Miller"
<dmiller@a...> wrote:

> 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