Come utilizzare i trigger SQL Server

I trigger sono un tipo speciale di procedure memorizzate che eseguono come risposta alle attività negli oggetti SQL Server. I trigger sono archiviati nella memoria del sistema e vengono eseguiti solo quando si verifica un evento specifico. A differenza delle normali procedure memorizzate, i trigger non accettano parametri né eseguiti manualmente.

In questo articolo, esploreremo come creare e lavorare con i trigger in SQL Server.

Esistono tre tipi principali di trigger in SQL Server:

1. Trigger DML
2. DDL Trigger
3. Trigger di accesso

Esploriamo questi trigger in questa guida.

SQL Server DML Trigger

I trigger del linguaggio DML o di manipolazione dei dati sono tipi di trigger che sparano in risposta a un inserto, aggiorna ed elimina l'operazione su una tabella o visualizza. I trigger DML verranno eseguiti quando viene eseguita qualsiasi operazione valida, se sono interessate qualsiasi riga.

Crea DML dopo trigger

In SQL Server, è possibile creare un trigger DML utilizzando l'istruzione Crea trigger.

Crea trigger schema_name.trigger_name
Su table_name
Dopo [inserire, aggiornare, eliminare]
COME
-- trigger code da eseguire

Rompi la sintassi sopra:

1. Schema_name - Il nome dello schema in cui viene memorizzato il trigger.
2. trigger_name - nome del trigger.
3. table_name - Il nome della tabella in cui si applicherà il trigger specificato.
4. Dopo - una clausola successiva per definire in base alla quale si applicherà le condizioni il trigger.

Per imparare a creare e utilizzare un grilletto DML, facciamo un esempio pratico.

Crea database di esempio e inserire i dati come previsto nel set di query mostrate di seguito:

-- Crea database di esempio
Creare vendite di database;
ANDARE
-- Switch DB;
Usa le vendite;
-- crea tabella
Crea vendite di tabelle (
Id int Identity (1,1) non null Key primario,
Product_Name Varchar (50),
Prezzo denaro,
quantità int
);
-- Inserire i dati di esempio
Inserisci le vendite (Product_Name, Price, quantità)
Valori ("Caricatore iPhone", $ 9.99, 10),
("Google Chromecast", $ 59.25, 5),
("Controller wireless Dualsense PlayStation", $ 69.00, 100),
("Xbox Series S", $ 322.00, 3),
('Oculus Quest 2', $ 299.50, 7),
("Netgear Nighthawk", $ 236.30, 40),
('Redragon S101', $ 35.98, 100),
("Action figure di Star Wars", $ 17.50, 10),
("Mario Kart 8 Deluxe", $ 57.00, 5);

Una volta che abbiamo i dati di esempio, possiamo procedere e creare un trigger DML per licenziare un'operazione di aggiornamento sulla tabella.

Considera l'esempio mostrato di seguito:

-- Crea la tabella per archiviare la cronologia degli aggiornamenti
Crea tabella ModifiedDate (Id int, Date_ DateTime)
ANDARE
-- Crea trigger
Crea trigger DBO.update_trigger
In vendita
Dopo l'aggiornamento
Non per la replicazione
COME
INIZIO
Inserire in una data modificata
Seleziona ID, getDate ()
Da inserito
FINE

La query sopra creerà un trigger che accende quando eseguiamo un aggiornamento sulla tabella. Per testare il trigger, possiamo eseguire un aggiornamento come:

-- Tabella di aggiornamento
Aggiorna il prezzo del set di vendite = $ 10.10
Dove id = 1;

Dopo l'esecuzione, possiamo verificare se il trigger ha funzionato selezionando le colonne nella tabella modificata.

-- Controllare la tabella modificata
Seleziona * da modificata;

In SSMS, è possibile visualizzare i trigger su una tabella espandendo l'opzione Trigger:

Crea invece di innescare

L'altro tipo di innesco DML in SQL Server è invece dei trigger. Questi sono tipi di trigger che eseguono invece dell'istruzione DML. Ad esempio, se specifichiamo un'istruzione DELETE, possiamo utilizzare invece i trigger per essere eseguiti prima dell'operazione.

La sintassi per la creazione di un trigger anziché è mostrata:

Crea trigger schema_name.trigger_name
Su table_name
Invece di [inserire, aggiornare, eliminare]
COME
-- dichiarazioni di trigger

Ad esempio, la query di seguito crea un trigger che visualizza un messaggio quando viene eseguita un'operazione di inserzione sulla tabella.

