Thread: trigger ON delete

Hi!

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 worry about people stealing your ideas.  If your ideas
are any
good, you\'ll have to ram them down people\'s throats.\"
        -- Howard Aiken

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.