| 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: table CLASS is mutating, trigger/function may not see it
A copy of this was sent to cirip_at_cmic.ca (Tomas CIRIP)
(if that email address didn't require changing)
On Sat, 30 May 1998 03:04:37 GMT, you wrote:
>Hi All,
>
>imagine I have table CLASS( A varchar2(10)
> B varchar2(10))
>When I delete record from table class, value of column B should be
>changed for some other records from the same table. This should be
>done in db trigger( before or after delete). But when I am trying to
>do this using UPDATE command on the same table, there is Oracle error
>"ORA-04091: table CLASS is mutating, trigger/function may not see it".
>Thanks for help.
>
>---------------------------------------------------------------------------------------------------------
>Tomas CIRIP
>tomas_at_cmic.ca
>-------------------------------
>Tomas CIRIP
>CMiC, North York, Ontario
>E-mail: tomas_at_cmic.ca
>
>Opinions are mine and do not necessarily reflect those of CMiC
You need 3 triggers and a package to do this. It might look like:
create table class_tbl( A varchar2(10),
B varchar2(10))
/
create or replace package class_pkg
as
type classArray is table of CLASS_TBL%ROWTYPE index by binary_integer;
deleted classArray;
empty classArray;
end;
/
create or replace trigger class_bd
before delete on class_tbl
begin
class_pkg.deleted := class_pkg.empty;
end;
/
create or replace trigger class_adfer
after delete on class_tbl
for each row
declare
n number default class_pkg.deleted.count+1; begin
class_pkg.deleted(n).a := :old.a;
class_pkg.deleted(n).b := :old.b;
end;
/
show errors trigger class_adfer
create or replace trigger calss_ad
after delete on class_tbl
begin
for i in 1 .. class_pkg.deleted.count loop
update class_tbl set b = ( select count(*)
from class_tbl
where a = class_pkg.deleted(i).a )
where a = class_pkg.deleted(i).a;
end loop;
To test it, I ran:
insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null ); insert into class_tbl values ( 1, null );
select * from class_tbl;
delete from class_tbl where rownum < 3;
select * from class_tbl;
delete from class_tbl where rownum < 3;
That produced:
A B
---------- ----------
1
1
1
1
1
1
1
7 rows selected.
2 rows deleted.
A B
---------- ----------
1 5 1 5 1 5 1 5 1 5
2 rows deleted.
A B
---------- ----------
1 3 1 3 1 3
which shows it works....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 29 1998 - 19:38:42 CDT
 |
 |