Mutating trigger problem.. [message #55001] |
Tue, 24 December 2002 06:55 |
sai sreenivas jeedigunta
Messages: 370 Registered: November 2001
|
Senior Member |
|
|
Hello all ,
I have a table as follows
Sampletab
___________
X y
100 a
101 b
102 c
now i am writing a trigger as follows
create or replace trigger mytrig after insert
for each row
declare
Code varchar2(1);
begin
Select y into code from sampletab where x=:new.x;
myprocedure(code);
end;
where myprocedure is my procedure...
myprocedure will not insert/update/delete in sampletab table.
when i try to insert i am getting mutating trigger error...how to resolve this..
sai
|
|
|
Re: Mutating trigger problem.. [message #55013 is a reply to message #55001] |
Thu, 26 December 2002 07:14 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
You have to use the before and after image, eventhough u dont use that in the PROCEDURE.
SQL> desc sampletab;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
X NUMBER
Y VARCHAR2(1)
SQL> select * from sampletab;
X Y
---------- -
1000 a
1001 b
1002 c
1004 d
SQL> create or replace package projPak
2 as
3 x number;
4 y varchar2(1);
5 procedure myprocedure (code in varchar2);
6 end;
7 /
Package created.
SQL> create or replace package body projpak
2 is
3 procedure myprocedure (code in varchar2) as
4 begin
5 dbms_output.put_line('this procedure does NO DML against the table');
6 end;
7 end;
8 /
Package body created.
SQL> create or replace trigger RcheckPCount
2 before insert on sampletab
3 for each row
4 begin
5 projPak.x := :new.x;
6 projPak.y := :new.y;
7 end;
8 /
Trigger created.
SQL>
SQL> create or replace trigger mytrig
2 after insert on sampletab
3 for each row
4 declare
5 Code number;
6 othercol varchar2(1);
7 begin
8 code:=projpak.x;
9 othercol:=projpak.y;
10 projpak.myprocedure(code);
11 end;
12
13 /
Trigger created.
SQL> insert into sampletab values(2333,'g');
this procedure does NO DML against the table
1 row created.
SQL> select * from sampletab;
X Y
---------- -
1000 a
1001 b
1002 c
1004 d
2333 g
SQL>
|
|
|