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