Re: Unique sequence generator with reclaim

One way would be to create a dictionary table of all possible sequence numbers ( assuming it's a finite list) and have a trigger:

• check the data tables for "deleted" numbers or when a number is deleted have it's status in the dictionary table changed back to available. This way, to get a new number just select the MIN(number) from the dictionary and you'll always retrieve the lowest unused number.
• grab the next number from the dictionary table and mark it as used
• retrieve the user-entered number and mark it as used in the dictionary table.

We've used this model in a current production application but the number list was only from 000001 to 999999 due to certain business formatting rules. The dictionary table also grabbed user information such as who took what number when and if any number was placed back in the available pool.

There was a little hit on performance in acessing the dictionary table all of the time but with proper indexing it worked fine.

Cliff Received on Wed Dec 29 1999 - 20:30:37 CST