Come utilizzare i trigger in MySQL

L'autore ha selezionato il programma Write for DOnations.

introduzione

Quando si lavora con database relazionali e UPDATE.

Tuttavia, i database SQL possono anche essere istruiti in modo che eseguano azioni predefinite automaticamente ogni volta che si verifica un evento specifico tramite trigger. Ad esempio, puoi utilizzare i trigger per conservare il registro dell'audit trail di tutte le istruzioni DELETE o aggiornare automaticamente i riepiloghi statistici aggregati ogni volta che le righe vengono aggiornate o aggiunte alla tabella.

In questo tutorial, utilizzerai diversi trigger SQL per eseguire automaticamente azioni in cui le righe vengono inserite, aggiornate o eliminate.

Prerequisiti

Per seguire questa guida, avrai bisogno di un computer che esegue un sistema di gestione di database relazionali basato su SQL (RDBMS). Le istruzioni e gli esempi in questa guida sono stati convalidati utilizzando il seguente ambiente:

• Un server che esegue Ubuntu 20.04, con un utente non root con privilegi amministrativi e un firewall configurato con UFW, come descritto nella nostra guida alla configurazione iniziale del server per Ubuntu 20.04.
• MySQL installato e protetto sul server, come descritto nel passaggio 3.
• Familiarità di base con l'esecuzione di query SELECT, INSERT, UPDATE e DELETE per manipolare i dati nel database come descritto nelle nostre guide Come eliminare i dati in SQL.
• Familiarità di base con l'utilizzo delle query nidificate come descritto nella nostra guida Come utilizzare le query nidificate in SQL.
• Familiarità di base con l'utilizzo di funzioni matematiche aggregate come descritto nella nostra guida Come utilizzare espressioni matematiche e funzioni aggregate in SQL.

Nota: molti RDBMS utilizzano la propria implementazione di SQL. Sebbene i trigger siano menzionati come parte dello standard SQL, lo standard non impone la loro sintassi o il modo rigoroso di implementarli. Di conseguenza, la loro implementazione differisce tra diversi database. I comandi descritti in questo tutorial utilizzano la sintassi per il database MySQL e potrebbero non funzionare su altri motori di database.

Avrai anche bisogno di un database con alcune tabelle caricate con dati di esempio in modo che tu possa esercitarti nell'uso delle funzioni. Ti invitiamo a consultare la seguente sezione Connessione a MySQL e configurazione di un database di esempio per i dettagli sulla connessione a un server MySQL e sulla creazione del database di test utilizzato negli esempi di questa guida.

Connessione a MySQL e impostazione di un database di esempio

In questa sezione, ti collegherai a un server MySQL e creerai un database di esempio in modo da poter seguire gli esempi nelle sezioni seguenti.

Per questa guida, utilizzerai una collezione immaginaria di oggetti da collezione. Archivierai i dettagli sugli oggetti da collezione attualmente in tuo possesso, manterrai il loro valore totale prontamente disponibile e ti assicurerai che la rimozione di un oggetto da collezione lasci sempre una traccia.

Se il tuo sistema di database SQL viene eseguito su un server remoto, SSH nel tuo server dalla tua macchina locale:

ssh sammy@your_server_ip

Quindi apri il prompt del server MySQL, sostituendo sammy con il nome del tuo account utente MySQL:

mysql -u sammy -p

Crea un database chiamato collectibles:

CREATE DATABASE collectibles;

Se il database è stato creato correttamente, riceverai un output come questo:

Output
Query OK, 1 row affected (0.01 sec)

Per selezionare il database collectibles, eseguire la seguente istruzione USE:

USE collectibles;

Riceverai il seguente output:

Output
Database changed

Dopo aver selezionato il database, puoi creare tabelle di esempio al suo interno. La tabella oggetti da collezione conterrà dati semplificati sugli oggetti da collezione nel database. Conterrà le seguenti colonne:

