Armadilhas na programação de procedimentos de gatilho [caso 1]
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
- Um procedimento trigger em T-SQL deve ser programado considerando-se que as tabelas virtuais podem ter uma ou mais linhas;
- Nem sempre é necessário o uso de procedimento trigger.