Solved

trigger mutation

Posted on 2007-11-27
17
2,010 Views
Last Modified: 2013-12-19
Hi,

I have two tables.
Table1
id  col1  col2  col3
Table2
id col4  col5  col6

I need a trigger on table 2 which would do this
update table3 set col1= (select sum(table2.col5) from table1, table2 where table1.col1=table2.col2) where table1.id=table3.id

It was giving mutation error. So I created another table table4 and updated the table2.id value in there and the trigger into 2 triggers. 1st trigger would populate the table2.id value in table4 and the trigger on table 4 would do the above SQL and update table3. Still I am getting mutation error.

Thanks fro your help.
0
Comment
Question by:PraKash
  • 5
  • 5
  • 2
  • +3
17 Comments
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20362057
All you did by creating the second table with the second trigger is just *delay* modifying the first table when the first table's trigger fires.

What you need to fundamentally get around mutating issues is update the table in a statement trigger (*not* for each row).  Tables CAN be updated from a statement trigger in this way.

Depending on your need, you may require populating a package array during the row level trigger, that is then iterated during the statement level trigger.
0
 

Author Comment

by:PraKash
ID: 20362113
Can you please explain a little bit more on what you mean in your last para?
The reason I went with for each row here is because the updates are always invoked rowwise. In this system the updates are never made as a group.
0
 

Author Comment

by:PraKash
ID: 20362140
Also Mike,
I need to check the OLD and NEW value and see if there is a change. This trigger should be based on that
0
 

Author Comment

by:PraKash
ID: 20362168
If I use a statement level trigger is it possible to identify the row that triggered the trigger? I dont want to update the whole table, just those row/rows that were created/updated
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20362398
I'll post some untested sample code that should illuminate the process even if it isn't usable code.  It is essential that you understand the PL/SQL ingredients are two triggers on the same table, one row and one statement as well as a package to handle both storing what to do (the row trigger CAN modify package state) and how to do (which is invoked by the statement trigger).


create table table1 ( id varchar2(10), col1 integer, col2 integer, col3 varchar2(10) );

create table table2 ( id varchar2(10), col1 integer, col2 integer, col3 varchar2(10) );

create table table3 ( id varchar2(10), col1 integer, col2 integer, col3 varchar2(10) );
 

create or replace package package1 as
 

   procedure update_table3;
 

   procedure add_to_array(p_value in table2.id%type);
 

end;
 

 

create or replace package body package1 as
 

   type associative_array_type is table of table2.id%type index by binary_integer;
 

   associative_array associative_array_type;
 

   procedure update_table3 is

   begin

   

      for i in 1 .. associative_array.count

      loop

      

         if associative_array.exists(i)

         then

         

            update table3 set table3.col1 = (select sum(table2.col2) from table1, table2 where table1.col1 = table2.col1) where table3.id = associative_array(i);

         

            associative_array.delete(i);

         

         end if;

      

      end loop;

   

   end update_table3;
 
 

   procedure add_to_array(p_value in table2.id%type) is

   begin

      associative_array(nvl(associative_array.last, 0) + 1) := p_value;

   end add_to_array;
 
 

end;
 

create or replace trigger table2_row

  before insert or update on table2

  for each row

declare

begin

   -- assumes id is a primary key and immutable
 

   if inserting

   then

      package1.add_to_array(:new.id);

   

   elsif updating

         and :new.col2 <> :old.col2

   then

      package1.add_to_array(:new.id);

   

   end if;
 

end;
 

create or replace trigger table2_statement

  before insert or update on table2

declare

begin

   package1.update_table3;

end;

Open in new window

0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20362411
Also, if performance becomes an issue, you can create a SQL nested table data type and use that instead of the associative array - then you can achieve the benefit of updating the table with one sql-to-pl/sql context switch instead of the potentially many switches that the for loop that processes the associative array may incur.
0
 

Author Comment

by:PraKash
ID: 20362566
Mike,

