Thanks for the question, Bhavesh.

Asked: June 08, 2000 - 4:34 pm UTC

Last updated: February 28, 2013 - 7:57 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi Tom:
I have a mutating trigger problem....
Here is the SIMPLIFIED problem I need the help for...
I have a table customer as shown below:
SQL> select * from customer;

CUSTID CUSTNAME STATEMENT NETSALES
---------- -------------------- --------- ----------
1 best buy 08-JUN-99 1000
2 best buy 08-JUN-98 1000
3 best buy 08-MAY-00 2000
Hi Tom:
I have a mutating trigger problem....
Here is the SIMPLIFIED problem I need the help for...
I have a table customer as shown below:
SQL> select * from customer;

CUSTID CUSTNAME STATEMENT NETSALES
---------- -------------------- --------- ----------
1 best buy 08-JUN-99 1000
2 best buy 08-JUN-98 1000
3 best buy 08-MAY-00 2000

Datatypes are obvious! Now, when ever we have >=3 rows PER CUSTOMER in this table(grab the latest 3 rows based on statementDate),
my trigger should fire which executes a procedure which does calculations and dumps results
in the OUTPUT table which is shown below with the one result for each customer
Best Buy
SQL> select * from output;

CUSTID CUSTNAME NETSALES_C NETSALES_2 NETSALES_3
---------- -------------------- ---------- ---------- ----------
1 best buy 2000 1000 1000
Since I need to know how many rows are there in customer table for every customer being updated/inserted
in the customer table, I have to use AFTER INSERT OR UPDATE ROW trigger...and that gives me mutating trigger error.

When I use before BEFORE INSERT OR UPDATE trigger to know the customer being inserted/updated which stores the custid in the package vairable,
and use AFTER INSERT OR UPDATE statement level trigger, it works only for the one company.
If batch insert or update takes place, the trigger fires my procedure only once for the last customer being inserted/updated.

In brief, my requirement is, when a customer is added/modified in the customer table and we have >=3 rows in customer table, use the latest three rows
to do calculation. This should work for batch updates and inserts too. So, the after INSERT OR UPDATE should be a row level trigger...

Hope I made myself clear..thanks a lot in advance...
Regards,
Bhavesh Tailor

and Tom said...

I have details on the mutating table issue at
</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>

It deals with the >1 row being updated/inserted at a time by using plsql tables. You should read through that paper to see why I do some things I do below...

This would be easier if you didn't have that 3 row rule, is it really necessary? (it would be easier from a programming perspective with or without the mutating table issue)...

Here is what I would code in 2 different ways. The first way follows your "3 row" rule. The second is the way I would do it -- i would just track the netsales for the last 3 regardless of whether they had 1, 2, 3, or more records. Its shows how simple the logic is then (regardless of mutating table or not -- the 3 row rule makes this *hard*). If you can, I would give serious consideration to dropping that requirement and just tracking it for everyone. It would make the process of inserting/updating much faster.

ops$tkyte@8i> create table t
2 ( custid int,
3 custname varchar2(25),
4 statement date,
5 netsales int
6 )
7 /
Table created.

ops$tkyte@8i>
ops$tkyte@8i> create table t2
2 ( custid int primary key,
3 netsales_1 int,
4 netsales_2 int,
5 netsales_3 int
6 )
7 /
Table created.

ops$tkyte@8i> create or replace package state_pkg
2 as
3 type myArray is table of number
4 index by binary_integer;
5
6 empty myArray;
7 newOnes myArray;
8 end;
9 /
Package created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_biu
2 before insert or update on t
3 begin
4 state_pkg.newOnes := state_pkg.empty;
5 end;
6 /

Trigger created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiuFEF
2 after insert or update on t for each row
3 begin
4 state_pkg.newOnes(:new.custid) := 1;
5 end;
6 /

Trigger created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiu
2 after insert or update on t
3 declare
4 l_custid number;
5 l_vals state_pkg.myArray;
6 begin
7 l_custid := state_pkg.newOnes.FIRST;
8 loop
9 l_vals := state_pkg.empty;
10 for x in ( select netsales
11 from t
12 where custid = l_custid
13 order by statement desc )
14 loop
15 l_vals( l_vals.count+1 ) := x.netsales;
16 exit when ( l_vals.count = 3 );
17 end loop;
18 if ( l_vals.count = 3 )
19 then
20 update t2
21 set netsales_1 = l_vals(1),
22 netsales_2 = l_vals(2),
23 netsales_3 = l_vals(3)
24 where custid = l_custid;
25
26 if (sql%rowcount = 0)
27 then
28 insert into t2
29 ( custid, netsales_1,
30 netsales_2, netsales_3 )
31 values
32 ( l_custid, l_vals(1),
33 l_vals(2), l_vals(3) );
34 end if;
35 end if;
36
37 exit when ( l_custid = state_pkg.newOnes.last );
38 l_custid := state_pkg.newOnes.NEXT(l_custid);
39 end loop;
40 state_pkg.newOnes := state_pkg.empty;
41 exception
42 when others then state_pkg.newOnes := state_pkg.empty;
43 end;
44 /

Trigger created.

ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-5, 100 );

1 row created.

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-4, 200 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200

ops$tkyte@8i> select * from t2;

