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