> I am using a Generator to create sequential ID numbers for most of my
> tables. It works great.
>
> However one table I have to create data in contains records that I want
> to be able to keep sequentially contiguous. Its an INVOICE table.
>
> The way I'm doing this is that when a user wants to create an INVOICE,
> I
> create a record in that table and get an ID back from the generator
> assigned to it. I then display an invoice form (this is via PHP on the
> web) to the user to fill out.
>
> Since I don't want two or more users creating an invoice with the same
> ID, I immediately commit the record creation stored procedure that
> gives
> me back the invoice number that I'm using. The record is then updated
> with the actual fields entered by the user when they submit their
> update.
>
> The problem occurs when a user elects to cancel or not complete the
> full
> invoice creation process. In this case I will delete the invoice
> record
> created, however I need to be able to 'recycle' the invoice number that
> was given, so that it becomes the next candidate invoice number given
> to
> the next user.

To keep them contiguous, I never delete the record. If the operator cancels,
then the record is annotated with "creation cancelled by operator' or words
to that effect.
This keeps the auditors happy. My Auditors don't like seeing a number out of
sequence with the creation date. i.e. lower numbers being created AFTER high
numbers. It's the same as voiding a cheque (check).

>
> I'm thinking that the best way to do this would be to reset the
> Generator to the number that was just recycled. That way when the user
> who next wants to create an invoice gets a number, it will come from
> the
> generator with that recycled number.

I'd never do this. Multi user - it's looking for trouble


Alan