Italiano - English

Firebird: A safe trigger for autoinc fields

Translation for this document is not available or is not complete,
if you are intrested to receive information please write to

As you know, Firebird doesn’t have Autoinc fields but you can use triggers and sequences (generators) in order to obtain Autoinc features. On the web you can find many example about this.

Unfortunately I can’t find examples where triggered field (autoinc field) could receive a NOT NULL value, as is in data import from script. Following common examples,  when the triggered field receives a value, the generator isn’t incremented so you may have “key violation” on future inserts.

In this document is shown a safe trigger example that can be used also when the triggered filed could receive a value.

Classico trigger per campi autoinc

(da http://www.firebirdsql.org/manual/generatorguide-rowids.html)

 
CREATE TRIGGER TrgiggerTest FOR TABLE1
active before INSERT position 0
AS
BEGIN
  IF ((NEW.id IS NULL) OR (NEW.id = 0)) THEN
    NEW.id = gen_id( gidTest, 1 );
END

In caso il campo ID vale NULL il generatore giTest viene incrementato e il nuovo valore viene assegnato al campo ID.

Ma cosa succede se in fase di inserimento il campo ID ha un valore, esempio vale 100 ? Nulla, se in tabella non esiste un ID=100 il record viene inserito senza problemi mentre il generatore giTest rimane invariato. Se il generatore ha un valore minore dell'ID appena inserito, Questo comportamento provoca inconsitenza e quando con successivi inserimenti con ID=NULL il generatore raggiungerà il valore 100 si avrà un key violation!

Trigger sicuro per campi autoinc

Il problema si puo' risolvere agevolmente inserendo un ramo "else" al trigger classico

 
CREATE TRIGGER TrgiggerTest FOR TABLE1
active before INSERT position 0
DECLARE VARIABLE diff INTEGER;
AS
BEGIN
  IF ((NEW.id IS NULL) OR (NEW.id = 0))
    NEW.id = gen_id( gidTest, 1 );
  ELSE
    IF (NEW.id > GEN_ID( gidTest, 0 ) ) THEN
      diff = gen_id( gidTest  , NEW.id - GEN_ID( gidTest, 0 ) );
END

Quindi se ID ha un valore e questo valore è maggiore del generatore, il generatore viene impostato al valore dell'ID

Un esempio pratico

Dobbiamo importare una struttura ad albero in una una tabella con relazione padre-figlio

 
CREATE TABLE FAMILY (
    ID           INTEGER NOT NULL ,
    IDFATHER     INTEGER DEFAULT NULL,
    NOME         VARCHAR(100)
);
 
/* Primary Keys*/
ALTER TABLE FAMILY ADD CONSTRAINT PK_FAMILY PRIMARY KEY (ID);
 
 
/* Foreign Keys*/
ALTER TABLE FAMILY ADD CONSTRAINT FK_FAMILY_1 FOREIGN KEY (IDFATHER)
REFERENCES FAMILY (ID) ON UPDATE CASCADE;


Il campo ID è la chiave e si utilizza un autoinc tramite generatore e trigger “Before Insert” classico

 
/* Generator */
CREATE GENERATOR GEN_FAMILY_ID;
 
/* Trigger: FAMILY_BI */
CREATE OR ALTER TRIGGER FAMILY_COMMON_BI FOR FAMILY
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF ( (NEW.id IS NULL) OR (NEW.id = 0)) THEN
    NEW.id = gen_id(gen_FAMILY_id,1);
END;

Supponiamo di dover importare una struttura ad albero di questo tipo

  • GrandFather1
    • Son1
      • GrandSon1
      • GrandSon2
    • Son2

Anche se la struttura, ovvero il campo ID può essere gestito con un autoinc dall’applicazione, in fase di importazione è necessario indicare un ID altrimenti non si può costruire la relazione padre-figlio.

Inseriamo i record necessari

 
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (1,NULL,'GrandFather1');
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (2,1,'Son1');
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (3,1,'Son2');
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (4,2,'GrandSon1');
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (5,2,'GrandSon2');

Ma in questo caso cosa succede al generatore GEN_FAMILY_ID, ovvero quanto vale dopo gli inserimenti ?
Purtroppo la risposta è che non viene modificato, quindi se è "vergine" vale 0. Se successivamente si fa un inserimento ad esempio di un terzo figlio, volendo utilizzare la funzionalità autoinc (passado NULL al campo ID):

 
INSERT INTO FAMILY (ID, IDFATHER,NOME) VALUES (NULL,1,'Son3');

si otterrà un errore di Key Violation in quanto il trigger assegna al campo l’ID 1 che è gia presente nel DB.

Una soluzione al problema è aggiornare il generatore dopo aver fatto l'insert multiplo con l'apposito comando

 
ALTER SEQUENCE GEN_FAMILY_ID RESTART WITH 5;

Una alternativa è utilizzare il trigger "sicuro" qui proposto

 
CREATE OR ALTER TRIGGER FAMILY_SAFE_BI FOR FAMILY
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE diff INTEGER;
BEGIN
  IF ( (NEW.id IS NULL) OR (NEW.id = 0)) THEN
    NEW.id = gen_id(gen_FAMILY_id,1);
  ELSE
    IF (NEW.id > GEN_ID( GEN_FAMILY_ID, 0 ) ) THEN
      diff = gen_id( GEN_FAMILY_ID  , NEW.id - GEN_ID( GEN_FAMILY_ID, 0 ) );
END;

Nel ramo “else” il generatore viene assegnato al valore del ID ricevuto come parametro, solo nel caso in cui il parametro è maggiore del valore del generatore. In questo modo si è certi che ogni nuovo inserimento otterrà un ID maggiore del massimo ID presente in tabella.

Anzichè utilizzare il ramo "ELSE" del trigger BEFORE INSERT si potrebbe pensare di inserire l'eventuale aggiornamento del generatorre in un trigger "AFTER INSERT/UPDATE". Ma questa soluzione non sarebbe robusta in un ambiente multiutente.

Vote this page:

0 Comments:

Leave your comment:

Note:
  • Your email email will not be visible or used in any way, and is not required
  • Please keep comments relevant
  • Any content deemed inappropriate or offensive may be edited and/or deleted
  • HTML code is not allowed. Please use BBCode to format your text
    [b]bold[/b], [u]underline[/u], [i]italic[/i], [code]code[/code]
The coding examples presented here are for illustration purposes only. The author takes no responsibility for end-user use
This work is property of Pk Lab. You can use it for free but you must retain author's copyright.