From: Rodrigo Sakai <rodrigo@2bfree.com.br>
To: pgsql-sql@postgresql.org
Subject: How to avoid (stop) a endless loop in a trigger
Date: Thu, 4 Mar 2004 11:54:07 -0300
Message-ID: <20040304145407.32236.qmail@hm10.locaweb.com.br> (raw)
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
=====================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rodrigo@2bfree.com.br
Subject: Re: How to avoid (stop) a endless loop in a trigger
In-Reply-To: <20040304145407.32236.qmail@hm10.locaweb.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox