Re: Best way to handle table trigger on update

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(at)pervasive(dot)com]
> Sent: Tuesday, January 31, 2006 4:01 PM
> To: Justin Pasher
> Cc: pgsql-general(at)postgresql(dot)org
> 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(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>