• name: questa colonna contiene il nome di ogni collezionabile, espresso utilizzando il tipo di dati varchar con un massimo di 50 caratteri. Li>
• valore: questa colonna memorizza il valore di mercato dell'oggetto da collezione utilizzando il tipo di dati decimal con un massimo di 5 valori prima della virgola decimale e 2 valori dopo di esso.

Crea la tabella di esempio con il seguente comando:

CREATE TABLE collectibles (
    name varchar(50),
    value decimal(5, 2)
);

Se viene stampato il seguente output, la tabella è stata creata:

Output
Query OK, 0 rows affected (0.00 sec)

La tabella successiva si chiamerà collectibles_stats e verrà utilizzata per tenere traccia del valore accumulato di tutti i collezionabili della collezione. Conterrà una singola riga di dati con le seguenti colonne:

• count: questa colonna contiene il numero di oggetti collezionabili posseduti, espresso utilizzando il tipo di dati int.
• valore: questa colonna memorizza il valore accumulato di tutti i collezionabili utilizzando il tipo di dati decimal con un massimo di 5 valori prima della virgola decimale e 2 valori dopo di esso.

Crea la tabella di esempio con il seguente comando:

CREATE TABLE collectibles_stats (
    count int,
    value decimal(5, 2)
);

Se viene stampato il seguente output, la tabella è stata creata:

Output
Query OK, 0 rows affected (0.00 sec)

La terza e ultima tabella si chiamerà collectibles_archive, che terrà traccia di tutti i collezionabili che sono stati rimossi dalla collezione per assicurarsi che non svaniscano mai. Conterrà dati simili alla tabella collectibles, ampliati con la data di rimozione. Utilizza le seguenti colonne:

• name: questa colonna contiene il nome di ogni collezionabile rimosso, espresso utilizzando il tipo di dati varchar con un massimo di 50 caratteri.< /li>
• value: questa colonna memorizza il valore di mercato dell'oggetto da collezione al momento dell'eliminazione utilizzando il tipo di dati decimal con un massimo di 5 valori prima il punto decimale e i valori 2 dopo di esso.
• removed_on: questa colonna memorizza la data e l'ora di eliminazione per ogni collezionabile archiviato utilizzando il tipo di dati timestamp con il valore predefinito di NOW(), ovvero la data corrente ogni volta che viene inserita una nuova riga in questa tabella.

Crea la tabella di esempio con il seguente comando:

CREATE TABLE collectibles_archive (
    name varchar(50),
    value decimal(5, 2),
    removed_on timestamp DEFAULT CURRENT_TIMESTAMP
);

Se viene stampato il seguente output, la tabella è stata creata:

Output
Query OK, 0 rows affected (0.00 sec)

Successivamente, carica la tabella collectibles_stats con lo stato iniziale per la raccolta di oggetti da collezione vuota eseguendo la seguente operazione INSERT INTO:

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

L'operazione INSERT INTO aggiungerà una singola riga a collectibles_stats con i valori calcolati utilizzando le funzioni di aggregazione per contare tutte le righe nella tabella collectibles e per sommare il valore di tutti i collezionabili usando la colonna value e la funzione SUM. Il seguente output indica che la riga è stata aggiunta:

Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

Puoi verificarlo eseguendo un'istruzione SELECT sulla tabella:

SELECT * FROM collectibles_stats;

Poiché non ci sono ancora collezionabili nel database, il numero iniziale di elementi è 0 e il valore accumulato dice NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Detto questo, sei pronto per seguire il resto della guida e iniziare a utilizzare i trigger in MySQL.

Comprensione dei trigger

I trigger sono istruzioni definite per una particolare tabella che vengono eseguite automaticamente dal database ogni volta che si verifica un evento specifico in quella tabella. I trigger possono essere utilizzati per garantire che alcune azioni vengano eseguite in modo coerente ogni volta che viene eseguita un'istruzione specifica su una tabella, piuttosto che gli utenti del database devono ricordarsi di eseguirli manualmente.

Ogni trigger associato a una tabella viene identificato con un nome definito dall'utente e una coppia di condizioni che indicano al motore di database quando eseguire il trigger. Questi possono essere raggruppati in due classi separate:

