Italiano - English

Firebird: Un trigger sicuro per campi Autoinc

Come è noto Firebird non possiede campi Autoinc che però possono essere gestiti trigger e sequenze (generatori). Sul web si possono trovare numerosissimi esempi.

Purtroppo tutti gli esempi non tengono conto del fatto che il valore per il campo sotto trigger potrebbe essere anche diverso da null, come accade durante un import dei dati. In questo caso il generatore non viene incrementato e non tiene conto del fatto che nel DB è stato inserito un certo valore ID.

In questo documento viene mostrato un esempio di trigger che tiene conto della possibilità che il valore del campo autoinc possa essere anche esistente.

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.

Vota questa pagina:

0 Commenti:

Lascia il tuo commento:

Note:
  • La tua email non è obligatoria e non sarà visibile in alcun modo
  • Si prega di inviare solo commenti relativi a questa pagina
  • Commenti inappropriati o offensivi saranno modificati o eliminati
  • Codici HTML non sono consentiti. Prego usare i BB code:
    [b]bold[/b], [u]underline[/u], [i]italic[/i], [code]code[/code]
Il codice, le illustrazioni e gli esempi riportati in questa pagina sono solo a scopo illustrativo. L'autore non prende alcuna responsabilità per il loro utilizzo da parte dell'utente finale.
Questo materiale è di proprietà di Pk Lab ed è utilizzabile liberamente a condizione di citarne la fonte.