Re: trigger ON delete

On Fri, 15 Nov 2002, [iso-8859-2] Uro Gruber wrote:

> I have problems with triggers on delete and i get some kind of
> loop. Here is my function.
>
>
> CREATE FUNCTION nset_delete() RETURNS opaque AS \'
> DECLARE
> visits int4;
> BEGIN
>
> visits := OLD.r - OLD.l + 1;
> EXECUTE \'\'DELETE FROM \'\' || TG_RELNAME || \'\' WHERE
> l BETWEEN \'\' || OLD.l || \'\' AND \'\' || OLD.r || \'\';\'\';
>
> EXECUTE \'\'UPDATE \'\' || TG_RELNAME || \'\' SET
> l=l-\'\' || visits || \'\' WHERE l>\'\' || OLD.l || \'\';\'\';
> EXECUTE \'\'UPDATE \'\' || TG_RELNAME || \'\' SET
> r=r-\'\' || visits || \'\' WHERE r>\'\' || OLD.r || \'\';\'\';
>
> RETURN NULL;
> END;
> \' LANGUAGE \'plpgsql\';
> CREATE TRIGGER nset_delete BEFORE DELETE ON nset FOR EACH ROW
> EXECUTE PROCEDURE nset_delete();
>
> When i delete some data from table nset and when it gets to
> first EXECUTE (wich is DELETE) it fires trigger again and
> again.
>
> How can i solve this. Is this a bug?

Don't think so. :) Triggered statements need to call triggers as well.

I'm not sure what you want the above to do precisely. It doesn't seem
to ever delete any rows (since it always returns NULL) so maybe there'd be
another way to formulate it. Or, maybe you could redo it as an after
trigger which might loop but should eventually end.