Trigger

La maggior parte dei database relazionali permette di estendere le funzionalità che abbiamo visto fin qui per aggiungere comportamenti o addirittura nuovi tipi di dati. In questa lezione approfondiremo proprio questi aspetti.

Trigger

Un trigger è un’azione che deve essere intrapresa quando succede un certo tipo di evento nel database. La sintassi di creazione di un trigger è definita nello standard ISO, sebbene essa non venga rispettata da tutti i database. Ad esempio Microsoft Sql Server ha una sintassi abbastanza personalizzata di CREATE TRIGGER, al contrario di MySQL che invece rispetta abbastanza lo standard. Consideriamo la seguente istruzione:

CREATE TRIGGER ins_month_if_year
BEFORE INSERT ON Book
FOR EACH ROW SET NEW.pub_month = COALESCE(NEW.pub_month, 'JAN');

Ciò comporterà, ad ogni inserimento nella tabella Book, l'impostazione automatica del valore JAN tutte le volte in cui non viene specificato un mese di pubblicazione.

Diamo un'occhiata alla sintassi: dopo la clausola CREATE TRIGGER viene specificato il nome del trigger da creare. Invece, con BEFORE INSERT ON indichiamo che vogliamo eseguire un’azione prima dell’inserimento nella tabella Book. Quando viene specificata la parola chiave BEFORE non si dovrebbero fare modifiche al database, piuttosto si dovrebbe intervenire sui dati che stanno per essere inseriti, altrimenti si rischia di perdere un po’ la visione di quello che sta succedendo. Si noti che non tutti i database supportano l'uso della parola chiave BEFORE: su Microsoft Sql Server, ad esempio, si può implementare un comportamento analogo con il trigger di alternativa all’esecuzione standard (INSTEAD OF).

Con la clausola FOR EACH ROW specifichiamo che l’istruzione che segue sarà eseguita per ogni riga da inserire. Infatti, è
possibile inserire più righe con una sola istruzione INSERT, come abbiamo visto nella lezione 9.

Ovviamente avremmo potuto ottenere lo stesso effetto impostando un valore di default, ma con TRIGGER la forzatura avverrà solamente in fase di inserimento e, per come abbiamo realizzato il trigger, anche per le INSERT che tentassero di impostare esplicitamente il valore NULL nel campo:

INSERT INTO Book(ID, title, publisher, pub_year, pub_month) values(2, 'year', 1, 2015, NULL);

Ovviamente si può introdurre un trigger analogo anche in aggiornamento, sostituendo BEFORE INSERT ON con BEFORE UPDATE ON.

I trigger di tipo BEFORE sono comodi quando ci si vuole assicurare una certa coerenza tra diversi dati di una stessa riga. Esempi sono i casi in cui una riga contiene un campo che deve essere la somma di altri due, oppure che non può assumere certi valori in certe condizioni, che non possono essere espresse con un semplice vincolo di colonna (CHECK).

È possibile fare in modo che il trigger venga eseguito dopo una certa operazione, ad esempio per inizializzare tabelle di relazione, per allineare dati o ricalcolare dati aggregati. Possiamo anche usare i trigger per loggare le modifiche: nell'esempio che segue, salveremo con un log le modifiche fatte alla tabella Person, salvandole nella nuova tabella LogPerson.

CREATE TABLE LogPerson(IdPerson int NOT NULL,surname varchar(160) NOT NULL,ts TIMESTAMP);
CREATE TRIGGER log_updates AFTER UPDATE ON Person
        FOR EACH ROW INSERT INTO LogPerson(ts, IdPerson, surname)
        VALUES(CURRENT_TIMESTAMP, NEW.id, NEW.surname);

Oracle: id auto-incrementali

I trigger sono molto utili in Oracle per ottenere una funzionalità presente in altri database come MySQL (AUTO_INCREMENT) e SQL Server (IDENTITY): i valori auto-incrementali.

Mentre su MySQL è possibile creare un campo numerico che si incrementa ad ogni inserimento tramite la parola chiave AUTO_INCREMENT (si veda la lezione 10 della Guida MySQL), su Oracle la medesima funzionalità si può ottenere con una sequenza, utilizzandola come descritto nella Guida Oracle ma dentro un trigger:

CREATE SEQUENCE PersonSeq;
CREATE OR REPLACE TRIGGER Person_inc
    BEFORE INSERT ON Person
    FOR EACH ROW
    BEGIN
          SELECT PersonSeq.NEXTVAL INTO :NEW.id FROM dual;
    END;

Questo trigger, all’inserimento di una persona, calcola il valore successivo della sequenza (NEXTVAL) e lo inserisce nel campo id della riga da inserire.