no rows selected

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-3, 250 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 250 200 100

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-2, 300 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
1 one 06-JUN-00 300

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200

ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select 2, 'two', statement, netsales*10
3 from t
4 /

4 rows created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000

8 rows selected.

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select custid+2, custid+2, statement-10, netsales*10
3 from t
4 /

8 rows created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
3 3 24-MAY-00 1000
4 4 24-MAY-00 10000
3 3 25-MAY-00 2000
4 4 25-MAY-00 20000
3 3 26-MAY-00 2500
4 4 26-MAY-00 25000
3 3 27-MAY-00 3000
4 4 27-MAY-00 30000
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000

16 rows selected.

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
3 3000 2500 2000
4 30000 25000 20000

ops$tkyte@8i>
ops$tkyte@8i>

Now, here is the simple way I would do it. It'll perform better and have less code to debug/maintain

ops$tkyte@8i> create table t
2 ( custid int,
3 custname varchar2(25),
4 statement date,
5 netsales int
6 )
7 /

Table created.

ops$tkyte@8i>
ops$tkyte@8i> create table t2
2 ( custid int primary key,
3 netsales_1 int,
4 netsales_2 int,
5 netsales_3 int
6 )
7 /

Table created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiuFER
2 after insert or update on t
3 for each row
4 begin
5 update t2
6 set netsales_2 = netsales_1,
7 netsales_3 = netsales_2,
8 netsales_1 = :new.netsales
9 where custid = :new.custid;
10
11 if (sql%rowcount=0)
12 then
13 insert into t2
14 ( custid, netsales_1 )
15 values
16 ( :new.custid, :new.netsales );
17 end if;
18 end;
19 /

Trigger created.

ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-5, 100 );

1 row created.

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-4, 200 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 200 100

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-3, 250 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 250 200 100

ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-2, 300 );

1 row created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
1 one 06-JUN-00 300

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200

ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select 2, 'two', statement, netsales*10
3 from t
4 /

4 rows created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000

8 rows selected.

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select custid+2, custid+2, statement-10, netsales*10
3 from t
4 /

8 rows created.

ops$tkyte@8i> select * from t order by statement;

CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
3 3 24-MAY-00 1000
4 4 24-MAY-00 10000
3 3 25-MAY-00 2000
4 4 25-MAY-00 20000
3 3 26-MAY-00 2500
4 4 26-MAY-00 25000
3 3 27-MAY-00 3000
4 4 27-MAY-00 30000
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000

16 rows selected.

ops$tkyte@8i> select * from t2;

CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
3 3000 2500 2000
4 30000 25000 20000

Rating

