Triggers para iniciantes no SQL Server – Parte 1/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 do mercado.

Olá caros sqlnautas. Neste artigo, trarei a vocês algumas dicas importantes sobre TRIGGERS no SQL Server. Este recurso nos possibilita disparar eventos programados ao se inserir, excluir ou alterar dados em uma tabela. Um exemplo de aplicação, que vamos abordar neste tópico, será a criação de uma trigger em uma tabela dos itens de uma nota fiscal. Quando os itens são gravados nesta tabela, automaticamente a trigger irá movimentar o estoque. Lembrando que nesta primeira parte, os scripts de exemplo foram desenvolvidos apenas para ilustrar. Na segunda parte, fornecerei scripts completos para realizar as simulações em seu próprio SQL Server.

CONHECENDO A ESTRUTURA DE UMA TRIGGER

Uma trigger (gatilho) é um bloco de código T-SQL (como um procedimento) que é executado no momento em que ocorre um INSERT, UPDATE e/ou DELETE na tabela onde foi criada.

Vamos então começar visualizando uma trigger. Para abrir uma trigger, podemos faze-lo de duas formas:

1. Localizar a trigger através do Object Explorer do Microsoft SQL Server Management Studio (imagem abaixo), clicar com o botão direito sobre ela e clicar em MODIFY (modificar).

 

2. Utilizando o comando sp_helptext

 
Vamos agora rapidamente olhar um código documentado de uma trigger completa (baixa estoque ao inserir itens na nota):

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. for insert
3. as
4. begin
5.   /*
6.    
7.   Autor: Fabiano Cores
8.   Data : 06/12/2010
9.   Site : http://sqlburger.blogspot.com
10.    
11.   Desenvolvido para demonstrar a utilização de  
12.   triggers no SQL Server. A reprodução deste  
13.   artigo só é permitida se forem mantidos os  
14.   créditos do autor.
15.    
16.   */
17.  
18.   /* A tabela inserted existe "virtualmente" durante     
19.      a execução de uma trigger. Ela possui exatamente    
20.      a mesma estrutura da tabela da trigger, e contem    
21.      todos os registros inseridos no evento INSERT,      
22.      que disparou a trigger. */
23.  
24.   declare @vcd_empresa   smallint
25.   declare @vnr_serie     char(3)
26.   declare @vnr_nota      int
27.   declare @vcd_item      char(13)
28.   declare @vqt_faturada  decimal(10,4)
29.   declare @vvl_unitario  money
30.    
31.   declare cur_NF_Item cursor
32.    local fast_forward read_only for
33.     select cd_empresa
34.          , nr_serie
35.          , nr_nota
36.          , cd_item
37.          , qt_faturada
38.          , vl_unitario
39.       from inserted
40.        
41.   open cur_NF_Item
42.    
43.   while 1 = 1
44.   begin
45.     fetch next from cur_NF_Item into @vcd_empresa
46.                                    , @vnr_serie
47.                                    , @vnr_nota
48.                                    , @vcd_item
49.                                    , @vqt_faturada
50.                                    , @vvl_unitario
51.     if @@fetch_status <> 0 break
52.  
53.  
54.     /* Para cada item que foi inserido, executo o
55.        procedimento que movimenta estoque (só para
56.        exemplificar) */
57.     exec spcMovimentaEstoque @vcd_empresa
58.                            , @vnr_serie
59.                            , @vnr_nota
60.                            , @vcd_item
61.                            , @vqt_faturada
62.                            , @vvl_unitario
63.      
64.      
65.   end
66.   close cur_NF_Item
67.   deallocate cur_NF_Item
68.    
69. end

CABEÇALHO DE UMA TRIGGER

• O primeiro parâmetro é o NOME (tgiNotaItem) da trigger, forma como o objeto será identificado no banco de dados. Quando uma trigger é criada, é gerado um registro na tabela SYSOBJECTS. Para se atribuir o nome da trigger, tenho como padrão colocar tgiTabela (trigger de insert), tgdTabela (trigger de delete), tguTabela (trigger de update), ou somente tgTabela, quando ambas as situações são contempladas no código.
  
• O segundo parâmetro é o nome da tabela que será vinculada a esta trigger (no exemplo abaixo, estamos colocando um gatilho na tabela NotaItem).
  
