0

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 :error message

Can someone explain me the right way ? Thanks a lot

2 Answers 2

0

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;
1
  • it doesn't work the way you wrote it, and i don't know what to edit. Anyway, i found a solution, thank you very much for your help
    – Cupain
    Commented May 28, 2020 at 15:46
0

My problem was that after each update on relation_b, the trigger asks to update relation_b, so it calls itself and enter in a loop.

I solved it configuring the trigger to begin when one field is updated, except the field it updates :

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 and b.id_b = new.id_b;

    RETURN new;
END
$$
LANGUAGE PLPGSQL;

DROP TRIGGER IF EXISTS trigger1 on relation_b;
CREATE TRIGGER trigger1
   AFTER UPDATE OF id_b, fk_a
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();

DROP TRIGGER IF EXISTS trigger2 on relation_b;
CREATE TRIGGER trigger2
   AFTER INSERT
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();
2
  • You don't need an UPDATE statement in the trigger if you make it a BEFORE trigger - which will be much more efficient. pastebin.com/8QJd3RSv
    – user1822
    Commented May 28, 2020 at 16:06
  • Indeed, it works, and it's more efficient. Do you want to post it as an anwer to let me accept it ?
    – Cupain
    Commented May 29, 2020 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.