• Evento database: il trigger può essere eseguito quando le istruzioni INSERT, UPDATE o DELETE vengono eseguite su una tabella.
• Ora dell'evento: inoltre, i trigger possono essere eseguiti PRIMA o DOPO l'istruzione in questione.

La combinazione dei due gruppi di condizioni produce un totale di sei possibilità di trigger separate che vengono eseguite automaticamente ogni volta che viene soddisfatta la condizione congiunta. I trigger che si verificano prima dell'esecuzione dell'istruzione che soddisfa la condizione sono BEFORE INSERT, BEFORE UPDATE e BEFORE DELETE. Questi possono essere utilizzati per manipolare e convalidare i dati prima che vengano inseriti o aggiornati nella tabella o per salvare i dettagli della riga eliminata per scopi di controllo o archiviazione.

I trigger che si verificano dopo l'esecuzione dell'istruzione che soddisfa la condizione sono AFTER INSERT, AFTER UPDATE e AFTER DELETE. Questi possono essere utilizzati per aggiornare i valori riepilogati in una tabella separata in base allo stato finale del database dopo l'istruzione.

Per eseguire azioni come la convalida e la manipolazione dei dati di input o l'archiviazione della riga eliminata, il database consente di accedere ai valori dei dati dall'interno dei trigger. Per i trigger INSERT, possono essere utilizzati solo i dati appena inseriti. Per i trigger UPDATE, è possibile accedere sia ai dati originali che a quelli aggiornati. Infine, con i trigger DELETE, solo i dati della riga originale sono disponibili per l'uso (poiché non ci sono nuovi dati a cui fare riferimento).

I dati da utilizzare all'interno del corpo del trigger sono esposti nel record OLD per i dati attualmente nel database e nel record NEW per i dati che la query salverà. Puoi fare riferimento a singole colonne utilizzando la sintassi OLD.column_name e NEW.column_name.

L'esempio seguente mostra la sintassi generale di un'istruzione SQL utilizzata per creare un nuovo trigger:

CREATE TRIGGER trigger_name trigger_condition
ON table_name
FOR EACH ROW
trigger_actions;

Analizziamo la sintassi in parti più piccole:

• CREATE TRIGGER è il nome dell'istruzione SQL utilizzata per creare un nuovo trigger nel database.
• trigger_name è il nome definito dall'utente del trigger, utilizzato per descriverne il ruolo, in modo simile a come vengono utilizzati i nomi delle tabelle e dei nomi delle colonne per descriverne il significato.< /li>
• ON table_name indica al database che il trigger dovrebbe monitorare gli eventi che si verificano sulla tabella table_name.
• trigger_condition è una delle sei possibili scelte che definiscono quando il trigger deve essere eseguito, ad esempio, BEFORE INSERT.
• FOR EACH ROW indica al database che il trigger deve essere eseguito per ogni riga interessata dall'evento di attivazione. Alcuni database supportano modelli di esecuzione aggiuntivi diversi da FOR EACH ROW; tuttavia, nel caso di MySQL, eseguire le istruzioni dal corpo del trigger per ogni riga interessata dall'istruzione che ha causato l'esecuzione del trigger è l'unica opzione.
• trigger_actions è il corpo del trigger e definisce cosa succede quando il trigger viene eseguito. In genere è una singola istruzione SQL valida. È possibile includere più istruzioni nel corpo del trigger per eseguire operazioni complesse sui dati utilizzando le parole chiave BEGIN e END per racchiudere l'elenco delle istruzioni in un blocco. Questo, tuttavia, non rientra nell'ambito di questo tutorial. Consulta la documentazione ufficiale per i trigger per saperne di più sulla sintassi utilizzata per definire i trigger.

Nella sezione seguente, creerai trigger che manipolano i dati prima delle operazioni INSERT e UPDATE.

Manipolazione dei dati con i trigger BEFORE INSERT e BEFORE UPDATE

In questa sezione, utilizzerai i trigger per manipolare i dati prima che vengano eseguite le istruzioni INSERT e UPDATE.

