Um caso de recursividade

Aproveitando um recente trabalho realizado, gostaria de descrever um relatório que rapidamente desenvolvi em uma base de dados SQL Server. A situação era a seguinte: eu havia importado a estrutura de um produto, e precisava exibir esta estrutura na forma de um relatório. Primeiro, vamos entender o termo “estrutura de um produto”. A empresa em questão é uma fábrica de impressoras de plotagem (1,8m a 3,2m). A ficha técnica da impressora é composta por vários níveis de componentes. Vou ilustrar uma composição:

 

1. Impressora XPTO modelo 1
1. Conjunto eletrônica XPTO 1804
1. Tomada de embutir 3 pinos
2. Cinta nylon média
3. Fusível pequeno 10A
4. Sensor de temperatura
5. Ventilador – secagem
1. Espaguete termo retrátil
2. Ventilador 120x120x38 c/ rosca
3. Conector modular miniatura H89F09
6. Blower – vácuo
1. Blower ( Siroco ) AD xxxx 24v
2. Conector modular miniatura H90F23
7. Aquecimento – cabeças
1. Resistor Fio 120R x 10W
2. Cabo paralelo 0,20mm
3. Apoio de silicone 13mm x 4mm
4. Terminal KK pequeno importado

 

O relatório precisa aparecer formatado como está acima (indentado nos níveis). Porém, a quantidade de níveis é indefinida. Desta forma, utilizei o conceito de “recursividade”.

Definições de recursividade na internet:

• Recursão é um método de programação no qual uma função pode chamar a si mesma. O termo é usado de maneira mais geral para descrever o processo de repetição de um objeto de um jeito similar ao que já fora mostrado. ...
  pt.wikipedia.org/wiki/Recursividade
  
• A recursividade na programação de computadores envolve a definição de uma função que pode invocar a si própria. Um exemplo de aplicação da recursividade pode ser encontrado nos analisadores gramaticais recursivos para linguagens de programação. ...
  pt.wikipedia.org/wiki/Recursividade_(ciência_da_computação)
  
• iteratividade; propriedade de formar infinitos elementos à partir de finitos aplicando-se regras lógicas ou matemáticas
  pt.wiktionary.org/wiki/Recursividade
  
• que chama a si mesmo (programa ou função)
  pt.wiktionary.org/wiki/Recursivo
  

Para desenvolver este relatório, eu utilizei este conceito de recursividade. Eu criei um procedimento insere os componentes, e para cada componente que está inserindo, chama a si mesmo para repetir a explosão, independente da quantidade de níveis que houver na estrutura.

O primeiro código que vamos analisar é o que retorna o relatório ao usuário. Ele somente recebe os parâmetros, e chama o procedimento de explosão da estrutura, chamado spcAdhExpEst (procedimento recursivo).

Code Snippet

1.  
2. CREATE proc spcAdhExplodeProduto
3.   @cd_item char(13)
4.   
5. as
6.  
7.   /*
8.    Autor: Fabiano Cores
9.    Data : 23/12/2010
10.    Site : http://sqlburger.blogspot.com
11.    Desenvolvido para demonstrar a utilização de procedimentos
12.    recursivos no SQL Server. A reprodução deste artigo só é
13.    permitida se forem mantidos os créditos do autor.
14.    */
15.  
16.   -- Esta tabela possui papel fundamental na recursividade. Esta
17.   -- tabela irá existir durante toda a execução deste procedimento
18.   -- mesmo quando chamamos executamos um segundo procedimento.
19.   create table #retorno (
20.     cd_componente char(18) collate database_default null
21.   , qt_aplicada decimal(10,4) null
22.   , nivel smallint null
23.   , ordem int not null identity)
24.   
25.   -- Insiro o item pai, para que o usuário saiba de qual produto
26.   -- está tirando o relatório
27.   insert into #retorno (cd_componente, qt_aplicada, nivel)
28.     select @cd_item, 1, 1
29.   
30.   -- Este é o procedimento recursivo. Ele fará referencia a ele
31.   -- mesmo, enquanto preenche a tabela #retorno
32.   exec spcAdhExpEst @cd_item
33.  
34.   -- Aqui, eu pego os registros da tabela #retorno, e retorno
35.   -- ao software .NET, para exibição ao usuário.
36.   select convert(char(18), space((nivel-1) * 2) + a.cd_Componente) as 'Código'
37.        , convert(char(65), space((nivel-1) * 2) + b.descricao) as 'Descrição'
38.        , a.qt_aplicada 'Quantidade'
39.        , a.nivel -1 as 'Nível'
40.     from #retorno a, item b
41.    where a.cd_componente = b.cd_item
42.    order by a.ordem
43.  
44. --

Este é o código do procedimento recursivo. Ele insere na tabela #retorno, que foi criada pelo seu procedimento antecessor (spcAdhExplodeEstrutura), e faz referência a si mesma:

Code Snippet

