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 ; !!


Lire la suite...