1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Mutating Concepts in PL/SQL

Discussion in 'SQL PL/SQL' started by kiran.marla, Feb 20, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Plz anybody, tell all the mutating concepts in PL/SQL like what is
    1. Mutating Trigger
    2. Mutating Table
    3. Mutating Error
    4. Mutating Table Error
    5. Mutating Trigger Error

    Thanks in Advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Triggers don't mutate; tables do. A mutating table error is raised when a table is modified by the trigger against it. As an example consider a modified EMP table, with an additional column named INCREASE; creating the following update trigger will raise a mutating table error:

    Code (SQL):
    CREATE OR REPLACE TRIGGER updt_emp_sal
    BEFORE UPDATE ON emp
    FOR each ROW
    BEGIN
         IF :NEW.sal <> :OLD.sal THEN
              UPDATE emp SET increase = :NEW.sal - :OLD.sal
              WHERE empno = :NEW.empno;
         END IF;
    END;
    /
     
    The trigger is modifying the table which is already being modified by the UPDATE statement. Oracle will raise an ORA-04091 error and execution will stop, preventing the data from being modified.

    As mentioned before there are no mutating triggers, so the first and last 'events' cannot occur. The middle three are all the same error, an ORA-04091. Using a procedure in an autonomous transaction to update the table and calling that procedure from the trigger can eliminate such errors. Tom Kyte and others have discussed this topic many times and these discussions are easily found by using google.com.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    I tried as u have told before and i was unsuccessful, can u pls give that procedure autonomous transaction code which is called in the trigger.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The procedure would be:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE updt_emp_increase(p_empno IN numnber, p_newsal IN NUMBER, p_oldsal IN NUMBER) IS
    pragma autonomous_transaction;
    BEGIN
              UPDATE emp SET increase = p_newsal - p_oldsal
              WHERE empno = p_empno;
    END;
    /
     
    The trigger would then be written:

    Code (SQL):
    CREATE OR REPLACE TRIGGER updt_emp_sal
    BEFORE UPDATE ON emp
    FOR each ROW
    BEGIN
         IF :NEW.sal <> :OLD.sal THEN
              updt_emp_increase(:NEW.empno, :NEW.sal, :OLD.sal);
         END IF;
    END;
    /
     
    which should elimiinate the ORA-04091 error.

    Examples of this are available on the web so try, again, to use google.com to find them.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi, i got the error while i tried with ur code. is my execution is correct?
    here i used table dupemp instead of emp.

    Code (SQL):
    SQL> UPDATE DUPEMP SET SAL = 1.1 * SAL WHERE DEPTNO = 30;
    UPDATE DUPEMP SET SAL = 1.1 * SAL WHERE DEPTNO = 30
           *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting FOR resource
    ORA-06512: at "KIRAN.UPDT_EMP_INCREASE", line 4
    ORA-06512: at "KIRAN.UPDT_EMP_SAL", line 3
    ORA-04088: error during execution OF TRIGGER 'KIRAN.UPDT_EMP_SAL'

    actually i tried with stud table, as u said for emp table.
    my stud table contains rno,name,m1,m2,m3,tot,avgg columns.
    as i enter rno,name,m1,m2,m3
    tot and avgg get calculated and insert into stud table.
    i use same autonomous transaction code what u said earlier. plz solve my problm
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This was my error; create the trigger with the autonomous transaction pragma and remove that pragma from the procedure.
     
  7. Mutation Error is actually a frnd of developer , when we try to break the read consistent rule of oracle , it prevent us to do so.

    1. Mutation error you will face with Triggers and Functions

    In Triggers , you face then with row level triggers only , no statement level triggers obvious

    2. When you refer to same table on which trigger fires, it can be select , delete , update or insert event.

    3. For Insert , we have one exception with before row level triggers

    You can insert single row with values clause but not multiple tows with sun query .

    4. Trigger refering the diff table , when there is PK - Fk Cons with on delete casacde option

    5 . With Functions

    More Details on my Blog
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And your blog is ... where?

    It would have been helpful to have a link to the referenced post rather than make members attempt to search for this blog.
     
  9. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Zargon,

    Did you get answer for your Mutating table Trigger.
    Mutating table trigger can be avoided using Autonomous Transaction.

    let me know detail of your reqirement or problem i can help you out on these.

    Cheers
    Kamal (kamal.love@gmail.com)
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't have a mutating table error -- someone else does and I've been answering the thread. Your offering mentioned your blog post but included no link to it. It is common practice to include such a link when a blog post is mentioned. It's frustrating to be offered a blog entry that may provide assistance but absolutely no link to it -- people are not going to have the patience to go searching google for a blog by person 'x' then go searching the blog (if they find it) for the relevant post.