(27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Eye Opener

Santosh Shenoy, July 18, 2002 - 8:51 am UTC

Step by step explaination is highly useful. Things are greatly simplified.

Mutating Trigger in case of Insert into as Select * from.....

Jignesh Karia, February 10, 2003 - 12:46 am UTC

Hi Tom,
Thank you very much for your reply. But I am facing one problem. --- >

Consider the following Trigger -->

CREATE TABLE MS_COMPANY (
CM_COMP_CD VARCHAR2 (6) NOT NULL,
CM_COMP_NAME VARCHAR2 (60),
CM_TDS_PAN VARCHAR2 (30));

CREATE TABLE MS_COMPS (
CM_COMP_CD VARCHAR2 (6) NOT NULL,
CM_COMP_NAME VARCHAR2 (60),
CM_TDS_PAN VARCHAR2 (30));

INSERT INTO MS_COMPS (CM_COMP_CD) VALUES ('COMP1');

CREATE OR REPLACE TRIGGER MS_Comp_Trigger
BEFORE INSERT ON Ms_Company
FOR EACH ROW
DECLARE
vNL_Count NUMBER ;
BEGIN
SELECT count(1)
INTO vNL_Count
FROM ms_Company ;
IF vNL_Count = 1 THEN
Raise_Application_Error(-20000, 'No More Company is allowed to add.') ;
END IF ;
END ;

INSERT INTO MS_COMPANY SELECT * FROM MS_COMPS;

Here i receive error for Mutating Table;

But if i write following statement it will work fine ->

INSERT INTO MS_COMPANY (CM_COMP_CD) VALUES ('COMP1');

Could you please explain why this is happening ? ? ? ?

February 10, 2003 - 7:49 am UTC

well, you need to look at your logic anyway -- say I just inserted a single row:

insert into ms_company values ( .... );

and did not get the mutating table. Does my trigger work??? Nope, it will not do what I want because there is this little thing called "multi-user concurrency" out there lurking about. If N sessions all inserted a single row at about the same time - your trigger would not see their inserts and make a bad decision -- logically corrupting the data

tell me -- what business rule are you trying to enforce. Lets start with the RULE you want to enforce, then i can tell you how to enforce that rule.

Would be grateful if U kindly solve this problem that i face-Mutating Trigger

M.Shankar, June 09, 2004 - 2:10 am UTC

Dear Tom,
I have a similar problem faced by others too of Mutating triggers,I want to understand why that is Happening and how could I overcome it.
My problem : 
I have a Table called eleavetrial that has say just 2 fields viz.,leavecode and leavename.Now I write a trigger stating that when one tries to delete the leavecode between 1 and 10 it should not allow him/her to delete.
My code goes like this :-

SQL> desc eleavetrial;
     Name       Null?      Type
----------------------------------

LEAVECODE                  NUMBER
LEAVENAME                  VARCHAR2(15)

SQL>select * from eleavetrial;

CODE     LEAVENAME
----    ---------------
   1        EL
   2         CL
   3         ML
   4         EOL
   5         BL
   6         FL
   7         DL
   8         LL
   9         OL
  10         KL
  11         PL
  12         AL
  13         UL
  14            WL
  15         ZL

15 rows selected.

SQL>  CREATE OR REPLACE TRIGGER CodeDel
 2    BEFORE DELETE ON ELEAVETRIAL
 3    FOR EACH ROW
 4    WHEN (OLD.LEAVECODE <= 10)
 5    BEGIN
 6    RAISE_APPLICATION_ERROR(-20001, 'U R Not Allowed To Delete');
 7    END;
/

Trigger created.

SQL> DELETE FROM ELEAVETRIAL WHERE LEAVECODE=12;
DELETE FROM ELEAVETRIAL WHERE LEAVECODE=12
                            *
ERROR  at line 1:
ORA-04091: table DSSADMIN.ELEAVETRIAL is mutating, trigger/function may not see it
ORA-06512: at "DSSADMIN.TRIGGED", line 4
ORA-04088: error during execution of trigger 'DSSADMIN.TRIGGED'

THIS IS WHAT THE ERROR I GET
HOPEFUL TO GET AN EARLY REPLY FROM U.

June 09, 2004 - 7:19 am UTC

(this keyboard issue is becoming an epidemic! It is even affecting developed software now... I wonder what the keyboard industry is doing to make this issue with vowels go away.)

DSSADMIN.TRIGGED is the trigger with the issue. but you show us CodeDel. CodeDel is fine, trigged is not.

no mutating error - 9i - before insert

Riyaz, March 15, 2005 - 12:43 am UTC

1 create or replace trigger t_emp
2 before insert on emp for each row
3 declare
4 vx number(5);
5 begin
6 select empno into vx from emp where rownum=1;
7* end;
scott@ORCL> /
Trigger created.

scott@ORCL> update emp set empno=empno where rownum=1;
1 row updated.

So it means mutating error does not come for rowlevel triggers with before/insert and statement level (before/after/insert/update) trigger. (oracle 9i)

Please confirm my understanding.

March 15, 2005 - 7:56 am UTC

you have a insert trigger

you run an update.....  insert trigger does not fire, hence I would be surprised to see a mutating table constraint.

this constraint is never raised during a BEFORE or AFTER trigger.

and only in FOR EACH ROW triggers when the trigger ACTUALLY fires and it is not known that only one row will be affected (therefore for any DML other than insert with the VALUES clause)

for example, using inserts:


ops$tkyte@ORA9IR2> create table emp as select empno from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> create or replace trigger t_emp
  2  before insert  on emp for each row
  3  declare
  4  vx number(5);
  5  begin
  6   select empno into vx from emp where rownum=1;
  7  end;
  8  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into emp (empno) values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into emp (empno) select 2 from dual;
insert into emp (empno) select 2 from dual
            *
ERROR at line 1:
ORA-04091: table OPS$TKYTE.EMP is mutating, trigger/function may not see it
ORA-06512: at "OPS$TKYTE.T_EMP", line 4
ORA-04088: error during execution of trigger 'OPS$TKYTE.T_EMP'
 

<b>when precisely one row is known to be modified -- insert values -- it permits the read as the read is known to be "not inconsistent" (just one row).  When it is not known -- the mutating table constraint is in effect</b>

thanks.

riyaz, March 15, 2005 - 11:28 pm UTC

Thanks. I was actually tried both update and insert statement (after creating trigger), but forgotten to copy and paste. Anyway, you pre-empted my question and answered the same.

Thanks again.

Updating a Foreign Key on Same Table

Michael, November 24, 2005 - 12:57 pm UTC

Hi Tom,

Your Mutating Trigger examples are priceless!

How would you sort out this problem?

-- Drop and Create table
drop table t cascade constraints;
create table T
(
T_ID NUMBER not null,
MGR_ID NUMBER,
MGR_TYPE VARCHAR2(1)
);

alter table T
add constraint T_PK primary key (T_ID);
alter table T
add constraint M_UK unique (MGR_ID);
alter table T
add constraint M_FK foreign key (T_ID)
references T (T_ID) on delete set null;
-- Create/Recreate check constraints
alter table T
add constraint MGR_TYPE
check (mgr_type in ('C','A'));
create sequence t_seq start with 1;

begin
for i in 1..6 loop
insert into t values (t_seq.nextval,null,null);
end loop;
commit;
end;
/

-- Business Rules
-- a one to one link must exist between only two t_id's
-- when updating a mgr_id to another t_id, the other link must be null'd, and the new link created.
-- the mgr_type when C in one link must be A in the other linked reference.

mj@O10g>select * from t;

T_ID MGR_ID M
---------- ---------- -
1
2
3
4
5
6

mj@O10g>update t set mgr_id = 2, mgr_type = 'A' where t_id = 1;

mj@10g>select * from t;

T_ID MGR_ID M
---------- ---------- -
1 2 A
2
3
4
5
6

What I would like to see when this happens is where t_id = 2
would have 1 as a mgr_id and 'C' as the mgr_type.

for example:

mj@O10g>update t

T_ID MGR_ID M
---------- ---------- -
1 2 A
2 1 C
3
4
5
6

If mgr_id is updated to 3 where t_id = 1 then the link from t_id would be null in both mgr fields and mgr_id and mgr_type would be updated with 1 and 'C' automatically.

If the mgr_id is set to null in an update statement where t_id = 1, then the references would also be updated.

I see the only option is your mutating trigger in this design. Is this correct?
What would be the best way to implement this?
Would it be advantageous to seperate this into parent/child design which would ease the development of triggers?

Thank you for your time.

November 24, 2005 - 5:16 pm UTC

this doesn't compute.

alter table T
add constraint M_FK foreign key (T_ID)
references T (T_ID) on delete set null;

but you model contains redundant data it would seem? why bother with the second row at all -- if 1,2,A -> 2,1,C, then 2,1,C is inferred from the existence of 1,2,A. No need to even consider storing it.

RE: Muting Table

Michael, November 25, 2005 - 9:51 am UTC

My mistake.should be this.
alter table T
add constraint M_FK foreign key (MGR_ID)
references T (T_ID) on delete set null;

If a person is on a form and displays member 2, then the person should see that member 1 is referenced. Note: if implied through member 1 then a trigger will enforce the one to one relationship?

Thanks

November 25, 2005 - 11:02 am UTC

Are you trying to make it so that if "1" relates to "2", "2" cannot relate to anything else?

Mutating Trigger

Michael, November 29, 2005 - 9:14 pm UTC

Yes, 2 is not allowed another link when linked to another id and vise versa. but will allow changes to an existing link, if changed then the link to 2 is null and the new link between 1 and 3 are valid. This is a link like a marriage, but can change over time and the previous link is null and the link between the two new id's for example is valid at that moment in time forward. So in the Application when viewing member id 1, the person can change a link to id 3 when id 2 is already there. This would then null out the entry where id = 2 and the new link is id 3 for id 1. I can do this simply in the application, but would rather have the code in the database. Just would like to know if there is an easier way than creating multiple triggers and processing a after statement array.

Thanks you

November 30, 2005 - 11:35 am UTC

I'm not going to suggest using automagical triggers for this, I would suggest writing a nice package to provide the complete API for modifying this table and revoke insert/udpate/delete on this table from all application schemas - forcing them to use the correct api to achieve this logic.

Mutating Table error in Dynamic Trigger

Bala krishna . Chetluri, January 02, 2006 - 8:36 am UTC

create  table test_tab1 as select table_name from user_Tables;

create table log_table (date1       date)

DECLARE
    CURSOR curr IS
        SELECT table_name
        FROM   test_tab1;
    lc_trigger_create VARCHAR2(4000);
    p_table_name      VARCHAR2(100);
BEGIN
    FOR i IN curr
    LOOP
        p_table_name      := i.table_name;
        lc_trigger_create := '
           CREATE OR REPLACE TRIGGER ' ||
                             p_table_name ||
                             '_IDU_P2_TRG1
           After INSERT Or UPDATE Or DELETE  ON ' ||
                             p_table_name || '
           For Each ROW
           DECLARE
   begin
   INSERT INTO LOG_TABLE(DATE1)VALUES(SYSDATE);
  end;';
        EXECUTE IMMEDIATE lc_trigger_create;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20901,
                                SQLERRM);