In questo esempio, utilizzerai i trigger per assicurarti che tutti gli oggetti da collezione nel database utilizzino nomi maiuscoli per coerenza. Senza usare i trigger, dovresti ricordarti di usare nomi collezionabili in maiuscolo per ogni istruzione INSERT e UPDATE. Se dimentichi, il database salverà i dati così come sono, portando a possibili errori nel set di dati.

Inizierai inserendo un oggetto da collezione di esempio chiamato modello di astronave del valore di $12,50. Il nome dell'elemento verrà scritto in minuscolo per illustrare il problema. Esegui la seguente istruzione:

INSERT INTO collectibles VALUES ('spaceship model', 12.50);

Il seguente messaggio conferma che l'elemento è stato aggiunto:

Output
Query OK, 1 row affected (0.009 sec)

Puoi verificare che la riga sia stata inserita eseguendo la query SELECT:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

L'oggetto da collezione è stato salvato così com'è, con il nome scritto solo con lettere minuscole.

Per assicurarti che tutti i collezionabili futuri siano sempre scritti in maiuscolo, creerai un trigger BEFORE INSERT. L'utilizzo di un trigger che viene eseguito prima dell'esecuzione dell'istruzione di attivazione consente di manipolare i dati che verranno passati al database prima che ciò avvenga.

Eseguire la seguente istruzione:

CREATE TRIGGER uppercase_before_insert BEFORE INSERT
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

Questo comando crea un trigger denominato uppercase_before_insert che verrà eseguito PRIMA di tutte le istruzioni INSERT sulla tabella denominata collectibles.

L'istruzione nel trigger SET NEW.name=UPPER(NEW.name) verrà eseguita per ogni riga inserita. Il comando SQL SET assegna il valore sul lato destro al lato sinistro. In questo caso, NEW.name rappresenta il valore della colonna name che l'istruzione di inserimento salverà. Applicando la funzione MAIUSC al nome del collezionabile e riassegnandolo al valore della colonna, stai convertendo la lettera maiuscola del valore che verrà salvato nel database.

Nota: quando si esegue il comando CREATE TRIGGER, è possibile che venga visualizzato un messaggio di errore simile a ERROR 1419 (HY000): non si dispone del privilegio SUPER e la registrazione binaria è abilitata (è possibile *potrebbe * desidera utilizzare la variabile meno sicura log_bin_trust_function_creators).

A partire da MySQL 8, il motore di database MySQL ha la registrazione binaria abilitata per impostazione predefinita, a meno che la configurazione dell'installazione locale non la sovrascriva. Il registro binario tiene traccia di tutte le istruzioni SQL che modificano i contenuti del database sotto forma di eventi salvati che descrivono le modifiche. Questi registri vengono utilizzati nella replica del database per mantenere sincronizzate le repliche del database e durante il ripristino dei dati point-in-time.

Con la registrazione binaria abilitata, MySQL non consente la creazione di trigger e stored procedure come precauzione per garantire la sicurezza e l'integrità dei dati negli ambienti replicati. Comprendere in che modo i trigger e le stored procedure possono influire sulla replica non rientra nell'ambito di questa guida.

Tuttavia, in un ambiente locale ea scopo di apprendimento, è possibile ignorare in modo sicuro il modo in cui MySQL protegge dalla creazione di trigger. L'impostazione sovrascritta non viene mantenuta e tornerà al valore originale al riavvio del server MySQL.

Per sovrascrivere l'impostazione predefinita per la registrazione binaria, accedi a MySQL come root ed esegui il seguente comando:

SET GLOBAL log_bin_trust_function_creators = 1;

L'impostazione log_bin_trust_function_creators controlla se gli utenti che creano trigger e funzioni memorizzate possono essere attendibili per non creare trigger che causano la scrittura di eventi non sicuri nel log binario. Per impostazione predefinita, il valore dell'impostazione è 0, consentendo solo ai superuser di creare trigger in un ambiente con registrazione binaria abilitata. Modificando il valore in 1, qualsiasi utente che emette istruzioni CREATE TRIGGER verrà ritenuto attendibile per comprenderne le implicazioni.

