On Thu, 2004-03-04 at 14:54, Rodrigo Sakai wrote: > Hi people, i have a problem here. > I'm doing a trigger that when a update occurs i need to do an update on the same > table (target table), but as known, it causes a endless loop whithin infinit > updates. So I need to stop the trigger after it does the first update, is there > any way????? > I tried to do a return null, but that was a very bad idea because it stops > completly the function fired by a trigger and all its computation is in vain... > > The test trigger that i did is like : > > CREATE OR REPLACE FUNCTION public.sp_teste_loop() RETURNS trigger AS ' > begin > raise notice \'Trigger Fired\'; > if (TG_OP = \'INSERT\') then > update teste_trigger > set flg_bool = \'S\' > where codigo=NEW.codigo; > > RETURN NEW; > > elsif (TG_OP = \'UPDATE\') then > update teste_trigger > set flg_bool = \'N\' > where codigo=NEW.codigo; > > RETURN NULL; > end if; > end; > ' LANGUAGE 'plpgsql' VOLATILE;
Does this update other records, or only the one you are inserting or updating? If the former, add " AND flg_bool IS NULL OR flg_bool != \'S\'" to the update condition (!=\'N\' for the update case); then records that are already OK will not be touched, so the recursion will stop automatically. If the latter, just change NEW.flg_bool and return NEW > CREATE TRIGGER tr_sp_teste_trigger > BEFORE INSERT OR UPDATE > ON public.teste_trigger > FOR EACH ROW > EXECUTE PROCEDURE public.sp_teste_loop(); > > Thank for any help and regards!!!! > > > ===================== > Rodrigo Sakai > Database Programmer > [EMAIL PROTECTED] > http://www.2bfree.com.br > Tel: (55) (11) 5083-5577 > Fax: (55) (11) 5549-3598 > ===================== > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
