--- [EMAIL PROTECTED] wrote: >> >> There is a very simple solution.
[solution details snipped] >> >> This is the simplest solution, lowest cost solution, >> and doesn't violate the >> definition of an SQL sequence. Tthis solution creates a couple different problems that are probably unexpected by the user. 1) An insert with a non-generated value near or at the end of the range of generated values will exhaust the generator much earlier, leading to failed inserts that would succeed under the current scheme. E.g. if the range of of the generator was 1-10 and the CBV was 3, then inserting a non-generated value of 10 means that no more inserts with generated values would work. In the current scheme six more inserts would succed (4-9). 2) An insert with a a non-generated value that is subsequently deleted or updated before any insert with a generated value affects the generator when under the current scheme it would not. This will reduce the range of available values when there is no need to. E.g. if the range of of the generator was 1-10 and the CBV was 3, then inserting a non-generated value of 10 means that no more inserts with generated values would work, even if the row with 10 is deleted before any other inserts occur. In the current scheme seven more inserts would succed (4-10). So solved one scenario and caused (at least) two new ones. Dan.