Dopo aver aggiornato l'impostazione, disconnettiti come root, accedi nuovamente come utente ed esegui nuovamente l'istruzione CREATE TRIGGER.

Per saperne di più sulla registrazione binaria e sulla replica in MySQL e su come si relaziona ai trigger, ti invitiamo a fare riferimento alla documentazione ufficiale di MySQL: How to Set Up Replication in MySQL.

Prima di utilizzare i trigger in un ambiente di produzione con replica in atto o severi requisiti di ripristino point-in-time, assicurarsi di averne valutato l'impatto sulla coerenza dei log binari.

Nota: a seconda delle tue autorizzazioni utente MySQL, potresti ricevere un errore durante l'esecuzione del comando CREATE TRIGGER: ERROR 1142 (42000): comando TRIGGER negato all'utente user@host per gli oggetti da collezione. Per concedere le autorizzazioni TRIGGER al tuo utente, accedi a MySQL come root ed esegui i seguenti comandi, sostituendo il nome utente e l'host MySQL secondo necessità:

GRANT TRIGGER on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

Dopo aver aggiornato le autorizzazioni utente, disconnettiti come root, accedi nuovamente come utente ed esegui nuovamente l'istruzione CREATE TRIGGER.

MySQL stamperà il seguente messaggio per confermare che il trigger è stato creato correttamente:

Output
Query OK, 1 row affected (0.009 sec)

Ora prova a inserire un nuovo collezionabile, sempre utilizzando un argomento minuscolo nella query INSERT:

INSERT INTO collectibles VALUES ('aircraft model', 10.00);

E ancora una volta, controlla le righe risultanti nella tabella oggetti da collezione:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Questa volta, tuttavia, la nuova voce dice AIRCRAFT MODEL con tutte le lettere in maiuscolo, diverse dalla voce che hai provato a inserire. Il trigger è stato eseguito in background e ha convertito la lettera maiuscola prima che la riga fosse salvata nel database.

Tutte le nuove righe sono ora protette dal trigger per garantire che i nomi vengano salvati in maiuscolo. Tuttavia, è ancora possibile salvare dati illimitati utilizzando le istruzioni UPDATE. Per proteggere le istruzioni UPDATE con lo stesso effetto, crea un altro trigger:

CREATE TRIGGER uppercase_before_update BEFORE UPDATE
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

La differenza tra i due trigger è nei criteri di trigger. Questa volta, è BEFORE UPDATE, il che significa che il trigger verrà eseguito ogni volta che un'istruzione UPDATE viene emessa sulla tabella, influenzando le righe esistenti a ogni aggiornamento , oltre alle nuove righe coperte dal trigger precedente.

MySQL produrrà una conferma che il trigger è stato creato correttamente:

Output
Query OK, 0 row affected (0.009 sec)

Per verificare il comportamento del nuovo trigger, prova ad aggiornare il valore del prezzo per il modello di astronave:

UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

La clausola WHERE filtra la riga da aggiornare in base al nome e la clausola SET modifica il valore in 15.00.

Riceverai il seguente output, a conferma che l'istruzione ha modificato una singola riga:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Controlla le righe risultanti nella tabella oggetti da collezione:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Ora, oltre all'aggiornamento del prezzo a 15.00 dall'istruzione eseguita, il nome ora dice SPACESHIP MODEL. Quando hai eseguito l'istruzione UPDATE, il trigger è stato eseguito, influenzando i valori sulla riga aggiornata. La colonna del nome è stata convertita in maiuscolo prima del salvataggio.

In questa sezione, hai creato due trigger che funzionano prima di INSERT e prima di UPDATE interrogazioni per conformare i dati prima di salvarli nel database. Nella sezione successiva, utilizzerai i trigger BEFORE DELETE per copiare le righe eliminate in una tabella separata per l'archiviazione.

Utilizzo di BEFORE DELETE Trigger per eseguire azioni prima di eliminare le righe

