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 primeiro caso.

Estudo de casos

Caso 1

Eis o primeiro caso a analisar:

Ou seja, quando há a inclusão de mais de uma linha na tabela NotaAlunos, em uma mesma instrução INSERT, o procedimento triNotasAlunos não funciona.

No tópico não consta qualquer informação sobre a tabela NotaAlunos. Então, para este caso vamos considerar que a tabela NotaAlunos possua a seguinte estrutura:

-- código #1
CREATE TABLE NotaAlunos (
     matricula char(4) not null,
     nota1 decimal (4,1) null,
     nota2 decimal (4,1) null,
     media numeric (4,1) null,
     constraint I1_NotaAlunos primary key (matricula)
);
go

Sendo que o código do procedimento trigger é:

-- código #2
create trigger triNotaAlunos
  on NotaAlunos
  after insert, update
as
begin
update NotaAlunos set media = ((nota1 + nota2) / 2)
where matricula = (select matricula from inserted);
end;
go

e os seguintes códigos para inclusão de linhas na tabela:

-- código #3
INSERT into NotaAlunos (matricula, nota1, nota2)
         values ('2091', 33, 76);
go

e

-- código #4
INSERT into NotaAlunos (matricula, nota1, nota2)
  values  ('0192', 67, 84),
       ('7291', 34, 55),
       ('2099', null, 85),
       ('6483', null, null);
go
No código #3 há uma instrução INSERT para a tabela NotaAlunos, com uma linha a ser incluída. O processamento ocorre sem erros.

Por sua vez no código #4 também há uma instrução INSERT para a tabela NotaAlunos, mas com 4 linhas a serem incluídas na mesma execução. Ocorre o seguinte erro:

Mensagem 512, Nível 16, Estado 1, Procedimento triNotaAlunos
A subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue um =, !=, <, <= , >, >= ou quando ela é usada como uma expressão.
A instrução foi finalizada.

A mensagem de erro é clara sobre a causa: "A subconsulta retornou mais de um valor". E a subconsulta a que se refere está na linha

where matricula = (select matricula from inserted);

Ou seja, a subconsulta

(select matricula from inserted)

retornou mais de uma linha. Como no código #4 são incluídas 4 linhas, em uma mesma instrução INSERT, então na tabela virtual INSERTED há 4 linhas.

Esse é o erro mais comum na programação de procedimentos trigger, em T-SQL: programar o procedimento como se a cada chamada do procedimento fosse recebida uma única linha nas tabelas virtuais. Neste caso a correção é bem simples, bastando substituir o operador = pelo operador in, ficando a cláusula WHERE com a seguinte construção:

where matricula in (select matricula from inserted);

O código #2 é então reescrito para

-- código #2 v2
create trigger triNotaAlunos
  on NotaAlunos
  after insert, update
as
begin
update NotaAlunos set media = ((nota1 + nota2) / 2)
where matricula in (select matricula from inserted);
end;
go
Mas é necessário o uso de gatilho para algo tão simples como calcular a média de duas colunas e armazenar o resultado em uma terceira coluna? No SQL Server existe o conceito de coluna calculada, que nada mais é do que uma expressão que utiliza valores de outras colunas para calcular o valor da coluna.

Neste caso, no lugar do procedimento trigger triNotaAlunos seria preferível o uso de coluna calculada. O código de criação da tabela NotaAlunos passaria então para

-- código #1 v2
CREATE TABLE NotaAlunos (
  matricula char(4) not null,
  nota1 decimal (4,1) null,
  nota2 decimal (4,1) null,
  media as cast(((nota1 + nota2) / 2) as decimal(4,1)),
  constraint I1_NotaAlunos primary key (matricula)
);
go

Conclusão

  1. Um procedimento trigger em T-SQL deve ser programado considerando-se que as tabelas virtuais podem ter uma ou mais linhas;
  2. Nem sempre é necessário o uso de procedimento trigger.

Referências