Tento článek se zaměří na různé způsoby zakázání spouštěčů v SQL Serveru, aby nebránily určitým operacím, například hromadnému vkládání.
Problém
Nastala situace, kdy potřebujeme načíst data do tabulek v jedné z našich databází. V těchto tabulkách bylo vytvořeno několik triggerů pro insert, update a delete a tyto triggery jsou nastaveny jako „NOT FOR REPLICATION“. Při načítání dat do tabulek nechceme, aby se tyto spouštěče spouštěly. Přemýšleli jsme o tom, že bychom tyto triggery v tabulkách zakázali, ale tím bychom je zakázali i pro ostatní uživatelské transakce. Chceme, aby se nevyvolávaly pouze pro mou relaci, ze které provádím skript pro načítání dat. V tomto článku se podíváme, jak zakázat spouštěče v SQL Serveru pro konkrétní.
Řešení
Nejprve musíme pochopit pojem „NOT FOR REPLICATION“ v SQL Serveru.
NOT FOR REPLICATION lze použít při vytváření spouštěčů v SQL Serveru. To znamená, že tyto triggery nebudou spuštěny, když replikační agent provede modifikaci dat (INSERT / UPDATE / DELETE) v tabulce.
Nejen pro triggery v SQL Serveru, tuto nápovědu lze použít také při vytváření cizích klíčů, sloupců identity a kontrolních omezení.
V případě cizích klíčů probíhá kontrola cizího klíče pouze tehdy, když uživatel modifikuje data v tabulce, a k ověření cizího klíče nedochází, když replikační agent synchronizuje tyto modifikace na druhou stranu (buď k odběrateli, nebo k odběrateli i vydavateli podle nakonfigurovaného typu replikace)
V případě sloupců identity se při vkládání dat do tabulky replikačním agentem negeneruje nová hodnota identity a použije se původní hodnota identity, která je generována u zdroje.
Vzhledem k tomu, že spouštěče v SQL Serveru byly na těchto tabulkách vytvořeny s příznakem „NOT FOR REPLICATION“, pokud jsme do tabulky vložili data jako běžný uživatel, byly tyto spouštěče spuštěny. Při vkládání, mazání a aktualizaci dat v tabulkách agentem replikace nebyly tyto spouštěče spuštěny. Takže předstírat, že jako replikační agent udělám svou práci. (Tj. spouštěče byly vyvolány pro všechny uživatelské relace a nebyly vyvolány pro konkrétní relaci, do které jsem se přihlásil jako replikační agent.)
Pro ilustraci vytvořím dvě ukázkové tabulky „Emp“ a „EmpJoining“ a spouštěč na tabulce „Emp“, který je vyvolán pro vložení data spojení při vložení nového řádku do tabulky „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
|
Nyní mám například v jiném systému id zaměstnance, jméno a datum jeho nástupu, které je třeba importovat do výše uvedených existujících tabulek. V tomto případě nechci, aby se při vkládání dat do tabulky Emp spustila spouštěcí logika.
V podstatě mám pro obě tyto tabulky vkládací skript T-SQL, který vloží id zaměstnance jméno do tabulky „Emp“ a jejich data připojení do tabulky „EmpJoining“.
Přihlásíme se jako normální uživatel a spustíme skript T-SQL pro vložení dat do tabulky.
Přihlaste se jako normální uživatel:
Otevřete SQL Server management studio. Nyní se přihlaste k SQL Serveru jako normální uživatel a použijte databázi, ve které jste vytvořili tabulku. Pomocí níže uvedeného skriptu vložte data do tabulky Emp a zkontrolujte, zda se spouštěč spustil, nebo ne.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‚Adam‘ )
select * from Emp
select * from EmpJoining
|
vidíme, že trigger se spustil a vložil data do tabulky EmpJoining.
Vlastnost relace můžeme zkontrolovat pomocí níže uvedeného skriptu.
|
1
|
select SESSIONPROPERTY ( ‚replication_agent‘ )
|
vrátí nulu, pokud se přihlašujeme jako normální uživatel a vrátí jedničku, pokud se přihlašujeme jako replikační agent.
Postupy pro přihlášení jako replikační agent:
Nyní se přihlásíme jako replikační agent a vložíme data pomocí skriptu.
Zavřeme SQL server management studio a znovu jej otevřeme.
Zadejte server, přihlašovací jméno a heslo, které chcete použít. klikněte na možnosti. Podívejte se na následující obrázek.
Přejděte na kartu další parametry připojení. V tabulce dalších parametrů připojení zadejte do textového pole REPLICATION=TRUE, jak je znázorněno na následujícím obrázku.
Můžete použít preferovanou metodu přihlášení, buď ověřování systému Windows, nebo ověřování serveru SQL.
Tím se přihlásíte jako replikační agent a všechny operace DML prováděné nad tabulkou budou prováděny jako replikační agent.
Nyní proveďte níže uvedený dotaz, abyste zjistili, zda jste přihlášeni jako replikační agent, nebo ne.
|
1
|
select SESSIONPROPERTY ( ‚replication_agent‘ )
|
Mělo by to vrátit hodnotu 1.
Nyní vložte několik řádků pomocí níže uvedeného skriptu.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‚Greg‘ )
select * from Emp
select * from EmpJoining
|
Vidíme, že trigger není spuštěn, protože jsme přihlášeni jako replikační agent a trigger je nastaven jako „NOT FOR REPLCIATION“.
Data jsou vložena pouze do tabulky Emp. Podívejte se na následující obrázek:
Zde v tomto případě není spouštěč zakázán a je k dispozici pro ostatní uživatelské transakce, které spustí spouštěč, když dojde k INSERT. Spouštěč není spuštěn pouze pro transakci, která je prováděna jako replikační agent.
Pomocí níže uvedeného dotazu zkontrolujte, zda je váš spouštěč označen jako „NOT FOR REPLCIATION“ nebo ne.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Existují další způsoby, jak zakázat spouštěče v SQL Serveru pro relaci pomocí zpracování kódu s podmínkami.
Například můžete v kódu spouštěče použít funkci CONTEXT_INFO() a vrátit. Použití funkce CONTEXT_INFO() nevyžaduje žádná zvláštní oprávnění.
Pokud se v tomto případě CONTEXT_INFO shoduje s hodnotou uvedenou ve spouštěči, spouštěč se vrátí a neprovede kód níže. Podívejte se prosím na níže uvedený kód.
|
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
|
V tomto případě musíme před vložením dat do tabulky „Emp“ nastavit hodnotu context_info na 0x1256698456.
Následující systémové pohledy také ukládají kontextové informace, ale přímé dotazování těchto pohledů vyžaduje oprávnění SELECT a VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Místo skriptu T-SQL INSERT předpokládejme, že data jsou soubor .txt nebo .csv. Pro načtení dat do tabulek bez spuštění spouštěčů můžeme použít možnosti BCP UTILITY nebo BULK INSERT.
Použití nástroje BCP
Pro hromadné načtení dat do tabulky bez spuštění spouštěčů v SQL Serveru můžeme použít nástroj BCP. Tato metoda bude fungovat pouze pro INSERTY do tabulky a nevyvolá spouštěče vytvořené „pro insert“ a „místo insertu“.
Ve výchozím nastavení nástroj BCP při načítání dat do tabulek spouštěče nevyvolá. Chceme-li vynutit spuštění triggerů, měli bychom v BCP při načítání dat do tabulky použít příkaz -h „FIRE_TRIGGERS“.
Níže jsou uvedeny ukázkové tabulky použité v tomto příkladu.
|
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
|
Viz níže uvedený příklad výchozího BCP pro načtení dat do tabulky „USERS“, který nebude spouštět spouštěče v SQL Serveru. Původní server, názvy databází a přihlašovací údaje jsem zamaskoval.
bcp .dbo.USERS v D:\bcp.txt -T -c -S „SERVERNAME“ -Uusername -Ppassword
Viz níže uvedený příklad BCP s hintem „FIRE_TRIGGERS“ pro načtení dat do tabulky USERS, která bude spouštět triggery.
bcp .dbo.USERS v D:\bcp.txt -T -c -S „SERVERNAME“ -Uusername -Ppassword -h „FIRE_TRIGGERS“
Použití BULK INSERT
Tato možnost bude také fungovat pouze pro INSERTY v tabulce a nevyvolá spouštěče vytvořené „pro insert“ a „místo insertu“.
Ve výchozím nastavení BULK INSERT nevyvolá spouštěče v SQL Serveru. Spuštění triggeru můžeme vynutit zadáním „FIRE_TRIGGERS“
Mám soubor test.txt, který jako data s „,“ jako FIELDTERMINATOR.
Podívejte se na níže uvedený kód pro výchozí BULK INSERT pro vložení dat do tabulky USERS, který v SQL Serveru nevyvolá spouštěče.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‚D:\\test.txt‘ –location with filename
WITH
(
FIELDTERMINATOR = ‚,‘,
ROWTERMINATOR = ‚\n‘
)
GO
|
Viz níže uvedený kód s hintem „FIRE_TRIGGERS“, který při načítání dat z testu spustí spouštěče v SQL Serveru.txt souboru.
|
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
|
V tomto článku jsme probrali chování triggerů v SQL Serveru pro níže uvedené případy.
- Přihlášení jako běžný uživatel.
- Přihlášení jako replikační agent a trigger je nastaven jako ne pro replikaci.
- Použití funkce CONTEXT_INFO v kódu triggeru.
- Použití výchozího nastavení BCP a s hintem „FIRE_TRIGGER“
- Použití výchozího nastavení BULK INSERT a s hintem „FIRE_TRIGGER“
Pro body 3, 4 a 5 je jedno, zda vytvoříme spouštěče s nastavením „NOT FOR REPLICATION“ nebo ne.
- Autor
- Poslední příspěvky
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Přehled příkazu Collate SQL – 22. října 2019
- Obnovení ztraceného hesla SA – 20. září 2019