Re: sequential numbering

Damon,

I use a table that holds a record that contains the last number used. To get the next number, I use an update statement to increment the value stored in the record (which also locks the record to prevent other people from updating it until I'm done):

update master_counter set value = value + 1 where counter_name = 'EMP_CNT';

I then select the value I have incremented:

select value from master_counter where counter_name = 'EMP_CNT';

Everyone else attempting to get a number has to wait until my transaction completes or rolls back. If it completes, they do the same thing, updating the value by 1. If it does NOT complete, both the data table insert AND the counter increment will roll back, allowing the next person to use that same number by doing the same thing (updating then selecting).

This prevents aborted transactions from burning numbers since a number is only burned as part of the COMMIT of the data values into the data table. Any activity that would prevent the commit of data values would prevent the commit of the counter increment. The next attempt to get a value would get that value.

Of course, deleting a record after it has been entered AND committed is another matter entirely (namely, do you wish to recycle the number).

Hope this helps (and I hope I'm coherent enough to make sense [its kinda late]).

Matt...

[Quoted] In article <32838676.2929_at_ix.netcom.com>, Damon Bowman <dbowman2_at_ix.netcom.com> wrote:

> does anyone know of a way to sequentially number records in a
> multi-record block? Business requirements preclude the use of sequences
> due to the possibility of dropped numbers.
Received on Wed Nov 13 1996 - 00:00:00 CET