Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Arnaud.

Asked: May 05, 2003 - 6:49 am UTC

Answered by: Tom Kyte - Last updated: September 21, 2018 - 12:36 am UTC

Category: SQL*Plus - Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

hello,

i've got a table MRC and a trigger on it (AFTER INSERT)

thus, after an insert in the table MRC, this trigger has to determine if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement of MRC

but i got a mutating table error
i understand the problem but how can i get over ??

thanks
Arnaud

and we said...

My personal opinion -- when I hit a mutating table error, I've got a serious fatal flaw in my logic.

Have you considered the multi-user implications in your logic? Two people inserting at the same time (about the same time). What happens then??? Neither will see eachothers work, neither will block -- both will think "ah hah, I am first"...

anyway, you can do too much work in triggers, this may well be that time -- there is nothing wrong with doing things in a more straightforward fashion (eg: using a stored procedure to implement your transaction)

but if you persist, you can use the technique - search the Presentations folder

https://asktom.oracle.com/pls/apex/asktom.search?file=MutatingTable.html#presentation-downloads-reg

to avoid the mutating table constraint -- but I would avoid the situation that gets me there in the first place. The logic is a whole lot more understandable that way (and maintainable and testable and everything)

and you rated our response

  (72 ratings)

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

Reviews

Ora-4091

May 05, 2003 - 5:45 pm UTC

Reviewer: A reader

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.

Tom Kyte

Followup  

May 05, 2003 - 8:31 pm UTC

they relaxed some of the constraining rules between 8.0 and 8.1 -- things are in general upwards (develop in 8.0 and goto 8.1) compatible but not backwards.

Why I can't get the 4091 error when insert?

January 05, 2004 - 3:39 am UTC

Reviewer: Li ys from CHINA

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.

Tom Kyte

Followup  

January 05, 2004 - 7:32 am UTC

thanks -- fixed the link.


Anyway, the mutating table issue won't crop up during a single row insert.

if you "insert into t (...) values (....)" the server recognizes that "hey, there is just one row -- it is 'safe', the table isn't being subjected to a multi-row modification. the trigger won't see an unstable set of rows"

if you turn that insert into t ( ... ) values ( ... ) into:

insert into t ( .... ) select .... from dual;

you'll get the mutating table issue -- for even though you are just inserting a single row, the database no longer knows that.

I get the new information:

January 05, 2004 - 4:13 am UTC

Reviewer: Li ys from CHINA

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?

backup data using trigger

January 26, 2004 - 3:17 pm UTC

Reviewer: Sean from NJ, USA

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  

Tom Kyte

Followup  

January 26, 2004 - 4:03 pm UTC

why would a delete on T1 query T2????

you would have had a row trigger on T2 that captured the T2 records and a row trigger on T1 to capture them.

Think about it -- when you goto delete from T1 there had better not be anything LEFT in t2 that points to it anymore!!!! If there was, you just found a bug (t2 would be devoid of child records by the time you get to t1 to delete, your insert as select from t2 could not possibly find any matching records!!!!!)

Back data before deleting

January 26, 2004 - 6:30 pm UTC

Reviewer: Sean from NJ, USA

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 
 

Tom Kyte

Followup  

January 26, 2004 - 7:01 pm UTC

insert into t1_arch( c1, c2 ) values ( :old.c1, :old.c2 );


you don't want to "select it", you already have it.

mutate table

February 06, 2004 - 1:31 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

February 07, 2004 - 1:38 pm UTC

BEFORE insert on t1
for each row
begin
:new.c2 := 'whatever you want to set it to';
end;


you don't want to UPDATE the row, you just want to set values. It is very natural just to use the assignment operation -- no? (forget sqlserver's way of doing everything! it is much easier this way...)

mutate table

February 06, 2004 - 1:36 pm UTC

Reviewer: Sean from NJ, USA

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

But why mutate at all..

May 17, 2005 - 9:12 am UTC

Reviewer: Arun from Hyderabad, India

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.

Tom Kyte

Followup  

May 17, 2005 - 9:42 am UTC

the data "as is" is "UNSTABLE", mutating, not consistent, halfway through being updated

update t set x = x+1;

