主页 / dba / 问题 / 267925
Accepted
Cupain
Asked: 2020-05-27 07:27:31 +0800 CST

如何使用触发器函数和联接更新数据

  • 772

我有两个关系：

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 中插入或更新数据，我会收到一条很长的错误消息，我无法理解：错误信息

有人能以正确的方式解释我吗？非常感谢

postgresql join

2 个回答

  1. 只是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;
    • 0
  2. Best Answer

    我的问题是，每次更新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();
    • 0

相关问题