MCSA – Trigger

Olá pessoal, como estão??!!

Iniciando a semana de posts gostaria de apresentar um objeto que é extremamente importante para controle  de atividades em tabelas que são as Triggers!

Triggers são objetos criados em uma determinada tabela que dependendo, impedem que certo tipo de ação seja feita, servindo como objeto de segurança na tabela. Os famosos updates sem where!!

Como podem perceber, trigger não é um objeto que pode ser acionado diretamente como por exemplo, uma procedure. Elas são totalmente dependentes de uma ação externa.

As triggers podem ser de dois tipos:

1. DML 
2. DDL

Os comandos com base DML são atrelados apenas a nível do objeto e podem disparar outras triggers em outros objetos já os comandos com configuração DDL podem ter dois tipos de escopo:

1. Servidor
2. Banco de dados

Neste post, iremos ver triggers DML, em outro post, retorno com as triggers DDL.

CRIANDO UMA TRIGGER DML

A trigger abaixo irá impedir que qualquer update seja feito em uma determinada tabela:

Este é o bloco de comando de criação da trigger, agora, veja quando o update é executado junto com o select.

Pela trigger ter sido configurada com After Update ela só executará o rollback na transação quando o update for finalizado, como aconteceu no exemplo acima.

UTILIZANDO INSTEAD OF

Quando utilizamos o instead of dentro de uma trigger estamos atribuindo que ela tome outra ação ao invés de simplesmente executar o comando DML na tabela.

Por exemplo a trigger abaixo que ao invés de executar o update  na tabela SalesOrderHeader, ela insere os valores em outra tabela.

Para este exemplo, criei uma tabela de ‘controle’, envolta na caixa branca e apliquei a propriedade na trigger!

Outra particularidade da propriedade INSTEAD OF  é que ela também pode ser aplicada em Views, diferente da After  que só pode ser aplicada em tabelas físicas do banco.

• Na caixa branca, o bloco de criação da trigger na view e qual ação ela deve tomar quando algum comando de delete for executado diretamente nela.

• Em vermelho a trigger sendo criada na view de exemplo.

Para este exemplo, aproveitei a tabela criada como auditoria para inserir as mudanças.

Depois da trigger criada execute o comando de delete, faça um select na tabela de auditoria e na view, verá que a linha ainda estará lá!!

FUNCIONAMENTO DA TRIGGER

Agora que já temos uma base de como as triggers são criadas e agem no banco, vamos entender como o SQL Server lida com elas de acordo com a sua propriedade.

Quando criamos a trigger e ela é disparada no banco, dependendo da ação que determinamos, ela irá consultar duas tabelas do TempDB:

1. INSERTED
2. DELETED

Cada uma destas tabelas armazena os dados de acordo com o comportamento da trigger, por exemplo.

Quando executamos o delete na view acima mas passamos pra trigger que ao invés dela deletar aquela linha, ela inserisse na tabela de auditoria, o que estamos ordenando é:

1. Vá ao TempDB
2. Consulte a tabela temporária Deleted
3. Insira esses valores na minha tabela Auditoria.

Veja este exemplo, com o bloco de comando abaixo:

Após criação da trigger, temos a execução de um update e dois selects, sendo um na tabela de auditoria e outro na tabela de vendedores. Repare que o update zera a venda de um vendedor.

Em azul, a tabela de auditoria e em vermelho a tabela de vendedores. Como podemos perceber, a tabela não sofreu nenhum tipo de alteração no valor dos vendedores.

Como essas tabelas trabalham com um conceito chamado Row Versioning conseguimos inserir tanto os dados que seriam deletados quanto os novos dados que iriam para tabela ou view.

Com o comando delete temos acesso aos dados ‘antigos’, o comando insert aos dados novos e o comando update dá acesso aos dois por agir de forma diferente. Quando executamos o Update, estamos deletando o valor que ali está  e inserindo um valor completamente novo.

Tendo este conceito em mente fica mais fácil entender e trabalhar com tabelas de auditorias em triggers.