on 10 rows -- when the trigger fires for the 5th time, half of the data is updated and half not.


Tell you what -- you give me an example where you think it would be "safe" to read the table and what the trigger is to accomplish (what business rule it is doing) and I'll tell you why it is not safe and show how the business rule would be violated if you could read the mutating table...

seriously -- it is there for our protection, honest.

The example you wanted....

May 17, 2005 - 10:48 am UTC

Reviewer: Arun from Hyderabad, India

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.





Tom Kyte

Followup  

May 17, 2005 - 2:04 pm UTC

<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>

 

so who protects us from ...

May 17, 2005 - 2:59 pm UTC

Reviewer: A reader

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 Kyte

Followup  

May 17, 2005 - 3:13 pm UTC

what is your point? apples to flying toaster oven comparision here.

A trigger on a TABLE, is not akin to a trigger on a VIEW. The trigger on the view is very much like the procedural code above.

And it would be a really bad idea, don't you think.


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

non-deterministic=bad

May 17, 2005 - 3:05 pm UTC

Reviewer: AR

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.

point is

May 17, 2005 - 3:25 pm UTC

Reviewer: A reader

"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


Tom Kyte

Followup  

May 17, 2005 - 3:35 pm UTC

because the implementation would just be plain *wrong*

why would we even consider it? to let people corrupt the integrity of their data easier? There are thousands of ways to corrupt the integrity of your data via faulty logic, this exception is thankfully there so people can come and say

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*"

that is why, data integrity is something I have a keen interest in personally. I sometimes wish triggers did not exist, they are used to break data as often as protect it (due to people not thinking about multi-user situations and such)

I do not accept Aruns proposal since in the set oriented thing that is an RDBMS- that the rows physical order in a table would affect my answer, my outcome is nothing I'm willing to accept.

That is my point, I'm much rather get them to try and figure out how to make the instead of trigger fail (which is probably not feasible) then even consider the alternative.

I understand you, but

May 17, 2005 - 3:49 pm UTC

Reviewer: Matthias Rogel from Kaiserslautern, Germany

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




Tom Kyte

Followup  

May 17, 2005 - 4:22 pm UTC

why doesn't java have pointers.

why does ada blow up when you subscript out of your array

..............................


it can do what it can do.

Thank you.

May 18, 2005 - 12:22 am UTC

Reviewer: Arun from Hyderabad, India


database triggers vrsus form triggers

June 28, 2005 - 5:35 pm UTC

Reviewer: mohannad from Jordan

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  

Tom Kyte

Followup  

June 28, 2005 - 5:42 pm UTC

there is a huge difference between

a) actually inserting into database table and
b) creating a record in a client application on a screen, but not putting it into a database table.


Now -- would you care to think about the max(empno) trick with more than a single user on your system??

In a multi-user situation, you won't see my insert -- I won't see yours, we generate the same number.


the forms "trigger" is happening before the client application says "do this insert", the database trigger happens AS the insert is happening


regardless, the logic has to change -- you cannot , you CANNOT use select max(), it just doesn't work.

Time for a sequence.


mutating table,

June 28, 2005 - 5:49 pm UTC

Reviewer: A reader

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.

data basetrigger versusform trigger

June 28, 2005 - 6:10 pm UTC

Reviewer: mohannad from Jordan

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.

Tom Kyte

Followup  

June 28, 2005 - 8:19 pm UTC

they are apples and toaster ovens, you cannot *even begin to compare them*

The forms client/server trigger is "hey, run this code AND THEN actually insert the row"

the other is "hey, as the insert is running, go ahead and run this code".

totally 100% not even a tiny bit comparable.

databasetriggers vrsus form triggers

June 29, 2005 - 8:42 am UTC

Reviewer: mohannad from jordan

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.

database trigger or form trigger

June 29, 2005 - 8:47 am UTC

Reviewer: mohannad from Jordan

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.




Tom Kyte

Followup  

June 29, 2005 - 9:24 am UTC

a database trigger fires DURING THE PROCESSING of the insert statement. the client issues an

insert into t select * from t;


a database trigger might fire right before the statement touched the first row (create trigger BEFORE INSERT)

