Re: recursive trigger

Tom Lane wrote:

>
>
>You should just do
>
> if new.parent <> old.parent then
>   new.name = ''old'';
>
>As you have it, the inner UPDATE pre-empts the outer because it is
>applied first.  When control comes back from the trigger, the row
>the trigger was handed is now dead (already updated) and can't be
>updated again.
>
>
Okay, above is an easy example. My original conception was maintaining
the article_index (for sorting) this way:

create table article (
article_id bigserial primary key,
tree_id bigint not null,
article_index int,
article_name varchar
);

create or replace function article_index() returns trigger as '
declare
  maxindex int;
begin
  if TG_OP = ''INSERT'' then
    select into maxindex article_index from article where tree_id =
new.tree_id order by article_index desc limit 1;
    new.article_index = COALESCE(maxindex + 1, 1);
    return new;
  elsif TG_OP = ''UPDATE'' then
    if new.tree_id <> old.tree_id then
      select into maxindex article_index from article where tree_id =
new.tree_id order by article_index desc limit 1;
      new.article_index = COALESCE(maxindex + 1, 1);
      update article set article_index = article_index - 1 where
article_index > old.article_index and tree_id = old.tree_id and
article_id <> old.article_id; -- this won't work
    end if;
    return new;
  elsif TG_OP = ''DELETE'' then
    update article set article_index = article_index - 1 where
article_index > old.article_index and tree_id = old.tree_id;
    return old;
  end if;
end;
' language plpgsql;

create trigger article_index before insert or update or delete on
article for each row execute procedure article_index();

insert into article (article_name, tree_id) values ('a',1);
insert into article (article_name, tree_id) values ('b',1);
insert into article (article_name, tree_id) values ('c',1);

update article set tree_id = 2;

-----

I don't understand, what's the problem, because the inner update never
updates the actual row fired the trigger. (the "old.article_id <>
article_id" condition is not necessary btw.). Does this mean, if i
change any other rows in a row level before update trigger, rows changed
won't be updated anymore in the same statement?

       Mage