| From: | Robert Fitzpatrick <robert(at)webtent(dot)com> | 
|---|---|
| To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> | 
| Subject: | UPDATE inside an Update trigger | 
| Date: | 2004-06-18 22:26:53 | 
| Message-ID: | 1087597612.3186.9.camel@columbus | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
I have a trigger that performs an UPDATE query on the same table that
the Update trigger is defined. Of course, it loops infinitely, is there
a way to do this?
CREATE OR REPLACE FUNCTION "public"."clear_common_groups" () RETURNS
trigger AS'
DECLARE
  norows integer;
BEGIN
  RAISE NOTICE ''Looking for common areas in Building
%'',NEW.hud_building_id;
  IF NEW.common_area = ''t'' THEN
    UPDATE tblhudunits SET common_area = NULL WHERE unit_id <>
NEW.unit_id AND hud_building_id = NEW.hud_building_id;
  END IF;
  IF NEW.exterior_area = ''t'' THEN
    UPDATE tblhudunits SET exterior_area = NULL WHERE unit_id <>
NEW.unit_id AND hud_building_id = NEW.hud_building_id;
  END IF;
  GET DIAGNOSTICS norows = ROW_COUNT;
  IF NOT FOUND THEN
     RAISE NOTICE ''Nothing updated'';
  ELSE
     RAISE NOTICE ''% rows updated'',norows;
  END IF;
  RETURN NULL;
END;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "insert_new_common_area" AFTER INSERT OR UPDATE
ON "public"."tblhudunits" FOR EACH ROW
EXECUTE PROCEDURE "public"."clear_common_groups"();
-- 
Robert
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2004-06-18 23:04:42 | Re: Trigger to update records out of memory | 
| Previous Message | DeJuan Jackson | 2004-06-18 21:55:43 | Re: Trigger to update records out of memory |