SQL Server: como desativar o gatilho para uma atualização apenas para a sua sessão atual?
Estou trabalhando no SQL Server 2008 R2.
Eu tenho um benefício de tabela que possui um gatilho AFTER INSERT, UPDATE chamado tiu_benefit .
Quero escrever uma instrução UPDATE para esta tabela para atualizar 1 linha, mas não quero que seu gatilho seja acionado. Sei que posso desativar o gatilho antes de UPDATE e, em seguida, habilitá-lo depois de UPDATE:
Mas esse gatilho de desativação e ativação afetará todos os usuários conectados no momento. Portanto, existe a possibilidade de outro usuário executar um UPDATE / INSERT enquanto o gatilho estiver desativado pelo meu script, o que não é bom. É por isso que eu só quero desativar e ativar o gatilho para a minha sessão atual. É possível? Se sim, por favor, diga como.
— srh
Se você não pode modificar seu gatilho, a resposta é não.
Fiz alguns testes e acho que você ficaria bem se executar o processo em uma única transação.
Nos meus testes, apenas destaquei e executei o BEGIN TRANSACTIONe o DISABLE TRIGGERprimeiro. Eu, então, abriu uma nova (segunda) janela de consulta e tentou correr várias instruções DML ( SELECT, INSERT, UPDATE DELETE) contra a tabela base. Todas as tentativas de acessar a tabela base na segunda janela de consulta aguardavam os bloqueios mantidos pela janela com a transação explícita. Após confirmar (ou reverter) minha transação explícita, a segunda janela conseguiu acessar a tabela.
— Scott Hodgin
Isso funcionará, mas os bloqueios podem causar problemas não intencionais a jusante, dependendo de quanto tempo você mantém a transação aberta.
@ CaM - Eu diria que uma atualização de uma linha não levaria muito tempo, assumindo que o OP confirma ou reverte a transação rapidamente. Espera-se que existe um índice em benefit_id:)
realmente gostei esta solução como eu não tenho que fazer qualquer alteração para o gatilho
Para resolver seu problema, precisamos adotar uma abordagem programática. Existem duas rotas que você pode ir aqui. O motivo para a necessidade dessas abordagens é porque você não pode desativar um gatilho para uma instrução específica, ela pode ser desativada apenas para a totalidade da tabela.
Opção 1: Context_Info ()
Samuel Vanga no MS SQL Tips teve um ótimo exemplo:
Agora, quando Samuel não quer que o gatilho seja executado, eles usam isso:
Context_Info usa as seguintes visualizações do sistema para obter informações sobre a sessão atual:
A ideologia aqui é que a string binária que você está configurando é exposta apenas à sessão atual; portanto, quando o gatilho for executado durante a sessão, ele verá o escopo e a configuração variável da Context_infofunção e pulará para a parte de escape do gatilho. em vez de.
Opção 2: tabela temporária
Itzik Ben-Gan tem uma ótima solução em seu livro "Por dentro da programação T-SQL do Microsoft SQL Server 2008: programação T-SQL", que também está em seu livro posterior Consulta T-SQL . O principal problema com isso na context_infofunção é a sobrecarga secundária do TempDB.
Para estragar a surpresa, mas não estragar a trama dos livros (achei que valem a pena comprar e ler), você alterará seu gatilho.
Seu gatilho deve verificar se há uma tabela temporária. Se a tabela temporária existir, o gatilho deve saber finalizar e não executar as ações.
Na instrução de atualização que você deseja executar, crie a tabela temporária primeiro. Ele será visto na mesma transação que o gatilho e fará com que o gatilho ignore sua declaração.
Exemplo de instrução inicial quando você não deseja que o gatilho seja executado:
Colocando tudo no seu exemplo:
— Shaulinator
Eu usaria context_info () em vez da tabela temporária no gatilho. Em outra palavra, se um gatilho detectar que context_info retorna um valor específico, o gatilho funcionará de acordo. Você pode se referir a questão tão relevante aqui: stackoverflow.com/questions/3025662/...
Você também pode fazer uma verificação semelhante ao context_infouso original_login()para dizer ao gatilho para nunca executar se uma pessoa específica estiver pressionando o gatilho.
Eu usaria um CONTEXT_INFOou o mais novo SESSION_CONTEXT. Ambos são valores baseados em sessão.
CONTEXT_INFOé um VARBINARY(128)valor único . Está disponível desde pelo menos o SQL Server 2000. CONTEXT_INFOé visível para qualquer pessoa VIEW SERVER STATE, pois é um campo retornado pela sys.dm_exec_sessionsDMV. Eu usei este antes 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. Abordo isso na seguinte postagem no blog:
Por que CONTEXT_INFO () não retorna o valor exato definido por SET CONTEXT_INFO?
Session_context é um par de SQL_VARIANTvalores de chave / valor . Isso foi introduzido no SQL Server 2016. A separação de valores para propósitos diferentes é bastante agradável. Session_context é visível apenas pela sessão atual.
Defina esse valor via sp_set_session_context
Obtenha esse valor via SESSION_CONTEXT
Uma coisa a considerar em relação à opção da tabela temporária local e até à opção desativar / ativar o acionador: ambas requerem uma certa quantidade de atividades de bloqueio e de log. Ambas as opções aumentam o potencial de contenção, mesmo que minimamente. As duas opções de "contexto" devem ser de menor peso / somente memória.
context_info é um analgésico, sempre que você deseja executar uma alteração nos dados de produção, isso é útil, especialmente desabilitar o gatilho pode fazer com que outras operações não o disparem.