E neste exemplo, percebemos que a trigger além de impedir a atualização, também armazenou e retornou o valor antigo do vendedor.

NOTA: Sempre que for acessar o TempDB para retornar esses valores e inserir em outra tabela, busque utilizar comandos que façam acessos em grandes blocos de dados para não ter problemas de desempenho, já que estas duas tabelas não possuem index.

E podemos unir os dois conceitos criando uma trigger  que armazena os valores antigos e os atuais, como uma espécie de controle.

Primeiro crie uma tabela temporária, simples.

Agora, crie a trigger que irá armazenar a quantidade do produto em variáveis para depois inserir na tabela que criamos acima.

Essa foi uma trigger criada em uma view que sempre que houver alguma atualização na coluna do mês de Jan, irá disparar esse comando abaixo.

Após a criação da tabela e da trigger, vamos aos testes.

Como podemos perceber, não houve nenhuma alteração na view e ainda registramos o valor antigo, o novo, a data que houve a atualização, o usuário e id do produto.

TRIGGERS E RESTRIÇÃO DE INTEGRIDADE

Em algumas situações, podemos utilizar triggers para impedir que uma determinada situação fira a integridade do banco.

Este procedimento não é muito aconselhável, sendo melhor utilizar uma constraint na tabela, mas se precisar, eis aqui uma situação que podemos aplicar.

Se por um acaso tivermos uma política ao qual é proibido aumentar salário acima de 5%, poderíamos criar  a seguinte trigger.

A trigger tem um IF para caso haja alguma atualização na coluna Rate(salário) e uma lógica que se a diferença entre os salários for maior que 5%, irá falhar a transação.

Este é mais um exemplo de como trabalhar com as tabelas temporárias do TembDB!!

Lembrando que ainda que seja possível a boa prática é utilizar constraints no banco para garantir a integridade, trigger pode ser burlada de maneira mais fácil.

Executando um update que seja menor que o restrito na trigger:

RECURSÃO DE TRIGGER

Em algumas situações pode ser que quando uma trigger disparar, por consequência ela acabe disparando outra na mesma ou em outra tabela. Quando isso ocorre, temos um aninhamento ou recursividade de trigger.

A recursividade pode ser direta ou indireta:

• Direta – quando a trigger A é disparada, realiza a ação e dispara novamente ou então a trigger dispara uma ação para outra trigger que devolve a ação entrando em loop.

• Indireta – quando outra trigger do mesmo tipo (after ou instead of) é acionada e essa retorna para a trigger acionadora o comando que realiza uma nova ação.

Veja o exemplo de uma recursão indireta com duas triggers do mesmo tipo: Instead Of.

Quando um comando é disparado para uma determinada tabela, ela redireciona para outra tabela.

A tabela que recebeu o comando redirecionado, devolve o comando entrando assim em loop.

Neste exemplo eu executei um update na tabela Person.Person que redirecionou para a tabela Person.PersonPhone. 

A trigger da tabela Person.PersonPhone devolveu o comando para a tabela Person.Person e assim, entram em loop. O count máximo do loop são 32 execuções.

Agora, quando temos uma recursão direta, veja o que ocorre:

A trigger em branco [HumanResources].[EmployeeDepartmentHistory] quando recebe o comando do update irá enviar um sinal para a trigger na tabela HumanResources.Department ordenando que essa insira em uma tabela, os dados recebidos na tentativa do update.

CONCLUSÃO

Esse post foi bem extenso e com muitos exemplos de uso de uma trigger, tentei explicar os principais contextos e seus usos.

Tenha cuidado quando for buscar dados das tabelas inserted e deleted do banco TempDB, trabalhe sempre com grandes conjuntos, evite acesso linha por linha.

Se for testar restrição de integridade, evite usar trigger para isso e sim as constraints.

Tenha cuidado com a recursão, planeje bem seu uso para não causar problemas no ambiente.

E se quiserem baixar o script com mais alguns exemplos, aqui está.

Espero que gostem, saúde!!