0
Mine has been giving an error that I can not understand talking q is wrong in $, but when I used another example internet trial almost in the same way as mine worked, then the mistake is being on how to make the trial.
The table operation has some fields, id_pk operation, float value, varchar description, char(1), id_conta fk. And the account table has the pk id_account field, float balance, name. I want the process to update the balance of the table account when there is some change in the operation table.
Follow the code I’m trying to use to make the process:
CREATE OR REPLACE FUNCTION atualizasaldo(tipo char, valor float) RETURNS trigger AS $$
BEGIN
if (tipo = 'E') then
update conta
set saldo = saldo + valor
WHERE id_conta = OLD.id_conta;
elsif (tipo = 'S') then
update conta
set saldo = saldo - valor
WHERE id_conta = OLD.id_conta;
end if;
END
$$ LANGUAGE PLPGSQL;
create trigger operacao_trigger after insert or update or delete on operacao
for each row execute procedure atualizasaldo(tipo, valor);
ERROR: trigger functions cannot have declared arguments
HINT: The arguments of the trigger can be accessed through TG_NARGS and TG_ARGV instead.
CONTEXT: compilation of PL/pgSQL function "atualizasaldo" near line 1
SQL state: 42P13
Thanks for your help.
Solved:
Code of the resolution:
CREATE OR REPLACE FUNCTION atualizasaldo() RETURNS trigger AS $$
DECLARE
tipo char;
valor float;
BEGIN
if(TG_OP = 'DELETE') then
tipo = OLD.tipo;
valor = OLD.valor;
if (tipo = 'E') then
update conta
set saldo = saldo - valor
WHERE id_conta = OLD.id_conta;
elsif (tipo = 'S') then
update conta
set saldo = saldo + valor
WHERE id_conta = OLD.id_conta;
end if;
RETURN NULL;
elsif(TG_OP = 'UPDATE') then
tipo = NEW.tipo;
valor = NEW.valor;
if (tipo = 'E') then
update conta
set saldo = saldo + valor
WHERE id_conta = OLD.id_conta;
elsif (tipo = 'S') then
update conta
set saldo = saldo - valor
WHERE id_conta = OLD.id_conta;
end if;
RETURN NULL;
elsif(TG_OP = 'INSERT') then
tipo = NEW.tipo;
valor = NEW.valor;
if (tipo = 'E') then
update conta
set saldo = saldo + valor
WHERE id_conta = NEW.id_conta;
elsif (tipo = 'S') then
update conta
set saldo = saldo - valor
WHERE id_conta = NEW.id_conta;
end if;
RETURN NULL;
end if;
END
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER tg_operacao AFTER INSERT OR UPDATE OR DELETE ON operacao
FOR EACH ROW EXECUTE PROCEDURE atualizasaldo();
Insert into operacao(descricao, valor, tipo, id_categoria, id_conta) values('Teste', 100, 'S', 1, 1);
I’m voting to close because the question is receiving BUMP, the AP seems to have abandoned it and the AP posted the solution within the question itself.
– Icaro Martins