badhu

• Sep 4th, 2006

 

Hi Friend

  Trigger Mutating will araise, when u want to do more than one DML operation on a table u will get Trigger Mutation.

 For example

U have table Stud with Sno, Mark1, Mark2, total,Avg fields.

U write a trigger Like below

Create or Replace Trigger Stud_Trig AFTER INSERT ON STUD FOR EACH ROW

begin

   Update Stud SET total=:NEW.mark1 + :NEW.mark2, Avg = (:New.mark1 + :new.mark2) / 2 where sno = :new.sno; 

end;

Here u will get trigger Mutating.

The Trigger Mutating also appear in another time. I am not sure abt that time

I will work out and give the sample to u.

Regards

badhu

anonymous

• Oct 4th, 2006

 

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement.When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. This Mutating Trigger restriction applies only to the for each row triggers.

If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.

Ashish M

• Nov 13th, 2006

 

I got rid of same kind of error while writing a trigger. I wrote a new function using AUTONMOUS TRANSACTION keyword within it and called that function from the trigger. It worked for me. Hope it works for you as well.

be17be

 Profile Answers by be17be Questions by be17be

• Dec 1st, 2006

 

Ashish, can you please give an example?

amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 13th, 2007

 

1) make the trigger as a statement level trigger.
2) if u want to use :old and :new qualifer then create 2 trigger ,1st before insert trigger and use PL/SQL table to select the values,2nd after insert trigger and insert the values from PL/SQL tables.