END;
SQL>  insert into emp(empno) values(56);
 insert into emp(empno) values(56)
             *
ERROR at line 1:
ORA-04091: table SCOTT.LOG_TABLE is mutating, trigger/function may not see it


What i don't understand is the cursor is on one table and insert is on other table.Iam suprised to see this error here.
Kindly explain.
Thanks in advance
Bala krishna. Chetluri

January 02, 2006 - 10:27 am UTC

well, first - this is not a "dynamic trigger", you have a plsql block that is dynamically creating a trigger, but by the time of the insert into - you have a quite "static" trigger (so lose the plsql block in the example, not relevant - just create the static trigger)

second, test_tab1 is empty - I don't know what should have been in there so your example doesn't run.

so, if you provide to me a SIMPLE (eg: plsql block just obscures stuff, we don't need it - we just need the trigger IT creates) example that can be run from start to finish - I'll take a look

(and odds are - when you get the simple example, the issue will become "obvious" - I find 999 times out of 1000 when I make my simple example - I see my own mistake :)

Mutating Table error in Dynamic Trigger

bala krishna.ch, January 03, 2006 - 6:09 am UTC

Hi tom


SQL> create table emp_test1 as select * from emp;

Table created.

SQL> CREATE OR REPLACE TRIGGER xcxx
  2     After INSERT Or UPDATE Or DELETE  ON emp_test1
  3     For Each ROW
  4     DECLARE
  5     begin
  6     INSERT INTO LOG_TABLE(DATE1)VALUES(SYSDATE);
  7    end;
  8  /

Trigger created.

SQL> INSERT INTO EMP_TEST1 (EMPNO) VALUES (136);
INSERT INTO EMP_TEST1 (EMPNO) VALUES (136)
            *
ERROR at line 1:
ORA-04091: table SCOTT.LOG_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.LOG_TABLE_IDU_P2_TRG", line 4
ORA-04088: error during execution of trigger 'SCOTT.LOG_TABLE_IDU_P2_TRG'
ORA-06512: at "SCOTT.XCXX", line 3
ORA-04088: error during execution of trigger 'SCOTT.XCXX'

January 03, 2006 - 7:51 am UTC

the mutating table is coming from a trigger:

ORA-04088: error during execution of trigger 'SCOTT.LOG_TABLE_IDU_P2_TRG'

you have chosen to not share with us. Need complete example.

Mutating Table error in Dynamic Trigger

A reader, January 03, 2006 - 10:44 am UTC

Good Heavens! There is a trigger on log_table itself.
Therefore when ever iam inserting a record in the emp_test1, trigger is firing to insert a record in log_table which again is having a Trigger that inserts a record in log_table itself causing mutating table error.

Great Tom, Thank you so much for your scrutiny.

January 03, 2006 - 10:54 am UTC

(this is why 100% complete, yet concise, but FULLY SELF CONTAINED test cases rock... In order to reproduce this in a standalone environment, you would have had to of introduced the additional trigger. That is exactly why I wrote:

(and odds are - when you get the simple example, the issue will become "obvious"
- I find 999 times out of 1000 when I make my simple example - I see my own
mistake :)

)

Concurrency

Scott, October 24, 2007 - 12:40 pm UTC

Tom,

I'm a Java programmer who is getting more involved on the database side of things. My question probably stems from a lack of understanding about "scope" and/or transactions in oracle.

I have adapted the following example from your examples at http://asktom.oracle.com/~tkyte/Mutate/index.html

t1 stores the maximum value entered for associated t2 recs. The field_1_max value should be kept in synch with changes to t2 (inserts, updates and deletes).

create table t1 (
  ctrl_num int primary key,
  field_1_max number default 0
);

create table t2 (
  ctrl_num int primary key,
  t1_ref int references t1(ctrl_num), 
  field_1 NUMBER
);

create or replace package state_pkg
AS
  TYPE ridArray IS TABLE OF t1.ctrl_num%type INDEX BY binary_integer;
  newRows ridArray;
  empty   ridArray;
END;
/

create or replace trigger t2_bdiu
  before delete or insert or update on t2
BEGIN
  state_pkg.newRows := state_pkg.empty;
END;
/

create or replace trigger t2_bdiufer
  before delete or insert or update of field_1 on t2
  for each row
begin
  if deleting then
    state_pkg.newRows(state_pkg.newRows.count+1) := :old.t1_ref;
  else
    state_pkg.newRows(state_pkg.newRows.count+1) := :new.t1_ref;
  end if;
end;
/

create or replace trigger t2_adiu
  after delete or insert or update of field_1
  on t2
DECLARE
  l_field_max NUMBER;
  l_t1_id NUMBER;
BEGIN

  FOR i IN 1 .. state_pkg.newRows.count LOOP

    l_t1_id := state_pkg.newRows(i);

    select max(field_1) into l_field_max
      from t2
      where t1_ref = l_t1_id;

    update t1
      set field_1_max = l_field_max
      where ctrl_num = l_t1_id;

  end loop;
end;
/


insert into t1 values(1,0);
insert into t2 values(1,1,3);
insert into t2 values(2,1,2);

-- This should output 3 which is currently the largest val.
select field_1_max from t1 where ctrl_num = 1;

-- Delete row from t2 containing 3 making 2 the largest val.
delete from t2 where ctrl_num = 1;

-- Now prints 2
select field_1_max from t1 where ctrl_num = 1;

This all seems to work fine in a single user environment. My question is what happens if two transactions (tx1 and tx2 below) begin modifying t2 at the same time but on different non-conflicting records.

1. tx1 resets state_pkg.newRows by triggering t2_bdiu.
2. tx1 begins modifying rows thus triggering t2_bdiufer.
3. Before t2_adiu is called in tx1 tx2 triggers t2_bdiu.

Will this reset the collection that tx1 is working on or does tx2 get its own copy to work with?

Thanks,
Scott

October 24, 2007 - 3:34 pm UTC

everybody has their own package state, their own "data segment"

think of each session like a separate java program being run - your variables are your variables.

plsql is the same - what you do to your variables in your session are private to your session.

mutating trigger

rajesh, June 02, 2008 - 6:20 am UTC

I have a mutating trigger problem....

i have three table
1 mater and 2 detail table
in 2nd detail table one field is not null then then user can't update the master table if it is null then user can update give the solution for this problemb using check constraint not using package

June 02, 2008 - 11:32 am UTC

sense no makes this

I did not follow this 'precise' description at all.

I can say, if you have a mutating table constraint being raised, you are almost certainly doing it wrong (please, for the love of whatever, remember reads are not blocked by writes and vice versa)

this sounds a lot like "we did not design our system very well"

When and if you come back to explain in great, precise detail - please remember that you will need an example and in this case the example only needs two tables (there needed be a "first" child table, it doesn't seem at all relevant to the problem statement). You need very few columns, and you need to explain not only what you want to have happen - but why - I cannot imagine the case whereby you would "lock" a parent record because SOME child record contains a null value in some attribute - explain the business case here, help us understand what you are really trying to do.

mutating trigger

anish, June 05, 2008 - 4:55 am UTC

This is my table I try to update combination of reference number applicant cpr update will mutating trigger problemb
CREATE TABLE FPE_CASE_REGISTRATION (
APPLICANT_FILE_NO VARCHAR2 (8) NOT NULL,
APPLICANT_CPR VARCHAR2 (9) NOT NULL,
APPLICANT_NAME VARCHAR2 (120),
REFERENCE_NUMBER VARCHAR2 (9),
COURT_NAME VARCHAR2 (65),
CASE_FEE NUMBER (13,3),
APPLICANT_REQUEST VARCHAR2 (100),
TELEPHONE_NO VARCHAR2 (50),
APPLICATION_DATE DATE,
APPL_CREATORE_ID VARCHAR2 (6),
EXEMPTED_AMOUNT NUMBER (10,3),
DELAYED_AMOUNT NUMBER (10,3),
JOB_TITLE VARCHAR2 (100),
DECISION_DETAIL VARCHAR2 (1),
CONSTRAINT APP_CASE
PRIMARY KEY ( APPLICANT_FILE_NO ) ) ;

Insert is working for update only problemb

/This trigger giving mutating trigger/

CREATE OR REPLACE TRIGGER fpe_update
before update on fpe_case_registration
for each row
declare
ref_num number;
BEGIN
select count(*) into ref_num from case_register
where case_register.reference_number=:new.REFERENCE_NUMBER
and requester_cpr=:new.applicaNt_cpr ;
if ref_num>2 then
RAISE_APPLICATION_ERROR(-20000, 'this combination of applicant cpr and reference number have 3 records ');
END IF;
END;

This my table here I want insert only 3 of record for the combination applicant cpr
And reference
For the updating it is giving mutating trigger problemb
How to solve this problemb

June 05, 2008 - 10:11 am UTC

ugh, I HATE TRIGGERS

do you know that your logic, if it didn't hit the mutating table constraint, would never work in a multi user situation?????????

what happens if two people updated information at the same time.

have you thought about that???

I update - trigger fires, it is OK
You update - trigger fires, it cannot see my changes, does not count my stuff
I commit
You commit

we end up with 4 (because we both put 2 in).

Unless you lock the table, you cannot enforce this rule in a trigger.

http://asktom.oracle.com/Misc/another-of-day.html

I don't follow your logic here at all. why are you selecting from case_register - what is case_register.

mutating trigger

anish, June 06, 2008 - 3:37 pm UTC

Hi tom

CREATE TABLE REG (
APPNO VARCHAR2 (8) NOT NULL,
APPPR VARCHAR2 (9) NOT NULL,
APP_NAME VARCHAR2 (120),
REFNUMBER VARCHAR2 (9),
C_NAME VARCHAR2 (65),
C_FEE NUMBER (13,3),
APPREQUEST VARCHAR2 (100),
TELEPHONE_NO VARCHAR2 (50),
APP_DATE DATE,
APPID VARCHAR2 (6),
EXE NUMBER (10,3),
DEL NUMBER (10,3),
JOB VARCHAR2 (100),

PRIMARY KEY ( APP _NO ) ) ;
Foreign key in this table are APPID,refnumber

I want upadte the table with combination apppr and refnumber
With 3 records only not more then 3 /This trigger giving mutating trigger/
So wrote this trigger
CREATE OR REPLACE TRIGGER fupdate
before update on reg
for each row
declare
ref_num number;
BEGIN
select count(*) into ref_num from reg
where case_register.reference_number=:new.REFNUMBER
and requester_cpr=:new.apppr ;
if ref_num>2 then
RAISE_APPLICATION_ERROR(-20000, 'this combination of 3 records ');
END IF;
END;

This trigger is not fireing and giving the mutating trigger error problemb

So how can I solve this problemb I want update the same table with combination of apppr and refnumber with 3 records only it should only combination of refnumber and apppr only 3records and 2 field other field can change in the above table
Thanks

June 09, 2008 - 10:36 am UTC

look at the other places you've posted this same stuff.

gosh, do I hate triggers or what.

Please do read the other places you've posted this so you can learn why your trigger cannot work - LOGIC prevents it from working. You need to think MULTI-USER HERE.

mutating trigger in 10g

anish, June 09, 2008 - 12:39 am UTC

Hi tom

CREATE TABLE REG (
APPNO VARCHAR2 (8) NOT NULL,
APPPR VARCHAR2 (9) NOT NULL,
APP_NAME VARCHAR2 (120),
REFNUMBER VARCHAR2 (9),
C_NAME VARCHAR2 (65),
C_FEE NUMBER (13,3),
APPREQUEST VARCHAR2 (100),
TELEPHONE_NO VARCHAR2 (50),
APP_DATE DATE,
APPID VARCHAR2 (6),
EXE NUMBER (10,3),
DEL NUMBER (10,3),
JOB VARCHAR2 (100),

PRIMARY KEY ( APP _NO ) ) ;
Foreign key in this table are APPID,refnumber

I want upadte the table with combination apppr and refnumber
With 3 records only not more then 3 /This trigger giving mutating trigger/
So wrote this trigger
CREATE OR REPLACE TRIGGER fupdate
before update on reg
for each row
declare
ref_num number;
BEGIN
select count(*) into ref_num from reg
where case_register.reference_number=:new.REFNUMBER
and requester_cpr=:new.apppr ;
if ref_num>2 then
RAISE_APPLICATION_ERROR(-20000, 'this combination of 3 records ');
END IF;
END;

This trigger is not fireing and giving the mutating trigger error problemb

So how can I solve this problemb I want update the same table with combination of apppr and
refnumber with 3 records only it should only combination of refnumber and apppr only 3records and
2 field other field can change in the above table
Thanks

Mutating error

Abisha, March 27, 2009 - 12:29 am UTC

Hi Tom,
I have a question.
I need to avoid overalapping of dates in a table.
When i insert a new record i have to ensure that, current dates are not overlapping. To achieve this i need to check the dates existing in the same table. I end up with mutaion error. Kindly give me some solution to avoid this mutating error.
The below code code gives mutating error. Please help me to solve it..
CREATE OR REPLACE TRIGGER trg_test1
AFTER INSERT OR UPDATE on test1
for each row
DECLARE
m_effective_frm DATE;
m_effective_to DATE;
BEGIN

IF :NEW.effective_frm IS NOT NULL AND :NEW.effective_to IS NOT NULL THEN

SELECT effective_on
INTO m_effective_frm
FROM test1
ORDER BY effective_on;

SELECT effective_thru
INTO m_effective_to
FROM test1
ORDER BY effective_on;

IF :NEW.effective_on BETWEEN m_effective_frm AND m_effective_to THEN
raise_application_error(-20000, 'Dates are ovelapping');
ELSIF :NEW.effective_thru BETWEEN m_effective_from AND m_effective_to THEN
raise_application_error(-20000, 'Dates are ovelapping');
END IF;

END IF;
--mutating trigger

END trg_test1;

March 30, 2009 - 3:24 pm UTC

thank goodness you did.

Tell me, what happens when you have more than one user? Suppose the above code actually run - would it WORK? (rhetorical question, NO, resoundingly NO)

read some of these
http://asktom.oracle.com/pls/ask/search?p_string=avoiding+overlaps

Mutating Trigger : To avoid dates overlapping

ABISHA, March 31, 2009 - 3:45 am UTC

Hi Tom,
Thanks for your reply.
The below link helped me to solve the problem.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101
Thank u!

Handling mutating table error in 10g

Praveen, April 24, 2009 - 6:46 am UTC

Hi,
I have been using the methods you have been suggesting to handle the mutating table errors.

http://asktom.oracle.com/~tkyte/Mutate/index.html

But do you still believe this is the best strategy, even now, when Oracle 10g is around.

Thanks for your comments.

April 27, 2009 - 12:01 pm UTC

I think today in the year 2009 (having modified my thoughts) that if you are having to work around a mutating table constraint you have made a serious design flaw.

So, if you are using that technique, you have probably done something wrong (and there is a better than good chance that the code you implemented is incorrect - it is probably not multi-user safe)

Instead of trigger not seeing the update changes

Praveen, April 24, 2009 - 10:43 am UTC

Hi Tom,

Please see the code snippet below:

CREATE TRIGGER a_trigger
INSTEAD OF INSERT OR UPDATE ON some_veiw
FOR EACH ROW
:
:
:
BEGIN
IF INSERTING
INSERT INTO another_table (...) VALUES (...); ----> Working fine.
:
:
ELSIF UPDATING
UPDATE another_table
SET a_column =
(SELECT SUM(a_column)
FROM some_view
WHERE primary_key=:NEW.primary_key
GROUP BY primary_key); -----> The changes are not seen by the trigger. Why?
END IF;
END;

Why the update statement is giving incorrect result, although? How to write it in the correct way?

Thankyou for the comment.

April 27, 2009 - 12:24 pm UTC

you are in the middle of an update - think about it. Has the update happened yet?

This logic is so convoluted - I'm going to beg you to stop - just backup - stop don't do this. This will be so fragile (eg: non-maintainable)

But think about this

you have an instead of UPDATE on a view called "SOME_VIEW"

it is INSTEAD OF update (so, changes are not made to the underlying view base tables)

remember - "instead of update"....

So, in the trigger that fires INSTEAD OF UPDATING the base tables - you read "SOME_VIEW" again.

The answer in the trigger cannot be different - we are not updating the view base tables!

I don't know how to write it "correct", for I have no idea what you are actually trying to accomplish.

Instead of trigger not seeing the update changes

Praveen, April 25, 2009 - 3:02 am UTC

I'm sorry,

Please read

"WHERE primary_key = :NEW.primary_key
GROUP BY primary_key"

as

WHERE nonunique_key = :NEW.nonunique_key
GROUP BY nonunique_key

April 27, 2009 - 1:35 pm UTC

see above

why this will have mutating problem

A reader, July 03, 2012 - 2:55 pm UTC

hi tom,

i understand that mutating table error might occur when a trigger is trying to query the underlying table that triggers the trigger to prevent data integrity issues.

however, i dont somehow understand how this will cause the mutating error problem.

http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php - Under the Mutating Table Demonstration portion.

Why would it cause the exception when
i) a row is inserted and
ii) the trigger is an after insert row trigger
iii) the TAB1.ID is a primary key (so multiple insert of the same value cant happen too)

