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!