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