We create trigger in the test server (8i) its working without error, and when we created at life (8) we get the following error:
ORA-04091: table XXXX is mutating, trigger/function may not see it.

I only want to prove the mutating table by this triggers:
CREATE TABLE r_Module
(
Bureauno NUMBER(3),
Moduleno NUMBER(3),
primary key
(
Bureauno,
Moduleno
)
);

CREATE OR REPLACE TRIGGER LimitTest
BEFORE INSERT OR UPDATE ON r_Module
FOR EACH ROW
DECLARE
v_MaxModuleNum CONSTANT NUMBER := 5;
v_CurModuleNum NUMBER;
BEGIN
SELECT COUNT(*) INTO v_CurModuleNum FROM r_Module WHERE Bureauno = :new.Bureauno;
IF v_CurModuleNum + 1 > v_MaxModuleNum THEN
RAISE_APPLICATION_ERROR(-20000,'Too many Module in the Bureauno:'||:new.Bureauno);
END IF;
END;
/

When I insert some record into r_module table,I can't get the 4091 error,but when I update one, the error come.
I wonder if the insert can't lead the mutating table error?
Thank you very much!

By the way,The link (Oracle Server Application Developers Guide ) in the:
</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
may be invalidation.

If I use 'Insert into table SELECT ...',The 4091 error will happen,if only insert one record,It does not happen,but I want to know why?

Hi Tom,

I want to move the records of parent and child table to archive tables before delete.  I created the trigger to do that.  But I was not able to do that because of the mutating table.  (We use 817)


The reason that we wanted to use trigger instead of calling stored procedure to move data is that we have several places in application to delete this record. If we don't use trigger, we need to add calling stored procedure code in all those places of application.

I understand the concept of mutating table, but just but don't know how to implement it in this case.

Here is the sample:

create table t1(c1 number primary key, c2 number);

create table t2(c1 number , c2 number,
    constraint t2_fk foreign key(c1) references t1(c1));


create table t1_arch(c1 number, c2 number);

create table t2_arch(c1 number, c2 number);


insert into t1(c1, c2)
values(1, 10);

insert into t1(c1, c2)
values(2, 20);

insert into t2(c1, c2)
values(1, 100);

insert into t2(c1, c2)
values(2, 200);


create or replace trigger tri1
before delete on t1
for each row
begin
    insert into t1_arch(c1, c2)
    select c1, c2 from t1 
        where c1 = :old.c1;

    insert into t2_arch(c1, c2)
    select c1, c2 from t2
    where c1 = :old.c1;
end;
/

SQL> delete from t1
  2  where c1= 1;
delete from t1
            *
ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRI1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRI1'

Thanks so much for your help.

Sean  

Hi Tom,

Sorry for the confusion.  Actually, I just wanted to archive t1 data before delet it.


Here is the sample:

create table t1(c1 number primary key, c2 number);

create table t1_arch(c1 number, c2 number);

insert into t1(c1, c2)
values(1, 10);

insert into t1(c1, c2)
values(2, 20);

create or replace trigger tri1
before delete on t1
for each row
begin
    insert into t1_arch(c1, c2)
    select c1, c2 from t1 
        where c1 = :old.c1;

end;
/



SQL> delete from t1
  2    where c1= 1;
delete from t1
            *
ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function 
ORA-06512: at "SCOTT.TRI1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRI1

Thanks so much for your help.

Sean 
 

Hi Tom,

I want to update the record after insert by using trigger, but I got
mutating table error. Here is the example.


create table t1(c1 number, c2 varchar2(20));

create or replace trigger trg1
after insert on t1
for each row
begin
-- Get c2 values from other table, then update herre.
update t1
set c2 = 'B'
where c1 = :new.c1;

end;
/


insert into t1(c1)
values(1);

ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG1", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG1'


Thanks so much for your help.

Sean

Hi Tom,

I want to update the record after insert by using trigger, but I got
mutating table error. (I post it again because I forgot to put my personal info. Sorry for that)

Here is the example.


create table t1(c1 number, c2 varchar2(20));

create or replace trigger trg1
after insert on t1
for each row
begin
-- Get c2 values from other table, then update herre.
update t1
set c2 = 'B'
where c1 = :new.c1;

end;
/


insert into t1(c1)
values(1);

ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG1", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG1'


Thanks so much for your help.

Sean

Hi Tom,

What exactly do you mean by 'unstable set of rows' seen by a trigger.

Why can't the default behaviour be that a trigger sees the data in an 'as is' condition, i.e.

1. If it's a before update row trigger, the trigger would not see the current row being inserted but would see other rows that may have been inserted by the update statement that triggered the trigger.
2. If it's an after insert row trigger, the trigger would see all rows updated so far.

Then developers can keep this in mind and code accordingly.

I'm sure I'm missing something. Please point it out.

Thanks a lot.

create table employee
(
EmpId varchar2(10) primary key,
Salary number(18,3)
)

create or replace trigger checkSalary
after update on employee
for each row
declare
AvgSal number;
begin
select avg(salary)
into AvgSal
from employee;

if((:new.Salary / 2 )> AvgSal )
then
RAISE_APPLICATION_ERROR(-20000,”Error”)
end if;
end;

Assume initially:
Emp ID Salary
1 1000
2 2000

update employee
set salary = salary + 3000

Hypothetical steps of execution:
1. Since its an after update trigger, the trigger would use the updated values for the rows updated so far.
2. The first time the trigger is executed, average is (4000 + 2000) / 2 = 3000. And (4000/2) < 3000 so Row 1 passes.
3. For row 2, average is (4000 + 5000) / 2 = 4500. And (5000/2) < 4500 so passes.
4. Hence the update succeeds.
5. If any row had failed, the entire update is rolled back.

I agree that the order in which the rows are updated could make a difference in the result. But shouldn’t it be that way? If I’m updating only one row today, would you allow me to bypass the validation just because tomorrow I plan to update 10 other rows which would increase the average salary. If that’s my requirement, I have to find a different way to implement it. Doesn’t this provide greater flexibility.

“seriously -- it is there for our protection, honest.”. I believe you 100% Tom. I’m asking this just to improve my understanding.

Thanks a lot for your time and effort.

<b>Ok, using your example, but a tad more data....

(can you in english explain what RULE you are trying to enforce here?  It is a random rule)

No, if the order in which the rows are updated makes a DIFFERENCE IN THE RESULT, we call that "non-deterministic" in the world of relational databases and we very must hate that word.  If I have 4 rows, and you have 4 rows and we in isolation perform a set based operation on those 4 rows -- we had BETTER GET THE SAME ANSWER, else we have basically "a random outcome", why bother updating at all..

It is exactly this that you are being protected from.  If you don't want to be -- you write procedural code.  The following is your trigger in procedural code and shows what would happen:</b>


ops$tkyte@ORA9IR2> create table emp ( empid number, sal number );
Table created.
 
ops$tkyte@ORA9IR2> insert into emp values ( 1, 1000 );
1 row created.
 
ops$tkyte@ORA9IR2> insert into emp values ( 2, 2000 );
1 row created.
 
ops$tkyte@ORA9IR2> insert into emp values ( 3, 3000 );
1 row created.
 
ops$tkyte@ORA9IR2> insert into emp values ( 4, 4000 );
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_sal     number;
  3      l_avg_sal number;
  4  begin
  5      for x in ( select * from emp )
  6      loop
  7          update emp
  8             set sal = sal * 2
  9           where empid = x.empid
 10          returning sal into l_sal;
 11
 12          select avg(sal)
 13            into l_avg_sal
 14            from emp;
 15
 16          dbms_output.put_line( 'Empid = ' || x.empid || ' old sal = ' || x.sal || ' new sal = ' || l_sal || ' avg_sal = ' || l_avg_sal );
 17          if ( l_sal/2 > l_avg_sal )
 18          then
 19              raise_application_error( -20000, 'Error' );
 20          end if;
 21      end loop;
 22  end;
 23  /
Empid = 1 old sal = 1000 new sal = 2000 avg_sal = 2750
Empid = 2 old sal = 2000 new sal = 4000 avg_sal = 3250
Empid = 3 old sal = 3000 new sal = 6000 avg_sal = 4000
Empid = 4 old sal = 4000 new sal = 8000 avg_sal = 5000
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update emp set sal = (mod(empid+2,4)+1)*1000;
 