What I see is,
1st) do a insert on the table
2nd) the trigger is being triggered
3rd) read from the table again within the trigger , well there is a count of 1
4th) ok insert into the tab1_audit table.

-----------
In this scenario above, how would it corrupt the data ? and why would oracle want to prevent this from happening?

Regards,
Noob

July 03, 2012 - 5:42 pm UTC

it has to handle the general case.

in general,the insert statement could be inserting billions of rows, not one.

it doesn't matter if you have an example of one row - in general, it is many rows.

why this will have mutating problem

A reader, July 04, 2012 - 2:34 pm UTC

Hi tom,

In the event that if a insert statement might insert more then 1 or million of rows? why would it cause any discrepancies ?

In any case it is a AFTER row trigger, so after each and every row inserted, it does a count and the value are tally.
-----------------------------

Do you mean that the INSERT statement does not wait for the AFTER row trigger to complete before inserting the next row ?

T1) INSERT statement
T2) Insert row 1
T3) After row trigger triggered
T4) Insert row 2
T5) After row trigger triggered

or is it

T1) INSERT statement
T2) Insert row 1
T3) Insert row 2 and so for
T4) After row triggered for row 1 triggered
T5) Insert row 3
T6) Insert row 4
T7) Insert row 5,6,7,8
T8) After row triggered for row 2 triggered ?

