pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to avoid (stop) a endless loop in a trigger
2+ messages / 2 participants
[nested] [flat]
* How to avoid (stop) a endless loop in a trigger
@ 2004-03-04 14:54 Rodrigo Sakai <rodrigo@2bfree.com.br>
0 siblings, 1 reply; 2+ messages in thread
From: Rodrigo Sakai @ 2004-03-04 14:54 UTC (permalink / raw)
To: 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 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
=====================
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to avoid (stop) a endless loop in a trigger
@ 2004-03-04 15:13 Oliver Elphick <olly@lfix.co.uk>
parent: Rodrigo Sakai <rodrigo@2bfree.com.br>
0 siblings, 0 replies; 2+ messages in thread
From: Oliver Elphick @ 2004-03-04 15:13 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; +Cc: pgsql-sql
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
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-04 15:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-04 14:54 How to avoid (stop) a endless loop in a trigger Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-03-04 15:13 ` Oliver Elphick <olly@lfix.co.uk>
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