4 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_sal     number;
  3      l_avg_sal number;
  4  begin
  5      for x in ( select * from emp )
  6      loop
  7          update emp
  8             set sal = sal * 2
  9           where empid = x.empid
 10          returning sal into l_sal;
 11
 12          select avg(sal)
 13            into l_avg_sal
 14            from emp;
 15
 16          dbms_output.put_line( 'Empid = ' || x.empid || ' old sal = ' || x.sal || ' new sal = ' || l_sal || ' avg_sal = ' || l_avg_sal );
 17          if ( l_sal/2 > l_avg_sal )
 18          then
 19              raise_application_error( -20000, 'Error' );
 20          end if;
 21      end loop;
 22  end;
 23  /
Empid = 1 old sal = 4000 new sal = 8000 avg_sal = 3500
declare
*
ERROR at line 1:
ORA-20000: Error
ORA-06512: at line 19


<b>Now, if you were enforcing some business rule here, you very well might violate it for some rows afterwards.

That is, during the processing of the update, the rule said "OK", but if you ran the rule against the data POST PROCESS -- it would say "nope".  But since the order of rows matters -- how can you say if the rule is violated or not????  it would be non-deterministic, bad </b>

 

create view emp(empid, sal) as select empid, salary from employee

create or replace trigger checkSalary
instead of update on emp
for each row
declare
AvgSal number;
begin
select sum(salary)/count(*)
into AvgSal
from employee;
if((:new.Sal / 2 )> AvgSal )
then
RAISE_APPLICATION_ERROR(-20000,'Error');
end if;

update employee
set salary = :new.sal
where empid = :new.empid;

end;

?

Tom,
You're pretty amazing at explaining things. Most people would've written a sentence or two as a response.

It sure helps when you demonstrate things. Way cool.

"In a multi-user environment - they are absolutely
100% flawed. "

absolutely agree

however Arun's question was

Why can't the default behaviour be that a trigger sees the data in an 'as is'
condition ?

Why do you think Oracle has to protect us from a flawed implementation using an after row trigger on a table ?
(when we can do the same flawed implementation in any
other way ?)

Point is:
why not accept Arun's proposal

faulty implementations are always the
developper's fault as I think

You cannot protect a developper of
writing a faulty implementation if he
really wants to do that
:-)

my halfpenny

i write the same trigger at the database level and at the form level (at the database level it gives me mutating table error but at the form level there is no problem);

at the database level

SQL> create or replace trigger ttt before insert on emp
2 for each row
3 begin
4 select max(empno)+10 into :new.empno from emp;
5 end;
6 /

Trigger created.

insert into emp (select * from emp_back)

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TTT", line 2
ORA-04088: error during execution of trigger 'SCOTT.TTT'

but at the form level i made a pre-insert trigger at the emp data block
.
.
select max(empno)+10 into :emp.empno from emp;
.
.

when i insert more than one emp and i press the save button there is no problem the trigger works right for all the rows,so what is the reason for this difference.
Thank alot  

few years ago, I was asked a question about how to work around the mutating table error in an interview. I knew what the error was but I wasn't quite sure how to resolve it. My answer was "avoid mutating table error by fixing the logic".

When I saw your opinion "to avoid the mutating table constraint -- but I would avoid the situation that
gets me there in the first place" at the beginning of this thread, I felt little confident about my answer. However, I didn't get the job at that time.

Anyway, I just felt to share it.

i know that using sequence is better but my question is about the diffrence in the two triggers,and the pre-insert trigger at the form level execute after the row is insert into the databse ,i did not mean just create a record i mean when i press thhe save button the pre-insert trigger at the form level will execute ,it have the same meaning as before insert on emp at the databse level ,but i did not understand why only at the databse level the mutating error raised.

i think that i have am a bit confused
now when i write a databse trigger i say (after insert on emp) or (before insert on emp) and not (while inserting on emp) so while trigger will be fire while inserting the record (before or the after)
Thanks al ot.

i think that i have am a bit confused
now when i write a databse trigger i say (after insert on emp) or (before insert
on emp) and not (while inserting on emp) so which trigger will be fire while
inserting the record (before or the after)?????
Thanks al ot.

thanks for your respond ;
why the mutaing table error only fires in the database trigger not in the form trigger for eaxmple:
create trigger fff before insert on emp
for each row
declare
i number(5);
begin
select max(sal) into i from emp;
if i>4000 then
........
.......
end;
if i insert into emp table a mutating table error will fires but if i wirite the same code in the pre-insert trigger at the form level no error will be raised so what is the reason.

i think you did not understand my point ,at the form level i am inserting data not quering a data the pre-insert trigger code looks like that:
decalre
i number(10);
begin
select max(sal) into i from emp;
if i>4000 then
.
.
end;

here if i insert more than one row and i press save button no mutating error will be raised,but if the trigger where written at the database level the mutating table error will raise.

but i can not understand what do you mean by there is a query then insert (i can not understand what is the query here)
Thanks .

YOU SAY:
........... from forms, there is

a) a query
b) and later and insert

WHAT DO YOU MEAN BY A QUERY ,IF I AM INSERTING A DATA FROM THE FORM WHERE IS THE QUERY.

THAMKS.

i am using a sequence number to assign a primary key value for an invoice but i want that squence to be set to one at the end of the day (i mean that the compination between the sysdate and the squence will build the primary key) so how can i set the sequence to one for the first invoice for each day.

what is the row_number()

i have the following foramt for the invoice number
ddmmyyyy|nnnnnn
for example
29062005000001
29062005000002
.
.
29062005000980
next day
30062005000001
i do this using the pre-insert query in the form,i did not use any squence
i write
select nvl(to_number(substr(to_char(max(inv_no)),9)),0)+1
from invoice
where
substr(to_char(inv_no),1,8)=to_char(sysdate,'ddmmyyyy');
now i want to use a sequence instead of using max(inv_no)+1
any help

hallo monahad,

decalre
i number(10);
begin
select max(sal) into i from emp; -- <<-- THIS IS A QUERY
if i>4000 then
.
.
end;

drop it and re-create it again
do it in a job on
00:00:00 every day

>>you do realize that sequences are never ever never never >>never ever gap free.

>>sorry, this is a "bad idea".

i did not understand you do what is the bad idea exectly

then is there any way to do the invoicve number(ddmmyyyy|nnnnnn) using the sequence number if no,then if i use the select max(invno)+1 it will not work in multiuser enviroment,so what is the solution???
thanks alot

i think that i get lost.....
any way the primary key is really the sequence number,but i concatenate the date and the sequence just to display them in the report,but i want that sequence to start from 1 for each day,and i want to do that using the sequence number.

Ok , i use your method and it works well,
but i want to make sure that i did the right thing

i create a sequence and in the pre-insert trigger at the form level i write the following code
select a.nextval into :inv_no from dual;

