Triggers para iniciantes no SQL Server – Parte 2/3

Este artigo se aplica ao Microsoft SQL Server a partir da versão 2000, porém o conceito de triggers se aplica à maioria dos sistemas gerenciadores de bancos de dados disponíveis, porém podem ocorrer diferença de sintaxe.

Olá caros sqlnautas, sejam bem vindos à segunda parte da saga sobre triggers (gatilhos) no SQL Server. Para quem deseja ler a primeira parte, segue aqui o link para a primeira parte:

Triggers para iniciantes no SQL Server - Parte 1/3

PARA COMEÇAR

Para esta segunda parte do nosso tutorial, vamos partir para a parte prática. Para começar, abra uma nova janela de Query no seu SQL Management Studio para criarmos duas tabelas, com estruturas idênticas, porém com diferentes propósitos, para que possamos executar nossos testes, e entender o comportamento das triggers em todas as operações.

Code Snippet

1. create table exTrigger1 (
2.   codigo     char(13) not null
3. , quantidade smallint not null
4.     , valor      moneynot null
5.     , total      moneynot null )
6.  
7. GO
8.  
9. create table exTrigger2 (
10.   codigo     char(13) not null
11. , quantidade smallint not null
12.     , valor      moneynot null
13.     , total      moneynot null )
14.  
15. GO
16.  
17.  
18. -- Execute estes inserts na tabela exTrigger2, vamos usar
19. -- mais pra frente.
20.  
21. insert into exTrigger2 (codigo, quantidade, valor, total)
22.   select '1', 1, 1, 1
23.  
24. insert into exTrigger2 (codigo, quantidade, valor, total)
25.   select '2', 2, 2, 4
26.  
27. insert into exTrigger2 (codigo, quantidade, valor, total)
28.   select '4', 3, 3, 9

EXPLORANDO AS TABELAS INSERTED E DELETED

As tabelas INSERTED e DELETED, como comentado no post anterior, são tabelas “virtuais”, que existem somente durante a execução de uma trigger. Na tabela INSERTED temos os registros inseridos na tabela, ou os novos valores atribuídos aos registros alterados durante um update. Na tabela DELETED, o conceito é o mesmo, porém com os registros excluídos, ou no caso do update, os valores anteriores ao update. A trigger que estamos criando, será disparada sob qualquer operação de manipulação de dados que houver na tabela (INSERT, DELETE, UPDATE). Como foi falado anteriormente, você pode ter uma trigger configurada para disparar somente no evento INSERT ou somente no DELETE, mas aqui vamos colocar todas as operações para explorá-las melhor.

Lembrando que sistematicamente, todo processo executado em um sistema, geralmente possui um fluxo de retrocesso, estorno, ou algo similar para esta mesma operação. É neste momento que se deve tomar muito cuidado ao se criar trigger. Se você criou uma trigger para quando inserisse na tabela, movimentasse estoque, você também deve ter uma trigger que ao excluir o registro dessa tabela, estorne esta movimentação, caso contrário seu banco de dados não ficará consistente, e assim por diante.

A trigger que vamos criar possui o propósito específico de mostrar o comportamento das tabelas INSERTED e DELETED. Quando realizarmos operações na tabela exTrigger1, na tela de resultado da Query irá aparecer a contagem de registros incluidos, excluídos e alterados.

Vamos então criar nossa trigger na tabela exTrigger1:

Code Snippet

1.  
2. create trigger tgExTrigger1 on exTrigger1
3. for insert, delete, update
4. as
5. begin
6.  
7.  
8.    /*
9.    Autor: Fabiano Cores
10.    Data : 20/12/2010
11.    Site : http://sqlburger.blogspot.com
12.    Desenvolvido para demonstrar a utilização de
13.    triggers no SQL Server. A reprodução deste
14.    artigo só é permitida se forem mantidos os
15.    créditos do autor.
16.    */
17.  
18.  
19.   declare @excluidos int
20.   declare @incluidos int
21.   declare @alterados int
22.   declare @mensagem  varchar(100)
23.    
24.   -- Quantidade de registros excluidos (delete)
25.   select @excluidos = count(*)
26.     from deleted
27.    where codigo not in (select codigo
28.                           from inserted)
29.  
30.   -- Quantidade de registros incluídos (insert)
31.   select @incluidos = count(*)
32.     from inserted
33.    where codigo not in (select codigo
34.                           from deleted)
35.  
36.   -- Quantidade de registros alterados (update)
37.   select @alterados = count(*)
38.     from inserted, deleted
39.    where inserted.codigo = deleted.codigo
40.     
41.   -- Monto a string de mensagem
42.   select @mensagem =
43.          'Incluídos: ' + rtrim(@incluidos) + char(13) + char(10)
44.        + 'Excluídos: ' + rtrim(@excluidos) + char(13) + char(10)
45.        + 'Alterados: ' + rtrim(@alterados)
46.  
47.   -- Exibo na tela do Query Result os calculos.
48.   print @mensagem
49.  
50. end
51. --