does a INSERT statement wait for a after ROW trigger to finish it actions before it proceed with the next row ?

or the INSERT inserts asynchronously without any regards to the row trigger ?

Regards,
Noob

July 05, 2012 - 7:39 am UTC

In the event that if a insert statement might insert more then 1 or million of
rows? why would it cause any discrepancies ?

because you would be seeing the table as it appears IN THE MIDDLE OF A STATEMENT.

You are allowed to see the database as it looks after complete statements are executed.

Think about this, you issue a statement like:

update DEPT set deptno = deptno+10;

Now, dept currently has rows 10, 20, 30, 40 in it right.... DEPTNO is a primary key. Now, as you update that table row by row (your trigger would see it row by row) - we might update 10 to be 20, 20 to be 30, 30 to be 40, and 40 to be 50 (we could, it might be done in some other order but that order is possible).

Now, if we update 10 to be 20 - guess what? There are two deptno=20s in the table all of a sudden - or are there? Of course there isn't, but there would appear to be. We do not permit this view of the data.

The database becomes momentarily inconsistent during the processing of a SQL statement often, you are not allowed to see those inconsistencies.

In a set of triggers - before, before for each row, after for each row, after - the processing is:

before the statement is executed at all, run your before trigger

loop
   fire the before for each row trigger
   do the operation on that row
   fire the after for each row trigger
