Home » RDBMS Server » Server Administration » Mutating trigger problem..
Mutating trigger problem.. [message #55001] Tue, 24 December 2002 06:55 Go to next message
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 Go to previous message
Mahesh Rajendran
Messages: 10707
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> 

Previous Topic: dblink Oracle9.2 -> Oracle8.1.7 -> Oracle7.3
Next Topic: recovery
Goto Forum:
  


Current Time: Fri Sep 20 05:25:57 CDT 2024