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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