Anche se non possiedi più un articolo, potresti voler lasciare una voce sull'eliminazione in una tabella separata. All'inizio di questo tutorial, hai creato una seconda tabella chiamata collectibles_archive per tenere traccia di tutti i collezionabili che sono stati rimossi dalla collezione. In questa sezione, archivierai le voci eliminate con un trigger che verrà eseguito prima delle istruzioni DELETE.

Controlla se la tabella di archivio è completamente vuota eseguendo la seguente istruzione:

SELECT * FROM collectibles_archive;

Il seguente output verrà stampato sullo schermo, confermando che la tabella collectibles_archive è vuota:

Output
Empty set (0.000 sec)

Ora, se esegui una query DELETE sulla tabella collectibles, qualsiasi riga della tabella potrebbe essere eliminata senza lasciare traccia.

Per rimediare, creerai un trigger che verrà eseguito prima di tutte le query DELETE sulla tabella collectibles. Lo scopo di questo trigger è salvare una copia dell'oggetto eliminato nella tabella di archivio prima che avvenga l'eliminazione.

Esegui il seguente comando:

CREATE TRIGGER archive_before_delete BEFORE DELETE
ON collectibles
FOR EACH ROW
INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

Il trigger è denominato archive_before_delete e si verifica PRIMA di qualsiasi query DELETE sulla tabella collectibles . Per ogni riga che verrà eliminata, verrà eseguita l'istruzione INSERT. A sua volta, l'istruzione INSERT inserisce una nuova riga nella tabella collectibles_archive con i valori dei dati presi dal record OLD, che è quello previsto per cancellazione: OLD.name diventa la colonna name e OLD.value diventa la colonna value.

Il database confermerà la creazione del trigger:

Output
Query OK, 0 row affected (0.009 sec)

Con il grilletto in posizione, prova a eliminare un collezionabile dalla tabella principale dei collezionabili:

DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

L'output conferma che la query è stata eseguita correttamente:

Output
Query OK, 1 row affected (0.004 sec)

Ora, elenca tutti i collezionabili:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

Rimane solo il MODELLO DI AEROMOBILE; il SPACESHIP MODEL è stato cancellato e non è più nella tabella. Tuttavia, con il trigger creato in precedenza, questa cancellazione dovrebbe essere registrata nella tabella collectibles_archive. Controlliamo quello.

Esegui un'altra query:

SELECT * FROM collectibles_archive;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

L'eliminazione è stata annotata automaticamente in quella tabella dal trigger. Le colonne name e value sono state riempite con i dati della riga che è stata eliminata. La terza colonna, removed_on, non è impostata esplicitamente attraverso il trigger definito, quindi prende il valore predefinito deciso durante la creazione della tabella: la data di creazione di ogni nuova riga. Per questo motivo, ogni voce aggiunta con l'aiuto del trigger sarà sempre annotata con la data di eliminazione.

Con questo attivatore in atto, ora puoi essere certo che tutte le query DELETE risulteranno in una voce di registro in collectibles_archive, lasciando dietro di sé informazioni sui collezionabili precedentemente posseduti.

Nella sezione successiva, utilizzerai i trigger eseguiti dopo le istruzioni di attivazione per aggiornare la tabella di riepilogo con valori aggregati basati su tutti gli oggetti da collezione.

Utilizzo dei trigger AFTER INSERT, AFTER UPDATE e AFTER DELETE per eseguire azioni dopo la manipolazione dei dati

In entrambe le sezioni precedenti, hai utilizzato i trigger eseguiti prima delle istruzioni principali per eseguire operazioni basate sui dati originali prima di aggiornare il database. In questa sezione, aggiornerai la tabella di riepilogo con un conteggio sempre aggiornato e il valore accumulato di tutti i collezionabili utilizzando i trigger che vengono eseguiti dopo le dichiarazioni previste. In questo modo sarai sicuro che i dati della tabella di riepilogo tengano conto dello stato attuale del database.

Inizia esaminando la tabella collectibles_stats:

SELECT * FROM collectibles_stats;

