How to update data with a trigger function and a join I have two relations :

CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO relation_a VALUES (1,100), (2,101), (3,102);
INSERT INTO relation_b VALUES (1,1), (2,2), (3,3)


I want to create a trigger which always set :

SET b.field_1 = a.field_1 FROM relation_a a, relation_b b WHERE b.fk_a = a.id_a


I tried something like this :

CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS 
$$
BEGIN
    UPDATE relation_b as b
    SET field_1 = a.field_1
    FROM relation_a a
    WHERE a.id_a = new.fk_a;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger1
   AFTER INSERT OR UPDATE 
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();


If i try to insert or update data in relation_b, i got a very long error message i can't understand :

Can someone explain me the right way ? Thanks a lot
Just SET the value for field_1 BEFORE INSERT OR UPDATE.  Just, make sure you use the alias for the new row of data.

needs to be edited for postgresql syntax

CREATE TRIGGER trigger1
   BEFORE INSERT OR UPDATE 
   ON relation_b
   FOR EACH ROW
begin
  -- "new" represent "current row being inserted/updated"
  -- just modify the values BEFORE it is stored in the DB

  select a.field_1
    into new.field_1
  from relation_a a
  where a.id_a = new.fk_a;

  -- add exception handle where "no data is found"
end;