Thread: recursive trigger

       Hi,

what's wrong with this?

---
create table test (id int, name text, parent int);
insert into test values (1,'a',1);
insert into test values (2,'b',1);
insert into test values (3,'c',1);

create or replace function test() returns trigger as '
begin
 raise info ''id: %, oldname: %'',old.id, old.name;
 if new.parent <> old.parent then
   update test set name = ''old'' where parent = old.parent;
 end if;
 return new;
end;
' language plpgsql;

create trigger test_trigger before update on test for each row execute
procedure test();

update test set parent = 2;
---

INFO:  id: 1, oldname: old
INFO:  id: 1, oldname: old
CONTEXT:  PL/pgSQL function "test" line 4 at SQL statement
INFO:  id: 2, oldname: old
CONTEXT:  PL/pgSQL function "test" line 4 at SQL statement
INFO:  id: 3, oldname: old
CONTEXT:  PL/pgSQL function "test" line 4 at SQL statement
UPDATE 0

-----

PgSQL 7.4.1

       Mage

Mage <mage@mage.hu> writes:
> what's wrong with this?

>  if new.parent <> old.parent then
>    update test set name = ''old'' where parent = old.parent;

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.

            regards, tom lane

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