PostgreSQL Trigger Insert in table B after insert in table A with a foreign key

Question

I'm using Postgres 9.3 and I have this problem: I have a table INPUT where I'll insert new record. I want insert a new record in table A when I insert it in INPUT:

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

But AFTER I want to insert a row in table B also, where there is a foreign key with table A (B.col1b must be in A.col1). I wrote this trigger:

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

So I insert in INPUT, I write in A and after I write in B, but it doesn't work! Error by foreign key violated. Where I make a mistake?

Thank you very much!

Denis de Bernardy · Accepted Answer · 2014-04-17 15:04:44Z

Two ways to fix it: one wrong, and one correct.

The wrong way to fix it is to use a before trigger. I say wrong because, simply put, side effects on other tables belong in an after trigger like you did. This not just in theory: side effects to expect at some point or another include incorrect counts reported by psql, wrong aggregate values because your row doesn't exist in a table yet (or its old value does, in case of updates or deletes), the list of quirks goes on and on ad nausea.

The correct way is to fix it is to make your foreign key deferrable:

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 ]

You probably want deferrable initially deferred.

Collectives™ on Stack Overflow

PostgreSQL Trigger Insert in table B after insert in table A with a foreign key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related