end loop

execute the after trigger

why mutating for noobs

Biswaranjan, July 05, 2012 - 1:13 am UTC

Hi Noobs,

Please read the whole page of the below link ,hope you will
clear the doubt. :)

######
https://forums.oracle.com/forums/thread.jspa?threadID=956355
#######

Regards,
Biswaranjan.

why this will have mutating problem

A reader, July 05, 2012 - 12:46 pm UTC

hi all,

thanks.
Yeap how could i forget atomic in ACID.

thanks!

Where is http://asktom.oracle.com/~tkyte/Mutate/index.html

A reader, February 27, 2013 - 11:48 pm UTC

Hi Tom,

The following link is getting webpage cannot found error 404. Is it still available anywhere.

http://asktom.oracle.com/~tkyte/Mutate/index.html

Thanks in advance.

February 28, 2013 - 7:57 am UTC

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Eye Opener

Mutating Trigger in case of Insert into as Select * from.....

Would be grateful if U kindly solve this problem that i face-Mutating Trigger

no mutating error - 9i - before insert

thanks.

Updating a Foreign Key on Same Table

RE: Muting Table

Mutating Trigger

Mutating Table error in Dynamic Trigger

Mutating Table error in Dynamic Trigger

Mutating Table error in Dynamic Trigger

Concurrency

