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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
