Thread: Cascading Trigger Prevention

Hello All,

I have a table that contains a nested set (using paths), and I'm
trying to create a trigger that updates the timestamps of a node and
all of its parents on a modification.  Unfortunately, this is causing
infinitely recurring triggers.

The insert occurs, which fires the trigger, which then updates all of
the parent rows, which then fires the trigger again for each update,
which recurses yet again.

I have been looking with no success for a way to turn off triggers,
and am surprised that there is no method of preventing a trigger from
running when performing a query.  Even more bothersome is the fact
that a trigger has no way of knowing when it has been called by
another trigger.

I don't want to have to move my modified timestamps to a separate
table just so that the triggers don't recurse themselves, but this is
looking like it will be the case.

Does anyone have any thoughts or alternate suggestions?

On Wed, Nov 28, 2007 at 02:00:58PM -0800, JonXP wrote:
> I have a table that contains a nested set (using paths), and I'm
> trying to create a trigger that updates the timestamps of a node and
> all of its parents on a modification.  Unfortunately, this is causing
> infinitely recurring triggers.
>
> The insert occurs, which fires the trigger, which then updates all of
> the parent rows, which then fires the trigger again for each update,
> which recurses yet again.

You can disable triggers on a table but it's definitly not recommended
(deadlock prone) but it seems to me that if when the trigger is fired
it only updates its parent everything should work, right? As it
recurses up the tree eventually it reaches the end, surely?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

On Nov 29, 5:09 pm, klep...@svana.org (Martijn van Oosterhout) wrote:
> On Wed, Nov 28, 2007 at 02:00:58PM -0800, JonXP wrote:
> > I have a table that contains a nested set (using paths), and I'm
> > trying to create a trigger that updates the timestamps of a node and
> > all of its parents on a modification.  Unfortunately, this is causing
> > infinitely recurring triggers.
>
> > The insert occurs, which fires the trigger, which then updates all of
> > the parent rows, which then fires the trigger again for each update,
> > which recurses yet again.
>
> You can disable triggers on a table but it's definitly not recommended
> (deadlock prone) but it seems to me that if when the trigger is fired
> it only updates its parent everything should work, right? As it
> recurses up the tree eventually it reaches the end, surely?
>
> Have a nice day,
> --
> Martijn van Oosterhout   <klep...@svana.org>  http://svana.org/kleptog/
>
> > Those who make peaceful revolution impossible will make violent revolution inevitable.
> >  -- John F Kennedy
>
>
>
>  signature.asc
> 1KDownload

Yes, that was one of two approaches I am considering taking.  I was
trying to update all of the parent nodes in one query (which is why I
used this particular method of nested sets) but just recursing up the
tree could also work.