sql - PostgreSQL 触发器在使用外键插入表 A 后插入表 B

Question

我正在使用 Postgres 9.3，但遇到了这个问题:我有一个表 INPUT，我将在其中插入新记录。当我将它插入 INPUT 时，我想在表 A 中插入一条新记录:

   CREATE FUNCTION A() RETURNS trigger AS $a$
    BEGIN
       INSERT INTO A(
        col1, col2,col3)
    values (
        NEW.col1,NEW.col2,NEW.col3
        );

    RETURN NEW;
END;
   $a$ LANGUAGE plpgsql;

   CREATE TRIGGER a AFTER INSERT ON INPUT
FOR EACH ROW EXECUTE PROCEDURE a();

但是之后我也想在表 B 中插入一行，其中表 A 有一个外键(B.col1b 必须在 A.col1 中)。我写了这个触发器:

   CREATE FUNCTION b() RETURNS trigger AS $b$
BEGIN
       INSERT INTO B(
       col1b, col2b)
 select   NEW.col1, inp.col5
   from INPUT inp 
where inp.col1=NEW.col1 
   ;
   RETURN NEW;
END;
   $b$ LANGUAGE plpgsql;

   CREATE TRIGGER b AFTER INSERT ON A
FOR EACH ROW EXECUTE PROCEDURE B();

所以我在 INPUT 中插入，我在 A 中写入，然后在 B 中写入，但是它不起作用!违反外键错误。我哪里做错了？

非常感谢!

Answer 1

两种修正方法:一种错误，一种正确。

修复它的错误方法是使用 before 触发器。我说错了，因为简单地说，对其他表的副作用像您一样属于 after 触发器。这不仅仅是理论上的:在某些时候预期的副作用包括 psql 报告的错误计数，错误的聚合值，因为你的行还不存在于表中(或者它的旧值存在，在更新或删除的情况下)，怪癖列表会继续下去并令人作呕。

修复它的正确方法是使您的外键可延迟:

http://www.postgresql.org/docs/current/static/sql-createtable.html

FOREIGN KEY ( column_name [, ... ] )
    REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ]
    [ DEFERRABLE | NOT DEFERRABLE ]
    [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

您可能希望 deferrable initially deferred。