如何使用觸發器函式和聯接更新數據

我有兩個關係：

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)

我想創建一個始終設置的觸發器：

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

我試過這樣的事情：

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();

如果我嘗試在relation_b 中插入或更新數據，我會收到一條很長的錯誤消息，我無法理解：

有人能以正確的方式解釋我嗎？非常感謝

我的問題是，每次更新relation_b後，觸發器都會要求更新relation_b，所以它會呼叫自己並進入一個循環。

我解決了將觸發器配置為在更新一個欄位時開始的問題，除了它更新的欄位：

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();

只是SET價值field_1 BEFORE INSERT OR UPDATE。只是，請確保您new對數據行使用別名。

需要針對 postgresql 語法進行編輯

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;

引用自：https://dba.stackexchange.com/questions/267925