I want to generate an ID this ID must be saved to a table let's say
GENERATED_IDS table, I want to use this way to keep track of all my id's
there may be no gaps

Now the problem is that this needs to happen in 2 calls

1) Generate ID
2) Insert into Generated_IDS the generated ID has extra column
(status), status (I - Inprogress, C = Complete)


The problem is that if I can do that in one transaction I am guaranteed that
if anything goes wrong all is rolled back
But what if something goes wrong after step 1 ie. Db machine crashes, or
client or middle-tier supposed to do step 2 crashes

I know most people would take this as an admissible small chance that this
may happen, but you know Murphy.
So if Generators in Firebird runs outside of transactions, how could you
achieve this integrity
I guess you could write this to occur in a stored proc which further
minimizes the chance of failure since it all happens
On one thread on the db machine and is lot less likely to failure during
this process, would this be the safest.

But I would prefer not to use a stored proc, many reasons not wanting to
explain right now.

Is there a way to achieve this with a generator?

Thanks.


[Non-text portions of this message have been removed]