Delphi, Firebird, FibPlus. Part 4 – Autoincrement Fields

Posted on by

There are no native autoincrement fields in Firebird. Instead, the autoincrement fields are implemented using a generator and a trigger database objects. It takes a little more work to create an autoincrement field compared with other database servers with native autoincrement field support, but as an award we have a better control over autoincrement fields on the client side. First and foremost, we can get the value of an autoincrement field before the “INSERT” query is executed. The FibPlus datasets support this feature.

Select the previously created “dsetAuthor” dataset in the data module:


Expand the “AutoUpdateOptions” property of “dsetAuthor” component in the object inspector:


Set the “UpdateTableName” to “AUTHOR”, “KeyFields” to “AUTHOR_ID”, “GeneratorName” to “GEN_AUTHOR_ID” and “WhenGetGenID” to “wgBeforePost”:


Now every time before we insert a new record into the “AUTHOR” database table we get a new autoincremented value of the “GEN_AUTHOR_ID” generator and send the record to server with already filled “AUTHOR_ID” field. Let us have a look at the fragment from the original database generation script from the Part 1:

CREATE GENERATOR GEN_AUTHOR_ID;
..
CREATE TRIGGER BI_AUTHOR FOR AUTHOR
ACTIVE BEFORE INSERT POSITION 0
AS
  BEGIN
    IF (NEW.AUTHOR_ID IS NULL) THEN
      NEW.AUTHOR_ID = GEN_ID(GEN_AUTHOR_ID, 1);
  END^

The “BI_AUTHOR” trigger sees that the key field “AUTHOR_ID” is already filled and does not generate a new autoincremented value on server.

In our case we need to obtain the value of the “AUTHOR_ID” field on the client side before posting a new record for the “Refresh” query to work properly, so I have set the “wgBeforePost” value for the “AutoUpdateOptions.WhenGetGenID” property. Sometimes we may need to obtain a value for an autoincrement field even more earlier, and in that case we should use the “wgOnNewRecord” value for the “AutoUpdateOptions.WhenGetGenID” property.

Now we have set all properties of the “dsetAuthor” dataset we need. Drop a new dataset to the data module, name it “dsetBook” and set its transactional, SQL and autoincremental properties to access the “BOOK” database table.

Leave a comment