then for each row inserted:
a database trigger might fire for that row before the insert physically takes
place (create trigger BEFORE INSERT FOR EACH ROW)

a database trigger might fire for that row right after the insert physically
takes place (create trigger AFTER INSERT FOR EACH ROW)


then at the end, as the insert is finishing up, an AFTER INSERT trigger might fire.


database triggers fire as part of the processing of an insert.

A forms trigger is a piece of code that runs before the insert is even sent to the database or runs after the insert returns from the database. Totally separately activities, totally unrelated activities.


mutaing table error

June 29, 2005 - 9:47 am UTC

Reviewer: mohanad from jordan

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.

Tom Kyte

Followup  

June 29, 2005 - 10:34 am UTC

because their is a huge difference between:

a) query a table
b) perform an insert into the table

(that is forms) and


a) perform an insert into the table that also tried to READ the same table.


that integrity constraint you have would not be anything to do in a trigger either.



mutating table errorq

June 29, 2005 - 1:22 pm UTC

Reviewer: mohanad from jordan

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.

Tom Kyte

Followup  

June 29, 2005 - 1:33 pm UTC

I think it is the other way around :) you are not understanding me...


you do understand the very difference between:

a) running a query AND THEN
b) running an insert

as opposed to

a) running an insert that happens to fire a trigger as part of its processing that happens to run a query


........... from forms, there is

a) a query
b) and later and insert


when you use a database trigger, there is just an insert that happens to fire some more code while it is running and that code tries to read the table that is in the middle of being modified by the insert and gets the mutating table constraint.

mutating table error

June 29, 2005 - 1:46 pm UTC

Reviewer: mohanad from jordan

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 .

Tom Kyte

Followup  

June 29, 2005 - 3:06 pm UTC

in order to get the mutating table, you wrote a trigger. in that trigger, called as PART OF THE PROCESSING of an insert statement, you are reading the table you are modifying.

Hence, the table is "mutating", or "changing" WHILST the trigger is being fired.


MUTATING TABLE

June 29, 2005 - 3:48 pm UTC

Reviewer: MOHANNAD from JORDAN

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.

Tom Kyte

Followup  

June 29, 2005 - 8:04 pm UTC

your "pre-insert" trigger you said you were running.

squence number

June 29, 2005 - 5:16 pm UTC

Reviewer: mohanad from jordan

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


Tom Kyte

Followup  

June 29, 2005 - 8:21 pm UTC

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

sorry, this is a "bad idea".

to mohanad: what is a query ?

June 30, 2005 - 6:15 am UTC

Reviewer: A reader

hallo monahad,

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

to monahad: how can i set the sequence to one for the first invoice for each day.

June 30, 2005 - 6:18 am UTC

Reviewer: A reader

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

Tom Kyte

Followup  

June 30, 2005 - 9:42 am UTC

ugh, i feel like deleting this one.

it is a horrible idea, both the idea of dropping and creating a sequence at midnight as well as resetting it each day (concept wise)

jobs -- might run at midnight, might not run till 3am, might not run.
jobs -- might drop the sequence and then fail. no sequence, no work.
another way to "recompile your database every day" with the invalidations.


the concept of reseting the sequence is fundementally flawed since sequences are not gap free anyway. You won't get 1, 2, 3, 4, 5, .... from them in any event.

sequence number

June 30, 2005 - 8:57 am UTC

Reviewer: mohannad

>>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


Tom Kyte

Followup  

June 30, 2005 - 9:50 am UTC

trying to pretend that sequences are something gap free and should be reset every night at midnight.

encoding the date and a number starting from one each day -- that is a bad idea. the DATE is an attribute, the sequence just a surrogate key.

The table should be nothing more than:

