Message-ID: <20040304145407.32236.qmail@hm10.locaweb.com.br>
From: "Rodrigo Sakai" <rodrigo@2bfree.com.br>
Date: Thu,  4 Mar 2004 11:54:07 -0300
To: pgsql-sql@postgresql.org
Subject: How to avoid (stop) a endless loop in a trigger
References: 
In-Reply-To: 
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit


  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;

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