觸發器創建對稱行

我有一張表，描述了兩個項目之間的某種關係。這些項目由 引用ID。我想創建一個觸發器，所以每當你創建一個 row 時(a,b)，就會創建一個對稱的行(b,a)。我嘗試在觸發器後創建，但它超過了堆棧深度，因為它在插入時呼叫了它自己。正確的方法是什麼？

表看起來有點像這樣：

CREATE TABLE MY_RELATION(
ID SERIAL,
THING1 INT NOT NULL,
THING2 INT NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE MY_THING(
ID SERIAL,
VAL INT NOT NULL
PRIMARY KEY(ID)
);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING1) REFERENCES MY_THING(ID);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING2) REFERENCES MY_THING(ID);

正確的方法可能是INSERT通過儲存過程（或 Postgres 中的函式）讓您的應用程序。

如果儲存過程不是一個選項，請創建一個視圖並針對該視圖重寫插入，以便它們影響其他一些表。

CREATE TABLE t (
    a integer,
    b integer
);
CREATE VIEW v AS SELECT t.a, t.b FROM t;
CREATE RULE mirror AS
   ON INSERT TO v DO INSTEAD
   INSERT INTO t (a, b) VALUES (new.a,new.b), (new.b,new.a);

順便說一句，使用視圖將允許您通過使用其他規則重寫UPDATEs 和s 來強制執行 AB、BA 配對。DELETE

會話資訊功能pg_trigger_depth()將解決您的觸發遞歸問題。需要 Postgres 9.2或更高版本。手冊：

PostgreSQL 觸發器的目前嵌套級別（如果未從觸發器內部直接或間接呼叫，則為 0）

最好在**WHEN**子句中用於CREATE TRIGGER：

CREATE OR REPLACE FUNCTION my_relation_mirror_things()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
  INSERT INTO my_relation (thing1, thing2) VALUES (NEW.thing2, NEW.thing1);
  RETURN NEW;
END
$func$;

CREATE TRIGGER trg_insbef
BEFORE INSERT ON my_relation
FOR EACH ROW
**WHEN (pg_trigger_depth() < 1)**
EXECUTE PROCEDURE my_relation_mirror_things();

從 Postgres 11 開始，使用更合適的語法：

...
EXECUTE FUNCTION my_relation_mirror_things();

有關的：

• 如何防止 PostgreSQL 觸發器被另一個觸發器觸發？

db<>fiddle here

舊sqlfiddle

規則（如@Chris 提供）是另一種方式。不過，通常觸發器比規則更易於管理。

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