Subject Re: [ib-support] Sequential running no using trigger and generator
Author Ivan Prenosil
> I need a continuos running number to be inserted into a field, but every time when there is any exception
during posting, the generator will get incremented, so my current thought is:
> 1. Create 2 generators, 1 to be called before insert, another to be called after insert.
> 2. Create 1 generator, insert 0 or -1 or any arbitrary no into the field, then call the generator to get
the number.
> Which way is better, any unwanted side effects? Or is there better ideas?

Generating continuous numbers is much more complicated than one can think.
If you search archives of IB forums, you will find lots of suggestions,
but each of them has some problems. e.g.
- your suggestion will solve the problem with failed insert statement,
but it will not work when you rollback the transaction.
- finding next value by SELECT MAX(... will not work reliably when more users work simultaneously
- retrieving next value from single row in additional table will cause lot of update conflicts

So first you should first state whenther you really need it (or just want it to get nicer numbers),
and what are your requirements (what is the reason for such demand).