Re: 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