I tipi di trigger in MySQL

Con la versione 5 di MySQL sono state introdotte nuove funzionalità che hanno potenziato notevolmente questo DBMS rendendolo concorrenziale con alternative commerciali ritenute più avanzate (si pensi per esempio ad Oracle); una delle novità più importanti attiene alla possibilità di creare trigger per la gestione delle procedure.

In pratica un trigger inserito all'interno di un database consente di gestire in modo automatico determinate procedure tramite regole definite, il tutto senza richiedere l'intervento dell'utente che dovrà digitare un numero inferiore di comandi ed istruzioni SQL; in questo modo la base di dati avrà la possibilità di dar vita a particolari comportamenti agendo in risposta al verificarsi di eventi esterni, parliamo quindi di basi di dati "attive".

La presenza di trigger incrementa il livello di astrazione dei dati grazie al quale le regole che danno vita ai comportamenti non sono visibili all'esterno e, nello stesso tempo, i comportamenti non vengono dettati soltanto dal DBMS ma dipendono dallo schema dei dati.

Alla base del funzionamento dei trigger vi sono tre elementi fondamentali (anche se non tutti obbligatori) che devono essere presi in considerazione in sede di definizione delle regole:

1. Eventi: questi possono essere il risultato di un comando SQL inserito in una query come per esempio un inserimento, un aggiornamento o la cancellazione di uno o più record, anche un ulteriore trigger può generare un evento (cascade triggers); gli eventi sono sempre rivolti verso una tabella che, per questo motivo, prende il nome di "target".
2. Condizioni: non sempre sono presenti, si tratta infatti di argomenti opzionali che possono influire sull'applicazione o meno di determinate regole; il loro funzionamento è basato sulla logica booleana (TRUE o FALSE) e vengono espresse tramite istruzioni scritte in linguaggio SQL del quale rispettano la sintassi.
3. Azioni: sono i comportamenti (eventualmente condizionati) che si verificano sulla base degli eventi; possono presentarsi come semplici istruzioni SQL, alcuni database integrano però dei linguaggi specifici per la loro definizione.

Tipologie di trigger e di azioni

L'amministratore di un database ha a disposizione alcune tipologie di trigger che si differenziano sia per le diverse parti dello schema di dati a cui fanno riferimento sia per il loro ambito di utilizzo:

• Ttrigger a livello di riga (o tupla o record): sono eseguiti una singola volta per ogni record su cui agisce una determinata istruzione (evento); vengono utilizzate spesso in operazioni di sincronizzazione dei dati.
• Trigger a livello di istruzione: utilizzati raramente per operazioni che influiscono sulla struttura dei dati, essi prevedono un'unica esecuzione per ogni istruzione anche se coinvolgono più record.
• Trigger con clausole BEFORE ed AFTER: i trigger possono essere definiti sulla base di una linea temporale, dato che essi reagiscono a determinati eventi è quindi possibile stabilire se l'azione conseguente all'istruzione debba verificarsi precedentemente o successivamente rispetto a quest'ultima.
• Trigger a livello di basi di dati: vengono eseguiti quando sono in corso operazioni che riguardano lo schema dei dati come per esempio in conseguenza di istruzioni per la creazione, l'alterazione, la rinomina e la rimozione di una tabella o la revoca di privilegi sulla tabella stessa. Forniscono uno strato di protezione per l'integrità delle basi di dati.

Vi sono poi alcune particolari tipologie di trigger come per esempio quelli denominati INSTEAD OF che vengono utilizzati con una certa frequenza per la gestione dei dati in database come Oracle: in pratica la clausola INSTEAD OF indica quali operazioni debbano essere eseguite in sostituzione di quelle che hanno reso necessaria la chiamata del trigger.

Per quanto riguarda le tipologie di azione di un trigger, queste possono essere distinte in due gruppi fondamentali:

• Azioni eseguite in un'unica occorrenza in seguito all'invocazione del trigger (come nel caso dei trigger a livello di istruzione), in esse le variabili disponibili hanno il valore della tabella di transazione che conserva lo stato precedente e successivo alle modifiche della tabella target.;
• Azioni eseguite per ogni singolo record interessato dall'istruzione (come nel caso dei trigger a livello di riga), in esse le variabili associano ad ogni azione il valore del relativo record memorizzato nella tabella di transizione.

Sintassi per la creazione dei trigger

Prima di mostrare un semplice esempio di trigger che ci permetterà di osservarne la corretta sintassi, è bene specificare che, a differenza di altri database, MySQL permette di definire trigger unicamente attraverso l'utilizzo di istruzioni scritte in linguaggio SQL, inoltre, non sarà possibile attivare i trigger utilizzando API che non inviino comandi SQL al DBMS.

Detto questo passiamo ora a proporre uno schema sintattico che riassuma il costrutto necessario per la generazione di un trigger:

CREATE
    [DEFINER = { nome_utente | CURRENT_USER }]
    TRIGGER nome_trigger_name momento_azione evento
    ON nome_tabella FOR EACH ROW azione

Quindi per creare un trigger attraverso il comando CREATE TRIGGER è necessario indicare il suo nome, il momento in cui dovrà essere compiuta l'azione prevista (BEFORE o AFTER), l'evento che dovrà scatenare l'azione (un'istruzione INSERT, UPDATE o DELETE), il nome della tabella target, la tipologia di trigger (a livello di record o di istruzione) e l'azione da compire in caso di invocazione.

