UPDATE inside an Update trigger

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: Raw Message | Whole Thread | 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

Browse pgsql-general by date

  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