EXECUTANDO OS TESTES

Agora que toda a estrutura de objetos está criada, vamos iniciar os nossos testes. Ao executar cada script, observe a estrutura do código da trigger e assimile ambos os códigos ao mesmo tempo, com calma, e irá entender com facilidade.

O primeiro teste que vamos realizar é um insert na tabela exTrigger1 (que possui a trigger). O script e a figura com o resultado seguem abaixo:

Code Snippet

1. insert into exTrigger1 (codigo, quantidade, valor, total)
2.   select 'A', 30, 10, 300

Agora, vamos fazer um update na tabela:

Code Snippet

1. update exTrigger1
2.    set quantidade = 20
3.      , total = 200
4. where codigo = 'A'

E para finalizar a primeira parte dos exemplos, vamos excluir o registro que inserimos, e ver o resultado que a trigger irá imprimir na tela.

Code Snippet

1.  
2. delete from exTrigger1
3. where codigo = 'A'
4. --

Agora, vou mostrar o por que criar uma trigger exige que sejam observados vários aspectos. Vamos agora utilizar os dados da tabela exTrigger2, que possui 3 registros que inserimos para teste. Vamos inserir dados em massa na tabela exTrigger1 (a que possui a trigger). Mas o que é inserir dados em massa? É quando inserimos em uma tabela o resultado de um select, que pode retornar mais de um registro. Vamos observar o que acontece:

Code Snippet

1.  
2. insert into exTrigger1 (codigo, quantidade, valor, total)
3. select codigo, quantidade, valor, total
4.    from exTrigger2
5. --

Observe atentamente que inserimos 3 registros na tabela, porém a trigger foi disparada uma única vez. Muitas vezes, programadores amadores nem conhecem esta característica, ou as vezes o programador “acha” que só ocorrerão operações na tabela que incluam, alterem ou excluam apenas um registro, e é nesse momento que a trigger pode se tornar um problema. Na minha opinião, triggers são perigosas, e devem ser criadas a partir de muito planejamento e documentação. É importante lembrar que podem haver intervensões manuais na tabela, etc.

Um exemplo de como você pode seguramente tratar todos os registros de uma tabela, é utilizando um cursor:

Code Snippet

1.  
2. CREATE trigger tgExemplo on TabExemplo
3. for insert
4. as
5. begin
6.  
7.   declare @vcol1 int
8.   declare @vcol2 int
9.   
10.   declare cp1 cursor local fast_forward read_only for
11.     select col1, col2, ...
12.       from inserted
13.       
14.   open cp1
15.   
16.   while 1 = 1
17.   begin
18.     fetch next from cp1 into @vcol1, @vcol2
19.     if @@fetch_status <> 0 break
20.     
21.     -- sql_statements...
22.         
23.   end
24.   close cp1
25.   deallocate cp1
26.  
27. end
28. --

O único conselho que insisto em descrever é que se tome cuidado ao criar uma trigger. Procure projetar todas as possibilidades de operações que possam ocorrer na tabela. Também é importante que, se você fez uma trigger que faz uma operação, provavelmente você tenha que fazer uma trigger inversa, que desfaça a operação. Exemplo: você tem uma trigger na tabela de apontamento de ordem de produção, que ao gravar o apontamento, a trigger realiza a baixa de estoque dos materiais da ordem de produção. Lembre-se que se você excluir este apontamento, você deverá fazer uma trigger de delete, que irá estornar o movimento do estoque, e assim por diante. E é aí que se encontra a importância de tratar multiplos registros inseridos ou excluídos.

No próximo capítulo, vamos tratar abordagens mais avançadas nas triggers, como desabilitalas temporariamente, definição de sequencia de disparo (multiplas triggers em uma única tabela), controle de redundância (a trigger modificar dados em sua própria tabela, disparando de forma cíclica a trigger), e outros conceitos muito úteis.

Até a próxima!