...
( id number primary key, -- into which you pop the seq.nextval
dt date,
....


and if you need this 1, 2, 3 thing you would use analytics in your reports


select id, dt, row_number() over (partition by dt order by id) rn, ....



sequence number

June 30, 2005 - 9:31 am UTC

Reviewer: mohannad from jordan

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

Tom Kyte

Followup  

June 30, 2005 - 9:52 am UTC

see above. JUST USE A SEQUENCE, period.

sequence number

June 30, 2005 - 10:08 am UTC

Reviewer: Mohannad

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.

Tom Kyte

Followup  

June 30, 2005 - 10:22 am UTC

and I showed you how to get 1, 2, 3....

you cannot get it from a sequence number, they are NOT gap free and "resetting" it each night would be "a really bad idea"

I showed you how to synthesize this information upon retrieval for a report using row_number().

squence number

June 30, 2005 - 5:46 pm UTC

Reviewer: mohannad

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?

Tom Kyte

Followup  

June 30, 2005 - 5:59 pm UTC

sequences are 100% multi-user "safe"



i have read about how we can pypass the mtating table error

June 30, 2005 - 7:41 pm UTC

Reviewer: mohannad

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

Tom Kyte

Followup  

June 30, 2005 - 9:07 pm UTC

what "methods"?!?

I just see a relatively normal "i'm going to select s.nextval from dual to get a sequence"

where is a mutating table constraint coming into play? You didn't mention any database triggers at all.

statment trigger

June 30, 2005 - 8:33 pm UTC

Reviewer: mohannad

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

Tom Kyte

Followup  

June 30, 2005 - 9:16 pm UTC

first of all, that is a AFTER statement trigger, they never raise mutating tables.

second, forms inserts slow by slow, row by row, a single row at a time. use an insert into as select and see how many times this fires.

Second, have two users each insert into this table from separate session.

Have them each insert 38000 as the salary.

Let them commit.

Then tell us what happens....

statment trigger

July 01, 2005 - 6:06 am UTC

Reviewer: mohannad

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

Tom Kyte

Followup  

July 01, 2005 - 10:14 am UTC

so, you really want to write your code so that it works with a single tool??? do you think that is wise? You are counting on the way forms processes data -- is that a "good idea"?


we are not talking about two uses accessing the same record. Look at your "rule":


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;

You seem to be trying to enforce the "rule" that the sum of all salaries in the emp table, for ALL ROWS, may not exceed 38,000


Now, in session 1

insert into emp ( empno, sal ) values ( 1, 38000 ); (trigger fires, sees this one row, all is well in the world)


In session 2

insert into emp ( empno, sal ) values ( 2, 38000 ); (trigger again fires, sees ONLY this row, the other row is not committed, it is not visible, and we are not sqlserver so no blocking occurrs. All is well in the world.


Commit;

In session 1

commit;


select sum(sal) from emp. Hmmm.


Think about multi-user stuff -- it is important.

sequence number

July 01, 2005 - 4:58 pm UTC

Reviewer: MOHANNAD

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

Tom Kyte

Followup  

July 01, 2005 - 6:06 pm UTC

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)

sequence number

July 01, 2005 - 6:52 pm UTC

Reviewer: mohannad

what is table blocks?

sequence number

July 01, 2005 - 6:53 pm UTC

Reviewer: mohannad

sorry i mean what is table locks???

Tom Kyte

Followup  

July 01, 2005 - 7:24 pm UTC

when you lock an entire table, lock table command for example

record blocks

July 01, 2005 - 8:17 pm UTC

Reviewer: mohannad

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)


Tom Kyte

Followup  

July 01, 2005 - 8:18 pm UTC

have you read the concepts guide at all? chapter on concurrency control and multi-versioning?

if not, mandatory reading, read the entire thing, the whole book

free, on otn.oracle.com

record lock

July 02, 2005 - 11:24 am UTC

Reviewer: mohannad

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.



Tom Kyte

Followup  

July 02, 2005 - 1:28 pm UTC

first case:

they cannot commit at the same time

open two windows and try. the first one to update empno=90 will have an exclusive lock on that row, the other guy doing the update of the same row will block and wait.

when the first guy commits, the second guy will unlock, do their update, and then commit.

last guy to commit wins.


second case:

if there is a primary key on empno, the first guy to insert will insert, the second guy will block and wait for the first guy to commit or rollback. if they commit, the second guy will get ora-00001, if they rollback the second guy will succeed.



I'm going to give you the most important advice of your budding database career -- read the CONCEPTS MANUAL

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/toc.htm <code>

from cover to cover, especially the concurrency control and locking sections


if you like my style of writing, I've written this stuff as well in Expert One on One Oracle in great detail.