• O terceiro parâmetro (“for insert”) determina o tipo de trigger, que vamos entender melhor cada uma adiante.
  

Code Snippet

1. create trigger trigger_name
2. on { table_name }
3. { FOR / AFTER | INSTEAD OF }
4. { [INSERT] [,] [DELETE] [,] [UPDATE] }
5. as
6. begin
7.  
8.    sql_statement [ ...n ]
9.  
10. end

Tipos de triggers

Existem 2 tipos de triggers no SQL Server (e na maioria dos SGBD do mercado).

AFTER: A trigger somente é executada após a gravação dos dados na tabela (padrão quando cria trigger)
INSTEAD OF: A trigger será disparada ANTES do registro ser modificado na tabela.

O tipo AFTER é o mais comum de ser utilizado. O tipo INSTEAD OF tem aplicações mais específicas. Os casos mais comuns em que utilizei este tipo de trigger foi para criar bloqueios em sistema que não detenho o código fonte (exemplo: lançamentos contábeis em período fechado, não permite gravar). Mais adiante falaremos mais sobre este tipo.

Exemplos:

AFTER

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. FOR { insert | delete | update }
3. as
4.  
5. OU
6.  
7. CREATE trigger tgiNotaItem on NotaItem
8. AFTER { insert | delete | update }
9. as

INSTEAD OF

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. INSTEAD OF { insert | delete | update}
3. as

Eventos de disparo de uma trigger

Uma trigger é disparada quando um evento de INSERT, DELETE ou UPDATE ocorre em uma tabela. Para isso, é necessário informar ao SQL que tipo de evento deseja tratar, que pode INSERT, DELETE ou UPDATE.

Exemplos:

Code Snippet

1.  
2. -- Trigger que será executada ao inserir um item no sistema     
3. CREATE trigger tgiNotaItem on NotaItem      
4. FOR insert
5. as
6.  
7.  
8.   sql_statement [ ...n ]
9.  
10.   
11. -- Trigger que será executada ao excluir um item no sistema
12. CREATE trigger tgdNotaItem on NotaItem
13. FOR delete
14. as
15.  
16.  
17.   sql_statement [ ...n ]
18.  
19.   
20. -- Trigger que será executada em qualquer operação de manipulação     
21. -- de dados na tabela (insert, delete ou update)
22. CREATE trigger tgNotaItem on NotaItem
23. FOR insert, delete, update
24. as
25.  
26.  
27.   sql_statement [ ...n ]

TABELAS “DE AMBIENTE” EXISTENTES NA EXECUÇÃO DE UMA TRIGGER

Ao desenvolver uma trigger, você precisa ter acesso aos dados EM ATUALIZAÇÃO (no caso de insert, quais dados estão sendo inseridos, para exclusão quais estão sendo excluídos, e assim por diante). Para isso, chamo-as de “tabelas de ambiente”, que só existem no momento em que uma trigger está sendo executada.

Existe duas tabelas, chamadas INSERTED (registros incluídos ou alterados – novos valores) e DELETED (registros excluídos ou alterados – valores anteriores). Estas tabelas “virtuais” possuem exatamente a mesma estrutura da tabela em que a trigger foi criada. Vamos separar somente o trecho do código que busca dados desta tabela.

Exemplo:

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. for insert
3. as
4. begin
5.  
6.   ...
7.  
8.   declare cur_NF_Item cursor
9.    local fast_forward read_only for
10.     select cd_empresa
11.          , nr_serie
12.          , nr_nota
13.          , cd_item
14.          , qt_faturada
15.          , vl_unitario
16.       from inserted
17.        
18.   open cur_NF_Item
19.    
20.   ...
21.    
22. end

Observe que estou buscando dados de uma tabela INSERTED, porém se olhar esta tabela no Object Explorer, perceberá que ela não existe. Ela somente vai existir durante a execução de uma trigger. Os updates são ainda mais interessantes. Toda vez que um UPDATE é feito em uma tabela, durante a execução da trigger nós teremos na tabela DELETED quais eram os dados antes do update, e na INSERTED quais são os novos valores dos registros alterados.

Fique atento para o próximo capítulo da saga. Na próxima parte vamos entender mais sobre a parte prática, parâmetros e o comportamentos das triggers.

=)