-- Crea invece di trigger
Crea trigger invece_insert
In vendita
invece di inserire
COME
INIZIO
Seleziona 'non è possibile inserire in questa tabella' come errore
FINE
-- Esegui invece_insert trigger
Inserisci le vendite (Product_Name, Price, quantità)
Valori ("Caricatore iPhone", $ 9.99, 10);

Una volta eseguita la query sopra, dovremmo ricevere un messaggio che indica che non possiamo eseguire un inserto sulla tabella.

Errore
-------------------------------

Non puoi inserire in questa tabella

SQL DDL Trigger

Il linguaggio DDL o Definizione dei dati sono trigger che rispondono agli eventi al server o al database anziché a una tabella. DDL Trigger risponderanno a eventi come Drop, Grant, Deny, Revok, Aggiorna le statistiche, Crea e Alter.

Crea trigger DDL

La sintassi per creare un trigger DDL è come mostrato:

Crea trigger trigger_name
Sul database | Tutto server
Con ddl_trigger_parameters
Per event_type | event_group
COME
-- dichiarazioni di trigger

Possiamo abbattere la sintassi come:

1. trigger_name - nome univoco del trigger.
2. database o tutto server: specificare dove viene eseguito il trigger. Database se si applica sul database o in tutto il server se si applica sull'ambito del server.
3. ddl_trigger_parameter - parametri ddl come eseguire come o crittografare come.
4. event_type - L'evento DDL che accende il trigger.

La query di esempio seguente crea un trigger DDL che si accende quando viene rilasciata un'istruzione tabella di caduta.

-- Crea trigger DDL
Crea trigger drop_ddl_trigger
Sul database
Per drop_table
COME
INIZIO
Seleziona eventData ();
FINE

Una volta eseguito un evento drop sul database, il trigger visualizzerà le informazioni sull'evento utilizzando la funzione eventData ().

Possiamo testare il grilletto:

-- trigger di prova
Vendite di caduta del tavolo;

La query dovrebbe restituire informazioni XML sull'evento come:

In SSMS, è possibile visualizzare i trigger espandendo i trigger di database in programmabilità nel database di destinazione.

Abilita/disabilita i trigger

SQL Server consente di abilitare e disabilitare i trigger. Per abilitare un trigger su una tabella, utilizzare la query come:

Abilita trigger update_trigger sulle vendite;

Dove update_trigger rappresenta il nome e le vendite del trigger rappresentano il nome della tabella.

Puoi anche abilitare tutti i trigger su un tavolo come:

Abilita trigger tutto su table_name;

Per abilitare il trigger del database, utilizzare la query:

Abilita trigger drop_ddl_trigger sulle vendite;

Qui, drop_ddl_trigger rappresenta il nome e le vendite del trigger rappresentano il database.

Per abilitare tutti i trigger di database, utilizzare la query:

Abilita Trigger tutto sulle vendite;

Per disabilitare un trigger di tabella o database, sostituire la parola chiave abilita con disabilita ☺️.

Elimina trigger

Per rimuovere un trigger, è possibile utilizzare l'istruzione DROP come mostrato:

Drop trigger se esiste trigger_name;

SQL Server mostra tutti i trigger

Per visualizzare tutti i trigger in un'istanza SQL Server, utilizzare la query come mostrato:

Seleziona nome, type_desc, is_disabled, is_instead_of_trigger da sys.trigger dove type = 'tr'

La query dovrebbe restituire tutti i trigger nell'istanza del server SQL come:

Scatenanti dell'accesso del server SQL

I trigger di accesso sono tipi di trigger che eseguono quando si verifica un'attività di accesso sul server. Questi tipi di trigger vengono eseguiti dopo un'autenticazione riuscita ma prima di creare una sessione utente. Poiché vengono utilizzati per gestire l'attività di accesso, li creiamo a livello di server, come mostrato nell'esempio seguente:

ATTENZIONE: Il trigger seguente può impedire l'accesso futuro al server. Assicurarsi di eliminare prima di accedere.

ATTENZIONE - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Crea trigger di accesso
Crea trigger login_tg
Su tutto il server
Per l'accesso come
INIZIO
Seleziona 'un trigger dopo l'accesso come [messaggio]
FINE

Il trigger visualizzerà un messaggio quando l'utente accede al server.

Conclusione

In questa guida, hai compreso vari tipi di trigger, come creare, abilitare, disabilitare, eliminare e visualizzare i trigger in SQL Server.