Mutating table

July 25, 2005 - 12:29 pm UTC

Reviewer: Freddy G Molina from Pomona, CA USA

As usual tom's answers are superb. Always helpful

mutants battling with functions

February 07, 2006 - 9:16 am UTC

Reviewer: Stefan from Switzerland

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


Tom Kyte

Followup  

February 08, 2006 - 1:06 am UTC

that is very dangerous.

Lets think about when is the function maintained.

During an insert into the table.
During an update of the update.
During a delete

Ok, but what if........

insert into table (EMPTY_PHOTO) <<<=== index maintained
dbms_lob.write( the blob ); <<<=== index not maintained

result - length of 0 or NULL is registered in the index.


for you see, lobs are normally put into the database during the insert, they are written later - right after the insert.


You will need to maintain a scalar number field of your own instead of using the function in this case. definitely.

Perhaps you should encapsulate the insert into this table in a stored procedure so that it can

a) insert the row
b) load the photo
c) set the scalar field



February 08, 2006 - 5:08 am UTC

Reviewer: Stefan from Switzerland

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

Stefan

May 29, 2006 - 6:42 am UTC

Reviewer: Alessandro Nazzani from Italy

<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 

Tom Kyte

Followup  

May 30, 2006 - 8:16 am UTC

create trigger persons_au after update of id_dept on persons for each row


That is an AFTER FOR EACH ROW trigger.

That is not an AFTER trigger.


Only row triggers are subject to mutating table constraints, STATEMENT triggers (before/after) are not.

May 30, 2006 - 9:43 am UTC

Reviewer: Alessandro Nazzani from Italy

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



Tom Kyte

Followup  

May 30, 2006 - 10:09 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1286 <code>

<quote>
A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
</quote>


Can this be done?

April 02, 2007 - 4:05 pm UTC

Reviewer: Hash

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
Tom Kyte

Followup  

April 03, 2007 - 9:11 pm UTC

umm, obvious question (from my perspective)

drop table t1;

and have the code insert into t2. or create a view t1 as select id, name, sal from t2; after dropping t1.

Mutating error

August 08, 2007 - 5:26 pm UTC

Reviewer: Ramana Mallela from CA

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 

Tom Kyte

Followup  

August 14, 2007 - 10:35 am UTC

do not do that, you do not need to update the table (we are not sqlserver!!)
create trigger t before insert on t for each row
begin
   :new.column1 := value;
   :new.column2 := value2;
end;
/


Just ASSIGN values!

August 23, 2007 - 6:19 pm UTC

Reviewer: Fernando Sanchez from Madrid, Spain

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.


Tom Kyte

Followup  

August 24, 2007 - 1:59 pm UTC

...
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...


oh my, why bother? What is the possible real world use case for something like this.

more on mutating table

August 24, 2007 - 3:29 pm UTC

Reviewer: Fernando Sanchez from Madrid, Spain

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.



Tom Kyte

Followup  

August 27, 2007 - 3:59 pm UTC

please - impact the application

avoid triggers to perform black magic

you will never be sorry - you will be sorry if you have triggers doing all kinds of mystical magical "%T$@%$" in the background.

Hugely unmaintainable
Highly unstable
Massively slow

just do it "right"

mutating, trigger

September 20, 2007 - 2:12 pm UTC

Reviewer: Tony from Canada

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
Tom Kyte

Followup  

September 24, 2007 - 7:42 am UTC

in the relational database world, we call that a UNIQUE CONSTRAINT.

i am utterly at a loss as to why you would consider a trigger at all.

or why you count one's instead of count(*), but that is another story...

Just make job_name UNIQUE!!!!!! you are done.

thanks a lot Tom

September 25, 2007 - 4:56 pm UTC

Reviewer: Tony from Canada

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 Kyte

Followup  

September 26, 2007 - 9:36 pm UTC

remove all code (since it doesn't work) and explain only in the form of a textual REQUIREMENT what is needed.

it sounds like:

a) if a user is in the database group, they may have as many jobs as they like.
b) if a user is not in the database group, they may have only 1 job max