L'istruzione iniziale, DEFINER, determina quali privilegi dovranno essere applicati nel caso in cui il trigger venga invocato; essa specifica l'account dello user associato al trigger, se il trigger viene attivato saranno controllati i privilegi del relativo account; esso dovrà infatti possedere:

• Privilegi come superutente
• Il privilegio per effettuare SELECT sulla tabella target (quando richiesto in istruzione)
• Il privilegio per effettuare UPDATE sulla tabella target (quando richiesto in istruzione)
• I valori relativi alle tabelle
• I privilegi necessari per l'esecuzione dell'istruzione SQL

Per quanto riguarda invece i tre possibili eventi in grado di generare l'azione prevista, abbiamo già visto che questi possono essere di tre tipi:

1. Istruzione INSERT: in questo primo caso il trigger viene attivato nel momento in cui un nuovo record è inserito all'interno della tabella target; ciò può accadere, oltre che con INSERT, con i comandi LOAD DATA e REPLACE
2. Istruzione UPDATE: in questo secondo caso il trigger viene attivato se un record della tabella target subisce una modifica, per cui l'unico comando previsto è UPDATE
3. Istruzione DELETE: ultimo caso in cui il trigger viene attivato per la richiesta di rimozione di un record dalla tabella target, ciò può accadere con DELETE ma anche con il comando REPLACE

Per quanto riguarda invece l'azione da compiersi in reazione all'evento, questa può essere espressa anche sotto forma di stored procedure e quindi può essere inscritta all'interno dei delimitatori BEGIN ed END; si tenga però conto che:

• Attraverso un trigger non è possibile invocare procedure per la restituzione di dati all'utente o che includono SQL dinamico;
• Non è possibile invocare tramite un trigger delle procedure che iniziano o terminano una transazione, ad esempio in presenza dei comandi START TRANSACTION, END TRANSACTION, COMMIT o ROLLBACK.

Creazione e cancellazione di un trigger

Nonostante il gran numero di elementi compositivi, di proprietà ed eccezioni precedentemente esposte, è bene sottolineare che la creazione di un trigger è una procedura abbastanza semplice; osserviamo questo breve esempio che prevede la creazione di una tabella e la successiva introduzione di un trigger:

mysql> CREATE TABLE personale (codice INT, paga DECIMAL(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER test_sum BEFORE INSERT ON personale
-> FOR EACH ROW SET @sum = @sum + NEW.paga;
Query OK, 0 rows affected (0.03 sec)

Nell'esempio abbiamo creato una semplice tabella denominata personale in cui inserire il codice identificativo di ogni impiegato e la relativa paga; per quanto riguarda invece il secondo gruppo di istruzioni, questo è stato necessario per la creazione di un INSERT trigger con il compito di sommare i valori nella colonna paga.

Come è semplice notare, per l'accesso alle colonne presenti nella tabella target vengono utilizzati due alias denominati OLD e NEW che possono essere specificati nei due modi seguenti:

• OLD.nome_colonna (in modalità sola lettura) che contiene il riferimento alla colonna della tabella target nello stato precedente alla modifica effettuata dall'evento previsto per l'invocazione del trigger.
• NEW.nome_tabella che si riferisce alla colonna della tabella target così come di presenta successivamente alla modifica determinata dall'evento.

In un INSERT trigger, potrà essere utilizzata soltanto la variabile NEW, in un DELETE trigger potremo avvalerci soltanto della variabile OLD, infine, in un BEFORE trigger, sarà possibile usare il comando SET NEW.nome_colonna = valore, per effettuare modifiche o inserire un nuovo valore all'interno di un record.

Durante le procedure di creazione, è bene tenere conto del fatto che in MySQL non si possono creare due trigger che prevedono il medesimo evento su uno stesso target; non sarà quindi possibile avere due BEFORE INSERT triggers o due AFTER UPDATE triggers per la medesima tabella.

I trigger integrano poi un semplice ma efficace sistema per la gestione delle eccezioni, per cui:

• Nel caso in cui un BEFORE trigger dovesse fallire l'operazione causa dell'evento di attivazione, non potrà essere eseguita
• Un AFTER trigger viene eseguito soltanto nel caso in cui sono stati eseguiti precedentemente i BEFORE trigger
• Se si verifica un errore durante un BEFORE o un AFTER trigger fallirà anche l'intera procedura che ha attivato il trigger

Se si è in grado di impersonare un superutente, sarà possibile cancellare in ogni momento un trigger con una semplice istruzione basata sul comando DROP TRIGGER:

mysql> DROP TRIGGER nome_trigger ON nome_tabella

Cercare di cancellare un trigger inesistente determinerebbe la notifica di un errore da parte del DBMS, è quindi buona norma introdurre all'interno dell'istruzione la clausola IF EXISTS.

Conclusioni

In questa nostra breve trattazione abbiamo introdotto il discorso riguardante i trigger in MySQL descrivendone le caratteristiche, la sintassi, le eccezioni e le limitazioni nonché le procedure necessarie per le operazioni di creazione e di cancellazione. Prossimamente presenteremo qualche esempio completo per l'utilizzo in ambito pratico di questa grande novità introdotta in MySQL con l'avvento della versione 5.