Re: How to increased by exactly 1 ?

Am 24 Jan 2005 08:16:40 -0800 schrieb rogergorden_at_gmail.com <rogergorden_at_gmail.com>:

> I agree, there isn't any business purpose to do this.
> The requirement; as stated; says, "The
> business rule is that the system should have an option to assign the
> lowest
> next available Control Code for the new item created."
> Lowest, next available, is sequence folks. If you're multiuser, it's
> the way to go.
> If you really try hard to have 1,2,3,4 etc, and someone deletes a row,
> what will happen
> if you then have 1,2,4,5 etc?
>

We once had a customer (governmental financial department) request to implement an analogon to a pad of receipts. Part of the request was a numbering "without gaps" in the following sense:

• every receipt has a number
• if a receipt was created (insert into the receipt table) then the receipt no (pk of the receipt table) has to be the "next free number"
• if an error ocurred while "writing the receipt", this error together with the given number has to be logged.
• if a row in the receipt table is deleted, this event together with the receipt number has to be logged.

The financial department wants to be sure about every receipt number what happened. The reason for the request was to create a very coherent set of data for which manipulation is difficult.

We implemented the task by creating a table with candidate pk's in advance. The table (receipt_pk) has logging columns and the access to the pk's was done by a set of functions and procedures which lock explicitly when fetching a new pk. It was a strange thing but now it works fine.

-- 
Frank Piron,
defrankatkonaddot
(leftrotate two)