Within a trigger I am inserting the id of the table the trigger is assigned to as a foreign key in another table, but the 'new' id doesn't exist yet, so I get a foreign key constraint violation.
Here is a simplified version:
CREATE or replace FUNCTION trg_func() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$ DECLARE BEGIN INSERT INTO other_tbl ( my_tbl_fk ,my_calc ) SELECT new.id -- <- this one is the issue. Is fk in other_tbl & pk in my_tbl ,a.some_val / b.some_val calc FROM (...subquery here...) a ,(...subquery here...) b WHERE a.some_id = b.some_id; RETURN new ; END ; $BODY$ ; CREATE TRIGGER my_tbl_ins_upd_trg BEFORE INSERT OR UPDATE ON my_tbl FOR EACH ROW EXECUTE PROCEDURE trg_func();
I tried temporarily removing the fk before the insert then adding it back, but it doesn't seem to help.
I can remove the fk, trigger the trigger, then add the fk back again, so I know my insert values don't violate the constraint if applied generally.
I am guessing there is a method or strategy for doing this because it seems like something that would be done a lot for calculated fields via trigger.
I am trying to do the 'Discarding 3NF With Triggers' section of this link: http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html