DelphiFAQ Home Search:
General :: Databases :: InterBase
Help with InterBase, Borland's open source database.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Using InterBase generators for AutoIncrement fields

InterBase doesn't offer the convenient AutoIncrement datatype as some desktop database systems (MS-Access, Parados) do. In a project I simulated this for a unique index field by using a trigger combined with a generator.

The example below assumes that there is a table CUSTOMER with a uniquely indexed field CUST_HASH.
The generators' name is GEN_CUSTOMER.

The traditional technique would be to detect the current maximum number max and then insert a value of [max+1]:

SELECT MAX(cust_hash)+1 FROM customer
INSERT INTO customer (...) VALUES (...)


The risk with this approach is that a parallel user could theoretically do the same thing before you write the determined value and end the transaction. The parallel user would try to post the same number and either cause a unique-index violation or post a duplicated value!

The trick with the generator is also faster since you don't have to do the max() query for each insert.

CREATE GENERATOR gen_customer; 
 
 SET GENERATOR gen_customer TO 100;
 
 CREATE TRIGGER customer_autoinc FOR customer 
 BEFORE INSERT AS 
 BEGIN
   IF (NEW.cust_hash is NULL) THEN
     NEW.cust_hash = GEN_ID(gen_customer, 1); 
 END;
 

Generated 12:00:34 on Oct 20, 2017