I must not be following what you are saying. There are cases where NEW.position could be greater than OLD.position and it cannot ignore it. Here's the pseudo code for my store procedure. IF OLD.position <> NEW.position THEN drop the trigger IF NEW.position > OLD.position THEN bump the menu items down by one position that have position > OLD.position and position <= NEW.position END IF; IF NEW.position < OLD.position THEN bump the menu items up by one position that have position < OLD.position and position >= NEW.position END IF; finally, loop through all of the menu items that are in the same "group" as NEW and renumber them starting at 1 create the trigger END IF; RETURN NEW; The "bumping" is done with a pretty straight forward UPDATE query. The looping through to renumber the items also does a simple UPDATE with a counter starting at one. If an INSERT is done that assigns a position higher than the max position+1, it can form gaps. So taking my simplified example below, here is the possible sequence of events for an update (changing position of ID 3 entry to position 1). UPDATE menu_items SET position = 1 WHERE id = 3; -- fires trigger -- trigger sees NEW.position < OLD.position, so UPDATE menu_items and bump position+1 -- ID=1 and ID=2 need updating, so.... ---- fires trigger for ID=1 (because position goes from 1 to 2) ---- trigger sees NEW.position > OLD.position, so UPDATE menu_items and bump position-1 ---- ID=2 need updating, so.... ------ fires trigger for ID=2 (position goes from 2 to 1) ------ now ID=2 has position=1, which conflicts with our new update (and is the wrong position) I think this is right. It kinda makes my head hurt trying to actually follow the recursion. :) But I don't see how I can ignore the case of NEW.position > OLD.position, because if I go the opposite route (UPDATE menu_items SET position = 3 WHERE id = 1), the comparison in the trigger would fail and nothing would update right. Justin Pasher > -----Original Message----- > From: Jim C. Nasby [mailto:jnasby@xxxxxxxxxxxxx] > Sent: Tuesday, January 31, 2006 4:01 PM > To: Justin Pasher > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > 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 jnasby@xxxxxxxxxxxxx > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 >