Piores Práticas – Elaborar triggers preparadas para linhas e não para conjuntos

Bom Dia Pessoal,

Após uma tempestuosa semana e iniciada a contagem regressiva para o show do Paul Van Dyk, estou dando uma passadinha por aqui. Já faz um tempinho que não posto nada na categoria "Piores Práticas". Hoje vi no fórum MSDN uma dúvida a respeito de triggers e fiquei pensando um pouco sobre elas. Não exatamente sobre as questões de desempenho, mas sobre a forma como elas normalmente são construídas e exemplificadas. A receita é bem simples. Basta construir a trigger, capturar o valor de cada coluna da INSERTED ou da DELETED em uma variável e fazer as devidas manipulações.

Embora a receita básica funcione na maioria das situações e seja impressionante o quanto se codifica dessa forma, a verdade é que ela é deficiente. Trabalhar com variáveis em triggers é restringí-las a registros e operações linha a linha. Toda vez que se constrói uma trigger com essa lógica, normalmente se retira a capacidade delas em trabalhar com múltiplos registros, pois, haverá problemas ou BUGs de aplicação. Vejamos essas limitações um pouco mais de perto.

— Muda o contexto para o TempDB
Use TempDB

— Cria um tabela de Produtos
CREATE TABLE Produto (
    ProdutoID INT NOT NULL,
    NomeProduto VARCHAR(50) NOT NULL,
    Estoque INT NOT NULL,
    UltimaAtualizacao DATE NOT NULL,

    CONSTRAINT PK_Produto PRIMARY KEY (ProdutoID))

— Cria uma tabela de Vendas
CREATE TABLE Venda (
    VendaID INT NOT NULL,
    DataVenda DATE NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, Preco SMALLMONEY NOT NULL,
    CONSTRAINT PK_Venda PRIMARY KEY (VendaID))

O raciocínio é bem simples. A cada venda realizada, o estoque do produto deve ser diminuído, pois, uma venda representa uma saída de estoque. Isso pode ser feito via aplicação ou via stored procedure, mas esse é um caso clássico para utilização de triggers. Vejamos um exemplo bem típico de implementação para esse caso.

— Cria uma trigger para atualizar o estoque
CREATE TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Captura os valores das colunas para as variáveis
    SELECT @ProdutoID = ProdutoID, @Quantidade = Quantidade, @DataVenda = DataVenda
    FROM INSERTED

    — Atualiza o estoque
    UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
    WHERE ProdutoID = @ProdutoID
END

Vejamos agora a trigger em ação. O código a seguir irá criar alguns produtos e efetuar algumas vendas. Se tudo funcionar como proposto, a cada venda o estoque deve ter a devida baixa.

— Insere dois produtos
INSERT INTO Produto (ProdutoID, NomeProduto, Estoque, UltimaAtualizacao)
VALUES (1,‘MP3 Player’,20,‘20100120’)

INSERT INTO Produto (ProdutoID, NomeProduto, Estoque, UltimaAtualizacao)
VALUES (2,‘Pen Drive’,17,‘20100120’)

— Insere três vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (1,‘20100121’,1,3,150.00)

INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (2,‘20100121’,1,2,145.00)

INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (3,‘20100122’,2,1,90.00)

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Antes de verificarmos os resultados provocados pela trigger, podemos fazer uma simples conta. O produto 1 (MP3 Player) possui 20 unidades e sua data de atualização foi 20/01/2010. Como foram realizadas duas vendas no dia 21, e cada venda teve respectivamente a quantidade de 3 e 2 unidades, o estoque deve ficar em 15 unidades e a última atualização deve ser dia 21/01/2010. No caso do produto 2 (Pen Drive), o estoque possuía 17 unidades. Com a venda do dia 22/01/2010 em uma unidade, o estoque deve ficar em 16 unidades e a data da última atualização deve ser 22/01/2010. O resultado do SELECT é exibido conforme a tabela abaixo:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 15 21/01/2010
2 Pen Drive 16 22/01/2010

Os resultados bateram com a conta e a trigger parece estar funcionando adequadamente, mas será que só isso é suficiente ? A trigger está preparada para lidar com inserções individuais, mas o que aconteceria se houvesse uma carga em lote ?

— Cria uma tabela para armazenar as vendas ao longo do dia (supostamente de outra loja)
CREATE TABLE VendaFilial (
    VendaID INT NOT NULL,
    DataVenda DATE NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, Preco SMALLMONEY NOT NULL,
    CONSTRAINT PK_VendaFilial PRIMARY KEY (VendaID))

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (4,‘20100122’,1,2,147.50)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (5,‘20100123’,1,1,148.50)

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

