Best way to handle table trigger on update

From: "Justin Pasher" <justinp(at)newmediagateway(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Best way to handle table trigger on update
Date: 2006-01-31 19:45:09
Message-ID: 008801c6269e$d7cd92e0$7e01a8c0@justinp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mott Leroy 2006-01-31 19:50:39 help with locked table(s)/transactions(s)
Previous Message Jim C. Nasby 2006-01-31 19:42:21 Re: Hey!!!