Introduzione
I trigger MySQL applicano restrizioni alle tabelle durante l'aggiunta, l'aggiornamento o la rimozione di righe di tabella.
Le colonne in MySQL applicano una leggera quantità di limitazioni di valore. Ad esempio, impostando un tipo di dati di colonna come tiny int e non nullo richiede un input di valore numerico piccolo. Tuttavia, sono necessarie ulteriori restrizioni per mantenere l'integrità dei dati.
Questo tutorial mostra come utilizzare i trigger MySQL e fornisce esempi per ogni tipo di trigger.
Prerequisiti
- Un sistema che esegue MySQL su un server di database
- Account utente MySQL con privilegi di root
- Conoscenza dei comandi MySQL di base (fare riferimento al nostro cheat sheet dei comandi MySQL scaricabile)
Cos'è un trigger in MySQL?
Un trigger è un oggetto MySQL denominato che si attiva quando si verifica un evento in una tabella. I trigger sono un particolare tipo di stored procedure associata a una tabella specifica.
I trigger consentono l'accesso ai valori della tabella a scopo di confronto utilizzando NEW e OLD . La disponibilità dei modificatori dipende dall'evento trigger che utilizzi:
| Evento trigger | VECCHIA | NOVITÀ |
| INSERIRE | No | Sì |
| AGGIORNAMENTO | Sì | Sì |
| ELIMINA | Sì | No |
Il controllo o la modifica di un valore durante il tentativo di inserimento dei dati rende il NEW.<column name> modificatore disponibile. Questo perché una tabella viene aggiornata con nuovi contenuti. Al contrario, un OLD.<column name> value non esiste per un'istruzione di inserimento perché non esistono informazioni al suo posto in anticipo.
Quando si aggiorna una riga di tabella, sono disponibili entrambi i modificatori. C'è OLD.<column name> dati che vogliamo aggiornare a NEW.<column name> dati.
Infine, quando si rimuove una riga di dati, il OLD.<column name> modificatore accede al valore rimosso. Il NEW.<column name> non esiste perché nulla sostituisce il vecchio valore al momento della rimozione.
Esempio di trigger MySQL
Come esempio di trigger applicato, inserimento di nuovi valori nella tabella person restituisce un risultato diverso rispetto all'input originale:
Si noti che i nomi inseriti erano inizialmente minuscoli. Quando si seleziona la tabella, la prima lettera viene visualizzata in maiuscolo. Anche se non vi è alcuna indicazione di qualcosa di diverso da una normale istruzione insert, il trigger si è attivato prima dell'istruzione insert per mettere in maiuscolo la prima lettera del nome.
Utilizzo dei trigger MySQL
Ogni trigger associato a una tabella ha un nome e una funzione univoci basati su due fattori:
1. Tempo . BEFORE o AFTER un evento di riga specifico.
2. Evento . INSERT , UPDATE o DELETE .
I trigger MySQL si attivano a seconda del tempo di attivazione e dell'evento per un totale di sei combinazioni di trigger univoche. Le istruzioni before aiutano a controllare i dati e ad apportare modifiche prima di prendere impegni, mentre le istruzioni after prima salvano i dati e poi eseguono le istruzioni.
L'esecuzione di una serie di azioni avviene automaticamente, interessando tutte le righe inserite, eliminate o aggiornate nell'istruzione.
Crea trigger
Usa il CREATE TRIGGER sintassi dell'istruzione per creare un nuovo trigger:
CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
<trigger body>;La procedura consigliata è nominare il trigger con le seguenti informazioni:
<trigger time>_<table name>_<trigger event>Ad esempio, se un trigger si attiva prima dell'inserimento su un tavolo denominato dipendente , la migliore convenzione è chiamare il trigger:
before_employee_insertIn alternativa, una pratica comune consiste nell'utilizzare il seguente formato:
<table name>_<first letter of trigger time><first letter of trigger name>Il prima di inserire nome dell'attivatore per la tabella dipendente assomiglia a questo:
employee_bi
Il trigger viene eseguito in un momento specifico di un evento su una tabella definita da
Per eliminare un attivatore, utilizza il
In alternativa, usa:
Il messaggio di errore non viene visualizzato perché non è presente alcun trigger, quindi non viene stampato alcun avviso.
Creare un database per i codici di esempio del trigger con la seguente struttura:
1. Crea una tabella chiamata person con nome e età per le colonne.
Inserisci i dati di esempio nella tabella:
Seleziona la tabella per vedere il risultato:
2. Crea una tabella chiamata media_age con una colonna denominata media :
Inserisci il valore dell'età media nella tabella:
Seleziona la tabella per vedere il risultato:
3. Crea una tabella chiamata person_archive con nome , età e ora colonne:
Per creare un
Il
Esempio di trigger PRIMA DI INSERIRE
Crea un
L'inserimento dei dati attiva il trigger e verifica il valore di età prima di confermare le informazioni:
La console visualizza il messaggio di errore descrittivo. I dati non vengono inseriti nella tabella a causa del controllo del trigger fallito.
Crea un
Il
Esempio di trigger DOPO INSERTO
Inserimento di una nuova riga nella persona la tabella non aggiorna automaticamente la media nella media_age tavolo. Crea un
Inserimento di una nuova riga nella persona tabella attiva il trigger:
I dati si impegnano correttamente con la persona tabella e aggiorna la media_age tabella con il valore medio corretto.
Crea un
Il
Esempio di trigger PRIMA DELL'AGGIORNAMENTO
Se esiste un limite di età per la persona tabella prima di inserire i dati, il limite di età dovrebbe esistere anche prima di aggiornare le informazioni. Senza il
Aggiungi un
L'aggiornamento di un valore esistente attiva il controllo del trigger:
Aggiornamento dell'età a un valore inferiore a 18 visualizza il messaggio di errore e le informazioni non vengono aggiornate.
Utilizza il seguente blocco di codice per creare un
Il
Esempio di trigger DOPO L'AGGIORNAMENTO
Qualsiasi aggiornamento riuscito all'età dati nella tabella persona dovrebbe anche aggiornare il valore medio intermedio calcolato nella media_age tabella.
Crea un
L'aggiornamento dei dati esistenti modifica il valore nella persona tabella:
Aggiornamento della tabella persona aggiorna anche la media in average_age tabella.
Per creare un
Il
Esempio di trigger PRIMA DI ELIMINARE
Archivia i dati eliminati creando un
Eliminazione dei dati dalla tabella person archivia i dati nel archivio_persona tabella prima di eliminare:
Reinserire il valore nella persona la tabella conserva il registro dei dati eliminati nell'archivio_persona tabella:
Il
Crea un
Il
Esempio di trigger DOPO ELIMINA
Crea un
Eliminazione di un record dalla tabella person aggiorna la media_age tabella con la nuova media:
Senza il
MySQL non supporta l'attivazione simultanea di più trigger. Tuttavia, è possibile aggiungere più operazioni logiche allo stesso trigger. Usa il
Assicurati di modificare il delimitatore predefinito prima di creare un trigger con più operazioni.
Elenca tutti i trigger in un database con:
L'output mostra un elenco di tutti i trigger, inclusi il nome e il contenuto dell'istruzione:
Vengono visualizzate anche altre informazioni, come l'ora di creazione e l'utente che ha creato il trigger.
Come utilizzare le funzioni di stringa MySQL
Come installare e utilizzare MySQL Workbench su Ubuntu 18.04
Come installare MySQL su CentOS 8
Come usare il pannello Webuzo!!!
Come installare e utilizzare SQLite su Ubuntu 20.04
Come utilizzare lo strumento MySQL Optimize
per ogni riga interessata dalla funzione.
Elimina trigger
DROP TRIGGER dichiarazione:DROP TRIGGER <trigger name>;
DROP TRIGGER IF EXISTS <trigger name>;
Crea un database di esempio
CREATE TABLE person (name varchar(45), age int);INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);SELECT * FROM person;
CREATE TABLE average_age (average double);INSERT INTO average_age SELECT AVG(age) FROM person;SELECT * FROM average_age;
CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());
Crea un trigger PRIMA INSERT
BEFORE INSERT attivare, utilizzare:CREATE TRIGGER <trigger name> BEFORE INSERT
ON <table name>
FOR EACH ROW
<trigger body>;BEFORE INSERT trigger fornisce il controllo sulla modifica dei dati prima di eseguire il commit in una tabella del database. Scrivere in maiuscolo i nomi per coerenza, controllare la lunghezza di un input o rilevare input errati con BEFORE INSERT triggers fornisce inoltre limitazioni di valore prima di inserire nuovi dati.BEFORE INSERT trigger per controllare il valore dell'età prima di inserire i dati nella persona tabella:delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;
INSERT INTO person VALUES ('John', 14);
Crea un trigger DOPO INSERTO
AFTER INSERT attivare con:CREATE TRIGGER <trigger name> AFTER INSERT
ON <table name>
FOR EACH ROW
<trigger body>;AFTER INSERT trigger è utile quando la riga inserita genera un valore necessario per aggiornare un'altra tabella.AFTER INSERT trigger sulla persona tabella per aggiornare la media_age tabella dopo l'inserimento:delimiter //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;
INSERT INTO person VALUES ('John', 19);
Crea un trigger PRIMA DELL'AGGIORNAMENTO
BEFORE UPDATE attivare con:CREATE TRIGGER <trigger name> BEFORE UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;BEFORE UPDATE i trigger vanno insieme al BEFORE INSERT trigger. Se esistono restrizioni prima dell'inserimento dei dati, i limiti dovrebbero essere presenti anche prima dell'aggiornamento.BEFORE UPDATE trigger, il trigger del controllo dell'età è facile da evitare. Niente limita la modifica a un valore errato.BEFORE UPDATE trigger alla persona tabella con lo stesso corpo del BEFORE INSERT trigger:delimiter //
CREATE TRIGGER person_bu BEFORE UPDATE
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;
UPDATE person SET age = 17 WHERE name = 'John';
Crea un trigger DOPO AGGIORNAMENTO
AFTER UPDATE trigger:CREATE TRIGGER <trigger name> AFTER UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;AFTER UPDATE trigger aiuta a tenere traccia delle modifiche apportate ai dati. Molto spesso, qualsiasi modifica dopo l'inserimento delle informazioni avviene anche dopo l'aggiornamento dei dati.AFTER UPDATE trigger per aggiornare la media_age tabella dopo aver aggiornato una riga nella person tabella:delimiter //
CREATE TRIGGER person_au AFTER UPDATE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;
UPDATE person SET age = 21 WHERE name = 'John';
Crea un trigger PRIMA DELETE
BEFORE DELETE attivare, utilizzare:CREATE TRIGGER <trigger name> BEFORE DELETE
ON <table name>
FOR EACH ROW
<trigger body>;BEFORE DELETE trigger è essenziale per motivi di sicurezza. Se a una tabella padre sono allegati elementi secondari, il trigger aiuta a bloccare l'eliminazione e impedisce le tabelle orfane. Il trigger consente anche di archiviare i dati prima dell'eliminazione.BEFORE DELETE trigger sul tavolo persona e inserisci i valori nell'archivio_persona tabella:delimiter //
CREATE TRIGGER person_bd BEFORE DELETE
ON person
FOR EACH ROW
INSERT INTO person_archive (name, age)
VALUES (OLD.name, OLD.age); //
delimiter ;
DELETE FROM person WHERE name = 'John';
INSERT INTO person VALUES ('John', 21);
BEFORE DELETE trigger è utile per registrare eventuali tentativi di modifica della tabella.Crea un trigger DOPO ELIMINA
AFTER DELETE attivare con:CREATE TRIGGER <trigger name> AFTER DELETE
ON <table name>
FOR EACH ROW
<trigger body>;AFTER DELETE i trigger mantengono gli aggiornamenti delle informazioni che richiedono la scomparsa della riga di dati prima di eseguire gli aggiornamenti.AFTER DELETE trigger sul tavolo persona per aggiornare la media_age tabella con le nuove informazioni:delimiter //
CREATE TRIGGER person_ad AFTER DELETE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //
delimiter ;
AFTER DELETE trigger, le informazioni non si aggiornano automaticamente.Crea più trigger
BEGIN e END delimitatori per indicare il corpo del trigger:CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
BEGIN
<trigger body>;
END;Mostra trigger
SHOW triggers;