Introdução

Participar dos fóruns de SQL Server é uma forma de agilizar o aprendizado de T-SQL, pois há uma variedade de ambientes e situações. Ao longo do tempo observa-se que há alguns temas que são recorrentes, com maior número de perguntas. Um destes temas refere-se à programação de procedimentos trigger, isto é, procedimentos armazenados que são executados automaticamente quando determinado gatilho é disparado.

Nesta série de artigos Armadilhas na programação de procedimentos de gatilho, em cada artigo será selecionada pergunta postada em fórum sobre o tema, servindo de desenvolvimento para o texto do artigo. Serão indicados erros presentes no código apresentado pelo autor e apresentadas possíveis soluções. Desta forma, o assunto será aprofundado gradativamente.

Este artigo trata do terceiro caso.

Estudo de casos

Caso 3

O terceiro caso também está relacionado a procedimento trigger acionado por instrução DELETE.

O autor do tópico percebe que o procedimento trigger não funciona corretamente quando mais de uma linha é apagada na tabela TBDebitoCreditoDescricaoESaque, mas não sabe a causa.

O código do procedimento trigger é o seguinte:

CREATE TRIGGER TR_TBDebitoDescricaoESaque
    ON TBDebitoDescricaoESaque
    FOR INSERT,UPDATE,DELETE
    AS
DECLARE @DbtDescrIDNovo INT,
        @DbtDescrIDVelho INT
SELECT @DbtDescrIDNovo = DbtDescrID FROM INSERTED
SELECT @DbtDescrIDVelho = DbtDescrID FROM DELETED
--ATIVA O DEBITO ANTERIOR
UPDATE TBDebitoDescricao
   SET DbtDescrSttID = 1
   WHERE DbtDescrID = @DbtDescrIDVelho
--DESATIVA O DEBITO ANTERIOR
UPDATE TBDebitoDescricao
   SET DbtDescrSttID = 2
   WHERE DbtDescrID = @DbtDescrIDNovo

Este caso tem o mesmo comportamento do caso 2: está preparado para tratar somente uma linha. No caso do procedimento ser acionado por execução de instrução DELETE, o comando

     SELECT @DbtDescrIDVelho = DbtDescrID FROM DELETED

após ser completamente executado, somente armazenará o valor da última linha retornada pelo comando, sendo as demais ignoradas. A princípio a solução seria a mesma do caso 2. Entretanto, este procedimento trigger trata não somente a instrução DELETE mas também as instruções INSERT e UPDATE. Então, é necessário maior atenção à programação do procedimento.

Como mencionado anteriormente, um procedimento trigger pode ser acionado para tratar nenhuma, uma ou várias linhas. Desta forma, o primeiro passo do procedimento é verificar se há alguma linha para tratar nas tabelas INSERTED e DELETED.

-- código #c3.1
-- analisa presença de linhas a tratar
declare @nIns int, @nDel int;
set @nIns= (SELECT count(*) from (SELECT top (2) * from INSERTED) as I);
set @nDel= (SELECT count(*) from (SELECT top (2) * from DELETED) as D);
 
-- encerra processamento se não há linhas a tratar
IF (@nIns = 0) and (@nDel = 0) return;

Uma forma simples de avaliar se há linhas na tabela virtual seria contar o número de linhas e então comparar se o valor é maior do que 0.

      SELECT count(*) from INSERTED

Mas se houver centenas de linhas na tabela virtual todas elas serão lidas, quando o que necessitamos é somente de saber se há ou não linhas na tabela. Para evitar a leitura completa uma solução é limitar o número de leituras usando TOP():

      SELECT top (2) from INSERTED

e a seguir contar o número de linhas retornadas:

      SELECT count(*) from (SELECT top (2) * from INSERTED) as I

Se o resultado for 0, significa que não há nenhuma linha na tabela virtual; se for 1, significa que há somente uma linha e se for 2 significa que há duas ou mais linhas na tabela virtual.

A seguir, o procedimento deve analisar qual foi o tipo de gatilho (INSERT, UPDATE ou DELETE) que o acionou e executar as devidas ações. Isto pode ser definido pela análise conjunta do número de linhas das tabelas virtuais INSERTED e DELETED.

-- código #c3.2
IF (@nIns > 0) and (@nDel = 0)
  -- INSERT
  ação para inclusão
else
IF (@nIns = 0) and (@nDel > 0)
  -- DELETE
  ação para apagamento
else
  begin
  -- UPDATE
  ação para atualização
  end;

Juntando as partes, temos o código final do procedimento trigger para tratar ações INSERT, DELETE e UPDATE na tabela TBDebitoDescricaoESaque.

-- código #c3.3
CREATE TRIGGER R_TBDebitoDescricaoESaque
     ON TBDebitoDescricaoESaque
     AFTER INSERT, UPDATE, DELETE  as
begin
 
-- analisa presença de linhas a tratar
declare @nInsI int, @nDel int;
set @nIns= (SELECT count(*) from (SELECT top (2) * from INSERTED) as I);
set @nDel= (SELECT count(*) from (SELECT top (2) * from DELETED) as D);
 
-- encerra processamento se não há linhas a tratar
IF (@nIns = 0) and (@nDel = 0) return;
 
--
IF (@nIns > 0) and (@nDel = 0)
  -- INSERT (desativa o débito anterior)
  UPDATE T
    set DbtDescrSttID = 2
    from TBDebitoDescricao as T
            inner join INSERTED as I on I.DbtDescrID = T.DbtDescrID
 
else
IF (@nIns = 0) and (@nDel > 0)
  -- DELETE (ativa o débito anterior)
  UPDATE T
    set DbtDescrSttID = 1
    from TBDebitoDescricao as T
            inner join DELETED as D on D.DbtDescrID = T.DbtDescrID
 
else
  begin
  -- UPDATE
  -- ativa o débito anterior
  UPDATE T
    set DbtDescrSttID = 1
    from TBDebitoDescricao as T
            inner join DELETED as D on D.DbtDescrID = T.DbtDescrID;  
  -- desativa o débito anterior
  UPDATE T
    set DbtDescrSttID = 2
    from TBDebitoDescricao as T
            inner join INSERTED as I on I.DbtDescrID = T.DbtDescrID;
  end;

Conclusão

As modificações propostas no código tornaram-no compatível com mais de uma linha a tratar nas tabelas virtuais. Além disso, o procedimento passou a tratar adequadamente os diferentes tipos de gatilho que o podem acionar, evitando executar trechos de código sem relação com o gatilho. 

Referências