|Subject||Re: [ib-support] Sequential running no using trigger and generator|
> I need a continuos running number to be inserted into a field, but every time when there is any exceptionduring 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
>Generating continuous numbers is much more complicated than one can think.
> Which way is better, any unwanted side effects? Or is there better ideas?
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).