I trigger sono entità di database in SQL Server. Tecnicamente, sono una particolare classe di chiamate di funzione che risponde a specifiche operazioni del database.
Questa guida essenziale ti fornirà informazioni approfondite sui trigger SQL che possono essere molto utili nella tua professione. Iniziamo!
Sommario:
Cosa sono i trigger SQL?
La parola “trigger” descrive un’affermazione secondo cui un server esegue automaticamente la query ogni volta che il contenuto del database viene modificato.
Un trigger è un gruppo di query SQL con nome speciale che vengono archiviate nello spazio di memoria. È un tipo specifico di chiamata di funzione che viene richiamata immediatamente ogni volta che si verifica un evento del database. Ad ogni trigger è assegnata una tabella.
Ad esempio, un trigger potrebbe essere attivato ogni volta che viene aggiunta una nuova colonna a una tabella particolare o se vengono modificati record specifici.
Secondo Microsoft Developer Network, i trigger sono una classe particolare di stored procedure. In un’istruzione trigger, inizialmente definiamo quando il trigger deve essere eseguito, quindi forniamo l’azione da intraprendere dopo che il trigger è stato attivato.
Sintassi:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Spiegazione di ogni parametro
- CREATE TRIGGER trigger_name – Viene utilizzato per costruire un trigger o per modificare il nome di un trigger esistente.
- PRIMA/DOPO – Questa query viene utilizzata per definire il tempo di esecuzione del trigger (prima o dopo un determinato evento).
- INSERT/UPDATE/DELETE – Descrive l’azione che desideriamo eseguire sui tavoli.
- ON tableName – Qui definiamo il nome della tabella per impostare un trigger.
- PER OGNI RIGA – Questa istruzione si riferisce al trigger di riga, il che significa che i trigger verranno eseguiti ogni volta che una riga viene modificata.
- trigger_body – Specifica l’azione da intraprendere quando viene attivato il trigger.
I trigger sono funzioni memorizzate con identità distintive che ci consentono di riutilizzare le query che sono già state eseguite e archiviate in modo sicuro nella memoria. Ora proviamo a capire perché SQL ne ha bisogno.
I trigger vengono utilizzati principalmente per regolare la distribuzione del codice ogni volta che si verifica un evento. Per dirla in altro modo, l’uso dei trigger è la scelta ideale se è necessario eseguire costantemente un frammento di codice specifico in risposta a un evento specifico.
Di seguito sono riportati alcuni vantaggi dell’utilizzo dei trigger nelle operazioni del database SQL.
L’integrità referenziale è una proprietà fondamentale dei sistemi di database relazionali. Ciò significa che i dati conservati nel sistema di database devono essere sempre accurati per ogni transazione e ogni operazione.
Se due tabelle si trovano su database o sistemi separati, non esiste un modo per garantire la convalida dei dati al loro interno utilizzando valori di vincolo. In una situazione del genere, i trigger sono l’unica opzione per l’esecuzione.
Combinazione di argomenti trigger
Per ogni tabella, possiamo specificare sei diversi tipi di trigger. Si tratta della combinazione di argomenti Trigger inclusi nei Trigger a livello di riga SQL.
PRIMA DI INSERIRE: questi trigger eseguono l’azione sulle righe prima di eseguire qualsiasi operazione di INSERIMENTO nella tabella specificata o nel database.
AFTER INSERT: esegue l’azione sulle righe immediatamente successive a qualsiasi attività di INSERT del database.
PRIMA DELL’AGGIORNAMENTO: Con questi trigger, viene eseguita una funzione sulle righe prima dell’esecuzione di un’azione UPDATE sul database.
DOPO L’AGGIORNAMENTO: esegue l’azione sulle righe immediatamente successive a qualsiasi attività di AGGIORNAMENTO di database o tabelle specifiche.
PRIMA DELETE: esegue una determinata operazione sulle righe anche prima che il database o la tabella siano soggetti a un’azione DELETE.
DOPO DELETE: questi trigger eseguono l’azione sulle righe successive a ogni transazione DELETE.
Tipi di trigger SQL
I trigger SQL sono funzioni archiviate che vengono eseguite immediatamente quando si verificano eventi specifici. Assomiglia alla pianificazione basata su eventi. Le situazioni successive possono avviare l’esecuzione di trigger.
Trigger DML – DML sta per Data Manipulation Language. L’esecuzione del codice in risposta alla modifica dei dati è resa possibile utilizzando i trigger DML. Questo trigger viene attivato quando vengono eseguiti comandi DML come INSERT, UPDATE e DELETE. Questi sono anche chiamati “Trigger a livello di tabella”.
Trigger DDL – DDL sta per Data Definition Language. I trigger DDL ci consentono di eseguire codice in reazione alle modifiche dello schema del database, come l’aggiunta o l’eliminazione di tabelle, o eventi del server, ad esempio quando un utente effettua il check-in. Questi sono chiamati “Trigger a livello di database”.
Questi trigger possono essere attivati quando determinate istruzioni DDL come CREATE, ALTER o DROP vengono eseguite nel database attivo. Questi possono anche essere utilizzati per tenere d’occhio e gestire le attività che vengono eseguite.
Trigger di accesso: ogni volta che si verifica un evento di accesso (avvio, accesso, disconnessione, arresto), i trigger di accesso vengono richiamati immediatamente. Vengono eseguiti solo a seguito di un processo di autenticazione dell’utente anche prima dell’avvio della transazione dell’utente. I trigger di LOGON non verranno attivati se l’autorizzazione non riesce.
Questi trigger possono essere utilizzati per registrare la cronologia degli accessi o stabilire una restrizione di eventi per un accesso particolare, tra le altre funzioni di controllo e gestione dell’identità per le connessioni al server.
Trigger CLR – CLR sta per Common Language Runtime. I trigger CLR sono infatti un sottoinsieme unico di trigger basati principalmente su CLR all’interno della tecnologia .NET. Questi trigger sono utili se il trigger deve eseguire molti calcoli o deve essere correlato a un’entità diversa da SQL.
I trigger DML e DDL possono infatti essere costruiti abilitando la codifica dei trigger CLR supportati nelle tecnologie .NET tra cui Visual Basic, C# e F-sharp.
Esempio di trigger di SQL Server
Comprendiamo questi concetti trigger con un esempio.
Innanzitutto, creiamo un database utilizzando le istruzioni SQL.
CREATE DATABASE testdb; use testdb;
Qui, ho dato un “testdb” come nome del database. E il prossimo passo è creare una tabella.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Ho creato una tabella per memorizzare i dettagli dello studente. Ed ecco il comando per descrivere la struttura della tabella. Qui “studente” è il nome del tavolo che ho dato.
DESC student;
Di seguito è riportata la struttura della tabella che ho creato.
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Dopo aver creato una tabella, il passaggio successivo consiste nell’impostare un trigger. Proviamo a utilizzare l’argomento PRIMA INSERIRE.
Il nome del trigger che ho creato è “marks”. Non appena la tabella viene modificata con i voti dello studente, il trigger di seguito tenta di determinare automaticamente il voto complessivo dello studente.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Poiché è necessario sostituire i dati delle righe anziché lavorare con quelli precedenti, abbiamo definito “totale” utilizzando un nuovo nome di classe e tutte le espressioni successive sono precedute da nuove parole chiave dopo il totale utilizzando l’operatore punto. Ora aggiungeremo valori a ciascuna riga e vedremo i risultati. Inizialmente, il voto totale è 0 per ogni studente.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
L’istruzione trigger verrà automaticamente attivata quando i dati vengono inseriti nella tabella studente in questo caso. I voti totali di ogni studente saranno calcolati dal trigger. Ora, vediamo se il trigger viene invocato o meno utilizzando un’istruzione SELECT.
SELECT * FROM table_name;
Ed ecco l’output finale.
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
Nel risultato sopra, puoi vedere che tutti i voti delle materie vengono aggiunti automaticamente per ogni studente. Quindi, possiamo concludere che il trigger è stato invocato correttamente.
Ulteriori operazioni di attivazione
Possiamo eseguire molte operazioni usando i trigger. Alcuni possono essere semplici e altri un po’ complessi, ma una volta esaminate le query è facile da capire. Utilizzando le istruzioni Transact-SQL, puoi abilitare, disabilitare o eliminare i trigger utilizzando i seguenti comandi.
Interroga per verificare se un trigger specifico è presente o meno
Questo comando controlla il trigger specificato nell’intero database.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Interroga per visualizzare i trigger
Tutti i trigger disponibili nel database attivo verranno mostrati dalla seguente istruzione.
SHOW TRIGGERS;
Interroga per disabilitare il trigger
Il comando seguente disattiva il trigger nel database di lavoro.
DISABLE TRIGGER trigger_name ON DATABASE;
Puoi anche specificare un determinato nome di tabella per disabilitare un trigger.
DISABLE TRIGGER trigger_name ON table_name;
Interroga per abilitare il trigger
Il comando seguente disattiva prima un trigger specifico che è stato definito nella tabella specificata nel database attivo prima di riattivarlo.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Il trigger deve essere disabilitato, prima di provare ad abilitarlo,
Query per abilitare o disabilitare tutti i trigger in una tabella
Utilizzando l’istruzione SQL di cui sopra, possiamo disattivare o attivare tutti i trigger di tabella alla volta sostituendo “ALL” al posto di un nome di trigger specifico.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Query per eliminare o rilasciare trigger
Un trigger può essere eliminato cancellandolo o eliminando l’intera tabella. Ogni trigger correlato viene eliminato anche quando viene eliminata una tabella.
DROP TRIGGER [trigger_name];
Ogni volta che un trigger viene eliminato, i dati correlati vengono eliminati dalla tabella dei dati sys.objects.
Vantaggi dei trigger
- È semplice creare trigger e il trigger stesso può richiamare funzioni e metodi memorizzati.
- Gli utenti possono implementare un controllo semplice utilizzando i trigger.
- Tragicamente, non è possibile creare vincoli tra entità nei sistemi di database con SQL Server, sebbene sia possibile emulare il funzionamento dei vincoli utilizzando i trigger.
- I vincoli di integrità possono essere implementati tra i database utilizzando i trigger.
- Quando è richiesta la convalida di gruppo anziché la verifica riga per riga dei dati appena inseriti o modificati, i trigger possono essere utili.
Svantaggi dei trigger
I trigger SQL potrebbero non essere la scelta migliore in alcune situazioni a causa delle loro limitazioni.
- I trigger devono essere accuratamente documentati.
- A causa dell’esecuzione simultanea del database che potrebbe non essere accessibile ai componenti dell’applicazione, il debug dei trigger può essere difficile.
- Le istruzioni DML diventano più complesse quando vengono utilizzati i trigger.
- Anche un problema di innesco minore può potenzialmente portare a errori logici nell’affermazione.
Conclusione
I trigger sono componenti molto utili di Transact-SQL e SQL e puoi usarli anche in Oracle. L’uso di trigger è fondamentale quando si chiamano metodi archiviati. Questi trigger SQL ci consentono di analizzare le tempistiche delle attività e determinare come rispondere ad esse, se necessario. Possiamo anche verificare la presenza di una determinata tabella collegata a un trigger per acquisire dati.
La ricorsione può essere abilitata dai trigger. Ogni volta che un trigger su una tabella esegue un comando sulla tabella padre, viene attivata la seconda iterazione del trigger, nota come trigger ricorsivo. Questo aiuta mentre si cerca di risolvere una correlazione di identità.
Inoltre, i trigger regolano il modello di aggiornamento che il database può accettare. È estremamente vantaggioso mantenere i vincoli di integrità dei dati nel sistema di database se le chiavi di vincolo SQL non esistono principalmente la chiave primaria e la chiave esterna.
Spero che questo articolo ti sia stato utile per conoscere i trigger SQL.
Se desideri approfondire i database, ecco alcune eccellenti risorse per imparare SQL e NoSQL.