I triggers sono uno di quegli argomenti di cui si sente spesso parlare, ma difficilmente si approfondisce come si deve. In questo post, che non vuol essere un tutorial MySql sui triggers, volevo solo affrontare il tema in modo più completo e dettagliato per non far restare tale parola avvolta solo da un alone di mistero.
Tanto per citare, invece, un tutorial coi fiocchi sull’argomento :
However, as applications grow more and more complicated, the further we can abstract the layers of an application to handle what they should, the greater our internal development usability becomes
Questo non significa che occorre utilizzare e sfruttare un trigger solo in applicazioni con una enorme mole di dati, ma, va da se, i vantaggi sono tanto più evidenti quanto più grande è il database di progetto su cui applichiamo i triggers. Sempre che convenga..
In sostanza, i trigger sono semplici oggetti associati a tabelle che vengono attivati nel momento in cui un determinato evento si verifica relativamente a quella tabella. Essi consentono di applicare un controllo sull’integrità dei dati inseriti cioè di verificare se questi ultimi sono corretti e se vanno, eventualmente, modificati.
Relativamente a MySQL, si parla di trigger dalla versione MySQL 5.0.2
La definizione di un trigger deve essere stabilita dopo aver deciso a quale evento deve essere associato (inserimento di righe, modifiche o cancellazioni) e se deve essere eseguito prima o dopo tale evento.
Ecco i possibili tipi di trigger:
- BEFORE INSERT
- BEFORE UPDATE
- BEFORE DELETE
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
Vediamo, in dettaglio, la sintassi di base di un trigger tenendo presente che, anche se un trigger è associato ad una tabella, facendo parte di un database,il suo nome deve essere univoco all’interno del db stesso.
CREATE TRIGGER nome_del_trigger
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON nome_della_tabella
FOR EACH ROW
codice_SQL_da_applicare_con_il_trigger
La prima linea indica il nome del trigger che stiamo creando. Tale nome, così come per le tabelle o database, deve essere lungo al massimo 64 caratteri.
La seconda linea determina il tempo di esecuzione del trigger. Come detto, infatti, esso può agire prima dell’esecuzione vera e propria della query oppure dopo. In seguito capiremo cosa questo comporta.
La terza linea introduce il tipo di query SQL che genererà l’esecuzione del trigger. Attenzione perchè sono ammesse solo query esecutive (INSERT, UPDATE,DELETE) e non SELECT. Altra cosa importante da sottolineare è che la INSERT non rappresenta soltanto le query classica, ma tutte le query che prevedono l’inserimento dati come LOAD DATA o REPLACE in caso di un record nuovo.
La quarta linea specifica la tabella su cui attivare il trigger. È possibile avere un solo trigger attivo per lo stesso tipo di query sulla stessa tabella.
La quinta linea illustra l’individualità del trigger che si applica ad ogni riga singolarmente e non a tutta la tabella nel suo insieme. Per fare un esempio, una DELETE di massa sulla tabella, attiva e avvia tanti trigger quante sono le righe interessate.
La sesta linea infine rappresenta il codice SQL da eseguire all’attivazione dell’evento. Il codice SQL che possiamo utilizzare, normalmente, effettua una serie di controlli di flusso.
Prima di vedere un esempio su come utilizzare i trigger bisogna specificare una cosa. Poiché le istruzioni all’interno dello statement devono terminare con il famoso punto e virgola, esso non può essere sfruttato per indicare la terminazione di un trigger.
A tale scopo, viene definito // come terminazione quindi, prima della dichiarazione dei trigger bisogna definire il delimitatore con il seguente codice:
DELIMITER //
Ed ora, per completare il discorso, vediamo un esempio. Tra le tante modalità di applicazione, una delle più sfruttate è data dal controllo della correttezza dei valori inseriti nel database.
Pensiamo, ad esempio, al caso in cui abbiamo un campo i cui valori possono oscillare tra un massimo e un minimo (0-100). Il controllo potrebbe certamente esser fatto a posteriori tramite una query di UPDATE che aggiorni i vari record che presentano eventuali errori, ma anche sfruttando un trigger che agisca prima di una insert o di un update :
DELIMITER //
CREATE TRIGGER prodotti_insert_controllo
BEFORE INSERT ON prodotti
FOR EACH ROW
BEGIN
IF NEW.prezzo < 0 THEN
SET NEW.prezzo = 0;
END IF;
IF NEW.prezzo > 1000 THEN
SET NEW.prezzo = 1000;
END IF;
END; //
CREATE TRIGGER prodotti_update_controllo
BEFORE UPDATE ON prodotti
FOR EACH ROW
BEGIN
IF NEW.prezzo < 0 THEN
SET NEW.prezzo = 0;
IF NEW.prezzo > 1000 THEN
SET NEW.prezzo = 1000;
END; //
DELIMITER;
Come vedete, invece di preoccuparci a posteriori di intervenire sui valori fuori range, meglio prevenire con un trigger dedicato. Pensate, infatti, a dover controllare la correttezza di oltre 10000 records piuttosto che a prevenire ed avere una correttezza già garantita.
Conclusioni
Utilizzare i triggers per risolvere problemi all’interno di un database è davvero molto comodo, ma aggiungendo un carico di lavoro, dobbiamo capirne vantaggi reali prima di utilizzarli.
Infatti, a volte possiamo anche farne a meno optando per soluzioni diverse (jobs per schedulare azioni ricorsive, stored procedure per gestire operazioni ecc). Inoltre è fondamentale avere a mente il numero di righe che saranno coinvolte dall’azione del trigger,poichè fin tanto che si lavora con tabelle con poche centinaia di righe potrebbero esser utili senza sconvolgere le prestazioni del sistema, ma su database con milioni di righe, urge analisi approfondita prima di creare triggers!
