Bom tarde!
Estou com uma dúvida sobre o comportamento da variável OLD dentro de uma
trigger ao deletar várias linhas num mesmo comando. Segue o seguinte
exemplo:
Tabela de exemplo:
CREATE TABLE teste_delete(
seq bigint,
ant bigint,
qtd bigint,
sld bigint
);
INSERT INTO teste_delete (seq, ant, qtd, sld) VALUES (1,0,1,1), (2,1,2,3),
(3,3,4,7), (4,7,2,9), (5,9,9,18), (6,18,1,19);
Trigger para recalcular as colunas ant e sld ao remover algum registro:
CREATE OR REPLACE FUNCTION func_teste_delete()
RETURNS trigger AS
$BODY$
DECLARE
movReg RECORD;
seq int8;
qtdAnt int8;
qtdNova int8;
BEGIN
qtdAnt = OLD.ant;
FOR movReg IN (select * from teste_delete where teste_delete.seq >
OLD.seq)
LOOP
qtdNova = qtdAnt + movReg.qtd;
update teste_delete
set sld = qtdNova, ant = qtdAnt
WHERE teste_delete.seq = movReg.seq;
raise notice 'Seq: %, QtdAnt: %, Sld: %', movReg.seq, qtdAnt, qtdNova;
qtdAnt = qtdNova;
END LOOP;
RETURN OLD;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION func_teste_delete()
OWNER TO postgres;
CREATE TRIGGER tg_teste_delete
after DELETE
ON teste_delete
FOR EACH ROW
EXECUTE PROCEDURE func_teste_delete();
Ao remover um registro a trigger é disparada e todos os registros com
sequência maior que a removida tem seus campos "ant" e "sld" atualizados.
Por exemplo, após executar o comando "DELETE FROM teste_delete where seq =
2;", tem-se o seguinte resultado para a consulta "SELECT seq, ant, qtd, sld
from teste_delete order by seq;":
seq ant qtd sld
1 0 1 1
3 1 4 5
4 5 2 7
5 7 9 16
6 16 1 17
Porém ao remover vários registros no mesmo comando "DELETE FROM
teste_delete where seq IN (2,3,5)", tem-se o seguinte resultado:
seq ant qtd sld
1 0 1 1
4 3 2 5 -- 3 = OLD.ant do registro com
sequência 3
6 9 1 10 -- 9 = OLD.ant do registro com
sequência 5
Pelo que percebi, a variável OLD é preenchida com os valores dos registros
removidos antes de eles serem recalculados, possivelmente porque a trigger
é executada "depois" dos deletes (AFTER DELETE).
** Fiz um teste usando BEFORE DELETE, mas ele gerou um erro concorrência
e o postegres sugeriu usar o AFTER DELETE para propagar as mudanças para as
demais linhas, como mostra o log abaixo:
ERROR: tuple to be updated was already modified by an operation
triggered by the current command
HINT: Consider using an AFTER trigger instead of a BEFORE trigger to
propagate changes to other rows.
Se os comandos são executados na mesma transação em comandos separados,
como é mostrado abaixo, o recálculo funciona.
-- Comando:
BEGIN;
DELETE FROM teste_delete WHERE seq = 2;
DELETE FROM teste_delete WHERE seq = 3;
DELETE FROM teste_delete WHERE seq = 5;
COMMIT;
-- Resultado:
seq ant qtd sld
1 0 1 1
4 1 2 3
6 3 1 4
Enfim, seguem as questões:
1 - Qual o motivo do comportamento da trigger quando usado o comando
"DELETE IN"?
2 - Alguém teria outra ideia para recalcular essas colunas evitando essa
situação?
** Versão do Postgres: 9.4.4
--
*Atenciosamente. Rudieri Turchiello Colbek*
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
