Title: Generating Unique Value in InterBase
Question: One of the common problems in any relational database management system is how to effectively generate unique values (for primary or alternate keys) in multi-user environment.
Answer:
If you try to use something like:
SELECT MAX()+1 FROM
You risk that some parallel user will do the same thing before you write the value previous query returned into database and end the transaction. In this case this parallel user will get the same result from the above query and end up with duplicated value! Another downside is poor performance on large datasets.
The other solution would be to have separate table with one row and one field within it. When we need a unique value, we could simply read the value from the table, increment it and write it back to the table. But this solution suffers from the same weakness as the first one. Nobody guaranties that some other user will not attempt to obtain unique value in the very same moment as when we do - if somebody reads from the special table after we read but before we write into it, it will end up with the same value.
In this point we would need some form of inter-process (inter-user) synchronization like critical sections, but this is generally not available in any of major RDBMSs.
Solution: use built in solution ;)
The built in solution InterBase offers are so-called generators. This is similar to sequences in Oracle. Generators are specially protected by the RDBMS so no conflict can occur.
Let us say we have table T with field T_Id which is primary key and we need to generate unique value for T.T_Id whenever a new record is inserted into T.
First we create generator:
CREATE GENERATOR T_Generator;
The we create trigger which will pull-out the unique value out of the generator before any insert.
CREATE TRIGGER T_GenereteId FOR Category
BEFORE INSERT
AS BEGIN
NEW.T_Id = GEN_ID (T_Generator, 1);
END !!
We use built-in InterBase function GEN_ID to increment generator and get this new, incremented value.
Here is ERwin macro that automatically expands to appropriate generator and trigger for field which is table's primary key (you should name this field _Id). Use it if primary key is one numeric field.
CREATE GENERATOR %TableName_Generator;
SET TERM !! ;
CREATE TRIGGER %TableName_GenereteId FOR %TableName
BEFORE INSERT
AS BEGIN
NEW.%TableName_Id = GEN_ID (%TableName_Generator, 1);
END !!
SET TERM ; !!