On 8 Jan 2003 at 15:03, Ann W. Harrison wrote:

> At 09:39 AM 1/5/2003 -0500, pschmidt@... wrote:
>
> >There are really three methods, all are acceptable by may have pros
> >and cons.
> >
> >First is to store the number somewhere else
>
> Right.
>
>
> >Second, use a queue, if a record is rolled back, then the number is
> >stored in a queue, and inserts always get numbers from the queue
>
> That has the secondary problem that if the rollback is not done under
> program control (power or network failure, for example) numbers are
> lost.

Hmmm.... Gives me an idea for a new feature for Firebird, I'll post the idea on the
firebird developers list....

> >Third, the delayed number, in this case you delay inserting the
> >number, until the very last point, for example in an after insert
> >trigger.
>
> Changing the "new" value in an after insert trigger has no effect
> since the record is already stored.
>

The idea isn't to change the "new" value, but to do an UPDATE, just have to be
careful that the update triggers that get fired don't cause any problems.....

> The best solution in my opinion, is to use a generator. Depending on
> the load, I'd probably get the number and immediately store an empty
> record with a status of some sort indicating that this is a work in
> progress. After that is committed, I'd update the record to the
> desired state and commit that. You won't be able to use "not null"
> fields, of course, and you'll still need to run a program from time to
> time to find any numbers that were lost between the creation of the
> generated value and storing the dummy record.
>
> When using paper forms, there's always some way to indicate that
> a particular form was voided - people never having been infallible.

That would be a forth method, as to which one is the best, that would be application
specific. I don't expect that there would be too many records lost between the
generator and the dummy record, unless there was a server "failure".