As duas vendas realizadas na filial e registradas nas tabelas de vendas de filial foram referente ao produto 1 (MP3 Player) e totalizam 3 unidades. Se o estoque do produto 1 era de 15 unidades e houve uma baixa de 3 unidades sendo a última venda do dia 23/01/2010, o natural é esperar que o estoque fique em 12 unidades e a data de atualização seja a do dia 23/01/2010. Vejamos o resultado da última consulta:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 13 22/01/2010
2 Pen Drive 16 22/01/2010

Ao contrário do esperado, o produto 1 (MP3 Player) ficou com o estoque em 13 unidades (e não 12) e a data de atualização foi 22/01/2010 e não 23/01/2010. A lógica da trigger não parece ter erros mas o que será que aconteceu ? Há algumas respostas possíveis, mas o fato é que a trigger foi preparada para trabalhar com linhas e não com conjuntos. O trecho de código simula o mesmo problema da trigger.

— Declara as variáveis para atualização
DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

— Captura os valores das colunas para as variáveis
SELECT @ProdutoID = ProdutoID, @Quantidade = Quantidade, @DataVenda = DataVenda
FROM VendaFilial

— Recupera o valor das variáveis
SELECT @ProdutoID As ProdutoID, @Quantidade As Quantidade, @DataVenda As DataVenda

O resultado é interessante. Embora a tabela VendaFilial tenha duas linhas, a atribuição das variáveis capturou apenas uma das linhas (no meu caso a do dia 23). Isso é compreensível, pois, as variáveis estão preparadas para receber um valor e não múltiplos valores. Se há por exemplo duas quantidades, não há como a variavél @quantidade capturar as duas quantidades (2 e 1). Se há duas datas, não há como a variável @DataVenda capturar as duas datas (22/01/2010 e 23/01/2010). É por isso que a trigger não funcionou. Por que ela estava preparada para trabalhar com uma linha por vez e quando foram submetidas duas linhas, ou melhor um conjunto de duas linhas, a trigger se perdeu e não pode calcular corretamente o estoque. Ainda que trocássemos o SELECT por SET, o problema iria persistir.

— Atualiza o produto 1 em relação ao estoque
UPDATE Produto SET Estoque = 15, UltimaAtualizacao = ‘20100121’
WHERE ProdutoID = 1

— Exclui as duas últimas vendas para não ocorrer violação de PK
DELETE FROM Venda WHERE VendaID > 3

— Troca o SELECT da trigger por SET
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Captura os valores das colunas para as variáveis
    SET @ProdutoID = (SELECT ProdutoID FROM INSERTED)
    SET @Quantidade = (SELECT Quantidade FROM INSERTED)
    SET @DataVenda = (SELECT DataVenda FROM INSERTED)

    — Atualiza o estoque
    UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
    WHERE ProdutoID = @ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

A substituição do SELECT pelo SET não muda o fato de que variáveis só podem receber um único valor. Como as inserções são feitas da tabela de filial para a tabela de vendas e a tabela de filial contém duas linhas, a tabela INSERTED fatalmente terá duas linhas (as triggers são disparadas por evento e não por linhas). No momento da atribuição da variável @Quantidade, a tabela INSERTED terá duas quantidades (2 e 1). No momento da atribuição da variável @DataVenda, a tabela INSERTED terá duas datas (22/01/2010 e 23/01/2010). Mesmo o ProdutoID sendo repetido, não é possível armazenar o ID 1 duas vezes na mesma variável em um mesma instrução. Após a execução, um erro é gerado:

Msg 512, Level 16, State 1, Procedure trgBaixaEstoque, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

O erro só evidencia o que já foi colocado. O fato das três subqueries retornarem dois valores cada e tentarem atribuir os resultados às variáveis @ProdutoID, @Quantidade e @DataVenda faz com que múltiplos valores sejam atribuídos às variáveis incorrendo no erro "Subquery returned more than 1 value". Isso mostra que em algumas situações usar o SET pode ser mais "inteligente" que o SELECT, pois, o SELECT repassou um cálculo errado enquanto que o SET impediu o cálculo. Entretanto, ambas as implementações foram preparadas para utilizar linhas e não conjuntos.

Alguns praticantes do ORACLE vão dizer que "se fosse no ORACLE, teríamos o comando FOR EACH ROW dentro da trigger". Concordo que o FOR EACH ROW tem sua utilidade e que possivelmente veremos alguma coisa desse tipo em uma release futura do SQL Server, mas não acho que ele seja a solução para os problemas. Na verdade acho que ela é a continuação para a filosofia "linha a linha" que performaticamente não é a melhor situação para esse caso. Como o SQL Server ainda não possui o recurso de FOR EACH ROW dentro da trigger, a solução é utilizar os cursores que podem trabalhar linha a linha.

— "Prepara" a trigger para operar múltiplas linhas
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Declara um cursor para "varrer" a INSERTED
    DECLARE cVendas CURSOR
    FAST_FORWARD
    FOR SELECT