Poiché non hai ancora aggiunto informazioni a questa tabella, il numero di oggetti collezionabili posseduti è 0 e, quindi, il valore accumulato è NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Poiché non ci sono trigger per questa tabella, le query precedentemente emesse per inserire e aggiornare i collezionabili non hanno influenzato questa tabella.

L'obiettivo è impostare i valori in una singola riga nella tabella collectibles_stats per presentare informazioni aggiornate sul numero di oggetti da collezione e sul valore totale. Vuoi assicurarti che i contenuti della tabella vengano aggiornati dopo ogni operazione INSERT, UPDATE o DELETE.

Puoi farlo creando tre trigger separati, tutti eseguiti dopo la query corrispondente. Innanzitutto, crea il trigger AFTER INSERT:

CREATE TRIGGER stats_after_insert AFTER INSERT
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

Il trigger è denominato stats_after_insert ed eseguirà AFTER ogni query INSERT ai collectibles table, eseguendo l'istruzione UPDATE nel corpo del trigger. La query UPDATE influisce su collectibles_stats e imposta le colonne count e value sui valori restituiti dalle query nidificate:

• SELECT COUNT(name) FROM collectibles otterrà il conteggio dei collezionabili.
• SELECT SUM(value) FROM collectibles otterrà il valore totale di tutti i collezionabili.

Il database confermerà la creazione del trigger:

Output
Query OK, 0 row affected (0.009 sec)

Ora, prova a reinserire il modello di astronave precedentemente eliminato nella tabella dei collezionabili per verificare se la tabella di riepilogo verrà aggiornata correttamente:

INSERT INTO collectibles VALUES ('spaceship model', 15.00);

Il database stamperà il seguente messaggio di successo:

Output
Query OK, 1 row affected (0.009 sec)

Puoi elencare tutti i collezionabili posseduti eseguendo:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Ci sono due oggetti collezionabili per un valore totale di 25,00. Per esaminare la tabella di riepilogo dopo l'elemento appena inserito, eseguire la seguente query:

SELECT * FROM collectibles_stats;

Questa volta, la tabella di riepilogo elencherà il numero di tutti gli oggetti collezionabili posseduti come 2 e il valore accumulato come 25.00, che corrisponde all'output precedente:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

Il trigger stats_after_insert viene eseguito dopo la query INSERT e aggiorna la tabella collectibles_stats con i dati correnti (count e value) sulla raccolta. Vengono raccolte statistiche sull'intero contenuto della raccolta, non solo sull'ultimo inserto. Poiché la raccolta ora contiene due elementi (modelli di aeroplano e astronave), la tabella di riepilogo elenca due elementi e il loro valore sommato. A questo punto, l'aggiunta di qualsiasi nuovo elemento collezionabile alla tabella dei collezionabili aggiornerà la tabella di riepilogo con i valori corretti.

Tuttavia, l'aggiornamento di elementi esistenti o l'eliminazione di oggetti da collezione non influirà affatto sul riepilogo. Per colmare questa lacuna, creerai due trigger aggiuntivi, che eseguono operazioni identiche ma attivati da eventi diversi:

CREATE TRIGGER stats_after_update AFTER UPDATE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

CREATE TRIGGER stats_after_delete AFTER DELETE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

Ora hai creato due nuovi trigger: stats_after_update e stats_after_delete. Entrambi i trigger verranno eseguiti sulla tabella collectible_stats ogni volta che esegui un'istruzione UPDATE o DELETE sulla tabella collectibles.

La corretta creazione di questi trigger stamperà il seguente output:

Output
Query OK, 0 row affected (0.009 sec)

Ora, aggiorna il valore del prezzo per uno dei collezionabili:

UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

La clausola WHERE filtra la riga da aggiornare in base al nome e la clausola SET cambia il valore in 25.00.

L'output conferma che l'istruzione ha modificato una singola riga:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Ancora una volta, controlla il contenuto della tabella di riepilogo dopo l'aggiornamento:

SELECT * FROM collectibles_stats;

Il valore ora elenca 40.00, che è il valore corretto dopo l'aggiornamento:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

