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
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
set term ; !!
Lire la suite...