Subject Re: Generator and Triggers with IB6 from Delphi and IBX
--- In ib-support@y..., "Christopher P. Boothe" <cboothe@o...> wrote:
> I have a question.
> If I manually start a transaction for adding a new record. I'm
using the
> IBX components with IBSQL IBTransaction and IBUpdateSQL. Well
anyway If I
> manually start a transaction the user clicks to add a record, and
the user
> cancels and I then rollback the transaction. The generator will
> triggered and if the user then goes in and adds a new record because
> generator triggers again it will be 2 integers away from the last
> It will skip one number?
> Now this isn't a huge problem because the record will still be
unique. But
> I hate having order numbers skip every once in a while when someone
> an entry. Any way to take care of this?

Hi, Christopher. You can emulate transaction-context generator but
you should take care on lock conflicts when inserting in this case.

Create Table MyGenerator(
MyGen Integer

Initialize it by needed value and when inserting

Update MyGenerator Set MyGen=MyGen+1;
Select MyGen From MyGenerator;

Disadvantage is: if transactions are read_commited, another
transaction trying insert will get exception or wait for first
commit/rollback accordingly transaction parameters. If transactions
are snapshot, exception will be always raised if first commits. I
think this bottleneck have a right to live only in extraordinary
cases and generators are off transaction context namely for this

And what about delete? You'll re-fill all you primary key? :)

Best regards.