SQL Server: Como desabilitar o gatilho para uma atualização apenas para sua sessão atual?

Para resolver seu problema, temos que adotar uma abordagem programática para o problema. Existem duas rotas que você pode ir aqui. A razão para precisar dessas abordagens é porque você não pode desabilitar um gatilho para uma instrução específica, ele só pode ser desabilitado para a totalidade da tabela.

Opção 1: Context_Info()

Samuel Vanga em MS SQL Tips teve um ótimo exemplo:

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

Agora, quando Samuel não quer que o gatilho seja executado, eles usam isso:

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

Context_Infousa as seguintes visualizações do sistema para obter informações sobre a sessão atual:

• sys.dm_exec_requests

• sys.dm_exec_sessions

• sys.sysprocesses

A ideologia aqui é que a string binária que você está definindo é exposta apenas à sessão atual, então quando o gatilho for executado durante sua sessão, ele verá o escopo e a configuração da variável da Context_infofunção e pulará para a parte de escape do gatilho em vez de.

Opção 2: Tabela Temp.

Itzik Ben-Gan tem uma ótima solução em seu livro "Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming" que também está em seu livro posterior T-SQL Querying . O principal problema com isso sobre a context_infofunção é a sobrecarga menor do TempDB.

Para estragar a surpresa, mas não estragar o enredo dos livros (acho que vale a pena comprar e ler), você vai alterar seu gatilho.

Seu gatilho deve realizar uma verificação de uma tabela temporária. Se a tabela temporária existir, o gatilho deve saber encerrar e não executar as ações.

Na instrução de atualização que você deseja executar, crie primeiro a tabela temporária. Ele será visto na mesma transação que o gatilho e fará com que o gatilho ignore sua instrução.

Exemplo de gatilho:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

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

Exemplo de instrução inicial quando você não deseja que o gatilho seja executado:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Colocando tudo para o seu exemplo:

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

Fiz alguns testes sobre isso e acho que você ficaria bem se executasse seu processo em uma única transação.

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 

Nos meus testes, apenas destaquei e executei o BEGIN TRANSACTIONe o DISABLE TRIGGERprimeiro. Em seguida, abri uma nova (segunda) janela de consulta e tentei executar várias instruções DML ( SELECT, INSERT, UPDATE DELETE) na tabela base. Todas as tentativas de acessar a tabela base na segunda janela de consulta aguardaram os bloqueios mantidos pela janela com a transação explícita. Depois de confirmar (ou reverter) minha transação explícita, a segunda janela conseguiu acessar a tabela.

Eu usaria um CONTEXT_INFOou o mais recente SESSION_CONTEXT. Ambos são valores baseados em sessão.

• CONTEXT_INFOé um VARBINARY(128)valor único. Isso está disponível desde pelo menos o SQL Server 2000. CONTEXT_INFOpode ser visualizado por qualquer pessoa VIEW SERVER STATE, pois é um campo retornado pelo sys.dm_exec_sessionsDMV. Já usei este e funciona muito bem.

  Definir via SET CONTEXT_INFO
  Obter via CONTEXT_INFO() ou sys.dm_exec_sessions

  Dependendo do tipo de valor que você está armazenando CONTEXT_INFO, há algumas nuances a serem observadas. Eu abordo isso no seguinte post do blog:

  Por que CONTEXT_INFO() não retorna o valor exato definido por SET CONTEXT_INFO?

• Session_context é um par de SQL_VARIANTvalores chave/valor. Isso foi introduzido no SQL Server 2016. A separação de valores para diferentes propósitos é bastante agradável. Session_context só pode ser visualizado pela sessão atual.

  Defina este valor via sp_set_session_context
  Obtenha este valor via SESSION_CONTEXT

Uma coisa a considerar em relação à opção de tabela temporária local e até mesmo a opção de desabilitar / habilitar Trigger: ambas requerem alguma quantidade de atividade de bloqueio e tran log. Ambas as opções aumentam o potencial de contenção, mesmo que minimamente. As duas opções de "contexto" devem ser mais leves/somente memória.