In the meantime I tried something different. Not that it worked but just want to know what went wrong.
I created a statement trigger on the second table and then called a procedure and it still gives me that mutation error. I thought trigger by statement is not supposed to give a mutation error.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:PraKash
ID: 20362567
I meant I create the second trigger in table 3
0
 
LVL 1

Accepted Solution

by:
michaeljoneill earned 40 total points
ID: 20362852
mutating table issue = row level trigger + selecting or modifying the underlying table of that trigger.  period.  It doesn't matter if the trigger code calls a stored procedure or causes another trigger to fire that does it.  I've given you the time tested pattern to avoid this issue.  Feel free to reference http://asktom.oracle.com for a multitude of more examples and solutions.

If you want to post your real table DDL and real source, I'd be glad to steer your actual code in the right direction, but until you accept that the mutating table issue is what it is (and it is that way for good reason) you will continue misusing your time.

Good Luck
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 40 total points
ID: 20363113
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20363127
if you still want to select from the table on which trigger is firing, then write a pragma autonomous_transaction procedure which can return the value you want and try calling it from the trigger.

i remember very vaguely some old version of the database may not allow this kind of autonomous procedure/function to be called from trigger.

Thanks
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 40 total points
ID: 20363268
>> I need a trigger on table 2
If your trigger is on table2 and if it is a rowlevel trigger then you dont need table2 in the join at all. You may change the update in the trigger as:

update table3
set col1=
 (select sum(:new.col5)
  from table1
  where table1.col1=:new.col2)
where table1.id=table3.id;
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 40 total points
ID: 20371043
To avoid the "mutating table" limitation of Oracle triggers, you have to use a more complex approach.  This involves three components:
1. a "row-level" (for each row) trigger to save the rowid or primary key of each row being changed
2. a temporary place to hold these rowids or primary keys
2. an "after statement" trigger (with no "for each row" clause) that will retreive each rowid or primary key and do the actual processing that you want.

You need to first create a place to hold the rowids or primary keys.  This could be: a PL\SQL array, a global temporary table, or actually a standard table that does *NOT* have a foreign key relationship to your current tables.  The most efficient option is a PL\SQL array, but the coding for that is a bit complex.  The simplest option is another standard table, but this has a performance penalty.  So, the best option is usually a global temporary table.

Then, you need to change you current row-level trigger to just save each rowid or primary key to this global temporary table.

Then you write an new "after statement" trigger to retrieve each rowid or primary key, then do the actual processing that you want.  If you use a global temporary table with the default opion (on commit delete) then you don't have to explicitly clear it at the end of your statement-level trigger.  If you use some other option, then you may have to explicitlky clear or delete these records after you process them.
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20374581
regarding markgreer's suggestion:

> The most efficient option is a PL\SQL array, but the coding for that is a bit complex

Not really, I just posted how to do it above.


> You need to first create a place to hold the rowids or primary keys

I would recommend NOT using rowids because your triggers aren't going to be controlling your transaction, depending on the transaction level, rowids might not guarantee success.


> Then you write an new "after statement" trigger

This is correct and my previous post is wrong.  My statement trigger should be an after, not a before.
create or replace trigger table2_statement

  after insert or update on table2

declare

begin

   package1.update_table3;

end;

Open in new window

0
 
LVL 6

Assisted Solution

by:Ritesh_Garg
Ritesh_Garg earned 40 total points
ID: 21024615
Since you are reading the table2 in your trigger it is giving mutating table error.

For your need, I would suggest either of the two solutions:
1. Update the table3 in the after statement level trigger for all the records.
2. If the above is too time consuming and expensive for the database, the solution would be to create a package variable of PL/SQL Table (index by binary_integer).  Populate the PL/SQL Table with the necessary values (table2.col2 in the example you gave) in the row level trigger.  In the After Statement level trigger, loop through this PL/SQL Table and run the same update statement for each.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now