funny what would happen if your trigger worked (did not get the mutating table constraint). I'd love to be in the NULL group (I'd have the privs of a database guy) and you do know that unless we use a lock table command - your trigger "WILL NEVER EVER WORK" as coded right?? (please say "yes")


Thanks a lot

September 28, 2007 - 12:07 pm UTC

Reviewer: Tony from Canada

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.
Tom Kyte

Followup  

September 28, 2007 - 6:04 pm UTC

you are missing a lock table, so no - it is not correct.

(why would you bother to create a named exception, catch it your yourself and turn it into raise application error? why not just raise application error?? all you did there was generate more code and hide the actual line number from the caller!)

no, this is NOT correct.

phrase in english as a requirement what you are trying to enforce.

versioning rows

October 09, 2007 - 7:01 pm UTC

Reviewer: A reader

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

How is the mutating table dangerous in this case?

February 05, 2008 - 12:21 am UTC

Reviewer: Constantine

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 Kyte

Followup  

February 05, 2008 - 7:45 am UTC

... I've be overuled on the basis that CONNECT BYs will be 'a _lot_ slower
than joining through the edges' ...

hah, has anyone shown this to be true or are they guessing.

I'm guessing they are guessing.


If your trigger does NOT LOCK THE TABLE, it cannot work if you have more than one user.

remember - you cannot see other peoples changes, they cannot see yours.

What would happen if two people do work at the same time and cannot see eachothers modifications (and of course do not block either).


I would definitely be looking at not denormalizing the data, you'd be surprised how fast connect by is for trees of a reasonable size (hundreds or thousands of rows) if the correct indexes are in place to "walk the tree"

mutating table with parallel pipelined and merge

April 29, 2008 - 5:21 pm UTC

Reviewer: A reader from Dayton, OH USA

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)

Tom Kyte

Followup  

April 30, 2008 - 9:33 am UTC

can you convince me you need to use a pipelined function here - what are you doing in it that you cannot do simply in SQL alone?

April 30, 2008 - 10:49 am UTC

Reviewer: A reader

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.
Tom Kyte

Followup  

April 30, 2008 - 11:10 am UTC

do you have access to expert oracle database architecture? (my most recent book)

April 30, 2008 - 12:11 pm UTC

Reviewer: A reader

Yes.
Tom Kyte

Followup  

April 30, 2008 - 12:57 pm UTC

you might consider the "rowid - do it yourself - parallelism" I describe in there (same chapter with all of the parallel stuff)

sort of turn the problem inside out if you will - examples are in there, you can follow up if the idea isn't clear after seeing it.

April 30, 2008 - 4:33 pm UTC

Reviewer: A reader

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

ORA-04088

May 12, 2008 - 5:24 am UTC

Reviewer: Baiju P M from India

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.
Tom Kyte

Followup  

May 12, 2008 - 1:45 pm UTC

Umm, we are missing the lock table command somewhere?

and a before trigger to empty out the package variables?


and most importantly - we are missing the "statement of the rule we are trying to enforce", I see code that doesn't work, that won't work in a multi-user environment (for you see, you cannot see my uncommitted changes and I cannot see yours, when our triggers fire - they will consider OUR SESSIONS modifications - but not the other sessions - hence you'll get data in there that violates your rules and prevent data from being entered that should have been allowed - you need a lock table)


you don't have any code posted here with 47 lines, do, no way I can see offhand what you are doing wrong, but there are lots of things being done wrong in this code.


I urge you strongly, REALLY STRONGLY, to abandon this trigger, do it in a the code that does the insert, use the lock table command, do it right, do it easy.

ORA-04088

May 12, 2008 - 6:19 am UTC

Reviewer: Baiju P M from India

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

Can we give sequence while creating a table

September 25, 2008 - 1:54 am UTC

Reviewer: A reader from India

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
Tom Kyte

Followup  

September 25, 2008 - 3:14 pm UTC

sequences and tables are not 'associated'

you would

create table employee( emp_id number primary key, name ... );

you would then


insert into employee ( emp_id, name, .... )
values ( seq_emp_id.nextval, :bind, ... );


that is the correct way to approach this.


November 11, 2009 - 5:04 pm UTC

Reviewer: A reader

Tom Kyte

Followup  

November 15, 2009 - 1:19 pm UTC

neither does posting the same thing here, there and everywhere. Now you have to figure out which of the places you did this same posting might have a solution.... I forget already myself.

question

June 02, 2010 - 6:04 am UTC

Reviewer: andreea from Romania

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

Tom Kyte

Followup  

June 08, 2010 - 9:50 am UTC

why would you do that, just compute it.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:185012348071#2647688300346505979

mutating error when update

December 13, 2011 - 5:06 am UTC

Reviewer: A reader

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





Tom Kyte

Followup  

December 13, 2011 - 7:38 am UTC

you don't need an update statement at all.

just reference :new.net_val in a before, for each row trigger if you want to set it.


:new.net_val := whatever_you_want;


I don't understand from what you've written above "why" you cannot do that - why do you think you need to use "update"??



http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html



mutating table from STATEMENT trigger

December 18, 2011 - 1:00 pm UTC

Reviewer: Radu from Romania

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.


Tom Kyte

Followup  

December 18, 2011 - 2:54 pm UTC

1) on delete set null will issue:

