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.

>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 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.



Regards,

Ann
www.ibphoenix.com
We have answers.