Le stored functions sono simili alle stored procedures, ma hanno uno scopo più semplice, cioè quello di definire vere e proprie funzioni, come quelle già fornite da MySQL. Esse restituiscono un valore, e non possono quindi restituire resultset, al contrario delle stored procedures. Nelle versioni di MySQL precedenti alla 5.0 esistevano le “user-defined functions”, che venivano memorizzate esternamente al server. Ora queste funzioni sono ancora supportate, ma è sicuramente consigliabile utilizzare le nuove stored functions.
Vediamo come crearle:
CREATE FUNCTION nome ([parametro[,…]]) RETURNS tipo
[SQL SECURITY { DEFINER | INVOKER }] corpo //parametri:
nomeParametro tipo
Rispetto alle stored procedures, vediamo che si aggiunge la clausola RETURNS che specifica che tipo di dato la funzione restituisce. Inoltre nella lista dei parametri non esiste distinzione fra input e output, in quanto i parametri sono solo in input.
Esistono poi le istruzioni ALTER FUNCTION e CREATE FUNCTION che sono analoghe alle corrispondenti relative alle procedure. Anche le considerazioni sulla sicurezza sono le stesse relative alle stored procedures. Il codice contenuto può essere lo stesso, con la differenza che, come abbiamo già detto, non sono previsti parametri in output e non è possibile restituire un resultset. é invece obbligatorio restituire un dato del tipo indicato nella clausola RETURNS, attraverso l’istruzione RETURN valore.
Per informazioni più approfondite su stored procedures e stored functions vi rimandiamo al manuale ufficiale di MySQL.
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:
Fedora 24 beta entra nella fase di freeze
Il ciclo di sviluppo di Fedora 24 procede in modo rapido e senza intoppi e il team della distribuzione, tramite un annuncio pubblicato da Dennis Gilmore nella mailinglist del pr...
Promoted by Prime Real Time 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.