Trigger that updates record in a table

Question

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

Example:

I have two tables, where there are two columns, id_client and client name_name, what I need is that when the client name is changed in table 01, automatically the client name in table 02 that has the same id.

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 a record was deleted. If you have any ideas it would be great.

Answer 1

Instead of using insert, use the update:

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

Now a relevant information that is not an answer to your question: Maybe the best way to solve your problem is not by updating the other table with one trigger, and yes using a JOIN or a SUBQUERY to obtain the updated table information. Thus you avoid database redundancy and ensure that the information will be equal everywhere.

Answer 2

My Funtion() in the end it was 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 Trigger only:

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