SQL Server : T-SQL - Updates, Joins e Triggers - I

 Neste artigo vamos recordar o conceito de Triggers e sua aplicação de forma correta com T-SQL.

A Transact-SQL ou T-SQL é uma extensão da linguagem SQL implementada pela Microsoft para o SQL Server. Ela acrescenta recursos evoluindo as seguintes características do SQL:

O T-SQL é o cerne do SQL Server. Todos os aplicativos que se comunicam com uma instância do SQL Server o fazem enviando instruções Transact-SQL ao servidor, independentemente da interface do usuário.

O Microsoft SQL Server usa palavras-chave reservadas para definir, manipular e acessar bancos de dados. As palavras-chave reservadas fazem parte da gramática da linguagem Transact-SQL usada pelo SQL Server para analisar e compreender as instruções e os lotes Transact-SQL. Embora seja sintaticamente possível usar as palavras-chave reservadas do SQL Server como identificadores e nomes de objeto em scripts Transact-SQL, você só pode fazer isso usando identificadores delimitados.

Neste artigo vou mostrar como usar Triggers, Updates e Joins.  Um erro comum ao usar Triggers SQL é esquecer que uma operação que afeta várias linhas fará com que um disparador seja executado apenas uma vez. Para resolver esse problema podemos usar instruções UPDATE que incluem junções.

 Triggers

Os SQL Server Triggers são úteis quando você deseja adicionar scripts a um banco de dados que é executado automaticamente quando os dados são inseridos, atualizados ou excluídos. Por exemplo, você pode optar por auditar todas as modificações nos dados de uma tabela criando triggers que copiem os dados alterados para uma tabela separada, juntamente com a data e hora atuais e o nome do usuário que executa a ação.

Quando os dados são modificados, cada trigger que reage à mudança é executado apenas uma vez. Se você alterar dez linhas com uma única instrução UPDATE, um trigger vinculado vai ser executado uma vez, e não dez vezes. O conjunto completo de dados atualizados estará presente nas tabelas temporárias inseridas e excluídas durante o escopo do trigger. Se você precisa executar uma atualização para cada uma das linhas alteradas, você deve lidar com isso no código do trigger.

Um erro comum é criar triggers que são assumidos para serem executados uma vez por cada linha alterada. Outro erro é assumir que apenas uma linha será alterada em uma única atualização. Ambos podem introduzir bugs no seu software, o que pode não ser notado imediatamente.

Neste artigo, criaremos um banco de dados simples e adicionaremos um trigger de atualização para uma das suas tabelas. Vamos criar um Trigger defeituoso que falha quando usado para várias linhas, e , a seguir vamos corrigir o trigger e mostrar a sua operação correta.

Recursos usados:

Criando o banco de dados e as tabelas

Vamos supor que temos um sistema de controle de estoques onde temos um banco de dados Estudo.mdf e duas tabelas :  Estoque e Pedidos.

A tabela Estoque é usada para manter os níveis de estoque atuais dos itens estocados da empresa.

O Script SQL para criar a tabela Estoque é o seguinte:

use Estudo
CREATE TABLE Estoque
(
    CodigoEstoque CHAR(4),
    Quantidade INT NOT NULL,
    Alocado INT NOT NULL CONSTRAINT DF_Alocado DEFAULT 0,
    Disponivel AS Quantidade - Alocado
)

a - A coluna CodigoEstoque contém um código exclusivo para cada item;
b - A coluna Quantidade descreve o número de itens que estão presentes no estoque;
c - A coluna Alocado especifica o número de itens no estoque que foram alocados para pedidos e que podem ser vendidos novamente.(Para novos produtos o valor padrão alocado é zero)
d - A coluna Disponivel mostra o número de itens ainda disponíveis para venda;

A tabela Pedidos trata pedidos para itens em estoque.

O Script SQL para criar a tabela Pedidos é o seguinte:

use Estudo
CREATE TABLE Pedidos
(
    CodigoEstoque CHAR(4),
    Quantidade INT
)

Cada pedido é para um único item, definido pelo valor na coluna CodigoEstoque.

A coluna Quantidade trata o número de itens pedidos.

A seguir vamos definir um script SQL para incluir informações na tabela Estoque :

use Estudo
INSERT INTO Estoque (CodigoEstoque, Quantidade) VALUES('A100', 100)
INSERT INTO Estoque (CodigoEstoque, Quantidade) VALUES('B200', 200)
INSERT INTO Estoque (CodigoEstoque, Quantidade) VALUES('C300', 300)

Incluimos 3 registros na tabela Estoque para os campos ou colunas CodigoEstoque e Quantidade.

Executando uma consulta seleção na tabela Estoque :

SELECT TOP 100 [CodigoEstoque]
,[Quantidade]
,[Alocado]
,[Disponivel]
FROM [Estudo].[dbo].[Estoque]

Iremos obter o seguinte resultado:

Criando um Trigger que falha

Podemos criar um Trigger usando o comando Create Trigger do SQL Server em uma janela de consulta.

Quando você for criar um Trigger deverá definir :

  1. O nome
  2. A Tabela para o qual o Trigger irá ser criado
  3. Quando o Trigger deverá ser disparado
  4. Os comandos que determinam qual ação o Trigger deverá executar

Vamos criar um trigger chamado PedidosAutoAlocados conforme o script a seguir:

CREATE TRIGGER PedidosAutoAlocados
ON Pedidos
AFTER INSERT
AS 
DECLARE @CodigoEstoque CHAR(4)
DECLARE @Quantidade INT
 
SELECT @CodigoEstoque = CodigoEstoque, @Quantidade = Quantidade FROM inserted
 
UPDATE Estoque
SET Alocado = Alocado + @Quantidade
WHERE CodigoEstoque = @CodigoEstoque

 

Após executar o comando acima podemos verificar se o trigger foi realmente criado abrindo a tabela Pedidos e examinando os objetos Gatilhos (Triggers) :

Esse Trigger vai funcionar como esperado apenas se um único pedido for criado.

Execute executar o script a seguir que inclui uma linha na tabela e a seguir consulta a tabela Estoque:

INSERT INTO Pedidos
   (CodigoEstoque, Quantidade)
VALUES
   ('A100', 25)

SELECT * FROM Estoque

Veja o resultado obtido:

Vamos entender o que aconteceu:

Os níveis de estoque depois de encomendar 25, vinte e cinco, unidades de produtos "A100" mostram uma alocação adequada e um nível reduzido de estoque disponível(75).

Tudo bem. Era isso que estávamos esperando.

O problema com o Trigger vai aparecer quando incluirmos mais de uma linha por vez na tabela.

Tente executar o seguinte script :

INSERT INTO Pedidos(CodigoEstoque, Quantidade)
SELECT CodigoEstoque, 1 FROM Estoque

Aqui estamos criando um pedido para cada código de estoque conhecido. Veja como ficou a tabela Pedidos:

Agora ao exibirmos o conteúdo da tabela Estoque você vai ver que apenas um nível de estoque foi atualizado.

No resultado apresentado abaixo, os níveis de estoque "B200" e "C300" estão inalterados.

Dessa forma o Trigger falhou e não atualizou a tabela Estoque para B200 e C300.

Como podemos resolver esse problema ???

Aguarde a próxima parte do artigo onde vamos usar Update e Join para resolver o problema com o Trigger.

(Disse Jesus) - Eu sou a videira verdadeira, e meu Pai é o lavrador.
Toda a vara em mim, que não dá fruto, a tira; e limpa toda aquela que dá fruto, para que dê mais fruto.
João 15:1,2

Referências:

José Carlos Macoratti