Thread: How to avoid (stop) a endless loop in a trigger

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
=====================

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,
isthere 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
triggerand 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
> rodrigo@2bfree.com.br
> 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 majordomo@postgresql.org