L'ultimo passaggio consiste nel verificare che la tabella di riepilogo rispecchi correttamente l'eliminazione di un oggetto da collezione. Prova a eliminare il modello di aeromobile con la seguente istruzione:

DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

Il seguente output conferma che la query è stata eseguita correttamente:

Output
Query OK, 1 row affected (0.004 sec)

Ora, elenca tutti i collezionabili:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

Rimane solo il MODELLO DI NAVE SPAZIALE. Quindi, controlla i valori nella tabella di riepilogo:

SELECT * FROM collectibles_stats;

Verrà stampato il seguente output:

Output
+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

La colonna count ora mostra solo un collezionabile nella tabella principale. Il valore totale è 15.00, corrispondente al valore di SPACESHIP MODEL.

Questi tre trigger funzionano congiuntamente dopo le query INSERT, UPDATE e DELETE per mantenere sincronizzata la tabella di riepilogo con l'elenco completo dei collezionabili.

Nella sezione successiva imparerai come manipolare i trigger esistenti nel database.

Elenco ed eliminazione dei trigger

Nelle sezioni precedenti, hai creato nuovi trigger. Poiché i trigger sono oggetti denominati definiti nel database, proprio come le tabelle, puoi anche elencarli e manipolarli quando necessario.

Per elencare tutti i trigger, esegui l'istruzione SHOW TRIGGERS:

SHOW TRIGGERS;

L'output includerà tutti i trigger, compresi i loro nomi, l'evento di attivazione con il tempo (esecuzione dell'istruzione BEFORE o AFTER), così come le istruzioni che fanno parte del corpo del trigger e altri dettagli estesi della definizione di trigger:

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

Per eliminare i trigger esistenti, puoi utilizzare le istruzioni SQL DROP TRIGGER. Forse non vuoi più imporre lettere maiuscole per i nomi collezionabili, quindi uppercase_before_insert e uppercase_before_update non sono più necessari. Esegui i seguenti comandi per rimuovere questi due trigger:

DROP TRIGGER uppercase_before_insert;
DROP TRIGGER uppercase_before_update;

Per entrambi i comandi, MySQL risponderà con un messaggio di successo:

Output
Query OK, 0 rows affected (0.004 sec)

Ora, eliminati i due trigger, aggiungiamo un nuovo collezionabile in minuscolo:

INSERT INTO collectibles VALUES ('ship model', 10.00);

Il database confermerà l'inserimento:

Output
Query OK, 1 row affected (0.009 sec)

Puoi verificare che la riga sia stata inserita eseguendo la query SELECT:

SELECT * FROM collectibles;

Il seguente output verrà stampato sullo schermo:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Il collezionabile appena aggiunto è in lettere minuscole. Poiché il nome non è stato modificato rispetto all'output originale, hai verificato che il trigger che in precedenza convertiva le lettere maiuscole non è più in uso.

Ora sai come elencare ed eliminare i trigger per nome.

Conclusione

Seguendo questa guida, hai imparato cosa sono i trigger SQL e come usarli in MySQL per manipolare i dati prima delle query INSERT e UPDATE. Hai imparato come utilizzare il trigger BEFORE DELETE per archiviare la riga eliminata in una tabella separata, nonché come utilizzare i trigger dell'istruzione AFTER per mantenere costantemente aggiornati i riepiloghi.

È possibile utilizzare le funzioni per scaricare parte della manipolazione e della convalida dei dati sul motore di database, garantendo l'integrità dei dati o nascondendo alcuni dei comportamenti del database all'utente quotidiano del database. Questo tutorial copriva solo le basi dell'utilizzo dei trigger a tale scopo. Puoi creare trigger complessi costituiti da più istruzioni e utilizzare la logica condizionale per eseguire azioni in modo ancora più granulare. Per saperne di più, consulta la documentazione di MySQL sui trigger.

Se desideri saperne di più sui diversi concetti relativi al linguaggio SQL e lavorare con esso, ti invitiamo a consultare le altre guide nella serie How To Use SQL.