Subject Re: [IBO] getting sequence val before insert
Author Paul Vinkenoog
Hello Ronan,

> The problem is fixed. I was letting the trigger generate the
> sequence, but by calling GEN_ID( my generator) it was incremented
> once too often hence the -1 subtraction.

Good thing you changed it, because the subtraction does not turn back
the generator itself, it only decrements the returned ID-to-be by one.
BTW, this ID-to-be will later be overwritten by the one generated by
the trigger.

If you want to use the generated numbers efficiently, write your
trigger like I showed in my other post: only generate the new id if
it's NULL.

Gaps can still occur, e.g. if a transaction is rolled back. This is no
problem if you just want unique IDs. If you need a gapless sequence,
e.g. for invoice numbers, you must use special techniques.

Paul Vinkenoog