Subject Re: [ib-support] Sequential running no using trigger and generator
Author Svein Erling Tysvær
Hi Junior!

>Which way is better, any unwanted side effects? Or is there better ideas?

Don't know if my suggestion is any better, but it is at least different:

First of all, do not make this sequential number your primary key (PK)! Use
a separate, normal generator to populate the PK. Then make a unique index
on your sequential field. Get its value from a separate table (i.e. make
one separate table with one PK field populated from a generator (this field
should have a descending index (or store the negative PK value)) and a
field for current_sequential_value ) by something like SELECT
current_sequential_value FROM my_sequential_table WHERE PK = (SELECT
MAX(PK) FROM my_sequential_table), try to insert your record and post it
with this value+1 in your sequential field. If it fails due to a violation
of unique constraint on your sequential field, just increase the value
until you succeed. Then insert a new record to my_sequential_table with the
current_sequential_value you chose that succeeded.