Hi,
I have problem with migrating our system to PG 8.
problem is with rules and triggers. Basically i want to insert row to table 'a1', its primary key has default value nextval('"REO_ID_seq"'::text).
I have a rule that must insert row to another table 'a2' using currval('"REO_ID_seq"'::text). And i have trigger that should enforce referential integrity between two tables.
CREATE CONSTRAINT TRIGGER "fk_a1_id" AFTER INSERT OR UPDATE ON "a1" FROM "a2" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins" ('fk_a1_id', 'a1', 'a2', 'UNSPECIFIED', 'id', 'id');
CREATE RULE "rule_a1_INSERT" AS ON INSERT TO a1 DO INSERT INTO a2 ("name", "id") VALUES ('REO_HOUSE'::"varchar", int4(currval('"REO_ID_seq"'::text)));
These rule and trigger create statements are taken from dump script. (if i load this dump back to 7.2 then everything is working).
In PG 7.2 it worked nicely, but in PG8 i must create trigger with DEFERRABLE INITIALLY DEFERRED to get code to work, otherwise it complains about missing key in table a2.
Can anybody point out if i am missing something? Now when i got it to work in pg 8 i ask myself, why this code is even working on pg 7.2?
-- Rigmor Ukuhe
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
