I Trigger in MySQL

Sempre a partire dalla version 5.0.2, MySQL, oltre alla già viste Stored Procedures, ha introdotto i trigger, ovvero un meccanismo attraverso il quale è possibile automatizzare talune operazioni al verificarsi di eventi riguardanti i dati, quali INSERT, UPDATE o DELETE.

Creare un Trigger con MySQL

Quando definiamo un Trigger in MySQL dobbiamo stabilire se questo debba innescarsi prima o dopo un certo evento. Potremo quindi definire dei Trigger per queste diverse combinazioni di tempo/evento:

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

Ciascun Trigger deve essere necessariamente associato non solo ad uno specifico database MySQL (dovrà quindi avere un nome univoco al suo interno) ma anche ad una specifica tabella in esso contenuta.

La sintassi di base per la creazione di un Trigger è la seguente:

CREATE TRIGGER nome_trigger 
  tempistica evento ON nome_tabella
  FOR EACH ROW
BEGIN
  
  ...
  ...
  ...
  
END;

Vediamo un esempio concreto di Trigger che si attiva prima di oggi aggiornamento sulla tabella "clienti" del nostro ipotetico database, al fine di creare una sorta di log delle modifiche all’interno di una tabella chiamata "clienti_log":

DELIMITER $$;

CREATE TRIGGER log_upd_clienti 
  BEFORE UPDATE ON clienti
  FOR EACH ROW 
BEGIN
  INSERT INTO clienti_log SET
  vecchio_nome = OLD.nome,
  nuovo_nome = NEW.nome,
  vecchia_mail = OLD.email,
  nuova_email = NEW.email, 
  modificato = NOW(); 
END $$

DELIMITER ;

In pratica abbiamo creto un Trigger chiamato "log_upd_clienti" che viene invocato subito prima (BEFORE) dell’aggiornamento (UPDATE) della tabella "clienti".

Le istruzioni che dovranno essere eseguite sono contenuto – come accade nelle Stored Procedures – all’interno del blocco delimitato da BEGIN e END. Nel caso specifico, all’interno di questo blocco, effettuiamo un inserimento dati all’interno della tabella "clienti_log" dove registriamo i valori vecchio e nuovo per i campi "nome" ed "email" della tabella "clienti". Per farlo abbiamo utilizzato le keywords OLD e NEW per far riferimento, rispettivamente, al valore precedente alla modifoca ed a quello, eventualmente differente, successivo all’operazione di UPDATE.

In merito a queste due keyword è importante ricordare che il loro utilizzo non è sempre possibile, precisamente:

• INSERT: per le operazioni di inserimento dati è possibile utilizzare solo NEW in quanto non esiste alcun vecchio valore a cui far riferimento;
• UPDATE: è possibile utilizzare sia OLD che NEW a seconda che si desideri far riferimento al valore di un campo, prima o dopo l’operazione di UPDATE;
• DELETE: è possibile utilizzare solo OLD in quanto non si ha alcun nuovo valore.

Nel caso di Trigger scatenati mediante BEFORE per gli eventi INSERT e UPDATE, è anche possibile modificare il valore NEW di un dato campo prima che tale valore venga scritto nel database, facciamo un esempio:

delimiter $$;

CREATE TRIGGER controllo_eta
  BEFORE UPDATE ON clienti
  FOR EACH ROW
BEGIN
  IF NEW.anni < 0 THEN
    SET NEW.anni = 0;
  ELSEIF NEW.anni > 120 THEN
    SET NEW.anni = 120;
  END IF;
END $$

DELIMITER ;

Nel nostro esempio abbiamo creato un Trigger per effettuare una sorta di controllo sull’età dichiarata dai clienti: se questa è minore di 0 il valore che verrà scritto nel DB sarà 0, viceversa se viene indicata un età superiore di 120 anni il nuovo valore sarà impostato automaticamente a 120.

La clausola DEFINER nella creazione di un Trigger con MySQL

Come visto per le Stored Procedures e per le Stored Functions, è possibile inserire la clausola DEFINER per indicare al DBMS qual’è l’uteente legittimato a farricorso allo specifico Trigger che si sta creando. In mancanza, il DBMS assegnerà il Trigger all’utente corrente (cioè l’utente che stiamo utilizzando per crearlo, come se avessimo dichiarato esplicitamente DEFINER = CURRENT_USER).

Vediamo un esempio:

CREATE DEFINER=`nomeutente`@`localhost` TRIGGER ...

Nel nostro esempio abbiamo creato un nuovo Trigger accessibile per l’utente "nomeutente" connesso mediante localhost.

Gestire più Trigger associati allo stesso momento/evento

A partire dalla versione 5.7.2 di MySQL, è possibile che alla medesima tabella siano associati più Trigger attivabili allo stesso momento e per il medesimo evento. In tal caso i diversi Trigger saranno eseguiti dal DBMS secondo l’ordine di creazione, salvo che non sia stato specificato un ordine esplicito (mediante le parole chiave FOLLOWS e PRECEDES seguite dal nome del Trigger che si vuole seguire o anticipare).

Supponiamo, ad esempio, di voler attivare un altro Trigger (associato sempre alla stessa tabella per il medesimo tempo/evento) da eseguirsi prima di "controllo_eta" visto nell’esempio precedente:

CREATE TRIGGER controllo_username
  BEFORE UPDATE ON clienti
  FOR EACH ROW
  PRECEDES controllo_eta
...

Il nuovo Triger "controllo_username" verrà lanciato automaticamente porima di "controllo_eta" nonostante sia stato creato successivamente.

Conoscere i Trigger attivi su un DB MySQL

Per conoscere tutti i Triggers attivi all’interno del nostro database possiamo semplicemente digitare in console:

SHOW TRIGGERS;

Eliminare un Trigger in MySQL

Una volta creato un Trigger è sempre possibile rimuoverlo mediante il comando DROP TRIGGER, in questo modo:

DROP TRIGGER nome_trigger;

Anche per i Trigger è possibile aggiungere la clausola IF EXISTS in questo modo:

DROP TRIGGER IF EXISTS nome_trigger;