SQL Server: Jak wyłączyć wyzwalacz dla aktualizacji tylko dla bieżącej sesji?

Pracuję na SQL Server 2008 R2.

Mam korzyść tabeli, która ma wyzwalacz PO WSTAWIENIU , AKTUALIZACJI o nazwie tiu_benefit .

Chcę napisać instrukcję UPDATE dla tej tabeli, aby zaktualizować 1 wiersz, ale nie chcę, aby wyzwalacz zadziałał. Wiem, że mogę wyłączyć wyzwalacz przed UPDATE, a następnie włączyć wyzwalacz po UPDATE:

DISABLE TRIGGER tiu_benefit ON benefit;  
GO  
UPDATE benefit SET editor = 'srh' where benefit_id = 9876
GO
ENABLE TRIGGER tiu_benefit ON benefit;  
GO  

Ale to wyłączenie i włączenie wyzwalacza wpłynie na wszystkich zalogowanych użytkowników. Istnieje więc możliwość, że inny użytkownik uruchomi UPDATE / INSERT, podczas gdy wyzwalacz jest wyłączony przez mój skrypt, co nie jest dobre. Dlatego chcę tylko wyłączyć i włączyć wyzwalacz dla mojej bieżącej sesji. Czy to możliwe? Jeśli tak, proszę powiedzieć jak.

Dzięki

Przeprowadziłem kilka testów w tym zakresie i myślę, że byłoby dobrze, gdybyś uruchomił proces w jednej transakcji.

BEGIN TRANSACTION
GO

DISABLE TRIGGER tiu_benefit ON benefit;
GO

UPDATE benefit
SET editor = 'srh'
WHERE benefit_id = 9876
GO

ENABLE TRIGGER tiu_benefit ON benefit;
GO

--Decide to commit or rollback

--commit
--rollback 

W moich testach podkreśliłem i wykonałem tylko BEGIN TRANSACTIONten DISABLE TRIGGERpierwszy. Potem otworzyła nową (drugą) okno zapytań i próbował uruchomić różne oświadczenia DML ( SELECT, INSERT, UPDATE DELETE) przeciwko tabeli podstawowej. Wszystkie próby uzyskania dostępu do tabeli podstawowej w drugim oknie zapytania czekały na blokadach utrzymywanych przez okno z jawną transakcją. Po zatwierdzeniu (lub wycofaniu) mojej jawnej transakcji drugie okno mogło uzyskać dostęp do tabeli.

Aby rozwiązać Twój problem, musimy przyjąć programowe podejście do problemu. Możesz tu iść dwiema drogami. Powodem potrzeby takich podejść jest to, że nie można wyłączyć wyzwalacza dla określonej instrukcji, można ją wyłączyć tylko dla całej tabeli.

Opcja 1: Context_Info ()

Samuel Vanga na MS SQL Tips miał świetny przykład:

USE AdventureWorks; 
GO 
-- creating the table in AdventureWorks database 
IF OBJECT_ID('dbo.Table1') IS NOT NULL 
DROP TABLE dbo.Table1 
GO 
CREATE TABLE dbo.Table1(ID INT) 
GO 
-- Creating a trigger 
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE 
AS 
DECLARE @Cinfo VARBINARY(128) 
SELECT @Cinfo = Context_Info() 
IF @Cinfo = 0x55555 
RETURN 
PRINT 'Trigger Executed' 
-- Actual code goes here 
-- For simplicity, I did not include any code 
GO

Teraz, gdy Samuel nie chce, aby wyzwalacz był wykonywany, używają tego:

SET Context_Info 0x55555 
INSERT dbo.Table1 VALUES(100)

Context_Info korzysta z następujących widoków systemu, aby pobrać informacje dotyczące bieżącej sesji:

• sys.dm_exec_requests

• sys.dm_exec_sessions

• sys.sysprocesses

Ideologia polega na tym, że ustawiony ciąg binarny jest wystawiany tylko na bieżącą sesję, więc gdy wyzwalacz zostanie wykonany podczas sesji, zobaczy zakres i zmienne ustawienie Context_infofunkcji i przeskoczy do części zmiany znaczenia wyzwalacza zamiast.

Opcja 2: Tabela temperatur

Itzik Ben-Gan ma świetne rozwiązanie w swojej książce „Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming”, która znajduje się również w jego późniejszej książce T-SQL Querying . Podstawowym problemem związanym z tą context_infofunkcją jest niewielki narzut TempDB.

Aby zepsuć niespodziankę, ale nie zrujnować fabuły książek (czułem, że warto je kupić i przeczytać), zmienisz spust.

Twój wyzwalacz powinien wykonać sprawdzenie tymczasowego stołu. Jeśli tabela tymczasowa istnieje, wyzwalacz powinien wiedzieć, aby zakończyć i nie wykonywać akcji.

W instrukcji aktualizacji, którą chcesz wykonać, najpierw utwórz tabelę tymczasową. Będzie widoczny w tej samej transakcji co wyzwalacz i spowoduje, że wyzwalacz zignoruje twoje polecenie.

Przykład wyzwalacza:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
GO

Przykład instrukcji początku, gdy nie chcesz, aby wyzwalacz działał:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Podsumowując, przykład:

ALTER TRIGGER tiu_benefit ON benefit FOR 
... 
AS
...
IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
--... rest of code here
GO

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);
UPDATE benefit SET editor = 'srh' where benefit_id = 9876;
GO

Chciałbym użyć jednego CONTEXT_INFOlub nowszego SESSION_CONTEXT. Oba są wartościami sesji.

• CONTEXT_INFOjest pojedynczą VARBINARY(128)wartością. Jest to dostępne od co najmniej SQL Server 2000. CONTEXT_INFOjest widoczne dla każdego, VIEW SERVER STATEponieważ jest to pole zwracane przez sys.dm_exec_sessionsDMV. Użyłem tego wcześniej i działa całkiem dobrze.

  Ustaw za pomocą SET CONTEXT_INFO
  Pobierz za pośrednictwem CONTEXT_INFO () lub sys.dm_exec_sessions

  W zależności od rodzaju przechowywanej wartości CONTEXT_INFOnależy pamiętać o kilku niuansach. Omawiam to w następującym poście na blogu:

  Dlaczego funkcja CONTEXT_INFO () nie zwraca dokładnej wartości ustawionej przez SET CONTEXT_INFO?

• Kontekst_sesji to para SQL_VARIANTwartości klucz / wartość . Zostało to wprowadzone w SQL Server 2016. Rozdzielenie wartości dla różnych celów jest całkiem miłe. Kontekst_sesji jest widoczny tylko dla bieżącej sesji.

  Ustaw tę wartość za pomocą sp_set_session_context
  Uzyskaj tę wartość za pośrednictwem SESSION_CONTEXT

Jedną rzecz do rozważenia w odniesieniu do opcji lokalnego tymczasowego stołu, a nawet opcji włączania / wyłączania wyzwalacza: oba z nich wymagają pewnej aktywności blokowania i rejestrowania tran. Obie te opcje zwiększają prawdopodobieństwo rywalizacji, nawet jeśli są minimalne. Dwie opcje „kontekstowe” powinny mieć mniejszą wagę / tylko pamięć.