Dit artikel gaat over de verschillende manieren om triggers in SQL Server uit te schakelen, zodat ze bepaalde bewerkingen, zoals bulkinserts, niet hinderen.
Probleem
We hadden een situatie waarin we gegevens moesten laden in de tabellen in een van onze databases. Er waren een paar triggers gemaakt op deze tabellen voor insert, update en delete en deze triggers zijn ingesteld als “NOT FOR REPLICATION”. Tijdens het laden van data in de tabellen willen we niet dat deze triggers afgevuurd worden. We dachten eraan om de triggers op de tabellen uit te schakelen, maar dit zal ze ook uitschakelen voor andere gebruikerstransacties. We willen dat ze niet alleen worden geactiveerd voor mijn sessie van waaruit ik het data laad script uitvoer. In dit artikel laten we zien hoe we triggers in SQL Server kunnen uitschakelen voor een specifieke.
Oplossing
We moeten eerst het “NOT FOR REPLICATION” concept in SQL Server begrijpen.
NOT FOR REPLICATION kan worden gebruikt tijdens het maken van triggers in SQL Server. Dit geeft aan dat deze triggers niet worden afgevuurd wanneer replicatie agent gegevenswijzigingen (INSERT / UPDATE / DELETE) op de tabel doen.
Niet alleen voor triggers in SQL Server, kan deze hint ook worden gebruikt tijdens het maken van foreign keys, identiteit kolommen en controle constraints.
In het geval van vreemde sleutels, de vreemde sleutel controle gebeurt alleen wanneer een gebruiker gegevens wijzigen op de tabel en vreemde sleutel validatie gebeurt niet wanneer de replicatie agent synchroniseert deze wijzigingen aan de andere kant (hetzij naar abonnee of zowel abonnee en uitgever op basis van het type van replicatie geconfigureerd)
In het geval van identiteit kolommen, een nieuwe identiteit waarde wordt niet gegenereerd wanneer replicatie agent gegevens invoegen in de tabel en de oorspronkelijke identiteit waarde die is gegenereerd bij de bron wordt gebruikt.
Omdat de triggers in SQL Server zijn aangemaakt met “NOT FOR REPLICATION” op deze tabellen, werden deze triggers afgevuurd wanneer we als een normale gebruiker gegevens in de tabel invoegden. Deze triggers werden niet gevuurd wanneer de replicatie agent gegevens in de tabellen invoegt, verwijdert en bijwerkt. Dus, doen alsof ik een replicatie agent ben zal mijn werk doen. (D.w.z. de triggers werden afgevuurd voor alle gebruikers sessies en niet afgevuurd voor specifieke sessie die ik ingelogd als replicatie agent.)
Om dit te illustreren, zal ik twee voorbeeld tabellen maken “Emp” en “EmpJoining” en een trigger op “Emp” tabel die wordt afgevuurd om de datum in te voegen wanneer een nieuwe rij wordt ingevoegd in “Emp” tabel.
|
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 ingevoegd
END
GO
|
Nu heb ik bijvoorbeeld de werknemer id, naam en hun toetredingsdatum in een ander systeem dat moet worden geïmporteerd in bovenstaande bestaande tabellen. In dit geval wil ik niet dat de trigger-logica wordt afgevuurd wanneer ik gegevens invoeg in de tabel Emp.
In principe heb ik T-SQL insert script voor beide tabellen die werknemer id naam invoegt in “Emp” tabel en hun toetredingsdata in “EmpJoining” tabel.
Laten we ons aanmelden als normale gebruiker en T-SQL script uitvoeren om gegevens in de tabel in te voegen.
Inloggen als normale gebruiker:
Open SQL Server Management Studio. Log nu in op de SQL Server als een normale gebruiker en gebruik de database waar u de tabel heeft aangemaakt. Gebruik onderstaand script om gegevens in te voegen in Emp tabel en controleer of trigger is afgevuurd of niet.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
we kunnen zien dat de trigger is afgevuurd en gegevens in de tabel EmpJoining zijn ingevoegd.
We kunnen de sessie-eigenschap controleren met behulp van het onderstaande script.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
het zal nul teruggeven als we inloggen als normale gebruiker en het zal één teruggeven als we inloggen als replication agent.
Stappen om in te loggen als replicatie agent:
Laten we nu inloggen als replicatie agent en gegevens invoegen met behulp van script.
Sluit SQL server management studio en heropen het.
Voer server, login, en wachtwoord in die u wilt gebruiken. klik op opties. Zie de onderstaande afbeelding.
Navigeer naar het tabblad aanvullende verbindingsparameters. In de tabel met aanvullende verbindingsparameters voert u in het tekstvak REPLICATION=TRUE in, zoals in de onderstaande afbeelding wordt weergegeven.
U kunt de door u gewenste inlogmethode gebruiken: Windows-authenticatie of SQL Server-authenticatie.
Hierdoor wordt u ingelogd als replicatie-agent en alle DML-bewerkingen die u op een tabel uitvoert, zullen worden uitgevoerd als replicatie-agent.
Voer nu onderstaande query uit om te controleren of u bent ingelogd als replicatieagent of niet.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
Het moet 1 teruggeven.
Voeg nu enkele rijen in met behulp van het onderstaande script.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , naam ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
We kunnen zien dat de trigger niet is afgevuurd omdat we ingelogd zijn als replicatie agent en trigger is ingesteld als “NOT FOR REPLCIATION”.
De gegevens worden alleen in Emp tabel ingevoegd. Zie de afbeelding hieronder.
In dit geval is de trigger niet uitgeschakeld en beschikbaar voor andere gebruikerstransacties die de trigger afvuren als er een INSERT is. De trigger wordt niet alleen afgevuurd voor de transactie die wordt uitgevoerd als replicatie agent.
Gebruik onderstaande query om te controleren of uw trigger is gemarkeerd als “NOT FOR REPLCIATION” of niet.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Er zijn andere manieren om triggers in SQL Server voor een sessie uit te schakelen door code met voorwaarden te hanteren.
U kunt bijvoorbeeld CONTEXT_INFO() in de triggercode gebruiken en terugsturen. Het gebruik van CONTEXT_INFO() vereist geen speciale permissies.
In dit geval, als de CONTEXT_INFO overeenkomt met de waarde die in de trigger is gespecificeerd, komt de trigger terug en wordt onderstaande code niet uitgevoerd. Zie de onderstaande code.
|
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 ingevoegd
END
|
In dit geval moeten we de context_info waarde op 0x1256698456 zetten voordat we gegevens invoegen in de tabel “Emp”.
De volgende systeemweergaven slaan ook de contextinformatie op, maar om deze weergaven rechtstreeks op te vragen zijn SELECT- en VIEW SERVER STATE-machtigingen nodig.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
In plaats van T-SQL INSERT script, laten we aannemen dat de gegevens een .txt bestand of .csv bestand is. We kunnen BCP UTILITY of BULK INSERT opties gebruiken om gegevens in tabellen te laden zonder de triggers af te vuren.
Gebruik BCP utility
We kunnen BCP utility gebruiken om bulk gegevens in tabellen te laden zonder triggers af te vuren in SQL Server. Deze methode zal alleen werken voor INSERTS op de tabel en niet vuren triggers gemaakt “voor insert” en “in plaats van insert”.
Standaard, BCP-hulpprogramma niet vuren triggers bij het laden van gegevens in tabellen. Om de uitvoering van triggers te forceren, moeten we -h “FIRE_TRIGGERS” in BCP gebruiken tijdens het laden van gegevens in tabellen.
Hieronder staan voorbeeldtabellen die in dit voorbeeld zijn gebruikt.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE USERS
(ID int,
NAAM 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
|
Zie het onderstaande voorbeeld van standaard BCP om gegevens in de tabel “USERS” te laden die geen triggers in SQL Server zal activeren. Ik heb de originele server, database namen en login credentials gemaskeerd.
bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Password
Zie het onderstaande voorbeeld van BCP met hint “FIRE_TRIGGERS” om gegevens in USERS tabel te laden die triggers zal afvuren.
bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Password -h “FIRE_TRIGGERS”
BULK INSERT
Deze optie werkt ook alleen voor INSERTS op de tabel en vuurt geen triggers af die “for insert” en “instead of insert” zijn aangemaakt.
Standaard vuurt BULK INSERT geen triggers af in SQL Server. We kunnen de uitvoering van de trigger te forceren door het specificeren van “FIRE_TRIGGERS”
Ik heb test.txt bestand dat als gegevens met “,” als FIELDTERMINATOR.
Raadpleeg de onderstaande code voor standaard BULK INSERT om gegevens te laden in USERS tabel die niet zal vuren triggers in 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
|
Zie de onderstaande code met hint “FIRE_TRIGGERS” die triggers in SQL Server zal doen afgaan bij het laden van gegevens uit test.txt bestand.
|
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
|
In dit artikel hebben we het gedrag van triggers in SQL Server besproken voor onderstaande gevallen.
- Inloggen als normale gebruiker.
- Inloggen als replicatie agent en trigger is ingesteld als niet voor replicatie.
- Met behulp van CONTEXT_INFO functie in de trigger code.
- Gebruik BCP default en met hint “FIRE_TRIGGER”
- Gebruik BULK INSERT default en met hint “FIRE_TRIGGER”
Voor punten 3, 4 en 5 maakt het niet uit of we triggers maken met “NOT FOR REPLICATION” of niet.
- Auteur
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – 24 oktober, 2019
- Overzicht van het Collate SQL commando – 22 oktober 2019
- Herstel een verloren SA wachtwoord – 20 september 2019