UPDATE inside an Update trigger
От | Robert Fitzpatrick |
---|---|
Тема | UPDATE inside an Update trigger |
Дата | |
Msg-id | 1087597612.3186.9.camel@columbus обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: