Trigger

I trigger sono oggetti associati a tabelle, che vengono attivati nel
momento in cui un determinato evento si verifica relativamente a quella
tabella. Sono stati introdotti a partire da MySQL 5.0.2.

Quando definiamo un trigger, stabiliamo per quale evento deve essere
attivato (inserimento di righe, modifiche o cancellazioni) e se deve
essere eseguito prima o dopo tale evento; avremo quindi i seguenti tipi
di trigger:

• BEFORE INSERT
• BEFORE UPDATE
• BEFORE DELETE
• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

Il trigger stabilirà un'istruzione (o una serie di istruzioni) che
saranno eseguite per ogni riga interessata dall'evento.

Ecco la sintassi per la creazione di un trigger:

CREATE
    [DEFINER = { utente | CURRENT_USER }]
    TRIGGER nome tipo
    ON tabella FOR EACH ROW
    [{ FOLLOWS | PRECEDES} nome_di_altro_trigger]
    istruzioni

Il trigger è associato ad una tabella, ma fa parte di un database, per
cui il suo nome deve essere univoco all'interno del db stesso.

A partire dalla versione 5.7.2 di MySQL, è possibile definire più trigger, relativi ad una tabella, che vengano attivati con le medesime tempistiche per uno stesso evento. Di default, il loro ordine di attivazione è identico a quello di creazione ma può essere modificato sfruttando le parole chiave FOLLOWS e PRECEDES. Queste devono essere accompagnate dal nome del trigger che, rispettivamente, sarà seguito o preceduto da quello che stiamo definendo.

Nelle versioni precedenti alla 5.7.2, al contrario, era impossibile avere per una stessa tabella, ad esempio, più trigger BEFORE INSERT, mentre se ne potevano avere due con tempistica BEFORE oppure due relativi all'INSERT ma con tempistiche diverse.

È importante tener presente che, riferendosi ad inserimenti o a
cancellazioni delle righe, non si intende necessariamente una istruzione
INSERT o DELETE, ma qualsiasi operazione dalla quale scaturisca l'evento
interessato. Ad esempio, l'inserimento di dati può avvenire anche tramite
una istruzione LOAD DATA.

Le istruzioni da eseguire all'attivazione del trigger possono essere una
o più di una. In quest'ultimo caso si usa la sintassi per le istruzioni
composte del tipo BEGIN ... END come già visto nella precedente lezione
sulle stored procedures.
Da notare che solo a partire da MySQL 5.0.10 il codice dei trigger può
contenere riferimenti diretti alle tabelle.

La clausola DEFINER (introdotta in MySQL 5.0.17)
specifica se come creatore del trigger deve essere considerato l'utente
attuale (default) o un altro utente specificato nella forma
nome@host. Questo sarà l'utente di cui saranno
controllati i permessi al momento dell'esecuzione del trigger.

Una volta creato, il trigger può essere eliminato con l'istruzione DROP
TRIGGER:

DROP TRIGGER [database.]nome

Ovviamente il nome del database, se omesso, viene considerato uguale al
database in uso. È importante notare che, prima di MySQL 5.0.10, questa
istruzione richiedeva che il nome del trigger fosse qualificato non col
nome del database, ma con quello della tabella a cui era associato. Ne
consegue che, in caso di upgrade da una versione precedente la 5.0.10, è
necessario eliminare i trigger prima dell'aggiornamento e ricrearli in
seguito; in caso contrario le istruzioni DROP TRIGGER non funzionerebbero
dopo l'upgrade.

Vediamo un esempio pratico di trigger:

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
        IF NEW.amount < 0 THEN
                SET NEW.amount = 0;
        ELSEIF NEW.amount > 100 THEN
                SET NEW.amount = 100;
        END IF;
END;//
delimiter ;

Questo codice si attiva prima di ogni update sulla tabella account: su
ognuna delle righe da modificare viene controllato il valore che sta per
essere assegnato al campo amount, per verificare che sia compreso fra 0 e
100; in caso contrario viene riportato entro tali limiti. Come potete
vedere, quindi, attraverso il trigger siamo in grado di modificare il
valore che sta per essere aggiornato sulla tabella.

Il qualificatore NEW indica proprio che il nome di
colonna che stiamo utilizzando si riferisce al nuovo valore della riga
che sta per essere aggiornata. NEW si può utilizzare in caso di INSERT e
UPDATE. Analogamente è disponibile il qualificatore OLD
che fa riferimento ai valori precedenti la modifica, e si può utilizzare
in caso di UPDATE e DELETE. La modifica attraverso l'istruzione
SET è possibile solo per i valori NEW e solo nei trigger
di tipo BEFORE.

Per lavorare sui trigger è attualmente necessario il privilegio SUPER.