Hello,
I am having difficulty setting an on update rule which seems to be
caught in a recursive loop.
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;
