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:
B moves to 1
C moves to 2 ... but *so does A* ...
and I end up with:
A 2
B 1
C 2
So I'm trying to come up with a way to ensure that each row is visited only
once, but it doesn't seem to be possible. Ideas?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco