Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2026
  • Last Modified:

trigger mutation

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
PraKash
Asked:
PraKash
  • 5
  • 5
  • 2
  • +3
5 Solutions
 
michaeljoneillCommented:
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
 
PraKashAuthor Commented:
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
 
PraKashAuthor Commented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
PraKashAuthor Commented:
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
 
michaeljoneillCommented:
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
 
michaeljoneillCommented:
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
 
PraKashAuthor Commented:
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
 
PraKashAuthor Commented:
I meant I create the second trigger in table 3
0
 
michaeljoneillCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
sujith80Commented:
>> 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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
michaeljoneillCommented:
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
 
Ritesh_GargCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now