recursive trigger

[Mage]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Mage <ma**@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mage]

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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org