mutating trigger

mutating trigger

mutating trigger

mutating trigger in 10g

Mutating error

Mutating Trigger : To avoid dates overlapping

Handling mutating table error in 10g

Instead of trigger not seeing the update changes

Instead of trigger not seeing the update changes

More on Mutation problem

why this will have mutating problem

why this will have mutating problem

why mutating for noobs

why this will have mutating problem

Where is http://asktom.oracle.com/~tkyte/Mutate/index.html

More to Explore

PL/SQL demos

PL/SQL docs

Mutating Trigger..

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Eye Opener

Mutating Trigger in case of Insert into as Select * from.....

Would be grateful if U kindly solve this problem that i face-Mutating Trigger

no mutating error - 9i - before insert

thanks.

Updating a Foreign Key on Same Table

RE: Muting Table

Mutating Trigger

Mutating Table error in Dynamic Trigger

Mutating Table error in Dynamic Trigger

Mutating Table error in Dynamic Trigger

Concurrency

mutating trigger

mutating trigger

mutating trigger

mutating trigger in 10g

Mutating error

Mutating Trigger : To avoid dates overlapping

Handling mutating table error in 10g

Instead of trigger not seeing the update changes

Instead of trigger not seeing the update changes

More on Mutation problem

why this will have mutating problem

why this will have mutating problem

why mutating for noobs

why this will have mutating problem

Where is http://asktom.oracle.com/~tkyte/Mutate/index.html

More to Explore

PL/SQL demos

PL/SQL docs