Subject Re: [firebird-support] Numeration without hole, Is right Before Insert Trigger?
Author Walter R. Ojeda Valiente
Hello Luigi

If you can read Spanish, this article can be useful:



On Thu, Dec 24, 2015 at 7:05 AM, Svein Erling Tysvær setysvar@... [firebird-support] <> wrote:

Also, take a look at this ancient document that used to be the standard answer to people asking the same question as yours:


2015-12-22 20:26 GMT+01:00 Ann Harrison aharrison@... [firebird-support] <>:

On Tue, Dec 22, 2015 at 9:40 AM, Luigi Siciliano luigisic@... [firebird-support] <> wrote:

   I must assign a serial number, without hole, in a column of a fiscal
document.  I must assign the number only when I know if the document is complete
and I think the right moment is on a Before Insert Trigger for the table.

Yes that's a good place, but you've got to be very careful.  Generators/Sequences won't
work because they're deliberately non-transactional.  Once you take one its gone and if
your operation fails, you'll have a hole. 

Is right or the insertion can fail? If not right, when I must assign the
number to be sure of not have a hole in numeration?

One way to get numbers without holes is to create a table with one field that contains 
the seed for  your numbers.  In your before insert trigger update that field adding one to it, 
then read to get the new value.  Unfortunately, if someone else has inserted a record
concurrently, your transaction will wait then get an error and you'll need to re-run the
whole thing. 

 Check the FAQ's at for other ways of handling this problem.

Good luck,