Denne artikel fokuserer på de forskellige måder at deaktivere triggere i SQL Server på, så de ikke forstyrrer visse operationer som f.eks. bulkindsættelser.
Problem
Vi havde en situation, hvor vi skulle indlæse data i tabellerne i en af vores databaser. Der blev oprettet et par triggere på disse tabeller til indsættelse, opdatering og sletning, og disse triggere er indstillet som “NOT FOR REPLICATION”. Mens vi indlæser data i tabellerne, ønsker vi ikke, at disse triggere skal udløses. Vi tænkte på at deaktivere triggerne på tabellerne, men dette vil også deaktivere dem for andre brugertransaktioner. Vi ønsker, at de ikke skal udløses, men kun for min session, hvorfra jeg udfører scriptet til indlæsning af data. I denne artikel kan du se, hvordan du deaktiverer triggere i SQL Server for en specifik.
Løsning
Vi skal først forstå begrebet “NOT FOR REPLICATION” i SQL Server.
NOT FOR REPLICATION kan bruges, mens du opretter triggere i SQL Server. Dette indikerer, at disse triggere ikke affyres, når replikationsagenten foretager datamodifikationer (INSERT / UPDATE / DELETE) på tabellen.
Ikke kun for triggere i SQL Server, dette hint kan også bruges, mens du opretter fremmednøgler, identitetsspalte og check constraints.
I tilfælde af fremmednøgler sker kontrollen af fremmednøgler kun, når en bruger ændrer data i tabellen, og valideringen af fremmednøgler sker ikke, når replikationsagenten synkroniserer disse ændringer til den anden ende (enten til abonnenten eller både abonnent og udgiver baseret på den konfigurerede replikeringstype)
I tilfælde af identitetsspalter genereres der ikke en ny identitetsværdi, når replikationsagenten indsætter data i tabellen, og den oprindelige identitetsværdi, der genereres ved kilden, anvendes.
Da triggerne i SQL Server blev oprettet med “NOT FOR REPLICATION” på disse tabeller, blev disse triggere udløst, hvis vi indsætter data i tabellen som en normal bruger. Disse triggere blev ikke udløst, når replikationsagenten indsætter, sletter og opdaterer data i tabellerne. Så at foregive som en replikationsagent vil gøre mit arbejde. (Dvs. at triggerne blev affyret for alle brugersessioner og ikke affyret for den specifikke session, som jeg loggede ind som replikationsagent.)
For at illustrere dette vil jeg oprette to eksempeltabeller “Emp” og “EmpJoining” og en trigger på “Emp”-tabellen, som affyres for at indsætte joining dato, når en ny række indsættes i “Emp”-tabellen.
|
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
AS
BEGIN
SET NOCOUNT ON
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
GO
|
Nu har jeg for eksempel medarbejder-id, navn og deres tiltrædelsesdato i et andet system, som skal importeres til ovenstående eksisterende tabeller. I dette tilfælde ønsker jeg ikke, at triggerlogikken skal affyres, når jeg indsætter data i Emp-tabellen.
Dybest set har jeg T-SQL-indsætningsskripter for begge disse tabeller, som indsætter medarbejderens id-navn i “Emp”-tabellen og deres sammenføjningsdatoer i “EmpJoining”-tabellen.
Lad os logge ind som normal bruger og udføre T-SQL-scriptet for at indsætte data i tabellen.
Login som normal bruger:
Åbn SQL Server management studio. Log nu ind på SQL Server som en normal bruger, og brug den database, hvor du har oprettet tabellen. Brug nedenstående script til at indsætte data i Emp-tabellen og kontrollere, om triggeren er udløst eller ej.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
vi kan se, at udløseren er udløst og indsat data i EmpJoining-tabellen.
Vi kan kontrollere sessionsejendommen ved hjælp af nedenstående script.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
det vil returnere nul, hvis vi logger ind som normal bruger, og det returnerer en, hvis vi logger ind som replikationsagent.
Stræk til at logge ind som replikationsagent:
Lad os nu logge ind som replikationsagent og indsætte data ved hjælp af script.
Luk SQL server management studio, og åbn det igen.
Indtast den server, det login og den adgangskode, du vil bruge. klik på indstillinger. Se nedenstående billede.
Navigér til fanen Yderligere forbindelsesparametre. I tabellen for yderligere forbindelsesparametre skal du indtaste REPLICATION=TRUE i tekstboksen som vist på nedenstående billede.
Du kan bruge din foretrukne loginmetode, enten Windows-autentifikation eller SQL Server-autentifikation.
Dette vil logge dig ind som replikationsagent, og enhver DML-operation, du udfører på tabellen, vil blive udført som replikationsagent.
Udfør nu nedenstående forespørgsel for at kontrollere, om du er logget ind som replikationsagent eller ej.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
Det bør returnere 1.
Indsæt nu nogle få rækker ved hjælp af nedenstående script.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Vi kan se, at udløseren ikke er affyret, da vi er logget ind som replikationsagent, og udløseren er indstillet som “NOT FOR REPLCIATION”.
Dataene indsættes kun i Emp-tabellen. Se venligst nedenstående billede.
Her i dette tilfælde er udløseren ikke deaktiveret og tilgængelig for andre brugertransaktioner, som vil udløse udløseren, når der er en INSERT. Triggeren udløses ikke kun for den transaktion, der udføres som replikationsagent.
Brug nedenstående forespørgsel til at kontrollere, om din trigger er markeret som “NOT FOR REPLCIATION” eller ej.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Der er andre måder at deaktivere triggere i SQL Server for en session på ved at håndtere kode med betingelser.
Du kan f.eks. bruge CONTEXT_INFO() i triggerkoden og returnere. Brug af CONTEXT_INFO() kræver ingen særlige tilladelser.
I dette tilfælde, Hvis CONTEXT_INFO stemmer overens med værdien angivet i udløseren, vender udløseren tilbage og udfører ikke nedenstående kode. Se venligst nedenstående kode.
|
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
|
I dette tilfælde skal vi sætte værdien context_info til 0x1256698456, inden vi indsætter data i tabellen “Emp”.
Følgende systemvisninger gemmer også kontekstoplysningerne, men for at spørge direkte i disse visninger kræves der SELECT- og VIEW SERVER STATE-tilladelser.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Lad os i stedet for T-SQL INSERT-script antage, at dataene er .txt-fil eller .csv-fil. Vi kan bruge BCP UTILITY eller BULK INSERT-indstillingerne til at indlæse data i tabeller uden at affyre udløsere.
Brug af BCP utility
Vi kan bruge BCP utility til at indlæse bulkdata i tabellen uden at affyre udløsere i SQL Server. Denne metode virker kun for INSERTS på tabellen og affyrer ikke triggere, der er oprettet “for insert” og “instead of insert”.
Som standard affyrer BCP utility ikke triggere ved indlæsning af data i tabeller. For at fremtvinge udløserudførelse skal vi bruge -h “FIRE_TRIGGERS” i BCP, mens data indlæses i tabellen.
Nedenfor er der eksempler på tabeller, der anvendes i dette eksempel.
|
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)))
SKABEL TABEL USERHIST
(USERID int,
CREATEDDATE datetime )
SKABEL TRIGGER INS_USERS PÅ USERS
FOR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END
|
Se venligst nedenstående eksempel på standard BCP for at indlæse data i tabellen “USERS”, som ikke vil udløse triggere i SQL Server. Jeg har maskeret den oprindelige server, databasenavne og loginoplysninger.
bcp .dbo.USERS i D:\bcp.txt -T -T -c -S “SERVERNAME” -Uusername -Password
Se nedenstående eksempel på BCP med hint “FIRE_TRIGGERS” for at indlæse data i tabellen USERS, som vil affyre triggere.
bcp .dbo.USERS i D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Password -h “FIRE_TRIGGERS”
Brug af BULK INSERT
Denne indstilling virker også kun for INSERTS på tabellen og affyrer ikke triggere, der er oprettet “for insert” og “i stedet for insert”.
Som standard affyrer BULK INSERT ikke triggere i SQL Server. Vi kan tvinge udførelsen af triggeren ved at angive “FIRE_TRIGGERS”
Jeg har test.txt-fil, som data med “,” som FIELDTERMINATOR.
Se nedenstående kode for standard BULK INSERT for at indlæse data i USERS-tabellen, som ikke vil udløse triggere i SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‘D:\\\\test.txt’ –location with filnavn
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Se nedenstående kode med hint “FIRE_TRIGGERS”, som vil affyre triggers i SQL Server, når der indlæses data fra test.txt-fil.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‘D:\\test.txt’ –location with filnavn
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
I denne artikel diskuterede vi opførslen af triggers i SQL Server for nedenstående tilfælde.
- Log ind som normal bruger.
- Log ind som replikationsagent, og triggeren er indstillet som ikke til replikation.
- Brug af CONTEXT_INFO-funktionen i triggerkoden.
- Brug af BCP-standard og med hint “FIRE_TRIGGER”
- Brug af BULK INSERT-standard og med hint “FIRE_TRIGGER”
For punkt 3, 4 og 5 er det ligegyldigt, om vi opretter triggere med “IKKE TIL REPLIKATION” eller ej.
- Author
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databaser – 24. oktober, 2019
- Oversigt over kommandoen Collate SQL – 22. oktober 2019
- Gendanne en tabt SA-adgangskode – 20. september 2019