Using InterBase generators for AutoIncrement fields

Databases
InterBase (28)
MS-SQL (5)
mysql (32)
Oracle (1)

Exchange Links

About this site

Links to us

See also:

Fixing a broken generator (InterBase)

Using InterBase generators for AutoIncrement fields

5 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

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

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

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

Keywords:


	Search: