W tym artykule skupimy się na różnych sposobach wyłączania wyzwalaczy w SQL Server tak, aby nie przeszkadzały w niektórych operacjach, takich jak masowe wstawianie danych.
Problem
Mieliśmy sytuację, w której potrzebowaliśmy załadować dane do tabel w jednej z naszych baz danych. Na tych tabelach zostało utworzonych kilka wyzwalaczy dla wstawiania, aktualizacji i usuwania i te wyzwalacze są ustawione jako „NOT FOR REPLICATION”. Podczas ładowania danych do tabel nie chcemy, aby te wyzwalacze zostały odpalone. Pomyśleliśmy o wyłączeniu wyzwalaczy na tych tabelach, ale to spowoduje wyłączenie ich również dla innych transakcji użytkownika. Chcemy, aby nie były one odpalane tylko dla mojej sesji, z której wykonuję skrypt ładujący dane. W tym artykule zobaczymy jak wyłączyć wyzwalacze w SQL Server dla konkretnego użytkownika.
Rozwiązanie
Musimy najpierw zrozumieć pojęcie „NOT FOR REPLICATION” w SQL Server.
NOT FOR REPLICATION może być użyte podczas tworzenia wyzwalaczy w SQL Server. Oznacza to, że wyzwalacze te nie będą uruchamiane, gdy agent replikacji wykona modyfikacje danych (INSERT / UPDATE / DELETE) na tabeli.
Nie tylko dla wyzwalaczy w SQL Server, ta podpowiedź może być również użyta podczas tworzenia kluczy obcych, kolumn tożsamości i ograniczeń sprawdzających.
W przypadku kluczy obcych, sprawdzanie klucza obcego ma miejsce tylko wtedy, gdy użytkownik modyfikuje dane w tabeli, a sprawdzanie poprawności klucza obcego nie ma miejsca, gdy agent replikacji synchronizuje te modyfikacje do drugiego końca (albo do subskrybenta lub zarówno subskrybenta jak i wydawcy w oparciu o typ skonfigurowanej replikacji)
W przypadku kolumn tożsamości, nowa wartość tożsamości nie jest generowana, gdy agent replikacji wstawia dane do tabeli i używana jest oryginalna wartość tożsamości, która jest generowana u źródła.
Ponieważ wyzwalacze w SQL Server zostały utworzone z „NOT FOR REPLICATION” na tych tabelach, jeśli wstawimy dane do tabeli jako zwykły użytkownik, te wyzwalacze zostały zwolnione. Wyzwalacze te nie były odpalane, gdy agent replikacji wstawia, usuwa i aktualizuje dane na tych tabelach. Tak więc, udawanie agenta replikacji wykona moją pracę. (tzn. wyzwalacze zostały odpalone dla wszystkich sesji użytkownika i nie zostały odpalone dla konkretnej sesji, do której zalogowałem się jako agent replikacji.)
Aby to zilustrować, utworzę dwie przykładowe tabele „Emp” i „EmpJoining” oraz wyzwalacz w tabeli „Emp”, który jest odpalany w celu wstawienia daty złączenia, gdy nowy wiersz jest wstawiany do tabeli „Emp”.
|
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
|
Teraz na przykład, mam id pracownika, nazwę i datę dołączenia w innym systemie, które muszą być zaimportowane do wyżej istniejących tabel. W tym przypadku nie chcę, aby logika wyzwalacza była uruchamiana podczas wstawiania danych do tabeli Emp.
Zasadniczo mam skrypt wstawiania T-SQL dla obu tych tabel, który wstawia nazwę id pracownika w tabeli „Emp” i ich daty dołączenia w tabeli „EmpJoining”.
Zalogujmy się jako zwykły użytkownik i wykonajmy skrypt T-SQL, aby wstawić dane do tabeli.
Logowanie jako zwykły użytkownik:
Otwórz SQL Server Management Studio. Teraz zaloguj się do serwera SQL jako zwykły użytkownik i użyj bazy danych, w której utworzyłeś tabelę. Użyj poniższego skryptu aby wstawić dane do tabeli Emp i sprawdzić czy triger został uruchomiony.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , 'Adam’ )
select * from Emp
select * from EmpJoining
|
widzimy, że wyzwalacz został odpalony i wstawił dane do tabeli EmpJoining.
Możemy sprawdzić właściwość sesji używając poniższego skryptu.
|
1
|
select SESSIONPROPERTY ( 'replication_agent’ )
|
zwróci zero, jeśli zalogujemy się jako zwykły użytkownik i zwróci jeden, jeśli zalogujemy się jako agent replikacji.
Kroki do zalogowania się jako agent replikacji:
Teraz zalogujmy się jako agent replikacji i wstawmy dane za pomocą skryptu.
Zamknij SQL server management studio i otwórz je ponownie.
Wprowadź serwer, login i hasło, którego chcesz użyć. kliknij na opcje. Proszę zapoznać się z poniższym obrazkiem.
Przejdź do zakładki dodatkowe parametry połączenia. W tabeli dodatkowych parametrów połączenia wpisz REPLIKACJA=TRUE w polu tekstowym, jak pokazano na poniższym obrazku.
Możesz użyć preferowanej metody logowania: uwierzytelnianie windows lub uwierzytelnianie SQL Server.
Spowoduje to zalogowanie użytkownika jako agenta replikacji i wszelkie operacje DML wykonywane na tabeli będą wykonywane jako agent replikacji.
Teraz wykonaj poniższe zapytanie, aby sprawdzić, czy jesteś zalogowany jako agent replikacji, czy nie.
|
1
|
select SESSIONPROPERTY ( 'replication_agent’ )
|
Powinno zwrócić 1.
Teraz wstaw kilka wierszy za pomocą poniższego skryptu.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , nazwa ) values ( 2 , 'Greg’ )
select * from Emp
select * from EmpJoining
|
Widzimy, że wyzwalacz nie został odpalony, ponieważ zalogowaliśmy się jako agent replikacji, a wyzwalacz jest ustawiony jako „NOT FOR REPLCIATION”.
Dane są wstawiane tylko do tabeli Emp. Proszę spojrzeć na poniższy obrazek.
W tym przypadku triger nie jest wyłączony i jest dostępny dla innych transakcji użytkownika, które będą wywoływać triger w momencie wykonania INSERT. Triger nie jest uruchamiany tylko dla transakcji, która jest wykonywana jako agent replikacji.
Użyj poniższego zapytania aby sprawdzić czy Twój triger jest oznaczony jako „NIE DO REPLIKACJI” czy nie.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Istnieją inne sposoby wyłączenia wyzwalaczy w SQL Server dla sesji poprzez obsługę kodu z warunkami.
Na przykład możesz użyć CONTEXT_INFO() w kodzie wyzwalacza i zwrócić. Użycie CONTEXT_INFO() nie wymaga żadnych specjalnych uprawnień.
W tym przypadku, jeśli CONTEXT_INFO odpowiada wartości określonej w trigerze, triger zwraca wartość i nie wykonuje poniższego kodu. Proszę odnieść się do poniższego kodu.
|
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
|
W tym przypadku musimy ustawić wartość context_info na 0x1256698456 przed wstawieniem danych do tabeli „Emp”.
Następujące widoki systemowe również przechowują informacje o kontekście, ale bezpośrednie zapytanie do tych widoków wymaga uprawnień SELECT i VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Zamiast skryptu T-SQL INSERT, załóżmy, że dane są w pliku .txt lub .csv. Możemy użyć opcji BCP UTILITY lub BULK INSERT, aby załadować dane do tabel bez odpalania wyzwalaczy.
Użycie narzędzia BCP
Możemy użyć narzędzia BCP, aby załadować dane masowo do tabeli bez odpalania wyzwalaczy w SQL Server. Metoda ta będzie działać tylko dla INSERTS na tabeli i nie spowoduje odpalenia trigerów utworzonych „dla insert” i „zamiast insert”.
Domyślnie narzędzie BCP nie odpala trigerów podczas ładowania danych do tabel. Aby wymusić wykonanie wyzwalacza, należy użyć opcji -h „FIRE_TRIGGERS” w BCP podczas ładowania danych do tabeli.
Poniżej znajdują się przykładowe tabele użyte w tym przykładzie.
|
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
|
Proszę zapoznać się z poniższym przykładem domyślnego BCP do załadowania danych do tabeli „USERS”, który nie spowoduje odpalenia wyzwalaczy w SQL Server. Zamaskowałem oryginalny serwer, nazwy baz danych i poświadczenia logowania.
bcp .dbo.USERS in D:\bcp.txt -T -c -S „SERVERNAME” -Uusername -Ppassword
Proszę odnieść się do poniższego przykładu BCP z podpowiedzią „FIRE_TRIGGERS”, aby załadować dane do tabeli USERS, które będą wywoływać wyzwalacze.
bcp .dbo.USERS in D:bcp.txt -T -c -S „SERVERNAME” -Uusername -Ppassword -h „FIRE_TRIGGERS”
Użycie BULK INSERT
Ta opcja również zadziała tylko dla INSERTY na tabeli i nie spowoduje odpalenia triggerów utworzonych „dla insert” i „zamiast insert”.
Domyślnie BULK INSERT nie powoduje odpalenia triggerów w SQL Server. Możemy wymusić wykonanie wyzwalacza poprzez określenie „FIRE_TRIGGERS”
Mam plik test.txt, który zawiera dane z „,” jako FIELDTERMINATOR.
Proszę odnieść się do poniższego kodu dla domyślnego BULK INSERT do załadowania danych do tabeli USERS, który nie spowoduje odpalenia wyzwalacza w 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
|
Proszę zapoznać się z poniższym kodem z podpowiedzią „FIRE_TRIGGERS”, który spowoduje odpalenie triggerów w SQL Server podczas ładowania danych z pliku test.plik txt.
|
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
|
W tym artykule omówiliśmy zachowanie wyzwalaczy w SQL Server dla poniższych przypadków.
- Zaloguj się jako zwykły użytkownik.
- Zaloguj się jako agent replikacji i ustaw triger jako nie dla replikacji.
- Użycie funkcji CONTEXT_INFO w kodzie trigera.
- Użycie BCP default i z podpowiedzią „FIRE_TRIGGER”
- Użycie BULK INSERT default i z podpowiedzią „FIRE_TRIGGER”
Dla punktów 3, 4 i 5 nie ma znaczenia czy tworzymy wyzwalacze z „NOT FOR REPLICATION” czy nie.
- Autor
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Przegląd polecenia Collate SQL – 22 października 2019
- Odzyskiwanie utraconego hasła SA – 20 września 2019
.