( i make the trigger at the form level becuause i have a master (invoice) and detail (inv_detail) relation at the same screen,so i must fetch the value of the sequence into the :invo_no in the screen,to let the 'copy value from' property at the detail block works so the value of the foreign key will be copied from the sequence number at the screen.
will this method be a good one for multi user envirment(to select the a.nextval in the pre-insert trigger at the from level?

but my question is do you recommend to use those methods or it may be unsafe in multiuser enviroment????

i create the following database trigger:

create or replace trigger full222 after insert on emp
declare
i number(10);
begin
select sum(sal) into i from emp;
if i >38000 then
raise_application_error(-20555,'g');
end if;
end;

and i insert multi records from the form developer ,then the statment triggers fired for each row(not once),and there was no error(mutating table error which at the form level will be 'unable to insert record')
but when i changed the above trigger from statment trigger to row level(for each row)trigger and i do multirecord insert from the form, there was an error 'unable to insert record' which is because of the mutating table error ,so what is the reason behind this strange result, since the statment trigger in this situation fires for each row but without giving me any error,the opposite of for each row trigger althogh the two triggers here fire for each row???
THANKS A LOT

i know that statment trigger never raise mutating table error at all, but my point was if i make multirecord insert from a form then the statment triiger will fires for each row and there will never be a mutating table error,i try it and it works ,but i am not sure about the behavior of the trigger in multi user inveroment if two user insert two salaryies at the same time,so could you give me any guidlines to help in how should i write triigers in multi user enviroment ,really i was not aware of this i though that if two user access the same record one of them will be blocked until the other user finish,so if you could give me some guidlines to help me in how should i write triigers in multi user enviroment.
THANKS ALOT

ok, then can we say that there is no record blocking in Oracle if two users from two session access the same block at the same time even if the select statment will occur just before a commit(like writing the select statment in a pre-insert trigger at the form level where the select will be performed just before the insert is applid to the databse)???
THANKS A LOT FOR YOUR HELP

what is table blocks?

sorry i mean what is table locks???

i did not understand the last post could you explaine it deeper:

>>you can safely say that:
>>reads are not blocked by writes
>>writes are not blocked by reads
>>and a write of a row is blocked only by a write of the >>same row by another
>>session
>>(assuming no table locks came into play)

i have two question about how do oracle handle mutiple action from seperate session at the same time
1.let assume that session one try to update the emp table
update emp set sal=40 where empno=90;
commit;
and at the other session
update emp set sal=50 where empno=90;
commit;
and they commit at the same time which sal value will be assign to empno 90 (50 or 40).
2.let assume that session one try to insert a new emp
insert into emp (empno,ename) values(100,1000);
commit;
and at the other session
insert into emp (empno,ename) values(100,1000);
commit;
and they commit at the same time so which row will be inserted and which one will give an error because of theprimary key constraint.

As usual tom's answers are superb. Always helpful

Hey Tom,

i've got a table on a 10gr2 database:

cdb@CENTRAL> desc cdb$photo
Name Null? Type
----------- -------- -------------------------------------------------
PHOTO_ID NOT NULL NUMBER
SET_ID NOT NULL NUMBER
FILENAME NOT NULL VARCHAR2(255)
DATED DATE
COMMENTS VARCHAR2(4000)
PHOTO ORDSYS.ORDIMAGE
SIG ORDSYS.ORDIMAGESIGNATURE

the table contains ~40'000 images at the moment

the application on top of it, runs the following sql inside some pl/sql package:

SELECT photo_id
FROM "CDB$PHOTO" c
WHERE c.photo.getcontentlength() = :x;

this performs rather slow:
cdb@CENTRAL> set autotrace on exp stat
cdb@CENTRAL> SELECT photo_id
2 FROM "CDB$PHOTO" c
3 WHERE c.photo.getcontentlength() = 809059;

PHOTO_ID
----------
660


Execution Plan
----------------------------------------------------------
Plan hash value: 3567237237

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 395 | 99K| 3810 (1)| 00:00:46 |
|* 1 | TABLE ACCESS FULL| CDB$PHOTO | 395 | 99K| 3810 (1)| 00:00:46 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ORDIMAGE"."GETCONTENTLENGTH"("C"."PHOTO")=809059)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13933 consistent gets
15 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed.

now, oracle won't let me create an index on that function, as it is not deterministic.

so i figured i'd go along and create my own function to read out the content length of the image data:

CREATE OR REPLACE FUNCTION "CDB$PHOTO_FUNC" (p_id IN NUMBER)
RETURN NUMBER DETERMINISTIC
AS
len NUMBER;
BEGIN
SELECT c.photo.getcontentlength() INTO len FROM "CDB$PHOTO" c WHERE photo_id=p_id;
RETURN len;
END;
/

we NEVER have any UPDATEs on this table, only INSERT and DELETE. therefore, naming the function deterministic should be fine in this case, the images never change.

i can now happily create an index on the above function:

CREATE INDEX "CDB$PHOTO_FUNCIDX" ON "CDB$PHOTO" (CDB$PHOTO_FUNC(photo_id));

after gathering new stats, oracle also happily uses that index:

cdb@CENTRAL> SELECT photo_id
2 FROM "CDB$PHOTO" c
3 WHERE "CDB$PHOTO_FUNC"(photo_id) = 809059;

PHOTO_ID
----------
660


Execution Plan
----------------------------------------------------------
Plan hash value: 3530425928

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CDB$PHOTO | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CDB$PHOTO_FUNCIDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CDB"."CDB$PHOTO_FUNC"("PHOTO_ID")=809059)


Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

and it performs very nicely.

however, oracle now won't let me perform ANY dml on this table anymore:

cdb@CENTRAL> delete from cdb$photo where photo_id=660;
delete from cdb$photo where photo_id=660
*
ERROR at line 1:
ORA-04091: table CDB.CDB$PHOTO is mutating, trigger/function may not see it
ORA-06512: at "CDB.CDB$PHOTO_FUNC", line 6

am i missing something here, or is that really not possible to implement in this fashion ??

thanks for your time!

Stefan

Excellent, thanks for the explanation, i didn't think about that :)

Stefan

<quote>
first of all, that is a AFTER statement trigger, they never raise mutating tables.
</quote>
Would you be so kind and help me understand what I'm missing (something really obvious, probably)?

create table persons (id_pers number, id_dept number);
create table depts (id_dept number, name varchar2(10));
create table test (id_t number, id_pers number);
alter table depts add (constraint depts_pk primary key(id_dept));
alter table persons add (constraint persons_pk primary key(id_pers));
alter table persons add (constraint persons_fk foreign key(id_dept) references
depts(id_dept) on delete set null);
alter table test add (constraint test_pk primary key(id_t, id_pers));
alter table test add (constraint test_fk foreign key(id_pers) references
persons(id_pers) on delete cascade);
create trigger persons_au after update of id_dept on persons for each row
begin
  if (:new.id_dept is null) then
     delete from test where id_pers=:new.id_pers;
  end if;
end;
/
insert into depts values (1, 'test dept');
insert into persons values (1, 1);
insert into test values (10, 1);
commit;

SQL> select * from depts;

   ID_DEPT NAME
---------- ----------
         1 TEST DEPT

SQL> select * from persons;

   ID_PERS    ID_DEPT
---------- ----------
         1          1

SQL> select * from test;

      ID_T    ID_PERS
---------- ----------
        10          1

My business requirements:

- table TEST can contain only persons with ID_DEPT NOT NULL
- when deleting from DEPTS, set to NULL the correspondent ID_DEPT in PERSONS

Now:

SQL> update persons set id_dept=null;

1 row updated.

SQL> select * from test;

no rows selected

So far so good, but:

SQL> rollback;

Rollback complete.

SQL> delete from depts;
delete from depts
            *
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PERSONS_AU", line 3
ORA-04088: error during execution of trigger 'SCOTT.PERSONS_AU'

Thanks in advance for your time.

Alessandro 

Darn, I was blind... my apologies. :(

But, apart from that, is my logic flawed here or simply I'm not using the right tool (a trigger) for the job? I (still!) fail to see what's causing the error in my second example (why is TEST mutating).

Alessandro

Sir,
can I do this?

create or replace trigger trig_002
after insert on t1 for each row
begin
insert into t2 values(:new.id, :new.name, :new.sal);
delete from t1;
end;
/
ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_002", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRIG_002'

table t1 must be emptied/truncated after that record is transferred to t2

thanks

Hi Tom,

I am new to writing triggers, and got a mutating table error.

Here is what i am trying to accomplish, i have a table
wssusers 

SQL> desc wssusers
 Name                Null?    Type
 ------------------- -------- ----------------------------
 WSSUSERNAME                  VARCHAR2(255)
 WSSREMOTEIP                  VARCHAR2(255)

Through application when a new user is inserted/updated to wssusers table, I wrote a trigger to insert remote ipaddress to remoteip column on the same table. When i try to insert a new user , i get a mutating error and the insert fails.

The reason i am trying to insert 20 ipaddress is, the application will then allow only those logging on the application from those addresses only. Obviously Mutating error seems dumb logic.

Is there a simple way,i can accomplish this, other than a cronjob ?

Thanks in advance.
Ramana 

create trigger t before insert on t for each row
begin
   :new.column1 := value;
   :new.column2 := value2;
end;
/

Hello.

I need to implement a way of doing that when a parent record is deleted (set null rule), some of the child records (in the same table) becomes the new parent of its 'brothers', it does not matter which one.

Doing it with a trigger would help because I would not need to find for all the points in an application where the delete can be executed.

In order to avoid the 'mutating table' problem I have tried keeping the old values before deleting them:

CREATE TABLE PRUEBA_FERNANDO
(
  COL_PK  VARCHAR2(10) primary key,
  COL_FK  VARCHAR2(10),
  FOREIGN KEY (COL_FK) REFERENCES PRUEBA_FERNANDO (COL_PK) ON DELETE SET NULL
);

insert into prueba_fernando values ('Parent1','');
insert into prueba_fernando values ('Child11','Parent1');
insert into prueba_fernando values ('Single','');
insert into prueba_fernando values ('Parent2','');
insert into prueba_fernando values ('Child21','Parent2');
insert into prueba_fernando values ('Child12','Parent1');
insert into prueba_fernando values ('Child13','Parent1');
insert into prueba_fernando values ('Child22','Parent2');
insert into prueba_fernando values ('Child23','Parent2');

commit;

CREATE OR REPLACE PACKAGE TRIGGER_DELETE_PRUEBA_FERNANDO
IS
  type rHasAParent is record (
    COL_PK    prueba_fernando.COL_PK%TYPE,
    COL_FK    prueba_fernando.COL_PK%TYPE
  );
  
  type tHasAParent is table of rHasAParent index by PLS_INTEGER; 
  type tOldParent  is table of prueba_fernando.COL_PK%TYPE index by PLS_INTEGER;
  
  listOldParents tOldParent;              
  listRecordHasAParent tHasAParent;
  
  
END TRIGGER_DELETE_PRUEBA_FERNANDO;
/



CREATE OR REPLACE TRIGGER DELETE_PRUEBA_FERNANDO_BEFORE
 BEFORE DELETE ON PRUEBA_FERNANDO
FOR EACH ROW
DECLARE
  cont PLS_INTEGER;
BEGIN
  cont := TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents.COUNT;
  IF (:OLD.COL_FK is null) THEN
    TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents(cont+1) := :OLD.COL_PK;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(substr(sqlerrm,1,255));
END DELETE_PRUEBA_FERNANDO_BEFORE;
/


CREATE OR REPLACE TRIGGER UPDATE_PRUEBA_FERNANDO_BEFORE
 BEFORE UPDATE ON PRUEBA_FERNANDO
FOR EACH ROW
DECLARE
    cont PLS_INTEGER;
BEGIN
    IF ((:OLD.COL_FK is not null) and (:NEW.COL_FK is null))THEN
        dbms_output.put_line(:OLD.COL_PK||' has a Parent called: '||:OLD.COL_FK);
        cont := TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent.COUNT;
        TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(cont+1).COL_PK := :OLD.COL_PK;
        TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(cont+1).COL_FK := :OLD.COL_FK;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(substr(sqlerrm,1,255));
END;
/




CREATE OR REPLACE TRIGGER DELETE_PRUEBA_FERNANDO_AFTER
 AFTER DELETE ON PRUEBA_FERNANDO
BEGIN
  DECLARE
    newCOL_PK PRUEBA_FERNANDO.COL_PK%TYPE;
    j PLS_INTEGER;
    ParentFound BOOLEAN;
  BEGIN
    for i in 1..TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents.COUNT loop
                       
          j := 1;
          ParentFound := FALSE;
          while ( (j <= TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent.COUNT) ) loop

            if (TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents(i) = TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(j).COL_FK and (not ParentFound)) then
              ParentFound := TRUE;
              newCOL_PK := TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(j).COL_PK;
            end if;

                
            if not (TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(j).COL_PK = newCOL_PK) then
              update PRUEBA_FERNANDO
                 set COL_FK = newCOL_PK
               where COL_PK = TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent(j).COL_PK;
            end if;

            j := j+1;
          end loop;
   
          update PRUEBA_FERNANDO
             set COL_FK = NULL
           where COL_PK = newCOL_PK;  
    end loop;               

    TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents.DELETE;
    TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent.DELETE;
  END;      

   
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(substr(sqlerrm,1,255));
    TRIGGER_DELETE_PRUEBA_FERNANDO.listOldParents.DELETE;
    TRIGGER_DELETE_PRUEBA_FERNANDO.listRecordHasAParent.DELETE;
END DELETE_PRUEBA_FERNANDO_AFTER;
/

It seems to work well in some situations, but not always:

SQL> delete prueba_fernando where COL_PK in ('Parent1','Parent2','Single');

3 rows deleted.

SQL> select * from prueba_fernando;

COL_PK     COL_FK
---------- ----------
Child11
Child21
Child12    Child11
Child13    Child11
Child22    Child21
Child23    Child21

6 rows selected.

SQL> roll
Rollback complete.
SQL> delete prueba_fernando where COL_PK in ('Parent1','Child11','Child12','Child13');

4 rows deleted.

SQL> select * from prueba_fernando;

COL_PK     COL_FK
---------- ----------
Single
Parent2
Child21    Parent2
Child22    Parent2
Child23    Parent2

SQL> roll
Rollback complete.
SQL> delete prueba_fernando where COL_PK in ('Child11','Child12','Child13','Parent1');
delete prueba_fernando where COL_PK in ('Child11','Child12','Child13','Parent1')
       *
ERROR at line 1:
ORA-00600: internal error code, arguments: [13001], [], [], [], [], [], [], []

I do not know what causes this problem and how to avoid it.


Thanks in advance.

I am a developer in a team that mantains and develops new requirements for a communications application.

There is a table that stores information of communication devices. The table has been modified with this parent-child relationship because virtual devices can exist till now (the child records are the virtual ones, but when one non-virtual device is deleted one virtual becomes non-virtual).
In fact, all related devices are the same physical device. I am sorry I cannot tell what virtual devices mean, I do not know.

I suppose that the idea of only modifying one table was to have a smaller impact on the whole application.

Thanks a lot for all your help, I have a small trigger to check that if a job is already running second user can't start the same one, I'm getting mutating, trigger error.
Well definitely I'm doing something very wrong and need your expert opinion on how I can best achieve my motive.

CREATE OR REPLACE TRIGGER
job_queue_bi BEFORE INSERT OR UPDATE OF job_name ON job_queue_t FOR EACH ROW
declare
count1 NUMBER;
invalid_job_name EXCEPTION;
begin

SELECT count(1) INTO count1 FROM job_queue_t WHERE job_name = :new.job_name;
IF count1 > 0 THEN
raise invalid_job_name;
END IF;

EXCEPTION
when invalid_job_name then
raise_application_error(-20001, 'job not allowed');
when others then
raise_application_error (-20000, SQLERRM(SQLCODE));
end job_queue_bi;
/

Thanks a lot

My mistake actually I explained half the problem and why i;m trying to use trigger,
there is a column called USERNAME in job_queue_t and the requirement is that a user in
'DATABASE' user group can start any job. To find the user group we have a user_group_t table,
the trigger is like:

CREATE OR REPLACE TRIGGER
job_queue_bi BEFORE INSERT OR UPDATE OF job_name ON job_queue_t FOR EACH ROW
declare
count1 NUMBER;
invalid_job_name EXCEPTION;
v_user_grup varchar2(40);
begin
SELECT user_grup into v_user_grup FROM user_group_t WHERE username= :new.username;
IF (v_user_grup <> 'DATABASE') THEN
SELECT count(*) INTO count1 FROM job_queue_t WHERE job_name = :new.job_name;
IF count1 > 0 THEN
raise invalid_job_name;
END IF;
END IF;
EXCEPTION
when invalid_job_name then
raise_application_error(-20001, 'job not allowed');
when others then
raise_application_error (-20000, SQLERRM(SQLCODE));
end job_queue_bi;
/

Tom,
Thanks a lot for your quick and valuable comments and I say "yes" you are right but what if the user group is defined as not null, is this approach right in that case.

Hi

I have a requiremenet which is whenever a row is updated in table T1 the row which is being updated the old values must be inserted. i.e versioning the row

For example:

create table t1 (id number, name varchar2(10));

insert into t1 values(1, 'ORANGE');

insert into t1 values(2, 'APPLE');

commit;

create or replace trigger t1$trg1
after update on t1
referencing old as old
new as new
for each row
begin
insert into t1
values
(:old.id + 1000, :old.name);
end;
/

update t1 set name = 'POTATO' where id = 1;

*
ERROR at line 1:
ORA-04091: table SCOTT.T1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T1$TRG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.T1$TRG1'

I have had a look at http://asktom.oracle.com/tkyte/Mutate/index.html

Is it the only workaround using three triggers?

I am working in 10.2.0.2

Hi Tom.

<quote>
Them: "Tom, we are getting this mutant table thing"
Me: "show me what you are doing, oh -- let me tell you what would happened if that trigger actually worked and went into production. Now, let me tell you how to achieve your goal *safely*"
</quote>

I'll be 'Them', you be 'Me'.

Requirement: Given a forest of trees build, and maintain, the list of transitive edges defined by the trees.

Example:
Given the trees:
0 2
| / \
1 3 6
/ \
4 5

we should have the edges (descendent -> ancestor):
0->0
1->0
2->2
3->3
3->2
4->4
4->3
4->2
5->5
5->3
5->2
6->6
6->2

after deleting the node labelled 3 (4 and 5 will be reparented to null already) we should have the following trees:
0 2 4 5
^ ^
| |
1 6

and the edges:
0->0
1->0
2->2
4->4
5->5
6->6
6->2


In reality the edges are a denormalisation only required to circumvent joining up and down the tree. Currently they are being maintained by the application, which is dodgy. I have suggested simply using CONNECT BYs directly in the queries, I've be overuled on the basis that CONNECT BYs will be 'a _lot_ slower than joining through the edges'. I don't know whether that's true or not. So, triggers are deemed to be the solution.

Anyways, how would the following triggers cause trouble if the mutating table restriction wasn't in place? Looks like concurrent operations would either conflict or be commutative to me.
P.S. I'm pretty sure only the on update trigger actually needs to use the nodes table, the on insert and on delete could just use the edges table but hopefully you get my point.

Also, given 'plain old' triggers aren't going to work and I'd have to follow your Mutation work-around, any comments what's likely to be faster... Mutation work-around or CONNECT BYs directly in the queries?

Thanks very much.


CREATE SEQUENCE edgeIdSeq START WITH 1 INCREMENT BY 1;

CREATE TABLE nodes (
id number(15) NOT NULL primary key,
parent NUMBER(15)
);
ALTER TABLE nodes ADD CONSTRAINT fk_node_parent FOREIGN KEY (parent) REFERENCES nodes;

CREATE TABLE edges (
id number(15) NOT NULL primary key,
ancestor NUMBER(15) NOT NULL,
descendent NUMBER(15) NOT NULL
);
ALTER TABLE edges ADD CONSTRAINT fk_edge_anc FOREIGN KEY (ancestor) REFERENCES nodes;
ALTER TABLE edges ADD CONSTRAINT fk_edge_dsc FOREIGN KEY (descendent) REFERENCES nodes;
ALTER TABLE edges ADD CONSTRAINT fk_edge_nodes UNIQUE (ancestor,descendent);

/* Trigger to insert edges when node is inserted. */
CREATE OR REPLACE trigger node_ins
AFTER INSERT ON
nodes
/*REFERENCING new as insertedNode*/
FOR EACH ROW
BEGIN
INSERT INTO
edges (id, ancestor, descendent)
SELECT
edgeIdSeq.nextval,
id,
:new.id
FROM
nodes
START WITH id = :new.id
CONNECT BY
id = PRIOR parent;
END;
/
;

/* Trigger to delete and insert edges when a node is reparented. */
/* Trigger to delete delete edges when a node is deleted. */

INSERT INTO nodes values (0, NULL);
INSERT INTO nodes values (1, 0);
INSERT INTO nodes values (2, NULL);
INSERT INTO nodes values (3, 2);
INSERT INTO nodes values (4, 3);
INSERT INTO nodes values (5, 3);
INSERT INTO nodes values (6, 2);

Tom-

The following is a simplified version of more complex requirement/design.

drop table test_mutate;
drop package test_mutate_type;
drop package test_mutate_pkg;

--create table

create table test_mutate
(
a number,
b number
);

-- insert rows

insert into test_mutate values (1,1);
insert into test_mutate values (1,1);
insert into test_mutate values (2,2);
insert into test_mutate values (2,2);
insert into test_mutate values (2,2);
insert into test_mutate values (3,3);
insert into test_mutate values (4,4);
insert into test_mutate values (5,5);

-- create types package

create or replace package test_mutate_type
as

   type test_mutate_rectype is record ( a number, b number );
   type test_mutate_tabtype is table of test_mutate_rectype;
   type test_mutate_refcurtype is ref cursor return test_mutate_rectype;

end test_mutate_type;
/
show errors

--create pipelined func in a package

create or replace package  test_mutate_pkg
as

 function test_mutate_fn
    ( refcur_in   IN  test_mutate_type.test_mutate_refcurtype )
  return test_mutate_type.test_mutate_tabtype
  pipelined
  cluster refcur_in by (a)
  parallel_enable
  (partition refcur_in by hash (a));

  procedure initiate;

end test_mutate_pkg;
/
show errors
create or replace package  body test_mutate_pkg
as

  function test_mutate_fn
      ( refcur_in   IN  test_mutate_type.test_mutate_refcurtype )
    return test_mutate_type.test_mutate_tabtype
    pipelined
    cluster refcur_in by (a)
    parallel_enable
    (partition refcur_in by hash (a))
  is
    v test_mutate_type.test_mutate_rectype := null;
  begin
    fetch refcur_in into v;
    v.b := v.b * v.b;
    pipe row(v);
  end test_mutate_fn;

  procedure initiate
  is
  begin

    merge into test_mutate a
     using table ( test_mutate_pkg.test_mutate_fn(cursor(select * from test_mutate))) b
      on (a.a = b.a)
    when matched then
      update set a.b = b.b; 

  end initiate;

end test_mutate_pkg;
/
show errors

exec test_mutate_pkg.initiate;

<b>*
ERROR at line 1:
ORA-04091: table TEST.TEST_MUTATE is mutating, trigger/function may not see it
ORA-06512: at "TEST.TEST_MUTATE_PKG", line 14
ORA-06512: at "TEST.TEST_MUTATE_PKG", line 23
ORA-06512: at line 1

</b>

--drop table test_mutate;
--drop package test_mutate_type;
--drop package test_mutate_pkg;

I know that table function is reading data from the table and merge will be concurrently updating the same table, with requirements to run the statements(merge and select) in parallel, this is definitely producing 4091 as expected.

Question:

What is the best workaround?

Should we insert the output of the pipelined function into global temp table and make the second pass to merge it from global temp table into permanent table?
(I'd really like to avoid the intermediate table if possible and have to use the parallel pipelined function to parallelize the serial pl/sql execution. As my "cluster by" clause ensures that all rows for specific value in column "a" are given to only one slave, and each slave works on the specific row only, so I know this would not cause any inconsistencies that result out of usual mutating error)

I definitely need to parallelize this processing. I "dumbed" down the example too much that's why there does not seem to be need for pipelined function.

The fact is once a set of rows are fetched, they goes through whole lot of pl/sql "legacy" code and joins that row to several other tables and does a lots of other procedural logic on that set before it pipes those rows out. (I replaced the loop and probably 3000 lines of code with a*a for simplicity.). We could go back and try to refactor that pl/sql to make it one "giant" sql statement with bunch of analytic functions, but it won't be pretty and the understanding the new code would be very difficult (I think).

The key for us is to parallelize this pl/sql.

Yes.

Yes, I'm familiar with that approach; have used it also.

However, The only issue is that the process described above is one piece of a long running transaction, which itself runs as a job. Dividing by rowid ranges and running it as separate jobs would break a transaction into multiple transactions, which is not desirable.

I have been able to execute the select statement with the parallel pipelined function. The select running in parallel is much much faster than our existing pl/sql code. The only issue, I have is that I cannot merge into the same table.

I will try using the parallel pipelined approach, dump the data into temp table and then merge into final table to see how that works. I still think we should gain a lot of improvement over current code of row by row operations that execute serially.

Thanks

Sir,


Please help in clearing the error during execution of the trigger - [ORA-04088]. I created this trigger for avoiding the mutating table error.


CREATE TABLE EUR_MSTR (
EUR_ID NUMBER (6) ,
EUR_DATE DATE NOT NULL,
CONSTRAINT UQ_EUR_MSTR_DT
UNIQUE (EUR_DATE),
CONSTRAINT PK_EUR_MSTR_ID
PRIMARY KEY ( EUR_ID ) ) ;

CREATE TABLE EUR_DTLS (
EUR_ID NUMBER (6) NOT NULL,
EUR_DATE DATE NOT NULL,
SHIFT_ID NUMBER NOT NULL,
WORK_HRS_FROM DATE,
WORK_HRS_TO DATE,
BREAKDOWNS_FROM DATE,
BREAKDOWNS_TO DATE,
PROD_MOVES NUMBER (4),
UNPROD_MOVES NUMBER (4),
CONSOLIDATIONS NUMBER (4),
EQUIPMENT VARCHAR2 (5) NOT NULL ) ;

ALTER TABLE EUR_DTLS ADD CONSTRAINT FK_EUR_DTLS_ID
FOREIGN KEY (EUR_ID)
REFERENCES EUR_MSTR (EUR_ID) ;


INSERT INTO EUR_MSTR ( EUR_ID, EUR_DATE ) VALUES (
1, TO_Date( '04/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));



CREATE OR REPLACE package eur_data_pkg is
Type eur_dtl_rec is record ( p_eur_id eur_dtls.eur_id%type,
p_eur_date eur_dtls.eur_date%type,
p_shift_id eur_dtls.shift_id%type,
p_equipment eur_dtls.equipment%type,
p_work_hrs_from eur_dtls.work_hrs_from%type,
p_work_hrs_to eur_dtls.work_hrs_to%type
);
Type eur_dtl_type is table of eur_dtl_rec index by binary_integer;
l_eur_dtl eur_dtl_type;
l_eur_dtl_set_null eur_dtl_type;
l_counter binary_integer := 0;
end;
/


CREATE OR REPLACE TRIGGER eur_trg_chk_shift_hrs before insert or update on eur_dtls for each row
begin
eur_data_pkg.l_counter := eur_data_pkg.l_counter + 1;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_eur_id := :new.eur_id;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_eur_date := :new.eur_date;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_shift_id := :new.shift_id;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_equipment := :new.equipment;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_work_hrs_from := :new.work_hrs_from;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_work_hrs_to := :new.work_hrs_to;
exception
when others then
dbms_output.put_line('error in before insert for each row ');
raise;
end;


CREATE OR REPLACE TRIGGER EUR_TRG_CHK_SHIFT_HRS_STMT AFTER INSERT OR UPDATE ON EUR_DTLS
DECLARE
l_num_rows Number := 0;
I BINARY_INTEGER;
Begin
I := EUR_DATA_PKG.L_EUR_DTL.FIRST;
WHILE I IS NOT NULL LOOP
Select
count(1)
Into
l_num_rows
From
eur_dtls
Where
eur_dtls.shift_id = 1 And
EUR_DTLS.EUR_ID = eur_data_pkg.l_eur_dtl(I).p_eur_id And
EUR_DTLS.EUR_date = eur_data_pkg.l_eur_dtl(I).p_eur_date And
eur_dtls.shift_id = eur_data_pkg.l_eur_dtl(I).p_shift_id And
eur_dtls.equipment = eur_data_pkg.l_eur_dtl(I).p_equipment And
eur_dtls.work_hrs_from = eur_data_pkg.l_eur_dtl(I).p_work_hrs_from And
eur_dtls.work_hrs_to = eur_data_pkg.l_eur_dtl(I).p_work_hrs_to And
eur_date Between shift_effect_from And shift_effect_to
Group By
equipment, nvl(round((shift_end_time-shift_start_time)*24,2),0)
Having
nvl(round((shift_end_time-shift_start_time)*24,2),0) < sum(nvl(round((work_hrs_to-work_hrs_from)*24,2),0));
If l_num_rows > 0 Then
eur_data_pkg.l_eur_dtl.delete;
Raise_application_error(-20333,'Work Hrs For The Equipment Exceeds Actual Shift Hrs');
End If;
I := EUR_DATA_PKG.L_EUR_DTL.NEXT(I);
End Loop;
Exception
When Others Then
eur_data_pkg.l_eur_dtl := eur_data_pkg.l_eur_dtl_set_null;
Raise;
End;


INSERT INTO EUR_DTLS (EUR_ID, EUR_DATE, SHIFT_ID, WORK_HRS_FROM, WORK_HRS_TO, EQUIPMENT) VALUES (1,'01-APR-2008',1,
TO_DATE('01-APR-2008 07:00' ,'DD-MON-RRRR HH24:MI'), TO_DATE('01-APR-2008 14:30' ,'DD-MON-RRRR HH24:MI'),
'RTGC1');
ORA-01403: no data found
ORA-06512: at "TEUSNEW.EUR_TRG_CHK_SHIFT_HRS_STMT", line 47
ORA-04088: error during execution of trigger 'TEUSNEW.EUR_TRG_CHK_SHIFT_HRS_STMT'

Why is the trigger showing the ORA-04088 error whenever I am inserting values into the EUR_DTLS table.

Sir,

Sorry, I forgot to include the third table.

Please help in clearing the error during execution of the trigger - [ORA-04088] and [no data found]. I created this
trigger for avoiding the mutating table error.


CREATE TABLE EUR_SHIFT_MSTR (
SHIFT_ID NUMBER (2) NOT NULL,
SHIFT_START_TIME DATE,
SHIFT_END_TIME DATE,
SHIFT_EFFECT_FROM DATE NOT NULL,
SHIFT_EFFECT_TO DATE,
SHIFT_EXTEND_DAYS NUMBER,
ACT_INACT_IND CHAR (1),
CONSTRAINT PK_EUR_SHIFT_MSTR
PRIMARY KEY ( SHIFT_ID, SHIFT_EFFECT_FROM ) ) ;


INSERT INTO EUR_MSTR ( EUR_ID, EUR_DATE ) VALUES (
1, TO_Date( '04/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;


CREATE TABLE EUR_SHIFT_MSTR (
SHIFT_ID NUMBER (2) NOT NULL,
SHIFT_START_TIME DATE,
SHIFT_END_TIME DATE,
SHIFT_EFFECT_FROM DATE NOT NULL,
SHIFT_EFFECT_TO DATE,
SHIFT_EXTEND_DAYS NUMBER,
ACT_INACT_IND CHAR (1),
CONSTRAINT PK_EUR_SHIFT_MSTR
PRIMARY KEY ( SHIFT_ID, SHIFT_EFFECT_FROM ) ) ;


INSERT INTO EUR_SHIFT_MSTR ( SHIFT_ID, SHIFT_START_TIME, SHIFT_END_TIME, SHIFT_EFFECT_FROM,
SHIFT_EFFECT_TO, SHIFT_EXTEND_DAYS, ACT_INACT_IND ) VALUES (
1, TO_Date( '01/01/2008 07:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '01/01/2008 02:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '01/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '12/31/2030 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 0, 'Y');
INSERT INTO EUR_SHIFT_MSTR ( SHIFT_ID, SHIFT_START_TIME, SHIFT_END_TIME, SHIFT_EFFECT_FROM,
SHIFT_EFFECT_TO, SHIFT_EXTEND_DAYS, ACT_INACT_IND ) VALUES (
2, TO_Date( '01/01/2008 02:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '01/01/2008 09:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '01/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '12/31/2030 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 0, 'Y');
INSERT INTO EUR_SHIFT_MSTR ( SHIFT_ID, SHIFT_START_TIME, SHIFT_END_TIME, SHIFT_EFFECT_FROM,
SHIFT_EFFECT_TO, SHIFT_EXTEND_DAYS, ACT_INACT_IND ) VALUES (
3, TO_Date( '01/01/2008 09:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '01/02/2008 05:30:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '01/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '12/31/2030 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1, 'Y');
COMMIT;





CREATE TABLE EUR_DTLS (
EUR_ID NUMBER (6) NOT NULL,
EUR_DATE DATE NOT NULL,
SHIFT_ID NUMBER NOT NULL,
WORK_HRS_FROM DATE,
WORK_HRS_TO DATE,
BREAKDOWNS_FROM DATE,
BREAKDOWNS_TO DATE,
PROD_MOVES NUMBER (4),
UNPROD_MOVES NUMBER (4),
CONSOLIDATIONS NUMBER (4),
CREATED_BY VARCHAR2 (10),
CREATED_DT DATE,
MODIFIED_BY VARCHAR2 (10),
MODIFIED_DT DATE,
ATHRZ_BY VARCHAR2 (10),
ATHRZ_DATE DATE,
EQUIPMENT VARCHAR2 (5) NOT NULL ) ;

ALTER TABLE EUR_DTLS ADD CONSTRAINT FK_EUR_DTLS_ID
FOREIGN KEY (EUR_ID)
REFERENCES TEUSNEW.EUR_MSTR (EUR_ID) ;



CREATE OR REPLACE TRIGGER eur_trg_chk_shift_hrs before insert or update on eur_dtls for each row
begin
eur_data_pkg.l_counter := eur_data_pkg.l_counter + 1;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_eur_id := :new.eur_id;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_eur_date := :new.eur_date;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_shift_id := :new.shift_id;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_equipment := :new.equipment;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_work_hrs_from := :new.work_hrs_from;
eur_data_pkg.l_eur_dtl(eur_data_pkg.l_counter).p_work_hrs_to := :new.work_hrs_to;
exception
when others then
dbms_output.put_line('error in before insert for each row ');
raise;
end;
/

CREATE OR REPLACE TRIGGER EUR_TRG_CHK_SHIFT_HRS_STMT AFTER INSERT OR UPDATE ON EUR_DTLS
DECLARE
l_num_rows Number := 0;
I BINARY_INTEGER;
Begin
I := EUR_DATA_PKG.L_EUR_DTL.FIRST;
dbms_output.put_line('total elements in collection '||EUR_DATA_PKG.L_EUR_DTL.count||' and the first element is :'|| i);
WHILE I IS NOT NULL LOOP
dbms_output.put_line('elements in collection '|| i);
Select
count(1)
Into
l_num_rows
From
EUR_SHIFT_MSTR , eur_dtls
Where
eur_shift_mstr.shift_id = 1 And
EUR_DTLS.EUR_ID = eur_data_pkg.l_eur_dtl(I).p_eur_id And
EUR_DTLS.EUR_date = eur_data_pkg.l_eur_dtl(I).p_eur_date And
eur_dtls.shift_id = eur_data_pkg.l_eur_dtl(I).p_shift_id And
eur_dtls.equipment = eur_data_pkg.l_eur_dtl(I).p_equipment And
eur_dtls.work_hrs_from = eur_data_pkg.l_eur_dtl(I).p_work_hrs_from And
eur_dtls.work_hrs_to = eur_data_pkg.l_eur_dtl(I).p_work_hrs_to
Group By
equipment, nvl(round((shift_end_time-shift_start_time)*24,2),0)
Having
nvl(round((shift_end_time-shift_start_time)*24,2),0) < sum(nvl(round((work_hrs_to-work_hrs_from)*24,2),0));

DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_eur_id);
DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_eur_date);
DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_shift_id);
DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_equipment);
DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_work_hrs_from);
DBMS_OUTPUT.PUT_LINE(i||' '||eur_data_pkg.l_eur_dtl(I).p_work_hrs_to);
If l_num_rows > 0 Then
eur_data_pkg.l_eur_dtl.delete;
Raise_application_error(-20333,'Work Hrs For The Equipment Exceeds Actual Shift Hrs');
End If;
I := EUR_DATA_PKG.L_EUR_DTL.NEXT(I);
End Loop;
-- eur_data_pkg.l_eur_dtl.delete;
-- eur_data_pkg.l_eur_dtl := eur_data_pkg.l_eur_dtl_set_null;
Exception
When Others Then
dbms_output.put_line('Following error raised :' ||sqlerrm||' '||' on collection no :'||i);
eur_data_pkg.l_eur_dtl.delete;
eur_data_pkg.l_eur_dtl := eur_data_pkg.l_eur_dtl_set_null;
Raise;
End;
/


INSERT INTO EUR_DTLS (EUR_ID, EUR_DATE, SHIFT_ID, WORK_HRS_FROM, WORK_HRS_TO, EQUIPMENT) VALUES
(1,'01-APR-2008',1,
TO_DATE('01-APR-2008 07:00' ,'DD-MON-RRRR HH24:MI'), TO_DATE('01-APR-2008 14:30' ,'DD-MON-RRRR
HH24:MI'),
'RTGC1');
ORA-01403: no data found
ORA-06512: at "TEUSNEW.EUR_TRG_CHK_SHIFT_HRS_STMT", line 47
ORA-04088: error during execution of trigger 'TEUSNEW.EUR_TRG_CHK_SHIFT_HRS_STMT'


Why is the trigger showing the ORA-04088 and [no data found ] error whenever I am inserting values into the EUR_DTLS
table.

Please help.

Thanking you

Hi Tom,
Can we give sequence like thiswhile creating a table
if not please give me code to create sequence

CREATE TABLE employee ( emp_id NUMBER default nextval('seq_emp_id'), name VARCHAR2(20));


Thanks in advance

The link:
http://asktom.oracle.com/~tkyte/Mutate/index.html
does NOT work

how i put into a column of a table the difference between 2 dates(like numbers of days) excepting weekends using a trigger?

Hi,
I want to update value on column in table after insert or update ,
I have written a trigger on table EMPL_VAC below ,to avoid mutating

kindly follow :


CREATE TABLE EMPL_VAC
(
EMP_ID NUMBER,
LENGTH NUMBER,
NET_VAC NUMBER
)

CREATE OR REPLACE PACKAGE STORE_HIS IS
TYPE T_HIS IS RECORD ( EMP_ID NUMBER, LENGTH NUMBER, NET_VAC NUMBER, HIS_ROWID ROWID);
TYPE T_HIS_ARR IS TABLE OF T_HIS INDEX BY PLS_INTEGER;
HISUPD T_HIS_ARR;
TRIGINSERTS BOOLEAN := FALSE;
END;
/

CREATE OR REPLACE TRIGGER T_HIS_AR AFTER INSERT ON EMPL_VAC FOR EACH ROW
DECLARE
NEXTEL PLS_INTEGER;
BEGIN
IF NOT STORE_HIS.TRIGINSERTS THEN
NEXTEL := STORE_HIS.HISUPD.COUNT+1;
STORE_HIS.HISUPD(NEXTEL).EMP_ID := :NEW.EMP_ID;
STORE_HIS.HISUPD(NEXTEL).LENGTH := :NEW.LENGTH;
STORE_HIS.HISUPD(NEXTEL).NET_VAC := :NEW.NET_VAC;
STORE_HIS.HISUPD(NEXTEL).HIS_ROWID := :NEW.ROWID;
END IF;
END;
/


CREATE OR REPLACE TRIGGER T_HIS_AS AFTER INSERT ON EMPL_VAC
BEGIN
FOR I IN NVL(STORE_HIS.HISUPD.FIRST,1)..NVL(STORE_HIS.HISUPD.LAST,0) LOOP
FOR REC IN (SELECT EMP_ID ,LENGTH, NET_VAC FROM EMPL_VAC
WHERE EMP_ID = STORE_HIS.HISUPD(I).EMP_ID
AND LENGTH = STORE_HIS.HISUPD(I).LENGTH
) LOOP

UPDATE EMPL_VAC
SET NET_VAC = NVL(NET_VAC,0) + 10
WHERE EMP_ID = REC.EMP_ID
AND LENGTH = REC.LENGTH ;

END LOOP;
END LOOP;
END;
/

CREATE OR REPLACE TRIGGER T_HIS_BS BEFORE INSERT ON EMPL_VAC
BEGIN
IF NOT STORE_HIS.TRIGINSERTS THEN
STORE_HIS.HISUPD.DELETE;
END IF;
END;
/

After insert record :
Insert into EMPL_VAC
(EMP_ID, LENGTH)
Values
(1, 2);
COMMIT;

select * from EMPL_VAC;
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 2 12
It's ok




But when add update on trigger :

CREATE OR REPLACE TRIGGER T_HIS_AR AFTER INSERT OR UPDATE ON EMPL_VAC FOR EACH ROW

CREATE OR REPLACE TRIGGER T_HIS_AS AFTER INSERT OR UPDATE ON EMPL_VAC

CREATE OR REPLACE TRIGGER T_HIS_BS BEFORE INSERT OR UPDATE ON EMPL_VAC

When I insert or update give me errors:
ORA-00036
ORA-06512
ORA-04088





I know I can write trigger as below ,
but i have too to used after Insert or update,
because i have another calculation to NET_VAC with another function,
But I short the calculation to simple example ,
NET_VAC + 10

CREATE OR REPLACE TRIGGER T_NET_VAC BEFORE INSERT OR UPDATE
ON HURS.HRS_SAL_PERIOD_HSTY
FOR EACH ROW
BEGIN

:NEW.NET_VAC := :NEW.NET_VAC + 10;

END T_NET_VAC;
/




How can update on column NET_VAC after insert new record or update column NET_VAC,

Thanks in advance

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9579487119866

Hi Tom,
In Oracle 10.2.0.1.0, I have 3 tables linked with foreign keys and a statement level trigger on the middle one issuing a MUTATING TABLE error:

------------------------------
CREATE TABLE radu_1(
       radu_1_id NUMBER PRIMARY KEY
       );
       
CREATE TABLE radu_2(
       radu_2_id NUMBER PRIMARY KEY,
       radu_1_id NUMBER REFERENCES radu_1(radu_1_id) ON DELETE SET NULL
       );
       
CREATE TABLE radu_3(
       radu_3_ID NUMBER PRIMARY KEY REFERENCES radu_2(radu_2_ID) ON DELETE CASCADE,
       dummy_field NUMBER
       );

CREATE OR REPLACE TRIGGER radu_2_upd_trg
  AFTER UPDATE on radu_2
begin
  Update radu_3
   Set dummy_field = -1
   WHERE radu_3_id = 1;       
end ;       
/

SQL> DELETE FROM radu_1;

0 rows deleted.

SQL> insert INTO radu_1 VALUES (111);

1 row created.

SQL> DELETE FROM radu_1;
DELETE FROM radu_1
            *
ERROR at line 1:
ORA-04091: table RADU.RADU_3 is mutating, trigger/function may not see it
ORA-06512: at "RADU.RADU_2_UPD_TRG", line 3
ORA-04088: error during execution of trigger 'RADU.RADU_2_UPD_TRG'
-----------------------------------

1) I would believe the error is caused by the tables being linked with cascading foreign keys, but why would the delete from radu_1 propagate all the way down to radu_3, since radu_1 and radu_2 are linked with ON DELETE SET NULL (and not cascade)?
2) Why does the trigger cause the error, being a STATEMENT and not a ROW level trigger? Shouldn’t the tables be in a stable state when it is called?

Many thanks.

Regarding my above question: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9579487119866#4226050800346160048

Thank you for the clarification.
What I was trying to accomplish was an “on update set default” for a certain field (that dummy_field from the example).
The example is not the full real version (but the problem is the same), I simplified it for ease of reading.

I don’t like triggers neither, but it’s my company’s policy to implement logic like this in the DB only. The application calls that DELETE and I have to do the rest in the DB.

I guess I could do it by either moving the trigger to RADU_1 or remove the ON DELETE CASCADE and replace it with triggers code.
I think I’ll go for the first.

Thank you.

I understand the problem of mutating table but I have the following validation to use:

There is a table of parameters. A parameter has a range of time where is valid. For example, to use a financial account only in a range of dates.

When the user inserts or updates a parameter, I should check that there is only one parameter active for that range of time. For example, it would be an error if two financial accounts are active for a period of time even if the begin dates are different.

What do you suggest to implement this validation?

I was trying to use a trigger but I have the mutation table error when I do a select before inserting or updating a row.

Thanks,

Good

Hi Tom,

There is one BEFORE UPDATE trigger on a column slevel of table (called t1).
If :new.slevel is not null, it inserts record in t2 table.
If :new.slevel is null, then this trigger performs SELECT on t1 to find out its parent record's value for c1. After that, it inserts the parent's column slevel value to another table t2.

While performing an update on t1, trigger throws 'mutating table error'.

create table t1(id number(2), slevel number(2));
create table t2(id number(2), plevel number(2));

the trigger code is as follows:

CREATE OR REPLACE TRIGGER TRG
before UPDATE
ON t1 FOR EACH ROW
WHEN (
(NVL (NEW.slevel, 0) <> NVL (OLD.slevel, 0))
)

DECLARE
n_slevel NUMBER;

BEGIN

IF (:NEW.slevel IS NULL)
THEN
select slevel into n_slevel from t1 where ID= select fn(:NEW.ID) from dual) ; -- here fn finds out the parent of an id.
:new.slevel:=n_slevel;

END IF;

BEGIN

UPDATE t2
SET plevel = :NEW.slevel
WHERE id = :new.id;

EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (-20010, SQLERRM);
END;

END TRG;
/

Please suggest how to resolve the error. Also, if i use pragma autonomous_transactions, how good it will be?

Thanks in advance.

"actually -- triggers to enforce any CROSS ROW integrity constraints are fundementally flawed, period. In a multi-user environment - they are absolutely 100% flawed.
"

You wrote this 12 years ago in this thread. I don't really think that you've changed your mind. But I'd like to know what is the best solution to enforce a cross row rule.

Let's say we have a table (simplyfied real world example)
create table t1 (
id number primary key,
validfrom date not null,
validto date not null,
value number not null);
create sequence t1_seq;

and a rule: for any given instant there can be only one (or none) valid row.

One way to force this rule is to create a procedure and force use of it by not granting direct insert /update privileges.

This one should do it:

CREATE OR REPLACE PROCEDURE p1(a_validfrom IN DATE,
a_validto IN DATE,
a_value IN NUMBER) IS
l_id_conflict NUMBER;
BEGIN
LOCK TABLE t1 IN EXCLUSIVE MODE;
SELECT id
INTO l_id_conflict
FROM t1
WHERE a_validfrom <= t1.validto
AND a_validto >= t1.validfrom
and rownum = 1; --don't care if there is more than one conflict
ROLLBACK;
raise_application_error(-20000, 'Can''t insert. Conflicts with id ' || l_id_conflict);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO t1 VALUES (t1_seq.nextval, a_validfrom, a_validto, a_value);
COMMIT;
END;

Is it the best approach?
What to do if e.g. company policies don't allow me to prohibit direct DML? Is the "famous" 3 trigger "'round the mutating table" trigger solution the only way to go (even if triggers are "fundamentally flawed")?

same as above, only formatted better.

"actually -- triggers to enforce any CROSS ROW integrity constraints are fundementally flawed, period. In a multi-user environment - they are absolutely 100% flawed.
"

You wrote this 12 years ago in this thread. I don't really think that you've changed your mind. But I'd like to know what is the best solution to enforce a cross row rule.

Let's say we have a table (simplyfied real world example)

create table t1 (
  id number primary key, 
  validfrom date not null,
  validto date not null,
  value number not null);
create sequence t1_seq;

and a rule: for any given instant there can be only one (or none) valid row.

One way to force this rule is to create a procedure and force use of it by not granting direct insert /update privileges.

This one should do it:

CREATE OR REPLACE PROCEDURE p1(a_validfrom IN DATE,
                               a_validto   IN DATE,
                               a_value     IN NUMBER) IS
    l_id_conflict NUMBER;
BEGIN
    LOCK TABLE t1 IN EXCLUSIVE MODE;
    SELECT id
      INTO l_id_conflict
      FROM t1
     WHERE a_validfrom <= t1.validto
       AND a_validto >= t1.validfrom
       and rownum = 1;  --don't care if there is more than one conflict
    ROLLBACK;
    raise_application_error(-20000, 'Can''t insert. Conflicts with id ' || l_id_conflict);
EXCEPTION
    WHEN no_data_found THEN
        INSERT INTO t1 VALUES (t1_seq.nextval, a_validfrom, a_validto, a_value);
        COMMIT;
END;

Is it the best approach?
What to do if e.g. company policies don't allow me to prohibit direct DML? Is the "famous" 3 trigger "'round the mutating table" trigger solution the only way to go (even if triggers are "fundamentally flawed")?

Hi Tom,

The below link you mentioned is not working, please fix the link:

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

Thanks

The link in the response is outdated and leads into nirwana.