DelphiFAQ Home Search:

Using InterBase generators for AutoIncrement fields

 

comments7 comments. Current rating: 3 stars (4 votes). Leave comments and/ or rate it.

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;

Comments:

2005-12-08, 06:28:14
Equinoxe from Mexico  
rating
This approach has to consider some potential risks:

When using this Generator technique, at the moment you insert a new record to the table using the front-end application (programmed in Delphi, for example), you don't know which value the 'CUST_HASH' field will take. And if that field is your PRIMARY KEY, then you're in serious trouble, because you need to set your transaction to 'READ COMMITED' and refresh the dataset in order to see the real value that this field has after the record insert, since the trigger assigns it a value.

And also I would drop the IF (NEW.cust_hass is NULL) sentence in this trigger, since it opens the door for values for this field to be entered by the user, hence it could lead to duplicate values if later is uses the generator. Remember that the generator just increments by one when you call the GEN_ID function; if the generated value already exists in the table, then the generator will be unable to detect it.

The use of generators should be done with great care.
2006-03-29, 10:42:00
anonymous from United States  
rating
I have also encountered the problem that since the generator is not 'aware' of the table it is used for, even without the 'if new' the generator can be the wrong value and cause the insert to fail.

One option would be to combine the 2 methods by using gen_id(gen_customer, 0) to get the next value without incrementing it and testing for greater than max() to see if the generator needs to be set to a higher value.

There is still the risk of 2 simultaneous inserts but it is greatly reduced by only occuring when the trigger is faulty. If the trigger is not faulty, max() will return a value less than the trigger even if the other insert is still in process. Just don't test for only max()+1=gen_id()
2006-05-26, 16:44:17
anonymous from United Kingdom  
rating
If you just want to grab the next genetrator value to use in Delphi for example do this:

select gen_id(cust_hash,1) from rdb$database;

The value can then be used in a normal INSERT statement and is of course avaialbe to use in 1>M priary keys if detail records are added at the same time;

You do not even need to use it in a database - I use it as an incrementing value for document naming! - nothing to do with the db really.

Roger
2006-07-24, 05:37:17
anonymous from Australia  
I have never read such misinformed rubbish about client server databases in my life.

If anyone was to take the first two comments seriously - heaven forbid.

If you know so little about the topic, better to say little, rather than expose your own ignorance.


2007-08-16, 20:48:56
anonymous from Egypt  
rating
2010-05-17, 01:11:18
pawan0203 from India  
AD POSTING JOBS Join us to make money online from Ad Posting Jobs. ... Clerical Data Entry; Ad Posting Job \- Get Your Posted Id-831181P
2017-01-10, 14:28:37
anonymous from Indonesia  
JIKA ANDA BUTUH ANGKA TOGEL GAIB (_SGP,HKG,MLYS,SYD,THLND,TAIWAN_)
YANG DI JAMIN 100% TEMBUS SILAHKAN
Call/Sms Di No_o82_319_2o8_865_ AKI NUGROHO
Insya Allah Beliau Akan Siap Membantu Menyelesaikan Smua Masalah Anda,.
(_..KUNCI KESUKSESAN..._)
1. Pikiran Yang Positif,
2. Keyakinan Yang Bulat
3. Percaya Bahwa Kita Akan Berhasil
.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: