| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: help with triggers and mutating tables
heidi wrote:
> 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';
>> 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';
>> 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';
>> select * from foo;
>
> X Y C
> ---------- ---------- ------------
> 9 10
> 3 4 a
> 4 4 b
> 2 3 c
This is rather obviously school work. Please post the following too: 1. Oracle version number 2. The full and complete text of the error generated.
Different versions of Oracle have different rules around mutating tables.
That said ... go to http://tahiti.oracle.com and look up the problem. This is one you should be easily able to solve yourself.
If you don't understand the docs then come back and ask about the doc posting the link so we can see what you read.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri May 18 2007 - 15:16:50 CDT
 |
 |