In questo articolo, esamineremo i trigger in SQL Server, i diversi tipi di eventi trigger, l’ordine dei trigger e la NON REPLICA nei trigger. Un trigger è un oggetto di database che viene eseguito automaticamente quando si verifica un evento. Ci sono tre diversi tipi di eventi.
- Eventi DML
- Eventi DDL
- Evento LOGON – Il trigger di logon viene eseguito quando si verifica un evento LOGON, cioè quando una sessione utente viene stabilita
Trigger DML in SQL Server
I trigger DML in SQL Server vengono attivati quando si verifica un evento DML, cioè quando i dati vengono inseriti/aggiornati/cancellati nella tabella da un utente.
Creazione di trigger per un evento DML
Creiamo alcune tabelle e trigger di esempio in SQL Server.
|
1
2
3
|
CREATE TABLE Locations (LocationID int, LocName varchar(100))
CREATE TABLE LocationHist (LocationID int, ModifiedDate DATETIME)
|
Possiamo creare un trigger DML per un evento specifico o più eventi. I trigger in SQL Server (DML) sparano sugli eventi indipendentemente dal numero di righe interessate.
Di seguito è riportata la sintassi di esempio per la creazione di un trigger DML per un evento di aggiornamento.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
|
Questi trigger sono creati a livello di tabella. Dopo aver creato il trigger con successo, possiamo vedere i trigger navigando nella cartella Triggers a livello di tabella.
Invece dei trigger in SQL Server
Questi trigger vengono sparati prima dell’evento DML e i dati reali non vengono modificati nella tabella.
Per esempio, se specifichiamo un invece di trigger per la cancellazione su una tabella, quando l’istruzione di cancellazione viene rilasciata contro la tabella, il invece di trigger viene sparato e il blocco T-SQL all’interno del trigger in SQL Server viene eseguito ma la cancellazione effettiva non avviene.
T-Sintassi SQL per la creazione di un instead of trigger
|
1
2
3
4
5
6
|
CREATE TRIGGER TR_DEL_Locations ON Locations
INSTEAD OF DELETE
AS
BEGIN
Selezionare ‘Esempio invece di trigger’ come
END
|
- Se ci sono più trigger insieme a instead of trigger sulla tabella, il invece di trigger viene sparato per primo nell’ordine
- INSTEAD of trigger può essere creato sulle viste
- possiamo definire solo un invece di trigger per INSERT, UPDATE, o DELETE su una tabella o una vista
Abilitare e disabilitare i trigger DML su una tabella
Naviga fino alla cartella trigger a livello di tabella, seleziona il trigger, fai clic destro sul trigger e clicca su Abilita/Disabilita per abilitare o disabilitare il trigger usando SSMS.
Disabilitare un trigger specifico di SQL Server su una tabella usando T-SQL.
|
1
|
DISABLE TRIGGER TR_UPD_Locations2 on Locations
|
Abilitare un trigger specifico sulla tabella utilizzando T-SQL.
|
1
|
ENABLE TRIGGER TR_UPD_Locations2 on Locations
|
Per abilitare tutti i trigger su una tabella, utilizzare la seguente sintassi.
|
1
|
ENABLE TRIGGER ALL ON Locations
|
Per disabilitare tutti i trigger su una tabella, utilizzare la seguente sintassi. Questa dichiarazione non è supportata se la tabella fa parte della replica merge.
|
1
|
DISABLE TRIGGER ALL ON Locations
|
Cancellare un trigger su una tabella.
Per rilasciare un trigger DML sulla tabella utilizzando SQL Server management studio, navigate nella cartella Triggers sotto la tabella. Selezionate la tabella che volete eliminare, cliccate con il tasto destro sul trigger e cliccate su Delete. Fare clic su Ok.
T-SQL per drop un trigger sulla tabella.
|
1
|
DROP TRIGGER TRL_UPD_Locations2
|
L’abbandono di una tabella farà cadere tutti i trigger di SQL Server sulla tabella insieme alla tabella stessa.
Trigger DDL
I trigger DDL in SQL Server sono attivati su eventi DDL, cioè su istruzioni di creazione, modifica, eliminazione, ecc. Questi trigger sono creati a livello di database o di server in base al tipo di evento DDL.
Questi trigger sono utili nei casi seguenti.
- Prevenire le modifiche allo schema del database
- Controllare le modifiche allo schema del database
- Rispondere a un cambiamento nello schema del database
Creazione di un trigger DDL
Di seguito è riportata la sintassi di esempio per la creazione di un trigger DDL per l’evento ALTER TABLE su un database che registra tutte le dichiarazioni di modifica della tabella. Potete scrivere il vostro codice personalizzato per tracciare o controllare i cambiamenti dello schema usando EVENTDATA().
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)
CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
INSERT INTO TableSchemaChanges
SELECT EVENTDATA(),GETDATE()
END
|
È possibile specificare un gruppo di eventi che consiste di diversi eventi DDL. Se specifichiamo un gruppo di eventi durante la creazione di un trigger DDL, il trigger viene attivato quando si verifica un evento DDL nel gruppo.
Per esempio, se vogliamo creare un trigger per tutti gli eventi DDL a livello di database, possiamo semplicemente specificare il gruppo di eventi DDL_DATABASE_LEVEL_EVENTS come mostrato nell’immagine sottostante.
Per visualizzare i trigger a livello di database, accedere al server utilizzando SQL Server Management Studio e navigare al database. Espandere il database e navigare in Programmabilità -> Trigger di database.
Per visualizzare i trigger a livello di server, accedere al server utilizzando SSMS e navigare su Server Objects e poi sulla cartella Triggers.
Abilitare e disabilitare i DDL trigger
Usa la seguente sintassi T-SQL per disabilitare o abilitare il DDL trigger a livello di database.
|
1
2
3
4
5
|
ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
|
Utilizzare la seguente sintassi T-SQL per eliminare un trigger DDL creato a livello di database.
|
1
|
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE
|
Trigger LOGON in SQL Server
Questi trigger in SQL Server scattano in risposta ad un evento LOGON. I trigger LOGON si attivano dopo un’autenticazione riuscita e prima di stabilire la sessione utente.
I trigger LOGON sono creati a livello del server e sono utili nei seguenti casi.
- Per verificare l’attività di login
- Per controllare l’attività di login
Creazione di trigger LOGON
Puoi usare EVENTDATA() e scrivere il tuo codice personalizzato per tracciare o controllare le connessioni. Qui sto creando semplici trigger in SQL Server per l’evento LOGON. Di seguito è riportata la sintassi di esempio per la creazione di un trigger LOGON.
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE LoginActivity (LOGONEvent XML ,Logintime datetime)
CREATE TRIGGER ON ALL SERVER
FOR LOGON AS
BEGIN
INSERT INTO LoginActivity
SELECT EVENTDATA()
,GETDATE()
END
|
Dobbiamo essere cauti durante la creazione di questi trigger in quanto il login potrebbe fallire se l’esecuzione del trigger fallisce o se non si ha accesso agli oggetti referenziati nel trigger LOGON. In questi casi, l’unico membro del ruolo sysadmin può connettersi al server usando una connessione di amministratore dedicata. Quindi, è sempre meglio abilitare la connessione dedicata all’amministratore quando si usano questi trigger.
Abilitare e disabilitare i trigger LOGON
Usa la seguente sintassi T-SQL per disabilitare o abilitare il trigger LOGON.
|
1
2
3
4
5
|
ENABLE TRIGGER track_logins ON ALL SERVER
DISABLE TRIGGER track_logins ON ALL SERVER
GO
|
Usa la seguente sintassi T-SQL per eliminare un trigger LOGON.
|
1
|
DROP TRIGGER track_logins ON ALL SERVER
|
Ricorsione diretta
La ricorsione diretta è un caso in cui il trigger di SQL Server sulla tabella viene sparato ed esegue un’azione che innesca nuovamente lo stesso trigger.
Per esempio, fate riferimento al seguente esempio di trigger per un aggiornamento che è direttamente ricorsivo.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE .(
NULL,
(100) NULL,
DateUpdated datetime
) ON
GO
INSERT INTO Locations VALUES(1,’Richmond Road’, NULL)
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
AS
BEGIN
Update Locations set DateUpdated =GETDATE()
END
|
La ricorsione diretta può essere controllata dall’impostazione del database RECURSIVE_TRIGGERS. Se l’impostazione è attiva, il trigger di cui sopra genera un errore.
Se l’impostazione del database RECURSIVE_TRIGGERS è off, allora il trigger viene eseguito una sola volta e non va in loop.
Per cambiare l’impostazione RECURSIVE_TRIGGERS usando SSMS, vai al database, clicca col tasto destro sul database e seleziona Proprietà. Cliccare su Opzioni e cambiare l’impostazione con l’opzione desiderata.
Per impostare RECURSIVE_TRIGGERS OFF usando T-SQL, usare la seguente dichiarazione e sostituire il nome del database con il nome del proprio database.
|
1
2
|
ALTER DATABASE SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
|
Per impostare i RECURSIVE_TRIGGERS ON usando T-SQL, utilizzare la seguente dichiarazione e sostituire il nome del database con il nome del vostro database.
|
1
2
|
ALTER DATABASE SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
|
Ricorsione indiretta
Questo è un caso in cui un trigger viene sparato e richiama un altro trigger dello stesso tipo.
Di seguito è riportato un esempio di trigger per la ricorsione indiretta.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
CREATE TABLE Temp1 (id int)
GO
INSERT INTO Temp1 values (1),(2)
GO
CREARE TABLE Temp2 (id int)
GO
INSERT INTO Temp2 valori (1),(2)
GO
CREA TRIGGER TR_Temp1 su Temp1
FOR UPDATE
AS
BEGIN
UPDATE TEMP2 set ID =’5′ where id in (select id from inserted)
END
GO
CREATE TRIGGER TR_Temp2 su Temp2
FOR UPDATE
AS
BEGIN
UPDATE Temp1 set ID =’5′ where id in (select id from inserted)
FINE
|
Ora quando aggiorniamo un valore nella tabella Temp1, il trigger TR_Temp1 si attiva e aggiorna la tabella Temp2. TR_Temp2 si innesca e aggiorna la tabella Temp1 che fa scattare di nuovo TR_Temp1.
Questo comportamento può essere controllato disattivando i trigger annidati.
|
1
2
|
EXEC sp_configure ‘nested triggers’, 0 ;
GO
|
SQL Server trigger order
SQL Server permette più trigger sulla tabella per lo stesso evento e non esiste un ordine definito di esecuzione di questi trigger.
Possiamo impostare l’ordine di un trigger come primo o ultimo usando la procedura sp_settriggerorder. Ci può essere solo un primo o un ultimo trigger per ogni dichiarazione su una tabella.
Di seguito è riportata la sintassi di esempio per impostare l’ordine di trigger al primo per la dichiarazione INSERT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
CREATE TABLE TriggerOrderTest (id int)
CREARE TRIGGER TR_1 su TriggerOrderTest
FOR INSERT
come
BEGIN
PRINT ‘First Trigger’
FINE
GO
CREATE TRIGGER TR_2 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT ‘Second Trigger’
END
GO
CREATE TRIGGER TR_3 ON TriggerOrderTest
FOR INSERT
come
BEGIN
PRINT ‘Third Trigger’
END
GO
sp_settriggerorder @triggername =’TR_3′
, @order = ‘FIRST’
, @stmttype = ‘INSERT’
|
Ora, quando i dati vengono inseriti nella tabella “TriggerOrderTest” si verifica l’evento INSERT e il trigger TR_3 scatta per primo.
In caso di trigger DDL dobbiamo specificare il parametro namespace che è l’ambito del trigger di SQL Server nella stored procedure sp_settriggerorder.
Di seguito la sintassi di esempio per impostare l’ordine di trigger DDL.
|
1
2
3
4
|
sp_settriggerorder @triggername =’DDL_3′
, @order = ‘FIRST’
, @stmttype = ‘ALTER_TABLE’
, @namespace = ‘DATABASE’
|
NOT FOR REPLICATION
NOT FOR REPLICATION indica che il trigger non dovrebbe scattare quando l’agente di replica sincronizza le modifiche dei dati al sottoscrittore.
Per esempio, se si stanno replicando sia Locations che LocationHist. Ora quando si aggiorna un record su Location il trigger viene sparato, inserisce il record nella tabella della cronologia. Quando queste modifiche si sincronizzano con un’altra estremità (abbonati) non c’è bisogno che il trigger venga sparato di nuovo. Quindi, se contrassegniamo il trigger per “NOT FOR REPLICATION” il trigger non si attiva quando l’agente di replica sincronizza le modifiche e si attiva solo per le modifiche dei dati effettuate dall’utente.
Di seguito è riportata la sintassi di esempio per creare un trigger in SQL Server con not for replication.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
|
Se si desidera che i trigger in SQL Server vengano eseguiti quando l’agente di replica sincronizza i dati cambia ad un’altra estremità, basta creare il trigger senza specificare “NOT FOR REPLICATION”.
- Autore
- Post recenti
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Panoramica del comando Collate SQL – 22 ottobre 2019
- Recupera una password SA persa – 20 settembre 2019