autoinc know. What if I add a record and I will remove it for a moment or a transaction is not approved. then already have the jump in numbers. Any suggestions?
METHOD A:
Autoincrementing using a trigger & a generator is quite safe & is recommended for concurrent transactions.
What you want to achieve can be done by changing the last used value of the generator if you really want continuous numbers BUT IT IS NOT RECOMMENDED & IS VERY DANGEROUS. Another way of doing it is to recreate the table from scratch using an SQL script with records having continuous numbers & then resetting the generator to the last used number.
However I fail to see the advantage of doing this because if the table has heavy transaction traffic, you will actually slow down transaction processing & at worst may just corrupt the database entirely. I DON'T ADVISE THAT YOU DO THIS. It is best to let Firebird handle the assigning of numbers to records.
METHOD B:
You can create a table which contains the last used number. If your transaction in the main table is completed, retrieve the last used number from the number table, increase it by 1 AND then update both the main table record & the number table value. This can be done using triggers & stored procedures.
You'll find examples of this type of usage in the old Paradox apps for Delphi 7.
The risk here is you may still have exceptions where two main table records have the same number because the updating was not done fast enough.
So my final advice is:
use a trigger & a generator to handle the numbering & let Firebird handle it as it sees fit. You'll save yourself HUGE headaches in future believe me.