ProdutoID, Quantidade, DataVenda FROM INSERTED
    — É necessário ordenar para evitar que uma venda mais antiga atualize a data erroneamente
    ORDER BY ProdutoID, DataVenda    

    — Abre o cursor
    OPEN cVendas

    — Lê o primeiro registro
    FETCH NEXT FROM cVendas INTO @ProdutoID, @Quantidade, @DataVenda

    — Varre os demais registros
    WHILE @@FETCH_STATUS = 0
    BEGIN
        — Atualiza o estoque
        UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
        WHERE ProdutoID = @ProdutoID

        — Passa para o próximo registro
        FETCH NEXT FROM cVendas INTO @ProdutoID, @Quantidade, @DataVenda
    END

    — Fecha o cursor
    CLOSE cVendas

    — Desaloca o cursor
    DEALLOCATE cVendas
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Como o cursor faz a varredura linha a linha, dessa vez o estoque e a última atualização foram atualizados corretamente:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 12 23/01/2010
2 Pen Drive 16 22/01/2010

Dizem que cursores não são performáticos. Falam o mesmo sobre as triggers. Se cursores não são performáticos e triggers também não, é bem provável que colocar um cursor dentro de uma trigger não seja nada performático. Vejamos então como resolver isso de uma maneira simples e performática.

— Exclui as vendas da filial
DELETE FROM VendaFilial

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (6,‘20100124’,1,2,146.00)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (7,‘20100125’,2,3,85.50)

— Altera a trigger
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Atualiza a tabela de produtos
    UPDATE Produto SET Estoque = Estoque – Quantidade, UltimaAtualizacao = DataVenda
    FROM Produto As Prod
    INNER JOIN INSERTED As Ins ON Prod.ProdutoID = Ins.ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Se a conta for feita, o produto 1 (MP3 Player) tinha 12 unidades e a última atualização era 23/01/2010. No caso do produto 2, o estoque era de 16 unidades e a última atualização era de 22/01/2010. Com as últimas vendas, o produto 1 teve uma baixa de 2 unidades no dia 24/01/2010 e o produto 2 teve uma baixa de 3 unidades no dia 25, o que deixa os produtos com 10 e 13 unidades respectivamente.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 13 25/01/2010

Se o código dessa nova versão da trigger for comparado com o código anterior há notórias vantagens. O tamanho do código é muito inferior o que acaba tornando-o muito mais simples e fácil de manter. Substituir o uso do cursor por um único comando, sem declaração de variáveis, e atualizações linha a linha é também muito mais performático.

Embora a última versão da trigger tenha melhorado a qualidade do código, ela ainda possui uma falha. Vejamos o exemplo abaixo:

— Exclui as vendas da filial
DELETE FROM VendaFilial

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (8,‘20100127’,2,1,90.00)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (9,‘20100126’,2,4,87.50)

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

As duas vendas são do produto 2 (Pen Drive) e totalizam 5 unidades. Embora seja o primeiro registro, a venda mais recente é do dia 27/01/2010. Se a trigger funcionar corretamente, o estoque deve ficar em 8 unidades e a data da última atualização deve ser 27/01/2010.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 9 26/01/2010

A trigger não funcionou corretamente, pois, o estoque ficou em 9 unidades e a data da última atualização ficou em 26/01/2010. Visualmente é possível perceber que a primeira venda não foi considerada. Se o estoque possuía 13 unidades e ficou com apenas 9 unidades e a data de atualização foi 26/01/2010 é porque apenas a segunda venda foi considerada.

Isso ocorre porque um comando de updade só pode atualizar um registro apenas um vez. A presença de duas vendas para o produto 2 (Pen Drive) iria requerer duas atualizações para esse produto, mas não é possível fazer isso em um único update. Para evitar essa restrição, basta fazer um pequeno ajuste na trigger.

— Atualiza o produto 2 em relação ao estoque
UPDATE Produto SET Estoque = 13, UltimaAtualizacao = ‘20100125’
WHERE ProdutoID = 2

— Exclui as duas últimas vendas (8 e 9)
DELETE FROM Venda WHERE VendaID IN (8,9)

— Altera a trigger
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Totaliza as vendas por produto
    ;WITH UltimasVendas (ProdutoID, QuantidadeTotal, UltimaVenda)
    As (
        SELECT ProdutoID, SUM(Quantidade), MAX(DataVenda)
        FROM INSERTED
        GROUP BY ProdutoID)

    — Atualiza a tabela de produtos
    UPDATE Produto SET Estoque = Estoque – QuantidadeTotal, UltimaAtualizacao = UltimaVenda
    FROM Produto As Prod
    INNER JOIN UltimasVendas As UV ON Prod.ProdutoID = UV.ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

