--- don't think this made it through to the list ---
Hello,
I am having difficulty setting an on update rule which seems to be
caught in a recursive loop. What is the best way to modify values sent
to an update query?
If it is helpful, here is the specific situation: The context is a table
with three columns assigning users to groups with the third column being
boolean to flag the primary group. I would like to set an update rule
to enforce one primary group. Any suggestions on how to do this, or
exactly how the code is getting trapped? My code is below:
Thank you,
Lauren Matheson.
--change all others primarygroup to false when ours is true
CREATE RULE contactsecgrp_update_1pg_them
AS ON UPDATE TO contactsecuritygroup
WHERE NEW.primarygroup=true
DO
UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id =
NEW.contact_id and securitygroup_id != NEW.securitygroup_id;
--change our primarygroup to true when no true exists and we were false
CREATE RULE contactsecgrp_update_1pg_us
AS ON UPDATE TO contactsecuritygroup
WHERE NEW.primarygroup=false and (select count(contact_id) from
contactsecuritygroup where contact_id=NEW.contact_id AND
primarygroup=true)=0
DO
UPDATE contactsecuritygroup SET primarygroup=true WHERE contact_id =
NEW.contact_id and securitygroup_id = NEW.securitygroup_id;
