i |
How to create an autoincrement column |
Firebird does support autoincrement columns via BEFORE INSERT TRIGGERs and GENERATORs. Generators are also named SEQUENCES in Firebird 2.0 and above - and are compliant to the SQL standard.
For example, let's suppose you have a table named T1, with the following columns:
create table t1
(
id integer not null,
field1 varchar(20) not null
);
To make column ID autoincrement, we don't have to do anything special to the column itself, but we need to create a BEFORE INSERT trigger that will put a new value each time a new record is inserted. To make sure we get unique values consistently we use a GENERATOR (a.k.a. SEQUENCE):
CREATE GENERATOR gen_t1_id;
SET GENERATOR gen_t1_id TO 0;
SET GENERATOR statement is used to set the starting value of a sequence. Since we set it to zero, the first record will get a value of one.
Now we create a trigger:
set term !! ;
CREATE TRIGGER T1_BI FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
END!!
set term ; !!