MySQL: cos’è un trigger e come funziona
This entry was posted on February 12th, 2010 and is filed under mysql.
Spesso abbiamo il bisogno di monitorare in qualche modo le tabelle nel nostro database e di eseguire delle operazioni al compimento di una determinata operazione su di esse.
I trigger nei linguaggi SQL sono una sorta di procedure che vengono eseguite al verificarsi di un determinato evento. Di per sè sono uno strumento potentissimo a disposizione e, se ben usati, riducono di molto le richieste al server ed evitano l’utilizzo di crontab residenti sul server.
Come in quasi tutti i DBMS, anche MySQL possiede delle librerie per la gestione dei trigger. Bisogna però stare molto attenti al loro utilizzo e soprattutto alla versione di MySQL che si utilizza. In versioni antecedenti alla 5.0.10 sono presenti molti bug che potrebbero causare non pochi problemi.
Vediamo però come funzionano. Volendo essere un pò più precisi potremmo vedere un trigger come un handler che resta in attesa dell’evento per cui è stato dichiarato e, al verificarsi dell’evento, fa partire la procedura per cui è stato realizzato.
La dichiarazione di un trigger è molto semplice. La sintassi è la seguente:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Vediamo passo passo il significato del codice.
DEFINER: di default settata su CURRENT_USER definisce il contesto di sicurezza quando il trigger controlla i privilegi. Il più delle volte viene del tutto omessa questa clausola.
trigger_name: il nome del trigger. Tale nome deve essere univoco per ogni trigger all’interno di un database.
trigger_time { BEFORE | AFTER }: indica il momento in cui attivare il trigger. Prima o dopo il verificarsi di un determinato evento.
trigger_event { INSERT | UPDATE | DELETE }: indica l’evento per cui viene attivato il trigger. Insert si riferisce a tutti quegli eventi per cui viene aggiunto un elemento alla tabella (INSERT, REPLACE, LOAD DATA). Update all’aggiornamento della tabella e Delete all’eliminazione di dati dalla tabella. Bisogna stare attenti perchè non possono essere creati più di un trigger per la stessa coppia time-event mentre è possibile creare un trigger per BEFORE-event e uno per AFTER-event. Se abbiamo più azioni da effettuare bisogna includerle tutte in un unico trigger.
tbl_name: nome della tabella.
FOR EACH ROW: indica che il trigger eseguirà il codice per ogni riga coinvolta nell’evento.
trigger_stmt: il codice effettivo da caricare al verificarsi dell’evento. Se non è un singolo statement, il blocco di istruzioni viene delimitato da BEGIN…END.
Uno degli utilizzi più importanti che viene fatto dei trigger è quello di delegare molti dei controlli lato server direttamente al DBMS. Ad esempio, al momento dell’inserimento di un elemento nella tabella, potremmo aver bisogno di controllare che i dati siano validi, oppure che siano personalizzati in base a qualche proprietà. In questi e tanti altri casi ci vengono incontro i trigger che, se ben usati, ci fanno risparmiare molto tempo e prestazioni.
Prima di vedere un esempio di utilizzo dei trigger bisogna specificare un dettaglio. Dato che le istruzioni all’interno dello statement devono terminare con il punto e virgola, esso non può essere utilizzato per indicare la terminazione di un trigger quindi bisogna stabilire un nuovo carattere di terminazione. Come consuetudine viene definito // come terminazione quindi, prima della dichiarazione dei trigger bisogna definire il delimitatore con il seguente codice:
DELIMITER //
Vediamo quindi un semplice esempio di utilizzo. Supponiamo di avere una tabella contenente valori numerici che devono essere compresi in un intervallo finito, ad esempio 0-100, possiamo utilizzare un codice come il seguente per adattare i valori inseriti a tale intervallo.
DELIMITER //
CREATE TRIGGER check_value
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.value < 0 THEN
SET NEW.value = 0;
END IF;
IF NEW.value > 100 THEN
SET NEW.value = 100;
END IF;
END; //
La variabile NEW contiene al suo interno i valori dei campi della tabella quindi basta utilizzare come suffisso il nome del campo per accedere al suo valore.
Questo è solo un semplice esempio ma le potenzialità dei trigger sono davvero incredibili.
Benvenuto! Mi chiamo
February 12, 2010 at 2:09 pm
Ottimo articolo caro Simone. Alla fine ho optato proprio per un trigger, cercherò di ottimizzarlo… vediamo che ne esce fuori.
Un saluto.
February 12, 2010 at 3:13 pm
Ciao Pier!
Grazie per i complimenti. Buon lavoro allora!