Ez a cikk az SQL Serverben lévő triggerek letiltásának különböző módjaival foglalkozik, hogy azok ne zavarjanak bizonyos műveleteket, például a tömeges beszúrásokat.
Probléma
Volt egy olyan helyzet, amikor adatokat kellett betöltenünk az egyik adatbázisunk tábláiba. Ezekben a táblákban létrehoztunk néhány trigger-t a beszúráshoz, frissítéshez és törléshez, és ezek a triggerek “NOT FOR REPLICATION”-ként vannak beállítva. Az adatok táblákba való betöltése közben nem szeretnénk, ha ezek a triggerek kioldódnának. Arra gondoltunk, hogy letiltjuk a triggereket a táblákon, de ez letiltja őket más felhasználói tranzakciók számára is. Azt akarjuk, hogy csak az én munkamenetemben ne legyenek kilőve, ahonnan az adatbetöltési szkriptet hajtom végre. Ebben a cikkben lássuk, hogyan lehet letiltani a triggereket az SQL Serverben egy bizonyos.
megoldás
Először meg kell értenünk a “NOT FOR REPLICATION” fogalmát az SQL Serverben.
A NOT FOR REPLICATION az SQL Serverben a triggerek létrehozása során használható. Ez azt jelzi, hogy ezek a triggerek nem lépnek működésbe, amikor a replikációs ügynök adatmódosításokat (INSERT / UPDATE / DELETE) végez a táblán.
Nem csak az SQL Serverben lévő triggereknél, hanem idegen kulcsok, azonossági oszlopok és ellenőrzési korlátozások létrehozásakor is használható ez a súgó.
Az idegen kulcsok esetében az idegen kulcs ellenőrzése csak akkor történik meg, amikor a felhasználó adatokat módosít a táblán, és az idegen kulcsok érvényesítésére nem kerül sor, amikor a replikációs ügynök ezeket a módosításokat szinkronizálja a másik végpontra (vagy az előfizetőre, vagy az előfizetőre és a kiadóra is, a konfigurált replikáció típusától függően)
Az azonosító oszlopok esetében a replikációs ügynök adatainak a táblába való beszúrásakor nem generál új azonosító értéket, hanem az eredeti, a forrásnál generált azonosító értéket használja.
Mivel az SQL Serverben a triggerek “NOT FOR REPLICATION” jelzéssel lettek létrehozva ezekre a táblákra, ha normál felhasználóként adatot szúrunk be a táblába, ezek a triggerek működésbe lépnek. Ezek a ravaszok nem lettek kioldva, amikor a replikációs ügynök beilleszt, töröl és frissít adatokat a táblákban. Tehát úgy teszek, mintha replikációs ügynökként végezném a munkámat. (Vagyis a triggerek minden felhasználói munkamenetre ki lettek lőve, és nem lőttek ki az adott munkamenetre, amelybe replikációs ügynökként jelentkeztem be.)
Ennek szemléltetésére létrehozok két “Emp” és “EmpJoining” mintatáblát és egy trigger-t az “Emp” táblán, amely akkor lő ki, amikor egy új sor kerül beillesztésre az “Emp” táblába.
|
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
|
Most például megvan az alkalmazott azonosítója, neve és csatlakozási dátuma más rendszerben, amit a fenti meglévő táblákba kell importálni. Ebben az esetben nem akarom, hogy a kiváltó logika kioldódjon, amikor az adatokat az Emp táblába illesztem be.
Alapvetően mindkét táblához van T-SQL beszúrási szkriptem, amely beilleszti az alkalmazott azonosítóját, nevét az “Emp” táblába és a csatlakozási dátumukat az “EmpJoining” táblába.
Jelentkezzünk be normál felhasználóként, és hajtsuk végre a T-SQL szkriptet az adatoknak a táblába való beszúrásához.
Login as normal user:
Open SQL Server management studio. Most jelentkezzünk be az SQL Serverre normál felhasználóként, és használjuk azt az adatbázist, ahol a táblát létrehoztuk. Az alábbi szkript segítségével szúrjon be adatokat az Emp táblába, és ellenőrizze, hogy a trigger elsült-e vagy sem.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
láthatjuk, hogy a trigger elsült és beillesztette az adatokat az EmpJoining táblába.
Az alábbi szkript segítségével ellenőrizhetjük a munkamenet tulajdonságot.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
nullát ad vissza, ha normál felhasználóként jelentkezünk be, és egyet, ha replication agentként jelentkezünk be.
Lépések a replikációs ügynökként való bejelentkezéshez:
Most jelentkezzünk be replikációs ügynökként és szkript segítségével illesszünk be adatokat.
Zárjuk be az SQL server management studio-t és nyissuk meg újra.
Adjuk meg a szervert, a bejelentkezést és a jelszót, amit használni szeretnénk. kattintsunk az opciók gombra. Tekintse meg az alábbi képet.
Navigáljon a további kapcsolati paraméterek fülre. A további kapcsolati paraméterek táblázatban írja be a REPLICATION=TRUE szövegmezőbe az alábbi képen látható módon.
Használhatja a kívánt bejelentkezési módszert, akár windows hitelesítés, akár SQL Server hitelesítés.
Ezzel replikációs ügynökként fog bejelentkezni, és minden DML művelet, amelyet a táblán végez, replikációs ügynökként fog végrehajtódni.
Most hajtsa végre az alábbi lekérdezést annak ellenőrzésére, hogy replikációs ügynökként van-e bejelentkezve vagy sem.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
Vissza kell adnia 1.
Most szúrjon be néhány sort az alábbi szkript segítségével.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Láthatjuk, hogy a trigger nem lépett működésbe, mivel replikációs ügynökként jelentkeztünk be, és a trigger “NOT FOR REPLCIATION” értékre van állítva.
Az adatok csak az Emp táblába kerülnek beillesztésre. Kérjük, tekintse meg az alábbi képet.
Itt ebben az esetben a trigger nincs letiltva és elérhető más felhasználói tranzakciók számára, amelyek INSERT esetén tüzelni fogják a triggert. A kiváltó csak a replikációs ügynökként végrehajtott tranzakció esetében nem lép működésbe.
Az alábbi lekérdezéssel ellenőrizheti, hogy a kiváltó “NEM REPLIKÁCIÓRA” van-e jelölve vagy sem.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Az SQL Serverben más módon is letilthatók a triggerek egy munkamenetre vonatkozóan, a kód feltételekkel történő kezelésével.
Például használhatja a CONTEXT_INFO() funkciót a trigger kódban és visszaadhatja. A CONTEXT_INFO() használata nem igényel külön engedélyeket.
Ebben az esetben, ha a CONTEXT_INFO megegyezik a triggerben megadott értékkel, a trigger visszatér, és nem hajtja végre az alábbi kódot. Kérjük, tekintse meg az alábbi kódot.
|
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
|
Ebben az esetben a context_info értékét 0x1256698456-ra kell állítani, mielőtt az “Emp” táblába beszúrnánk az adatokat.
A következő rendszernézetek is tárolják a kontextusinformációkat, de ezek közvetlen lekérdezése SELECT és VIEW SERVER STATE jogosultságokat igényel.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
T-SQL INSERT script helyett tegyük fel, hogy az adatok .txt fájl vagy .csv fájl. Használhatjuk a BCP UTILITY vagy a BULK INSERT opciókat, hogy adatokat töltsünk be a táblákba a triggerek elsütése nélkül.
A BCP segédprogram használata
A BCP segédprogrammal tömeges adatokat tölthetünk be a táblába triggerek elsütése nélkül az SQL Serverben. Ez a módszer csak a táblába történő BESZERZÉS esetén működik, és nem lövi ki a “beszúráshoz” és a “beszúrás helyett” létrehozott triggereket.
Alapértelmezés szerint a BCP segédprogram nem lövi ki a triggereket az adatok táblákba való betöltésekor. A triggerek végrehajtásának kikényszerítéséhez a BCP-ben a -h “FIRE_TRIGGERS” parancsot kell használnunk, miközben adatokat töltünk be a táblába.
Az alábbiakban a példában használt mintatáblákat mutatjuk be.
|
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
|
Kérjük, tekintse meg az alábbi példát az adatok “USERS” táblába való betöltésére szolgáló alapértelmezett BCP-re, amely nem fog triggereket indítani az SQL Serverben. Az eredeti kiszolgálót, az adatbázis nevét és a bejelentkezési adatokat elfedtem.
bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Ppassword
Kérjük, tekintse meg az alábbi példát a “FIRE_TRIGGERS” tippel ellátott BCP-re, amely adatokat tölt be a USERS táblába, ami kiváltja a triggereket.
bcp .dbo.USERS a D:\bcp.txt-ben -T -c -S “SERVERNAME” -Uusername -Ppassword -h “FIRE_TRIGGERS”
A BULK INSERT használata
Ez az opció is csak a táblában végzett INSERTEK esetén fog működni, és nem lövi ki a “for insert” és “instead of insert” létrehozott triggereket.
Alapértelmezés szerint a BULK INSERT nem lövi ki a triggereket az SQL Serverben. A “FIRE_TRIGGERS”
Van egy test.txt fájlom, amely az adatok “,” mint FIELDTERMINATOR.
Kérjük, tekintse meg az alábbi kódot az alapértelmezett BULK INSERT-hez, hogy adatokat töltsön be a USERS táblába, amely nem fog kiváltani triggereket az SQL Serverben.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‘D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Kérjük, tekintse meg az alábbi kódot a “FIRE_TRIGGERS” hivatkozással, amely a tesztből történő adatbetöltéskor az SQL Serverben triggereket indít.txt fájl.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‘D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
Ebben a cikkben az alábbi esetekben tárgyaltuk a triggerek viselkedését az SQL Serverben.
- Bejelentkezés normál felhasználóként.
- Bejelentkezés replikációs ügynökként, és a ravasz nem replikációra van beállítva.
- CONTEXT_INFO függvény használata a ravaszkódban.
- BCP alapértelmezett használata és “FIRE_TRIGGER”
- BULK INSERT alapértelmezett használata és “FIRE_TRIGGER”
A 3., 4. és 5. pontok esetében nem számít, hogy “NOT FOR REPLICATION” típusú triggereket hozunk létre vagy sem.
- Author
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- A Collate SQL parancs áttekintése – 2019. október 22.
- Elveszett SA-jelszó helyreállítása – 2019. szeptember 20.