Subject Re: [firebird-support] Incremental key
Author Ann W. Harrison
At 01:15 PM 7/6/2004, Bjoern Reimer wrote:

> First: Don't use this number as primary key of your table.
> To access your data rows use a semantic insignificant primary key.

Good idea.

> I'd suggest:
> * Use a SELECT MAX (Number)+1 FROM TABLE to get the number
> * end your transaction
> * Use an before insert trigger to check if someone else has selected
> this number and do another select max if so and commit your
> transaction.

That won't work correctly if there are concurrent transactions inserting
records. Generators are the best answer, but they don't work well if
the user modifies the key value, and if the user takes a number then
decides not to use it, the number is gone. There are ways around that,
but they're error prone and limit concurrent inserts.