You should be able to detect for the case where NEW.position > OLD.position and ignore it, no?
On Tue, Jan 31, 2006 at 01:45:09PM -0600, Justin Pasher wrote: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
