Denna artikel kommer att fokusera på de olika sätten att inaktivera triggers i SQL Server så att de inte stör vissa operationer, t.ex. massinsättningar.
Problem
Vi hade en situation där vi behövde ladda in data till tabellerna i en av våra databaser. Det har skapats några triggers på dessa tabeller för insättning, uppdatering och borttagning och dessa triggers är inställda som ”NOT FOR REPLICATION”. När vi laddar data till tabellerna vill vi inte att dessa triggers ska aktiveras. Vi funderade på att inaktivera triggers på tabellerna, men detta kommer att inaktivera dem även för andra användartransaktioner. Vi vill att de inte ska utlösas endast för min session från vilken jag utför skriptet för dataladdning. I den här artikeln ska vi se hur man inaktiverar triggers i SQL Server för en specifik.
Lösning
Vi måste först förstå begreppet ”NOT FOR REPLICATION” i SQL Server.
NOT FOR REPLICATION kan användas när man skapar triggers i SQL Server. Detta indikerar att dessa triggers inte utlöses när replikeringsagenten gör datamodifieringar (INSERT / UPDATE / DELETE) på tabellen.
Inte bara för triggers i SQL Server, detta tips kan också användas när man skapar utländska nycklar, identitetskolumner och check constraints.
När det gäller främmande nycklar sker kontrollen av främmande nycklar endast när en användare ändrar data i tabellen och valideringen av främmande nycklar sker inte när replikeringsagenten synkroniserar dessa ändringar till den andra änden (antingen till abonnenten eller både abonnenten och utgivaren beroende på vilken typ av replikering som konfigurerats)
När det gäller identitetskolumner genereras inget nytt identitetsvärde när replikeringsagenten lägger in data i tabellen utan det ursprungliga identitetsvärdet som genereras vid källan används.
Eftersom triggers i SQL Server skapades med ”NOT FOR REPLICATION” på dessa tabeller, om vi lägger in data i tabellen som en normal användare, så utlöses dessa triggers. Dessa triggers utlöses inte när replikeringsagenten lägger in, tar bort och uppdaterar data i tabellerna. Så att låtsas som en replikeringsagent kommer att göra mitt jobb. (D.v.s. triggers avfyrades för alla användarsessioner och avfyrades inte för specifik session som jag loggade in som replikeringsagent.)
För att illustrera detta kommer jag att skapa två exempeltabeller ”Emp” och ”EmpJoining” och en trigger på tabellen ”Emp” som avfyras för att infoga datum för anslutning när en ny rad infogas i tabellen ”Emp”.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , name varchar (50) )
GO
SKAPA TABELLEN 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
|
Nu har jag t.ex. den anställdes id, namn och anslutningsdatum i ett annat system som måste importeras till ovanstående befintliga tabeller. I det här fallet vill jag inte att utlösarlogiken ska utlösas när jag lägger in data i tabellen Emp.
I grund och botten har jag ett T-SQL-insättningsskript för båda dessa tabeller som lägger in medarbetarens id-namn i tabellen ”Emp” och deras sammanfogningsdatum i tabellen ”EmpJoining”.
Låt oss logga in som normalanvändare och exekvera T-SQL-skriptet för att lägga in data i tabellen.
Logga in som normalanvändare:
Öppna SQL Server management studio. Logga nu in på SQL Server som en normal användare och använd databasen där du skapade tabellen. Använd nedanstående skript för att infoga data i tabellen Emp och kontrollera om utlösaren utlöses eller inte.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ’Adam’ )
select * from Emp
select * from EmpJoining
|
Vi kan se att utlösaren utlöses och att data läggs in i tabellen EmpJoining.
Vi kan kontrollera sessionens egenskap med hjälp av nedanstående skript.
|
1
|
select SESSIONPROPERTY ( ”replication_agent” )
|
det kommer att återge noll om vi loggar in som normal användare och det återger ett om vi loggar in som replikationsagent.
Steg för att logga in som replikationsagent:
Låt oss nu logga in som replikationsagent och infoga data med hjälp av skriptet.
Stäng SQL server management studio och öppna den igen.
Ange server, inloggning och lösenord som du vill använda. klicka på alternativ. Se bilden nedan.
Navigera till fliken ytterligare anslutningsparametrar. I tabellen för ytterligare anslutningsparametrar anger du REPLICATION=TRUE i textrutan enligt bilden nedan.
Du kan använda den inloggningsmetod som du föredrar, antingen Windows-autentisering eller SQL Server-autentisering.
Detta kommer att logga in dig som replikeringsagent och alla DML-operationer som du utför på en tabell kommer att utföras som replikeringsagent.
Utför nu nedanstående fråga för att kontrollera om du är inloggad som replikationsagent eller inte.
|
1
|
select SESSIONPROPERTY ( ’replication_agent’ )
|
Den ska ge 1.
Infoga nu några rader med hjälp av nedanstående skript.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ’Greg’ )
select * from Emp
select * from EmpJoining
|
Vi kan se att utlösaren inte utlöses eftersom vi loggade in som replikeringsagent och utlösaren är inställd som ”NOT FOR REPLCIATION”.
Uppgifterna läggs in i endast tabellen Emp. Se nedanstående bild.
I det här fallet är utlösaren inte inaktiverad och tillgänglig för andra användartransaktioner som utlöser utlösaren när det finns en INSERT. Utlösaren utlöses inte bara för den transaktion som utförs som replikeringsagent.
Använd nedanstående fråga för att kontrollera om din utlösare är markerad som ”NOT FOR REPLCIATION” eller inte.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Det finns andra sätt att inaktivera triggers i SQL Server för en session genom att hantera kod med villkor.
Du kan till exempel använda CONTEXT_INFO() i utlösarkoden och returnera. Att använda CONTEXT_INFO() kräver inga särskilda behörigheter.
Om CONTEXT_INFO matchar det värde som anges i utlösaren returnerar utlösaren i det här fallet och utför inte koden nedan. Se nedanstående kod.
|
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 det här fallet måste vi ställa in värdet för context_info till 0x1256698456 innan vi lägger in data i tabellen ”Emp”.
Följande systemvyer lagrar också kontextinformationen, men för att söka direkt i dessa vyer krävs behörigheter för SELECT och VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Istället för ett T-SQL INSERT-skript kan vi anta att data är en .txt-fil eller .csv-fil. Vi kan använda alternativen BCP UTILITY eller BULK INSERT för att läsa in data i tabeller utan att utlösa triggers.
Användning av BCP-verktyg
Vi kan använda BCP-verktyget för att läsa in bulkdata i tabeller utan att utlösa triggers i SQL Server. Den här metoden fungerar endast för INSERTS i tabellen och utlöser inte triggers som skapats ”for insert” och ”instead of insert”.
Som standard utlöser inte BCP-verktyget triggers vid inläsning av data i tabeller. För att tvinga fram utlösaren bör vi använda -h ”FIRE_TRIGGERS” i BCP när vi laddar in data till en tabell.
Nedan finns exempeltabeller som används i det här exemplet.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SKAPA TABELLEN USERS
(ID int,
NAME varchar(50))
SKAPA TABELLEN USERHIST
(USERID int,
CREATEDDATE datetime )
SKAPA TRIGGER INS_USERS PÅ USERS
FÖR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END
|
Se nedanstående exempel på standard BCP för att läsa in data i tabellen ”USERS”, vilket inte kommer att utlösa triggers i SQL Server. Jag har maskerat den ursprungliga servern, databasnamnen och inloggningsuppgifterna.
bcp .dbo.USERS in D:\bcp.txt -T -c -S ”SERVERNAME” -Uanvändarnamn -Password
Se nedanstående exempel på en BCP med ledtråd ”FIRE_TRIGGERS” för att läsa in data i tabellen USERS, vilket kommer att utlösa triggers.
bcp .dbo.USERS i D:\bcp.txt -T -c -S ”SERVERNAME” -Uusername -Password -h ”FIRE_TRIGGERS”
Användning av BULK INSERT
Det här alternativet fungerar bara för INSERTS i tabellen och utlöser inte utlösare som skapats ”för insättning” och ”i stället för insättning”.
Som standard utlöser BULK INSERT inte utlösare i SQL Server. Vi kan tvinga fram utförandet av triggern genom att ange ”FIRE_TRIGGERS”
Jag har filen test.txt som innehåller data med ”,” som FIELDTERMINATOR.
Se nedanstående kod för standard BULK INSERT för att läsa in data i tabellen USERS, vilket inte kommer att utlösa triggers i SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ’D:\\\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ’,’,
ROWTERMINATOR = ’\n’
)
GO
|
Se nedanstående kod med ledtråden ”FIRE_TRIGGERS” som utlöser triggers i SQL Server när data laddas från testet.txt-fil.
|
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
|
I den här artikeln har vi diskuterat beteendet hos triggers i SQL Server för följande fall.
- Logga in som vanlig användare.
- Logga in som replikeringsagent och utlösaren är inställd som inte för replikering.
- Användning av CONTEXT_INFO-funktionen i utlösarkoden.
- Användning av BCP standard och med hint ”FIRE_TRIGGER”
- Användning av BULK INSERT standard och med hint ”FIRE_TRIGGER”
För punkterna 3, 4 och 5 spelar det ingen roll om vi skapar utlösare med ”NOT FOR REPLICATION” eller inte.
- Author
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Översikt över kommandot Collate SQL – 22 oktober 2019
- Återställ ett förlorat SA-lösenord – 20 september 2019