How can I not lose the value of the generator when using them on the server side with Firebird?

Question

Check out this simple piece of code that the generator uses to create unique primary keys in the Firebird table:

CREATE OR ALTER TRIGGER ON_BEFOREINSERT_PK_BOOKING_ITEM FOR BOOKING_ITEM BEFORE INSERT POSITION 0 AS BEGIN IF ((NEW.booking_item_id IS NULL) OR (NEW.booking_item_id = 0)) THEN BEGIN SELECT GEN_ID(LastIdBookingItem, 1) FROM RDB$DATABASE INTO :NEW.booking_item_id; END END! 

This trigger captures and increments, then assigns the generated value to the identifier of the reservation element, thereby creating an automatically incrementing key for the BOOKING_ITEM table. The trigger even checks that the reservation ID has not yet been assigned a value.

The problem is that an automatically incrementing value will be lost (lost) if, for any reason, the BOOKING_ITEM record cannot be sent.

I have a couple of ideas on how to avoid this, but worry about each one. Here they are:

• Decrease counter if a posting error occurs . Inside the trigger, I set up a try-except block (are there try blocks other than blocks even in Firebird PSQL?) And run SELECT GEN_ID(LastIdBookingItem, -1) FROM RDB$DATABASE for post exceptions. Will this work? What if another transaction makes its way and increases the generator before I reduce it? That would really mess things up.

• Use a temporary identifier . Set id to some unique temporary value that I changed to the generator value that I want on the AFTER INSERT trigger. This method seems somewhat contrived and requires the temp identifier to be unique. But what if booking_item_id was provided to the client side, how would I distinguish this from a temporary identifier ?. Plus I need another trigger

• Use transaction management . This is similar to option 1. Except for using the try-except block to reset the generator, I start the transaction and then roll it back if the record is not deleted. I do not know the syntax for using transaction control. I thought I read somewhere that SAVEPOINT / SET TRANSACTION is not allowed in PSQL. In addition, the rollback should happen in the AFTER INSERT trigger, so again I need another trigger.

Of course, this is a problem for any Firebird developer who wants to use generators. Any other ideas? Is something missing?

Answer 1

Sequences are outside the control of transactions, and interfering with them to obtain brushless numbers will cause problems only because another transaction can simultaneously increase the sequence, which leads to spaces + duplicates, and not to spaces:

• start: generator value = 1
• T1: increment: value is 2
• T2: increment: value is 3
• T1: 'rollback', decrement: the value is 2 (not 1, as you expect)
• T3: increment: value is 3 => duplicate value

Sequences should primarily be used to generate artificial primary keys, and you should not care about the existence of spaces: it does not matter if the number uniquely identifies the record.

If you need a proven sequence of numbers, and the requirement is that there are no spaces, you should not use a database sequence to create it. You can use the sequence to assign numbers after creating and making the invoice itself (so that it is sure that it is saved). An invoice without a number is not yet final. However, even here there is a window of opportunity to get a space, for example, if an error occurs or another failure occurs between assigning an account number and fixing.

Another way could be to explicitly create a null account (marked as a canceled / lost number) with space numbers so that the auditor knows what happened to that account.

Depending on local laws and regulations, you should not “reuse” or recycle lost numbers, as this could be interpreted as fraud.

You can find other ideas in the "Audible Series of Numbers" . It also contains a Delphi project using IBObjects, but the document itself very well describes the problem and possible solutions.