Questo articolo si concentrerà sui vari modi per disabilitare i trigger in SQL Server in modo che non interferiscano con certe operazioni come gli inserimenti di massa.
Problema
Abbiamo avuto una situazione in cui abbiamo bisogno di caricare dati nelle tabelle di uno dei nostri database. Sono stati creati alcuni trigger su queste tabelle per l’inserimento, l’aggiornamento e la cancellazione e questi trigger sono impostati come “NOT FOR REPLICATION”. Durante il caricamento dei dati nelle tabelle non vogliamo che questi trigger vengano sparati. Abbiamo pensato di disabilitare i trigger sulle tabelle, ma questo li disabiliterà anche per altre transazioni utente. Vogliamo che non vengano attivati solo per la mia sessione da cui sto eseguendo lo script di caricamento dei dati. In questo articolo vediamo come disabilitare i trigger in SQL Server per uno specifico.
Soluzione
Dobbiamo prima capire il concetto di “NOT FOR REPLICATION” in SQL Server.
NOT FOR REPLICATION può essere usato durante la creazione di trigger in SQL Server. Questo indica che questi trigger non vengono sparati quando l’agente di replica fa modifiche ai dati (INSERT / UPDATE / DELETE) sulla tabella.
Non solo per i trigger in SQL Server, questo suggerimento può essere utilizzato anche durante la creazione di chiavi esterne, colonne di identità e vincoli di controllo.
Nel caso delle chiavi esterne, il controllo della chiave esterna avviene solo quando un utente modifica i dati sulla tabella e la convalida della chiave esterna non avviene quando l’agente di replica sincronizza queste modifiche all’altra estremità (o al sottoscrittore o sia al sottoscrittore che all’editore in base al tipo di replica configurato)
Nel caso delle colonne di identità, un nuovo valore di identità non viene generato quando l’agente di replica inserisce i dati nella tabella e viene utilizzato il valore di identità originale che viene generato alla fonte.
Poiché i trigger in SQL Server sono stati creati con “NOT FOR REPLICATION” su queste tabelle, se inseriamo i dati nella tabella come utente normale questi trigger vengono attivati. Questi trigger non sono stati attivati quando l’agente di replica inserisce, cancella e aggiorna i dati sulle tabelle. Quindi, fingendo di essere un agente di replica (cioè i trigger sono stati attivati per tutte le sessioni utente e non attivati per la sessione specifica in cui ho effettuato l’accesso come agente di replica).
Per illustrare questo, creerò due tabelle di esempio “Emp” e “EmpJoining” e un trigger sulla tabella “Emp” che viene attivato per inserire la data di aggregazione quando una nuova riga viene inserita nella tabella “Emp”.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , name varchar (50) )
GO
CREARE TABLE EmpJoining ( Empid int, JD datetime)
GO
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
GO
|
Ora, per esempio, ho l’id, il nome e la data di assunzione dei dipendenti in un altro sistema che deve essere importato nelle tabelle esistenti. In questo caso non voglio che la logica di trigger venga attivata quando inserisco i dati nella tabella Emp.
Fondamentalmente, ho uno script di inserimento T-SQL per entrambe queste tabelle che inserisce il nome dell’id del dipendente nella tabella “Emp” e le loro date di unione nella tabella “EmpJoining”.
Accediamo come utente normale ed eseguiamo lo script T-SQL per inserire i dati nella tabella.
Login come utente normale:
Aprire SQL Server management studio. Ora accedi al SQL Server come utente normale e usa il database dove hai creato la tabella. Usate il seguente script per inserire i dati nella tabella Emp e controllate se il trigger viene attivato o meno.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
possiamo vedere che il trigger viene eseguito e i dati inseriti nella tabella EmpJoining.
Possiamo controllare la proprietà della sessione usando il seguente script.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
restituirà zero se facciamo il login come utente normale e restituirà uno se facciamo il login come agente di replica.
Passi per accedere come agente di replica:
Ora facciamo il login come agente di replica e inseriamo i dati usando lo script.
Chiudi SQL server management studio e riaprilo.
Inserite il server, il login e la password che volete usare.
Passate alla scheda parametri di connessione aggiuntivi. Nella tabella dei parametri di connessione aggiuntivi inserisci REPLICATION=TRUE nella casella di testo come mostrato nell’immagine sottostante.
Puoi usare il tuo metodo di login preferito: autenticazione Windows o autenticazione SQL Server.
Questo ti farà accedere come agente di replica e qualsiasi operazione DML eseguita sulla tabella sarà eseguita come agente di replica.
Ora esegui la seguente query per controllare se sei loggato come agente di replica o no.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
Dovrebbe dare 1.
Ora inserisci alcune righe usando lo script sottostante.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Possiamo vedere che il trigger non viene eseguito perché abbiamo effettuato il login come agente di replica e il trigger è impostato come “NOT FOR REPLCIATION”.
I dati vengono inseriti solo nella tabella Emp.
Qui in questo caso il trigger non è disabilitato e disponibile per altre transazioni utente che spareranno il trigger quando c’è un INSERT. Il trigger non viene attivato solo per la transazione che viene eseguita come agente di replica.
Usare la seguente query per controllare se il trigger è contrassegnato come “NOT FOR REPLCIATION” o no.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Ci sono altri modi per disabilitare i trigger in SQL Server per una sessione gestendo il codice con condizioni.
Per esempio potete usare CONTEXT_INFO() nel codice del trigger e ritornare. L’uso di CONTEXT_INFO() non richiede permessi speciali.
In questo caso, se il CONTEXT_INFO corrisponde al valore specificato nel trigger il trigger ritorna e non esegue il codice sottostante. Si prega di fare riferimento al codice sottostante.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
AS
BEGIN
SET NOCOUNT su
DECLARE @CONT_INFO VARBINARY(128)
SELECT @CONT_INFO = CONTEXT_INFO()
IF @CONT_INFO = 0x1256698456
RETURN
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
|
In questo caso dobbiamo impostare il valore context_info a 0x1256698456 prima di inserire i dati nella tabella “Emp”.
Anche le seguenti viste di sistema memorizzano le informazioni sul contesto, ma l’interrogazione diretta di queste viste richiede i permessi SELECT e VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Invece dello script T-SQL INSERT, assumiamo che i dati siano un file .txt o .csv. Possiamo usare le opzioni BCP UTILITY o BULK INSERT per caricare i dati nelle tabelle senza sparare i trigger.
Usando BCP utility
Possiamo usare BCP utility per caricare i dati in massa nelle tabelle senza sparare i trigger in SQL Server. Questo metodo funziona solo per gli INSERIMENTI sulla tabella e non spara i trigger creati “per l’inserimento” e “invece di inserire”.
Per default, l’utilità BCP non spara i trigger sul caricamento dei dati nelle tabelle. Per forzare l’esecuzione dei trigger, dovremmo usare -h “FIRE_TRIGGERS” in BCP durante il caricamento dei dati nella tabella.
Di seguito sono riportate le tabelle di esempio utilizzate in questo esempio.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE USERS
(ID int,
NOME varchar(50))
CREATE TABLE USERHIST
(USERID int,
CREATEDDATE datetime )
CREATE TRIGGER INS_USERS ON USERS
FOR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END
|
Si prega di fare riferimento al seguente esempio di BCP predefinito per caricare i dati nella tabella “USERS” che non attiverà i trigger in SQL Server. Ho mascherato il server originale, i nomi dei database e le credenziali di accesso.
bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Ppassword
Si prega di fare riferimento al seguente esempio di BCP con suggerimento “FIRE_TRIGGERS” per caricare i dati nella tabella USERS che attiverà i trigger.
bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Ppassword -h “FIRE_TRIGGERS”
Usando BULK INSERT
Anche questa opzione funziona solo per INSERT sulla tabella e non attiva i trigger creati “for insert” e “instead of insert”.
Per default, BULK INSERT non attiva trigger in SQL Server. Possiamo forzare l’esecuzione del trigger specificando “FIRE_TRIGGERS”
Ho un file test.txt che contiene dati con “,” come FIELDTERMINATOR.
Si prega di fare riferimento al seguente codice per BULK INSERT predefinito per caricare i dati nella tabella USERS che non attiverà i trigger in SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‘D:\test.txt’ –location con nome file
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Si prega di fare riferimento al seguente codice con il suggerimento “FIRE_TRIGGERS” che attiverà i trigger in SQL Server durante il caricamento dei dati dal file test.txt.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‘D:\test.txt’ –location con nome file
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\\n’,
FIRE_TRIGGERS
)
GO
|
In questo articolo abbiamo discusso il comportamento dei trigger in SQL Server nei casi seguenti.
- Accesso come utente normale.
- Login come agente di replica e il trigger è impostato come non per la replica.
- Usando la funzione CONTEXT_INFO nel codice del trigger.
- Usando BCP default e con il suggerimento “FIRE_TRIGGER”
- Usando BULK INSERT default e con il suggerimento “FIRE_TRIGGER”
Per i punti 3, 4 e 5 non importa se si creano trigger con “NOT FOR REPLICATION” o no.
- Autore
- Postate recenti
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Panoramica del comando Collate SQL – 22 ottobre 2019
- Recuperare una password SA persa – 20 settembre 2019