So I solved my problem using a trigger/function combo, but I would still
like to know if what I was trying is possible using rules (see below).
Thank you.
Eric
----- Original Message -----
From: Eric Nielsen <ericnielsen@earthlink.net>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, January 04, 2001 11:13 PM
Subject: I think I know what I'm doing wrong, but....
> 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
>
>
>
>