My problem arises in a subset of three tables of my database, foo, bar, and
baz.
Foo hold high level information about an activity
Bar holds a many to many mapping of people to activities
baz holds some predicted information about the size of the mappings and the
current registrations and is keyed by the same values as Foo (ie its primary
key is also declared "references foo on delete cascade on update cascade)
(Yes I am aware that holding the current registrations is redundant and
could be generate using select count(*) from bar where activityid="baf", but
for performance reasons I was advised to keep it current with rules.)
So I made the tables, and the rules for insert,update, and deletes so that
bar and baz are kept consistent. Now I want to automate the initialization
of baz on the creation of foo. I thought I could continue to use rules,
such as
create rule foo_insert on insert to foo as insert into baz values
(new.activityid,0,0);
However the insert fails, with a referential integrity problem. I _think_
this is because it tries to do the rule's trigger before actually doing the
original select, so the key does not exist.
Can anyone confirm my hypothesis? Is there a solution using rules, or do I
need to use triggers, which seem to have a notion of before and after? (I
have not examined triggers in detail yet)
Thank you
Eric Nielsen
