Acest articol se va concentra pe diferitele modalități de dezactivare a declanșatoarelor în SQL Server, astfel încât acestea să nu interfereze cu anumite operații, cum ar fi inserțiile în masă.
Problemă
Am avut o situație în care trebuie să încărcăm date în tabelele din una dintre bazele noastre de date. Au fost create câteva declanșatoare pe aceste tabele pentru insert, update și delete, iar aceste declanșatoare sunt setate ca „NOT FOR REPLICATION”. În timpul încărcării datelor în tabele, nu dorim ca aceste declanșatoare să fie activate. Ne-am gândit să dezactivăm declanșatoarele pe tabele, dar acest lucru le va dezactiva și pentru alte tranzacții ale utilizatorilor. Dorim ca acestea să nu fie declanșate doar pentru sesiunea mea din care execut scriptul de încărcare a datelor. În acest articol, haideți să vedem cum să dezactivăm declanșatoarele în SQL Server pentru un anumit lucru.
Soluție
Trebuie să înțelegem mai întâi conceptul „NOT FOR REPLICATION” în SQL Server.
NOT FOR REPLICATION poate fi utilizat în timpul creării declanșatoarelor în SQL Server. Acest lucru indică faptul că aceste declanșatoare nu sunt declanșate atunci când agentul de replicare efectuează modificări de date (INSERT / UPDATE / DELETE) pe tabel.
Nu numai pentru declanșatoarele din SQL Server, acest indiciu poate fi utilizat și la crearea cheilor străine, a coloanelor de identitate și a constrângerilor de verificare.
În cazul cheilor străine, verificarea cheilor străine are loc numai atunci când un utilizator modifică datele din tabel, iar validarea cheilor străine nu are loc atunci când agentul de replicare sincronizează aceste modificări la celălalt capăt (fie la abonat, fie atât la abonat, cât și la editor, în funcție de tipul de replicare configurat)
În cazul coloanelor de identitate, nu se generează o nouă valoare de identitate atunci când agentul de replicare introduce date în tabel și se utilizează valoarea de identitate originală generată la sursă.
Deoarece declanșatoarele din SQL Server au fost create cu „NOT FOR REPLICATION” pe aceste tabele, dacă introducem date în tabel ca utilizator normal, aceste declanșatoare au fost declanșate. Aceste declanșatoare nu au fost declanșate atunci când agentul de replicare introduce, șterge și actualizează date în tabele. Așadar, pretinzând că un agent de replicare îmi va face treaba. (adică declanșatoarele au fost declanșate pentru toate sesiunile de utilizator și nu au fost declanșate pentru sesiunea specifică în care m-am conectat ca agent de replicare.)
Pentru a ilustra acest lucru, voi crea două tabele de probă „Emp” și „EmpJoining” și un declanșator pe tabela „Emp” care este declanșat pentru a insera data îmbinării atunci când un nou rând este inserat în tabela „Emp”.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , name varchar (50) )
GO
CREATE 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
|
Acum, de exemplu, am id-ul angajatului, numele și data la care s-a alăturat în alt sistem, care trebuie să fie importate în tabelele existente de mai sus. În acest caz, nu vreau ca logica de declanșare să fie declanșată atunci când introduc date în tabelul Emp.
Practic, am un script de inserție T-SQL pentru ambele tabele care introduce numele id-ului angajatului în tabelul „Emp” și datele de îmbinare a acestora în tabelul „EmpJoining”.
Să ne conectăm ca utilizator normal și să executăm scriptul T-SQL pentru a introduce date în tabel.
Conectați-vă ca utilizator normal:
Deschideți SQL Server management studio. Acum conectați-vă la SQL Server ca utilizator normal și utilizați baza de date în care ați creat tabelul. Utilizați scriptul de mai jos pentru a introduce date în tabelul Emp și verificați dacă declanșatorul este declanșat sau nu.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
putem vedea că declanșatorul s-a declanșat și a introdus date în tabelul EmpJoining.
Putem verifica proprietatea sesiunii folosind scriptul de mai jos.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
acesta va returna zero dacă ne logăm ca utilizator normal și va returna unu dacă ne logăm ca agent de replicare.
Pași pentru a ne autentifica ca agent de replicare:
Acum haideți să ne autentificăm ca agent de replicare și să inserăm datele folosind scriptul.
Închideți SQL server management studio și redeschideți-l.
Introduceți serverul, loginul și parola pe care doriți să le utilizați. faceți clic pe opțiuni. Vă rugăm să consultați imaginea de mai jos.
Navigați la fila Additional connection parameters (Parametrii de conexiune suplimentari). În tabelul Additional connection parameters introduceți REPLICATION=TRUE în caseta de text, așa cum se arată în imaginea de mai jos.
Puteți utiliza metoda de autentificare preferată, fie autentificarea Windows, fie autentificarea SQL Server.
Acest lucru vă va autentifica ca agent de replicare și orice operațiune DML pe care o efectuați pe tabelă va fi executată ca agent de replicare.
Acum executați interogarea de mai jos pentru a verifica dacă sunteți logat ca agent de replicare sau nu.
|
1
|
select SESSIONPROPERTY (‘replication_agent’ )
|
Ar trebui să returneze 1.
Acum inserați câteva rânduri folosind scriptul de mai jos.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Putem vedea că declanșatorul nu este declanșat deoarece ne-am logat ca agent de replicare și declanșatorul este setat ca „NOT FOR REPLICATION”.
Datele sunt inserate doar în tabelul Emp. Vă rugăm să consultați imaginea de mai jos.
Aici, în acest caz, declanșatorul nu este dezactivat și este disponibil pentru alte tranzacții de utilizator care vor declanșa declanșatorul atunci când există o INSERTARE. Declanșatorul nu este declanșat numai pentru tranzacția care este executată ca agent de replicare.
Utilizați interogarea de mai jos pentru a verifica dacă declanșatorul dvs. este marcat ca „NOT FOR REPLCIATION” sau nu.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Există și alte modalități de a dezactiva declanșatoarele în SQL Server pentru o sesiune prin manipularea codului cu condiții.
De exemplu, puteți utiliza CONTEXT_INFO() în codul de declanșare și să returnați. Utilizarea CONTEXT_INFO() nu necesită permisiuni speciale.
În acest caz, Dacă CONTEXT_INFO se potrivește cu valoarea specificată în declanșator, declanșatorul revine și nu execută codul de mai jos. Vă rugăm să consultați codul de mai jos.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
. FOR INSERT
AS
BEGIN
SET NOCOUNT on
DECLARE @CONT_INFO VARBINARY(128)
SELECT @CONT_INFO = CONTEXT_INFO()
IF @CONT_INFO = 0x1256698456
RETURN
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
|
În acest caz trebuie să setăm valoarea context_info la 0x1256698456 înainte de a introduce date în tabelul „Emp”.
Următoarele vizualizări de sistem stochează, de asemenea, informațiile de context, dar interogarea directă a acestor vizualizări necesită permisiuni SELECT și VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
În loc de scriptul T-SQL INSERT, să presupunem că datele sunt un fișier .txt sau un fișier .csv. Putem utiliza opțiunile BCP UTILITY sau BULK INSERT pentru a încărca date în tabele fără a declanșa declanșatoarele.
Utilizarea utilitarului BCP
Putem utiliza utilitarul BCP pentru a încărca date în masă în tabele fără a declanșa declanșatoarele în SQL Server. Această metodă va funcționa numai pentru INSERȚII în tabel și nu declanșează declanșatoarele create „for insert” și „instead of insert”.
În mod implicit, utilitarul BCP nu declanșează declanșatoarele la încărcarea datelor în tabele. Pentru a forța executarea declanșatorului, trebuie să folosim -h „FIRE_TRIGGERS” în BCP în timpul încărcării datelor în tabel.
Mai jos sunt tabelele de probă utilizate în acest exemplu.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE USERS
(ID int,
NAME 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
|
Vă rugăm să consultați exemplul de mai jos de BCP implicit pentru a încărca date în tabelul „USERS”, care nu va declanșa declanșatoare în SQL Server. Am mascat serverul original, numele bazei de date și acreditările de conectare.
bcp .dbo.USERS in D:\bcp.txt -T -c -S „SERVERNAME” -Uusername -Ppassword
Vă rugăm să consultați exemplul de mai jos de BCP cu indicația „FIRE_TRIGGERS” pentru a încărca date în tabelul USERS care va declanșa declanșatoare.
bcp .dbo.USERS in D:\bcpcp.txt -T -c -S „SERVERNAME” -Uusername -Ppassword -h „FIRE_TRIGGERS”
Utilizarea BULK INSERT
Această opțiune va funcționa, de asemenea, numai pentru INSERTIRI în tabel și nu va declanșa declanșatoarele create „for insert” și „instead of insert”.
În mod implicit, BULK INSERT nu declanșează declanșatoarele în SQL Server. Putem forța executarea declanșatorului prin specificarea „FIRE_TRIGGERS”
Am un fișier test.txt care conține date cu „,” ca FIELDTERMINATOR.
Vă rugăm să consultați codul de mai jos pentru BULK INSERT implicit pentru a încărca date în tabelul USERS, care nu va declanșa declanșatoare în SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
DIN ‘D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Vă rugăm să consultați codul de mai jos cu indicația „FIRE_TRIGGERS” care va declanșa declanșatoarele în SQL Server la încărcarea datelor din test.fișier txt.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERAȚI UTILIZATORII
DIN ‘D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
În acest articol am discutat comportamentul declanșatorilor în SQL Server pentru cazurile de mai jos.
- Conectați-vă ca utilizator normal.
- Autentificare ca agent de replicare și declanșatorul este setat ca nefiind pentru replicare.
- Utilizarea funcției CONTEXT_INFO în codul declanșatorului.
- Utilizarea BCP implicită și cu indicația „FIRE_TRIGGER”
- Utilizarea BULK INSERT implicită și cu indicația „FIRE_TRIGGER”
Pentru punctele 3, 4 și 5, nu contează dacă creăm declanșatoare cu „NOT FOR REPLICATION” sau nu.
- Autor
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – 24 octombrie, 2019
- Prezentare generală a comenzii Collate SQL – 22 octombrie 2019
- Recuperarea unei parole SA pierdute – 20 septembrie 2019