A primeira parte do script retorna a situação do produto para o momento anterior ao das vendas 8 e 9 realizadas na filial. Essa alteração foi feita, porque o estoque tinha sido incorretamente calculado no pela versão anterior da trigger. Ainda nessa primeira parte, as vendas 8 e 9 são excluídas da tabela Vendas para serem reinseridas com o disparo da trigger. A segunda parte do script altera o código da trigger e faz a reinserção das vendas da filial para que o estoque e data da última atualização dos produtos seja recalculada.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 8 27/01/2010

Dessa vez a atualização do produto foi feita corretamente. O estoque ficou em 8 unidades e a data da última atualização foi 27/01/2010. Como a trigger totalizou os dados de produto somando a quantidade e calculado a última data, cada produto terá apenas uma única ocorrência na CTE UltimasVendas. Isso possibilita que o UPDATE seja feito corretamente e o estoque seja atualizado. Mesmo com o passo adicional o código ainda se torna simples e enxuto. Não foi declarado um único cursor e nem sequer uma variável. Enquanto o primeiro código estava preparado para operar apenas um linha por vez, esse código está apto a trabalhar com quantas vendas ocorrerem. Essa é a diferença entre trabalhar em uma filosofia linha a linha e uma filosofia baseada em conjuntos. A filosofia em conjuntos além de mais performática, consegue trabalhar com linhas individuais e o contrário não é verdadeiro.

Haverá aqueles que questionarão esse exemplo. Se existir uma tabela de filial, a solução seria bem simples. Bastaria incluir a trigger na tabela de vendas da filial e assim os produtos seriam atualizados automaticamente. Para combinar as vendas um UNION ALL bastaria. A verdade é que dificilmente haverá uma tabela de vendas da filial. A idéia de criá-la foi apenas para mostrar as deficiências de triggers preparadas para lidar linha a linha principalmente em processos de carga. Podemos imaginar as mesmas consequências, se por exemplo, houvesse uma carga de vendas a partir de um arquivo texto, um job, um processo de replicação, ou um pacote SSIS. Independente do método, o fato é que triggers linha a linha não estão prontas para operar operações que envolvam mais de um registro e triggers com lógica baseadas em conjunto sempre estarão aptas a trabalhar com qualquer situação seja um ou registro por vez ou vários de uma única só vez. Por essas e outras que o mais recomendável é sempre pensar em conjuntos e nunca em linhas quando estiver codificando uma trigger.

[ ]s,

Gustavo

7 Respostas para “Piores Práticas – Elaborar triggers preparadas para linhas e não para conjuntos

  1. Salve Gustavo,Cara ja que tu falou de trigger, tenho uma dúvida relacionada a DDL.Eu preciso criar um log das ações efetuados pelos usuários (inserts, updates e deletes).Atualmente tenho uma DDL que fica logando isso em um banco separado da produção. Seria mais ineressante uma outra solução ou uma DDL embora podendo criar futuros problemas de performance ainda é a melhor solução?Estou usando SQL 2005 e projetando a migração para o 2008.

  2. Leonardo da Silva

    Amigo segui passo a passo que voce falou mais tem um erro que ate agora nao consegui solucionar:

    nessa ultima trigger que voce fez fica dando o erro:

    Incorrect syntax near the keyword ‘WITH’.
    Incorrect syntax near the keyword ‘END’.

    se tiver como arrumar o codigo desde ja agradeçemos pois esse conteudo é de muita valia!!

    • Olá Leonardo,

      Esse problema pode acontecer se você estiver utilizando o SQL Server 2000 que não dá suporte a CTEs. Qual SQL Server (versão e edição) você está utilizando ?

      [ ]s,

      Gustavo

      • Leonardo da Silva

        Sim gustavo realmente eu uso o SQL Server 2000, como eu posso resolver meu problemas de atualizar estoque com o 2000 ja que o mesmo nao tem suporte para CTEs???

      • Boa Tarde,

        O melhor mesmo seria que você “largasse” o 2000, pois, hoje está sem suporte e descontinuado (já estamos em 2011 (rs)). Para contornar a ausência das CTEs, tente utilizar um Subquery. Ex:

        UPDATE Produto SET Estoque = Estoque – QuantidadeTotal, UltimaAtualizacao = UltimaVenda
        FROM Produto As Prod
        INNER JOIN (SELECT ProdutoID, SUM(Quantidade) As QuantidadeTotal, MAX(DataVenda) As UltimaVenda
        FROM INSERTED
        GROUP BY ProdutoID) As UV ON Prod.ProdutoID = UV.ProdutoID

        Não testei, mas acredito que resolva o seu problema.

        [ ]s,

        Gustavo

  3. Leonardo da Silva

    Gustavo obrigado por sua atenção…

    eu migrei para o Sql 2005 e o codigo que estava dando erro agora esta totalmente funcional…

    e so uma curiosidade boba… o que significa [ ]s, na sua assinatura??

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s