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