1. --
2.  
3. CREATE proc spcAdhExpEst
4.   @cd_item char(13)
5.   
6. as
7.  
8.    /*
9.    Autor: Fabiano Cores
10.    Data : 23/12/2010
11.    Site : http://sqlburger.blogspot.com
12.    Desenvolvido para demonstrar a utilização de procedimentos
13.    recursivos no SQL Server. A reprodução deste artigo só é
14.    permitida se forem mantidos os créditos do autor.
15.    */
16.  
17.   declare @vcd_componente char(13)
18.   declare @vqt_aplicada decimal(19,4)
19.  
20.   -- Insere os componentes simples (não possuem estrutura abaixo)
21.   insert into #retorno (cd_componente, qt_aplicada, nivel)
22.     select a.cd_componente, a.qt_aplicada, @@nestlevel
23.       from processoitem a, item b
24.      where a.cd_item = @cd_item
25.        and a.cd_componente = b.cd_item
26.        and not exists (select 0
27.                          from processoitem c
28.                         where c.cd_item = a.cd_componente)
29.      order by b.descricao
30.  
31.   -- Aqui é montado um cursor nos itens complexos (que possuem
32.   -- estrutura). É utilizado um cursor por que para cada item
33.   -- que possui estrutura, eu vou fazer a chamada recursiva.
34.   declare c1 cursor local fast_forward read_only for
35.     select a.cd_componente, a.qt_aplicada
36.       from processoitem a, item b
37.      where a.cd_item = @cd_item
38.        and a.cd_componente = b.cd_item
39.        and     exists (select 0
40.                          from processoitem c
41.                         where c.cd_item = a.cd_componente)
42.      order by b.descricao
43.      
44.   open c1
45.   
46.   while 1 = 1
47.   begin
48.     fetch next from c1 into @vcd_componente, @vqt_aplicada
49.     if @@fetch_status <> 0 break
50.     
51.     insert into #retorno (cd_componente, qt_aplicada, nivel)
52.       select @vcd_componente, @vqt_aplicada, @@nestlevel
53.       
54.     -- Chamada recursiva (executando ele mesmo)
55.     exec spcAdhExpEst @vcd_componente
56.     
57.   end
58.   close c1
59.   deallocate c1
60.  
61. --

 

Detalhes importantes que devem ser observados:

1. A tabela temporária foi criada no primeiro procedimento. Se ela fosse criada no procedimento recursivo, daria erro, pois ele tentaria criar mais de uma vez a mesma tabela, desta forma, ela sendo criada apenas uma vez pelo procedimento pai, não haverá problemas.

2. O procedimento filho (spcAdhExpEst) consegue enxergar a tabela #retorno.

3. A variável @@nestlevel é uma variável de ambiente, que retorna o nível de aninhamento* da execução do procedimento. Isto é uma forma de saber quantas vezes o procedimento já foi disparado por ele mesmo (na primeira execução, @@nestlevel é igual a 0). Aproveitei esta variável para incluir no relatório a que nível o componente pertence, e também a utilizei como parâmetro para adicionar espaços à esquerda do código e da descrição, para melhorar visualmente a identificação dos níveis na leitura do relatório.

*Definições de aninhar na internet:

• aninhado - Quando se tem um objeto dentro do outro, como uma tabela, por exemplo , você possui um aninhamento , ou seja, um objeto funcionando como ninho para outro objeto.
  www.juliobattisti.com.br/tutoriais/keniareis/dicionarioinfo001.asp

 

 

Eu estou postando esta dica por que hoje tive uma experiência muito desagradável… um certo programador teve a capacidade de fazer 9 cursores (loops), um dentro do outro, para tratar possíveis 9 níveis do produto. Além de ser um código absurdamente mal pensado, é gigantesco, lento, péssimo de realizar manutenção, entre outros comentários que não vêem ao caso. E como padrão, quando pego código assim, tenho o hábito de selecionar tudo, apagar, refazer… e claro, compartilhar!

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!

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.

=)

SQL Dinâmico – usar código para criar código

Hoje vou demostrar um pequeno exemplo de como utilizar programação dinâmica no SQL. No exemplo aqui demonstrado, utilizo o SQL dinâmico para definir a estrutura de uma tabela temporária de acordo com os dados que desejo exibir.

Eu montei rapidamente um modelo similar a um relatório que fiz recentemente. Eu precisava retornar os fornecedores como colunas, e doze meses como linhas, para montar uma grande planilha de dados. Para poder retornar o fornecedor na coluna, montei um loop nos fornecedores que deverão entrar no relatório, e utilizei o nome fantasia como nome da coluna que receberá o valor do mês/ano específico. Dentro deste mesmo loop, já busco o valor dos títulos deste fornecedor, e atualizo a tabela temporária. Depois só retorno os resultados.

Vamos então ver o código. Tentei comentar da melhor forma possível para explicar cada passo do procedimento.

