How to avoid (stop) a endless loop in a trigger - Mailing list pgsql-sql

From Rodrigo Sakai
Subject How to avoid (stop) a endless loop in a trigger
Date
Msg-id 20040304145407.32236.qmail@hm10.locaweb.com.br
Whole thread Raw
Responses Re: How to avoid (stop) a endless loop in a trigger
List pgsql-sql
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
afterit does the first update, is there any way????? I tried to do a return null, but that was a very bad idea because
itstops 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;

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
rodrigo@2bfree.com.br
http://www.2bfree.com.br
Tel:  (55) (11) 5083-5577
Fax: (55) (11) 5549-3598
=====================



pgsql-sql by date:

Previous
From: Christoph Haller
Date:
Subject: Re: sub-select parameter problem
Next
From: Oliver Elphick
Date:
Subject: Re: How to avoid (stop) a endless loop in a trigger