Re: Why sequence jumps?

Nope, you are not wrong.
Deferring until pre-insert time will just cut back on the quantity of gaps. I never implemented the following logic but if you really want to stop gaps, you have to

  1 create a sequence table
  2 in your pre-insert trigger, get the next value and lock that table.   3 increment sequence by 1
  4 Repeat 2 and 3 for every row you are inserting   5 After commiting, you then update the sequence table with the new sequence value and unlock the table.

However, you have to control your locks and what about if someone inserts data, goes off to lunch without issuing a commit?

Mark

>Does the suggestion of assigning the primary key from a sequence via
>a pre-insert trigger guarantee no gaps in sequence numbers, or does
>it help cut down on the quantity of gaps?
>Ultimately, if you cannot tolerate any gaps at all I fail to understand
>how deferring acquisition of a sequence number to a pre-insert time
>will stop the wasting of this number should a rollback be done.
>
>I've been wrong before... corrections humbly requested.
>
>Mungo Henning
>
>
>--
>Mungo Henning - it's a daft name but it goes with the face...
>mungoh_at_itacs.strath.ac.uk.http://www.itacs.strath.ac.uk/
>(since everyone else does it) I speak for me, not my employer.
>
>
Received on Thu Jul 01 1999 - 17:08:31 CEST