Re: help with triggers and mutating tables

From: heidi <heidistettner_at_gmail.com>
Date: 18 May 2007 08:33:25 -0700
Message-ID: <1179502405.476710.158070@y80g2000hsf.googlegroups.com>

I posted a reply yesterday,but it seems to have never made it out. Here's a very simple illustration of the problem. First the tables and the triggers:

create table foo (

    x number,
    y number,
    c varchar(12),
)
/
create or replace package foo_pkg
as

        type array is table of foo%rowtype index by binary_integer;
        inserted array;
        deleted array;
        empty array;
        fire boolean default TRUE;

end;
/
create or replace trigger foo_bu
before update
on foo
begin
  foo_pkg.inserted := foo_pkg.empty;
  foo_pkg.deleted := foo_pkg.empty;
end;
/
create or replace trigger foo_au_trigger

    after update
    on foo
declare

    sum_x number;
    sum_y number;
begin
  if ( foo_pkg.fire )
  then
    foo_pkg.fire := FALSE;
    for fooPtCnt in 1 .. foo_pkg.inserted.count     loop
      if ( foo_pkg.deleted( fooPtCnt ).x <> foo_pkg.inserted( fooPtCnt ).x )

      then
          select sum(x) into sum_x
          from foo where foo.c  != ' ';
          update foo set x=sum_x where foo.c = ' ';
      end if;
      if ( foo_pkg.deleted( fooPtCnt ).y <>
foo_pkg.inserted( fooPtCnt ).y )
      then
          select sum(y) into sum_y
          from foo where foo.c  != ' ';
          update foo set y=sum_y where foo.c  = ' ';
      end if;

    end loop;
    foo_pkg.fire := TRUE;
 end if;
end;
/

The triggers are set up to ensure that the row with c=' ' always contains the sum of x and y from the row where c != ' '. The following sql illustrates that is works correctly if I only update x or y in a sql statement, but not if I update both at once.

> select * from foo;

         X Y C
---------- ---------- ------------

         6          8
         1          2 a
         4          4 b
         1          2 c

> update foo set x=3 where c='a';

1 row updated.

> select * from foo;

         X Y C
---------- ---------- ------------

         8          8
         3          2 a
         4          4 b
         1          2 c

> update foo set y=4 where c='a';

1 row updated.

> select * from foo;

         X Y C
---------- ---------- ------------

         8         10
         3          4 a
         4          4 b
         1          2 c

> update foo set x=2,y=3 where c='c';

1 row updated.

> select * from foo;

         X Y C
---------- ---------- ------------

         9         10
         3          4 a
         4          4 b
         2          3 c

Received on Fri May 18 2007 - 10:33:25 CDT