Dieser Artikel befasst sich mit den verschiedenen Möglichkeiten, Trigger in SQL Server zu deaktivieren, damit sie bestimmte Vorgänge wie Masseneinfügungen nicht behindern.
Problem
Wir hatten eine Situation, in der wir Daten in die Tabellen in einer unserer Datenbanken laden mussten. Für diese Tabellen wurden einige Trigger zum Einfügen, Aktualisieren und Löschen erstellt, und diese Trigger sind als „NOT FOR REPLICATION“ eingestellt. Während des Ladens von Daten in die Tabellen wollen wir nicht, dass diese Trigger ausgelöst werden. Wir dachten daran, die Trigger für die Tabellen zu deaktivieren, aber das würde sie auch für andere Benutzertransaktionen deaktivieren. Wir wollen, dass sie nicht nur für meine Sitzung ausgelöst werden, von der aus ich das Datenladeskript ausführe. In diesem Artikel erfahren Sie, wie Sie Trigger in SQL Server für einen bestimmten Benutzer deaktivieren können.
Lösung
Zuerst müssen wir das Konzept „NOT FOR REPLICATION“ in SQL Server verstehen.
NOT FOR REPLICATION kann beim Erstellen von Triggern in SQL Server verwendet werden. Dies bedeutet, dass diese Trigger nicht ausgelöst werden, wenn der Replikationsagent Datenänderungen (INSERT / UPDATE / DELETE) an der Tabelle vornimmt.
Dieser Hinweis kann nicht nur für Trigger in SQL Server, sondern auch bei der Erstellung von Fremdschlüsseln, Identitätsspalten und Prüfeinschränkungen verwendet werden.
Im Falle von Fremdschlüsseln erfolgt die Fremdschlüsselprüfung nur, wenn ein Benutzer Daten in der Tabelle ändert, und die Fremdschlüsselüberprüfung erfolgt nicht, wenn der Replikationsagent diese Änderungen mit dem anderen Ende synchronisiert (entweder mit dem Abonnenten oder sowohl mit dem Abonnenten als auch mit dem Herausgeber, je nach konfiguriertem Replikationstyp)
Im Falle von Identitätsspalten wird kein neuer Identitätswert generiert, wenn der Replikationsagent Daten in die Tabelle einfügt, und der ursprüngliche Identitätswert, der an der Quelle generiert wird, wird verwendet.
Da die Trigger in SQL Server mit „NOT FOR REPLICATION“ für diese Tabellen erstellt wurden, wurden diese Trigger ausgelöst, wenn wir als normaler Benutzer Daten in die Tabelle einfügen. Diese Trigger wurden nicht ausgelöst, wenn der Replikationsagent Daten in die Tabellen einfügt, löscht oder aktualisiert. Wenn ich also so tue, als wäre ich ein Replikationsagent, kann ich meine Arbeit machen. (d.h. die Trigger wurden für alle Benutzersitzungen abgefeuert und nicht für die spezifische Sitzung, in der ich mich als Replikationsagent angemeldet habe.)
Um dies zu veranschaulichen, werde ich zwei Beispieltabellen „Emp“ und „EmpJoining“ und einen Trigger für die Tabelle „Emp“ erstellen, der abgefeuert wird, um das Beitrittsdatum einzufügen, wenn eine neue Zeile in die Tabelle „Emp“ eingefügt wird.
|
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
|
Nun habe ich zum Beispiel die Mitarbeiter-ID, den Namen und das Beitrittsdatum in einem anderen System, das in die oben genannten Tabellen importiert werden muss. In diesem Fall möchte ich nicht, dass die Triggerlogik ausgelöst wird, wenn ich Daten in die Tabelle „Emp“ einfüge.
Im Grunde habe ich ein T-SQL-Einfügungsskript für beide Tabellen, das die Mitarbeiter-ID, den Namen und das Beitrittsdatum in die Tabelle „EmpJoining“ einfügt.
Melden Sie sich als normaler Benutzer an und führen Sie das T-SQL-Skript aus, um Daten in die Tabelle einzufügen.
Melden Sie sich als normaler Benutzer an:
Öffnen Sie SQL Server Management Studio. Melden Sie sich nun als normaler Benutzer am SQL Server an und verwenden Sie die Datenbank, in der Sie die Tabelle erstellt haben. Verwenden Sie das folgende Skript, um Daten in die Tabelle Emp einzufügen und zu prüfen, ob der Trigger ausgelöst wurde oder nicht.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‚Adam‘ )
select * from Emp
select * from EmpJoining
|
wir sehen, dass der Trigger ausgelöst wurde und Daten in die Tabelle EmpJoining eingefügt wurden.
Wir können die Sitzungseigenschaft mit dem folgenden Skript überprüfen.
|
1
|
select SESSIONPROPERTY ( ‚replication_agent‘ )
|
Es gibt null zurück, wenn wir uns als normaler Benutzer anmelden und es gibt eins zurück, wenn wir uns als Replikationsagent anmelden.
Schritte zur Anmeldung als Replikationsagent:
Jetzt melden wir uns als Replikationsagent an und fügen Daten mithilfe eines Skripts ein.
Schließen Sie SQL Server Management Studio und öffnen Sie es erneut.
Geben Sie den Server, das Login und das Passwort ein, das Sie verwenden möchten. Klicken Sie auf Optionen. Siehe Abbildung unten.
Navigieren Sie zur Registerkarte Zusätzliche Verbindungsparameter. In der Tabelle der zusätzlichen Verbindungsparameter geben Sie REPLICATION=TRUE in das Textfeld ein, wie in der folgenden Abbildung gezeigt.
Sie können Ihre bevorzugte Anmeldemethode verwenden, entweder die Windows-Authentifizierung oder die SQL Server-Authentifizierung.
Dadurch werden Sie als Replikationsagent angemeldet und jede DML-Operation, die Sie an einer Tabelle durchführen, wird als Replikationsagent ausgeführt.
Führen Sie nun die folgende Abfrage aus, um zu prüfen, ob Sie als Replikationsagent angemeldet sind oder nicht.
|
1
|
select SESSIONPROPERTY ( ‚replication_agent‘ )
|
Sie sollte 1 zurückgeben.
Fügen Sie nun einige Zeilen mit dem folgenden Skript ein.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‚Greg‘ )
select * from Emp
select * from EmpJoining
|
Wir können sehen, dass der Trigger nicht ausgelöst wird, da wir uns als Replikationsagent angemeldet haben und der Trigger als „NOT FOR REPLCIATION“ eingestellt ist.
Die Daten werden nur in die Tabelle Emp eingefügt.
In diesem Fall ist der Trigger nicht deaktiviert und für andere Benutzertransaktionen verfügbar, die den Trigger auslösen, wenn ein INSERT erfolgt. Der Trigger wird nicht nur für die Transaktion ausgelöst, die als Replikationsagent ausgeführt wird.
Verwenden Sie die folgende Abfrage, um zu überprüfen, ob Ihr Trigger als „NOT FOR REPLCIATION“ markiert ist oder nicht.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Es gibt noch andere Möglichkeiten, Trigger in SQL Server für eine Sitzung zu deaktivieren, indem man Code mit Bedingungen behandelt.
Sie können zum Beispiel CONTEXT_INFO() im Triggercode verwenden und zurückgeben. Die Verwendung von CONTEXT_INFO() erfordert keine besonderen Berechtigungen.
Wenn in diesem Fall CONTEXT_INFO mit dem im Trigger angegebenen Wert übereinstimmt, kehrt der Trigger zurück und führt den nachfolgenden Code nicht aus. Bitte beachten Sie den folgenden 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 inserted
END
|
In diesem Fall müssen wir den Wert context_info auf 0x1256698456 setzen, bevor wir Daten in die Tabelle „Emp“ einfügen.
Die folgenden Systemansichten speichern ebenfalls die Kontextinformationen, aber die direkte Abfrage dieser Ansichten erfordert SELECT- und VIEW SERVER STATE-Berechtigungen.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Anstelle eines T-SQL INSERT-Skripts können wir davon ausgehen, dass die Daten als .txt- oder .csv-Datei vorliegen. Wir können die Optionen BCP UTILITY oder BULK INSERT verwenden, um Daten in Tabellen zu laden, ohne die Trigger auszulösen.
Verwendung des BCP-Utility
Wir können das BCP-Utility verwenden, um Massendaten in Tabellen zu laden, ohne Trigger in SQL Server auszulösen. Diese Methode funktioniert nur für INSERTS in der Tabelle und löst keine Trigger aus, die „for insert“ und „instead of insert“ erstellt wurden.
Standardmäßig löst das BCP-Dienstprogramm keine Trigger beim Laden von Daten in Tabellen aus. Um die Ausführung von Triggern zu erzwingen, sollten Sie -h „FIRE_TRIGGERS“ in BCP verwenden, während Sie Daten in eine Tabelle laden.
Nachfolgend finden Sie die in diesem Beispiel verwendeten Beispieltabellen.
|
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
|
Bitte sehen Sie sich das folgende Beispiel eines Standard-BCP an, um Daten in die Tabelle „USERS“ zu laden, die keine Trigger in SQL Server auslösen. Ich habe den ursprünglichen Server, die Datenbanknamen und die Anmeldeinformationen maskiert.
bcp .dbo.USERS in D:\bcp.txt -T -c -S „SERVERNAME“ -Uusername -Ppassword
Bitte beachten Sie das folgende Beispiel für BCP mit dem Hinweis „FIRE_TRIGGERS“, um Daten in die Tabelle USERS zu laden, die Trigger auslösen.
bcp .dbo.USERS in D:\bcp.txt -T -c -S „SERVERNAME“ -Uusername -Ppassword -h „FIRE_TRIGGERS“
Verwendung von BULK INSERT
Auch diese Option funktioniert nur für INSERTS in der Tabelle und löst keine Trigger aus, die „for insert“ und „instead of insert“ erstellt wurden.
Standardmäßig löst BULK INSERT keine Trigger in SQL Server aus. Wir können die Ausführung von Triggern erzwingen, indem wir „FIRE_TRIGGERS“
Ich habe die Datei test.txt, die Daten mit „,“ als FIELDTERMINATOR enthält.
Bitte sehen Sie sich den folgenden Code für standardmäßiges BULK INSERT an, um Daten in die Tabelle USERS zu laden, die keine Trigger in SQL Server auslösen.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‚D:\\txt.txt‘ –location with filename
WITH
(
FIELDTERMINATOR = ‚,‘,
ROWTERMINATOR = ‚\n‘
)
GO
|
Bitte beachten Sie den folgenden Code mit dem Hinweis „FIRE_TRIGGERS“, der Trigger in SQL Server auslöst, wenn Daten aus test.txt-Datei.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‚D:\\txt.txt‘ –location with filename
WITH
(
FIELDTERMINATOR = ‚,‘,
ROWTERMINATOR = ‚\n‘,
FIRE_TRIGGERS
)
GO
|
In diesem Artikel haben wir das Verhalten von Triggern in SQL Server für folgende Fälle diskutiert.
- Anmeldung als normaler Benutzer.
- Anmeldung als Replikationsagent und Trigger ist als nicht für die Replikation eingestellt.
- Verwendung der Funktion CONTEXT_INFO im Triggercode.
- Verwendung von BCP-Standard und mit Hinweis „FIRE_TRIGGER“
- Verwendung von BULK INSERT-Standard und mit Hinweis „FIRE_TRIGGER“
Für die Punkte 3, 4 und 5 ist es egal, ob wir Trigger mit „NOT FOR REPLICATION“ erstellen oder nicht.
- Autor
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Überblick über den Befehl Collate SQL – 22. Oktober 2019
- Wiederherstellen eines verlorenen SA-Kennworts – 20. September 2019