Postgres 7.4.7 (I know, a little old, but we haven't had a chance to
upgrade)
I have a table that stores menu items for a side navigation menu for a web
site. Each menu item has a "position" column set that determines where to
put the menu item in the display. At any given time, the menu items should
not have any conflicting positions and should be sequential. For example
id | name | position
-----+-------------------+----------
1 | About Us | 1
2 | History | 2
3 | Support | 3
4 | Job Opportunities | 4
5 | Sitemap | 5
...
I have an UPDATE trigger defined on the table to handle keeping the
positions correct.
CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON "menu_items" FOR EACH ROW
EXECUTE PROCEDURE update_menu_item();
When I update an existing row (say ID 3) with a new position (let's say 1),
the trigger will bump the menu items with a lower position up by one
(position 2 becomes 3, 1 becomes 2) and everything is back to normal. The
catch is the trigger performs this position bumping by making an update on
the menu items table, thus firing the trigger again for each updated row
(and leading to chaos). Currently, the only workaround I have found is to
drop the trigger at the start of the stored procedure, make the updates,
then recreate the trigger.
What is the best way to handle a situation like this? I can't imagine that
dropping and recreating the trigger is the ideal solution. Thanks.
Justin Pasher