update radu_2 set radu_1_id = null where radu_1_id = :old.radu_1.radu_1_id

in effect, so you issue an update on radu_2 which will in turn fire the statement trigger that will in turn try to update radu_3.


2) table radu_1 is not stable, that is the problem here.

you have started - but not finished a delete on radu_1
that fires the update
that update fires the trigger
but the delete is still not done, we are in the middle of it.


I hate triggers:
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html


can you tell us what you are really trying to accomplish here - I don't believe you update a constant "first" row to a constant -1 value. It doesn't make sense - and it would cause MASSIVE serialization - you would turn the database into a single user database! Only one person at a time would be able to delete from radu_1 or update radu_2 - since everyone would need to update the same radu_3 record.

So, tell us what you are trying to accomplish - not how you are trying to accomplish it - maybe we can suggest a design that can accomplish that.


follow up

December 20, 2011 - 4:32 am UTC

Reviewer: Radu from Romania

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.

Tom Kyte

Followup  

December 20, 2011 - 8:06 am UTC

I don’t like triggers neither, but it’s my company’s policy to implement logic
like this in the DB only.


but how can you allow them to do the DELETE? I mean - come on, if they can delete - they too can update. This is transactional logic.

I think, if your company is serious, you should be invoking stored procedures and never calling delete/insert/update/merge directly in the program. That is the only way to implement their policy (and I'm a big fan of that!)

You cannot let them do a delete AND then say "but they cannot do the update" - it doesn't work that way. DELETE is "logic" - just as much as "on update set default" is.


I suggest to encapsulate the delete in a stored procedure that does the right thing and revoke delete from the application schema.

Problem with mutating table

October 22, 2013 - 3:23 pm UTC

Reviewer: A reader from Colombia

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,
Tom Kyte

Followup  

November 01, 2013 - 8:40 pm UTC

read through http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42171194352295 for ideas

good

December 05, 2013 - 9:14 pm UTC

Reviewer: A reader

Good

Mutating Table error in a trigger.

February 03, 2014 - 11:54 am UTC

Reviewer: Learner. from IND

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.

October 11, 2016 - 8:34 pm UTC

Reviewer: A reader


enforcing a cross row rule

May 08, 2017 - 8:33 pm UTC

Reviewer: Michal Pravda from Prague, Czech Republic

"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")?
Connor McDonald

Followup  

May 09, 2017 - 2:17 am UTC

Obviously a plsql wrapper is a good option for pretty much any part of any application.

Another option is the materialized view option if applicable as described here

http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html



enforcing a cross row rule

May 08, 2017 - 8:38 pm UTC

Reviewer: Michal Pravda from Prague, Czech Republic

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")?

link not working

September 01, 2017 - 3:06 pm UTC

Reviewer: A reader from USA

Hi Tom,

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

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

Thanks
Connor McDonald

Followup  

September 06, 2017 - 3:14 am UTC

Thanks, we've found a number of broken links and we're working to rectify that

Link not working

September 18, 2018 - 11:32 am UTC

Reviewer: A reader

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

Followup  

September 21, 2018 - 12:36 am UTC

We've updated the link

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.