Re: Best way to handle table trigger on update

> -----Original Message-----
> From: Sven Willenberger [mailto:sven(at)dmv(dot)com]
> Sent: Wednesday, February 01, 2006 2:13 PM
> To: Justin Pasher
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Best way to handle table trigger on update
>
>
> On Tue, 2006-01-31 at 13:45 -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.
>
> Rather than using a trigger why not create a function to do
> the update?
> The following will do the trick with the only modification needed to
> your table is the addition of the boolean column "isupdate"
> which should
> default to false. The two arguments taken by the function are the
> current position of the intended menu item and its new target
> position:
>
> create or replace function update_menu_item(int,int) returns void as '
> update menu_items set isupdate = true where position = $1;
> update menu_items set position = case when $1 > $2 THEN
> position +1 when
> $2 > $1 then position - 1 else position end
> where position <= case when $1 > $2 then $1 else $2 end and
> position >=
> case when $1 > $2 then $2 else $1 end and isupdate = false;
> update menu_items set position = $2 where position = $1 and isupdate;
> update menu_items set isupdate = false where isupdate = true;
> '
> LANGUAGE sql volatile;
>
> Then if you want to move Job Opportunities from position 4 to position
> 2, just call the function:
> select update_menu_item(4,2);
>
> HTH,
>
> Sven

This would work, but my goal is to create something that is transparent to
the user that is inserting the data (i.e. they perform a normal
INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion.

Justin Pasher