Armadilhas na programação de procedimentos de gatilho [caso 3]
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.