Re: Is there any way to stop triggers from cycling?

Josh Berkus wrote:
> Folks,
> 
> I'm experimenting with a set of triggers to automagically maintain 
> ltrees-organized tables.  I almost have it working, except for a pesky 
> problem with re-ordering groups.
> 
> The idea is that I want to set up a set of triggers such that:
> a) If the user moves item (1) to item (3), then the existing items (2) and 
> (3) will be "bumped down" to (1) and (2), or
> b) if the user moves item (3) to item (1) then the existing items (1) and 
> (2) will be "bumped up".
> 
> (btw, the reason I want to use triggers and not data-push functions is that 
> the triggers are *much* more reliable for maintaining the tree fields)
> 
> I have a set of triggers that are working except for a problem with 
> cycling.  What I'm looking for is a reliable, elegant way to make sure 
> that the trigger is executed for each row only once.
> 
> Currently I'm doing this by only cascade-updating the row adjacent to the 
> one I'm moving.  However, this is resulting in a cycle, and I don't see 
> how to break it.  Namely:
> 
> Given:
> 
> A    1
> B    2
> C    3
> 
> If I move A --> 3 then:

One more addition to Rod/Chester's comments...

It strikes me that the root of this problem is that you're trying to 
maintain the condition that sortorder is unique while breaking that 
condition by setting A=>3 while C=>3. Hence Rod's delete/insert matches 
what you're doing (delete, shuffle up to fill gap, insert with A=>3).

If you counted sortorder in steps (e.g. 10,20,30) then you could set 
A=35 and it would be clear what order you wanted.

Since the shuffled row is "unusual" (it isn't divisible by 10) you then 
can do one of two things:
1. If NEW.sortorder % 10 <> 0 THEN ...
Compare OLD.sortorder, NEW.sortorder and shuffle rows between to fill 
the gap.
Then alter your NEW.sortorder to be a "rounded" number (30).

2. A post-update statement trigger could do the whole thing by looking 
at the table as a whole. Might be useful if you do multiple re-ordering 
on a small table.

--   Richard Huxton  Archonet Ltd