Trigger that updates record in a table

Question

Trigger that updates record in a table

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I need to update an X table whenever there are any changes in the Y table. I have found numerous examples of how to save the changed records in the Y table, both when a record of the X table is deleted and when it is changed or inserted, but my need is not to create a new row in the Y table whenever a record is changed in table X , I need only change the line that has the same code and that had the information changed.

Example:

I have two tables, where there are two columns, customer_id and customer_name, what I need is that when the customer name is changed in table 01, the customer name in table 02 that has the same id is automatically changed. p>

I found how to add a new line with the following command:

create or replace function salvaexcluido()
returns trigger as
$BODY$ begin
insert into bk_transportadora values (old.codigo, old.nome, old.status);

return null;
end;$BODY$
language 'plpgsql'

In this case a new line would be added whenever any record was deleted. If you have any ideas, it would be great.

postgresql trigger pgadmin

asked by anonymous 11.05.2017 / 20:52

2 answers

1

My Funtion () at the end looks like this:

CREATE OR REPLACE FUNCTION public.atualiza_transportadora()
RETURNS trigger
AS
$$
begin

IF NEW.nome <> OLD.nome \ Aqui eu verifico se houve a alteração do nome
THEN
UPDATE bk_transportadora
SET transportadora = new.nome
where codigo = old.codigo; \ Aqui atualizo a informação da outra tabela apenas se o código é igual

END IF;
return null;
end
$$  LANGUAGE plpgsql

Then I had to trigger it with the trigger only:

CREATE TRIGGER atualizar_transportadora AFTER UPDATE
ON transportadora FOR EACH ROW  
EXECUTE PROCEDURE atualiza_transportadora();

11.05.2017 / 22:36

Add the results of for x in range How to create a .bat to run network file

score 2 · Accepted Answer

Instead of using insert , use update :

UPDATE bk_transportadora 
   SET nome = new.nome
 WHERE id = new.id;

Now relevant information that is not an answer to your question: Perhaps the best way to solve your problem is not by updating the other table with a trigger , but by using a JOIN or a SUBQUERY to get updated table information. So you avoid redundancy in the database and ensure that the information will be the same everywhere.