create proc spcExemploSQLDinamico as       /*        Autor: Fabiano Cores    Data : 04/12/2010    Site : http://sqlburger.blogspot.com         Desenvolvido para demonstrar a utilização de querys    dinâmicas no SQL Server. A reprodução deste artigo    só é permitida se forem mantidos os créditos do autor.      */    declare @sql            varchar(8000)     declare @vcd_cadastro   int     declare @vfantasia      varchar(20)     declare @valor          money     declare @data           smalldatetime        -- Crio a temporária que receberá modificações dinâmicas     create table #tmp (       mes tinyint null     , ano smallint null )        -- Preencho a tabela com as datas. Pego o ano atual,     -- e defino como primeiro de janeiro, para pegar o ano     -- inteiro.     select @data = rtrim(year(getdate())) + '-01-01'        set @sql = ''       while 1 = 1     begin          -- Aqui estou montando uma query dinâmica. Veja que estou       -- concatenando várias querys em uma variável VARCHAR, para       -- posterior execução.       select @sql = @sql                   + 'insert into #tmp (mes, ano) '                   + 'values ('                   + rtrim(month(@data)) + ','                   + rtrim(year(@data)) + ')'                   + char(13) + char(10)                        -- Caso tenha inserido os 12 meses do ano, saio do loop       if month(@data) = 12         break          -- A cada ciclo do loop, pulo um mês       select @data = dateadd(month, 1, @data)        end        -- Aqui executo a query montada acima.     exec (@sql)     -- limitei em um TOP 25 para não trazer muitos registros     -- O distinct é para que retorne apenas uma vez cada     -- fornecedor neste select.     declare cp1 cursor local fast_forward read_only for       select distinct top 25 a.cd_cadastro            , a.fantasia         from cadastro a            , fornecedor b            , titulofor c        where a.cd_cadastro         = b.cd_cadastro          and a.situacao            = 'A'          and a.cd_cadastro         = c.cd_cadastro          and year(c.dt_lancamento) = year(getdate())          and c.situacao           in ('A', 'L')        order by a.fantasia            /*    As tabelas aqui utilizadas e parâmetros devem ser     desconsiderados. Adapte este procedimento ao seu    banco de dados.    */       open cp1         while 1 = 1     begin       fetch next from cp1 into @vcd_cadastro                              , @vfantasia       if @@fetch_status <> 0 break             -- Adiciono colunas na tabela com o nome do fornecedor       select @sql = 'alter table #tmp add ['                   + rtrim(ltrim(@vfantasia))                                         + '] money null'             exec (@sql)             select @data = rtrim(year(getdate())) + '-01-01'         while 1 = 1       begin               -- Sempre inicialize uma variável dentro de um loop.         set @valor = 0;                 select @valor = sum(vl_titulo)           from titulofor          where situacao            in ('A', 'L')            and cd_cadastro          = @vcd_cadastro            and month(dt_lancamento) = month(@data)            and year(dt_lancamento)  = year(@data)               select @valor = isnull(@valor, 0)                 -- Para montar o update, uso novamente o nome        -- fantasia do fornecedor para saber qual o        -- nome da coluna que preciso atualizar o valor.         select @sql = 'update #tmp '                     + '   set ['                     + rtrim(ltrim(@vfantasia)) + '] = '                     + rtrim(@valor)                     + ' where mes = ' + rtrim(month(@data))                     + '   and ano = ' + rtrim(year(@data))                             exec (@sql)                 if month(@data) = 12           break               select @data = dateadd(month, 1, @data)             end                 end     close cp1     deallocate cp1         select @sql = 'select * from #tmp order by ano, mes'         exec (@sql)

O resultado é um relatório estruturado dinamicamente:

Afinal de contas, maiúsculo ou minúsculo?

Este é apenas um pequeno post “desabafo”. Muitos programadores me questionam por que eu programo em SQL usando somente letras minúsculas. Vamos fazer uma pequena comparação de um código do jeito que eu faço, um no padrão que os programadores dizem ser o “correto”, e mais para o final, um terceiro procedimento, que é código interno do SQL.

Maneira como eu programo	Padrão considerado “correto”

Em primeiro lugar, minha influência em programação veio de meu pai, que sempre programou em C. Uma coisa inclusive que detesto no C++/C# é o fato de ser case sensitive. Isso é irritante! Hoje com as ferramentas atuais (visual studio por exemplo) isso “teoricamente” não é mais problema.

Bom, eu honestamente, acho uma grande besteira ficar ligando e desligando o CAPS LOCK enquanto se programa. Se o SQL tem o recurso de identificar todas as palavras do código por cor, pra você identificar comandos, strings, operadores, não vejo sentido nenhum nessa formatação. E pessoalmente, acho muito mais agradável e bonito o código sem letras maiúsculas, pra mim só poluem a tela.

Mas se ainda insistir em achar que eu estou errado, vamos dar uma olhadinha então nos códigos de programadores que desenvolveram o SQL! Sim, podemos ver esses códigos! Vamos pegar aqui, duas funções muito comuns do SQL (sp_help e sp_spaceused), e ver como é o código delas (basta usar sp_helptext para visualizar o código do procedimento):

exec sp_helptext 'sp_help'

exec sp_helptext 'sp_spaceused'

Se quiser ver no seu próprio SQL, é só executar “ sp_helptext 'sp_spaceused' ” ou “ sp_helptext 'sp_help' ” e você vai ver como foram feitos o código T-SQL dos procedimentos internos mais utilizados do SQL Server.

:D