William L. Thomson Jr. wrote:

>Some time back, I stopped using Generators. I had to many issues with
>bad code or commits, incrementing, and not decrementing the generator.
>At times I would get gaps in numbers and etc. Overall working with them
>became a pain. I came up with a similiar way using a simple trigger, and
>was just curious to others thoughts on it.
>
>DECLARE VARIABLE max_num INTEGER;
>BEGIN
> IF(new.NUM is NULL)
> THEN
> BEGIN
> SELECT MAX(NUM) FROM MY_TABLE INTO max_num;
> new.NUM = max_num+1;
> END
>END
>
>I have been doing this for quite some time. Can't tell much if any
>difference on performance. If I delete a bunch of records in order, like
>the last 5 or 10. I do not have to worry about resetting, syncing,
>updating the generator. Or decrementing the generator at all.
>
>Anyway thought I would toss it out there. I would assume others to
>already be doing something similar?
>
>
>

Generator are transaction independent if you get a value and then
rollback the generator will not return to the previous value.

Generator could but should not be set/reset by hand, this can cause
trouble (getting duplicate values).

Generator should be something without meaning to the users, should not
matter if there is gaps in the numbering sequence.

The only thing you could garantee using generators is that no
transaction could get the same value twice (and that is what it should
be used for), and if you just issue gen_id(MyGen, 1) the value will
always be greather then the previous, no chance of collision, the value
will grow, but you can see gaps.

The approach you use will be fast enough if you have a descending index
on the column used in the MAX, but will be slow on very large tables
without that descending index. But this is not the major problem, the
major and big problem you have in your approach is that it's not
muti-user safe !

Two concurrent inserts could get the same values since one doesn't see
the other.

If you need an unbroken sequence take a look on this paper:
http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br