Este artigo irá focar nas várias maneiras de desabilitar triggers no SQL Server para que eles não interfiram com certas operações como inserções em massa.
Problema
Tivemos uma situação em que precisamos carregar dados nas tabelas de um dos nossos bancos de dados. Havia alguns triggers criados nestas tabelas para inserção, atualização e exclusão e estes triggers são definidos como “NÃO PARA REPLICAÇÃO”. Enquanto carregamos os dados nas tabelas não queremos que estes gatilhos sejam disparados. Nós pensamos em desabilitar os triggers nas tabelas mas isto irá desabilitá-los para outras transações de usuários também. Queremos que eles não sejam disparados somente para a minha sessão da qual estou executando o script de carregamento de dados. Neste artigo vamos ver como desabilitar triggers no SQL Server para um específico.
Solução
Precisamos entender sobre o conceito “NOT FOR REPLICATION” no SQL Server primeiro.
NOT FOR REPLICATION pode ser usado ao criar triggers no SQL Server. Isto indica que estes triggers não são disparados quando o agente de replicação faz modificações de dados (INSERT / UPDATE / DELETE) na tabela.
Não apenas para triggers no SQL Server, esta dica também pode ser usada ao criar chaves estrangeiras, colunas de identidade e restrições de verificação.
No caso de chaves estrangeiras, a verificação de chave estrangeira só acontece quando um usuário modifica dados na tabela e a validação de chave estrangeira não acontece quando o agente de replicação sincroniza essas modificações na outra extremidade (tanto para assinante ou para assinante e editor com base no tipo de replicação configurada)
No caso de colunas de identidade, um novo valor de identidade não é gerado quando o agente de replicação insere dados na tabela e o valor de identidade original que é gerado na fonte é usado.
Como os triggers no SQL Server foram criados com “NOT FOR REPLICATION” nestas tabelas, se inserirmos dados na tabela como um usuário normal, estes triggers foram disparados. Estes gatilhos não foram disparados quando o agente de replicação insere, elimina e atualiza os dados nas tabelas. Então, fingir como um agente de replicação fará meu trabalho. (ou seja, os gatilhos foram disparados para todas as sessões de usuário e não disparados para uma sessão específica que eu entrei como agente de replicação.)
Para ilustrar isto, criarei duas tabelas de exemplo “Emp” e “EmpJoining” e um gatilho na tabela “Emp” que é disparado para inserir data de junção quando uma nova linha é inserida na tabela “Emp”.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , nome varchar (50) )
GO
CREATE TABLE EmpJoining ( Empid int, JD datetime)
GO
CRIAR TRIGGER TR_INSEMPDETAILS ON Emp
PARA INSERIR
NÃO PARA REPLICAÇÃO
AS
BEGINAR A TRIGGER TR_INSEMPDETAILS ON
INSERIR PARA INSERIR
SELECCIONAR Vazio , GETDATE() FROM inserido
END
GO
|
Agora, por exemplo, eu tenho a identificação do funcionário, nome e data de adesão em outro sistema que precisa ser importado para cima das tabelas existentes. Neste caso não quero que a lógica de disparo seja disparada quando estou inserindo dados na tabela Emp.
Basicamente, tenho o script de inserção T-SQL para estas duas tabelas que insere o nome do empregado id na tabela “Emp” e suas datas de junção na tabela “EmpJoining”.
Vamos entrar como usuário normal e executar o script T-SQL para inserir dados na tabela.
Login como usuário normal:
Open SQL Server management studio. Agora faça login no SQL Server como usuário normal e use o banco de dados onde você criou a tabela. Use o script abaixo para inserir dados na tabela Emp e verifique se o gatilho está disparado ou não.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) valores ( 1 , Adam’ )
selecione * do Emp
selecione * do EmpJoining
|
podemos ver que o gatilho é disparado e os dados inseridos na tabela EmpJoining.
Podemos verificar a propriedade da sessão usando o script abaixo.
|
1
|
selecione SESSIONPROPERTY ( ‘replicação_agente’ )
|
retornará zero se nos logarmos como usuário normal e retornará um se nos logarmos como agente de replicação.
Passos para Login como agente de replicação:
Agora vamos logar como agente de replicação e inserir dados usando script.
Feche o SQL server management studio e reabra-o.
Insira o servidor, login e senha que você deseja usar. clique em opções. Consulte a imagem abaixo.
Navegue até a aba de parâmetros de conexão adicionais. Na tabela de parâmetros de conexão adicionais entre REPLICATION=TRUE na caixa de texto como mostrado na imagem abaixo.
Você pode usar o seu método de login preferido tanto para autenticação no Windows quanto para autenticação no SQL Server.
Isto fará o login como agente de replicação e qualquer operação DML que você executar na tabela será executada como agente de replicação.
Agora execute a consulta abaixo para verificar se você está logado como agente de replicação ou não.
|
1
|
selecione SESSIONPROPERTY ( ‘replicação_agente’ )
|
Deve retornar 1.
Agora insira poucas linhas usando o script abaixo.
|
1
2
3
4
|
INSERIR INTO Vazio ( Empid , name ) valores ( 2 , Greg’ )
selecione * do Emp
selecione * do EmpJoining
|
Podemos ver que o gatilho não é disparado quando entramos como agente de replicação e o gatilho é definido como “NÃO PARA REPLICAÇÃO”.
Os dados são inseridos apenas na tabela Emp. Consulte a imagem abaixo.
Aqui neste caso o trigger não está desabilitado e disponível para outras transações de usuário que dispararão o trigger quando houver um INSERT. O trigger não é disparado somente para a transação que é executada como agente de replicação.
Use a consulta abaixo para verificar se o seu trigger está marcado como “NÃO PARA REPLICAÇÃO” ou não.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Existem outras formas de desabilitar triggers no SQL Server para uma sessão, manipulando código com condições.
Por exemplo, você pode usar CONTEXT_INFO() no código de gatilho e retornar. O uso de CONTEXT_INFO() não requer permissões especiais.
Neste caso, se CONTEXT_INFO corresponder ao valor especificado no gatilho, o gatilho retorna e não executa o código abaixo. Por favor, consulte o código abaixo.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
>
CRIAR TRIGGER TR_INSEMPDEDETAIS SOBRE O Vazio
FOR INSERT
AS
BEGIN
SET NOCOUNT on
DECLARE @CONT_INFO VARBINÁRIO(128)
SELECT @CONT_INFO = CONTEXT_INFO()
IF @CONT_INFO = 0x1256698456
RETORNO
INSERIR PARA EmpJoining
SELECT Empid , GETDATE() FROM inserido
END
|
Neste caso devemos definir o valor da informação de contexto para 0x1256698456 antes de inserir os dados na tabela “Emp”.
As seguintes vistas do sistema também armazenam as informações de contexto, mas a consulta destas vistas requer permissões SELECT e VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Em vez do script T-SQL INSERT, vamos assumir que os dados são ficheiros .txt ou .csv. Podemos usar as opções BCP UTILITY ou BULK INSERT para carregar dados em tabelas sem disparar os gatilhos.
Using BCP utility
Podemos usar o utilitário BCP para carregar dados em massa em tabelas sem disparar gatilhos no SQL Server. Este método funciona apenas para INSERTS na tabela e não dispara os triggers criados “for insert” e “ao invés de insert”.
Por padrão, o utilitário BCP não dispara triggers ao carregar dados em tabelas. Para forçar a execução do trigger, devemos usar -h “FIRE_TRIGGERS” no BCP enquanto carregando dados em tabelas.
Abaixo estão exemplos de tabelas usadas neste exemplo.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CRIAR UTILIZADORES DE TABELA
(ID int,
NOME varchar(50))
CRIAR UTILIZADOR DA TABELA DE UTILIZAÇÃO
(USERID int,
CRIAR data/hora)
CRIAR UTILIZADORES DE TRIGREGADORES
PARA INSERIR
AS
BEGINAR
INSERIR UTILIZADORES
SELECCIONAR IDGETDATE() FROM INSERTED
END
|
Consulte o exemplo abaixo de BCP padrão para carregar dados na tabela “USUÁRIOS” que não dispararão gatilhos no SQL Server. Eu mascarei o servidor original, nomes da base de dados e credenciais de login.
bcp .dbo.USERS em D:\bcp.txt -T -c -c -S “SERVERNAME” -Uusername -Ppassword
Por favor consulte o exemplo abaixo de BCP com a dica “FIRE_TRIGGERS” para carregar dados na tabela “USERS” que irão disparar triggers.
bcp .dbo.USUÁRIOS em D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Password -h “FIRE_TRIGGERS”
Using BULK INSERT
Esta opção também funcionará apenas para INSERTS na tabela e não dispara gatilhos criados “for insert” e “em vez de insert”.
Por padrão, BULK INSERT não dispara gatilhos no SQL Server. Podemos forçar a execução do trigger especificando “FIRE_TRIGGERS”
Tenho o arquivo test.txt que como dados com “,” como FIELDTERMINATOR.
Por favor, consulte o código abaixo para o padrão BULK INSERT para carregar dados na tabela USERS que não dispararão triggers no SQL Server.
|
1
2
3
4
5
6
7
8
9
|
>
VOLUME
INSERIR UTILIZADORES
DE ‘D:\\teste.txt’ –localização com nome de arquivo
COM
(
> FIELDTERMINADOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Consulte o código abaixo com a dica “FIRE_TRIGGERS” que disparará gatilhos no SQL Server ao carregar dados do teste.arquivo txt.
|
1
2
3
4
5
6
7
8
9
10
|
A GRANEL
INSERIR UTILIZADORES
DE ‘D:\\teste.txt’ –localização com nome de arquivo
COM
(
> FIELDTERMINADOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
>
Neste artigo discutimos o comportamento dos triggers no SQL Server para os casos abaixo.
- Login como usuário normal.
- Login como agente de replicação e trigger é definido como não para replicação.
- Usando a função CONTEXT_INFO no código de trigger.
- Usando BCP padrão e com a dica “FIRE_TRIGGER”
- Usando BULK INSERT padrão e com a dica “FIRE_TRIGGER”
Para os pontos 3, 4 e 5 não importa se criamos triggers com “NOT FOR REPLICATION” ou não.
- Autor
- Posts recentes
- Replicação Geo em Criptografia Transparente de Dados (TDE) activada em bases de dados Azure SQL – 24 de Outubro, 2019
- Visão geral do comando Collate SQL – 22 de outubro de 2019
- Recuperar uma senha SA perdida – 20 de setembro de 2019