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!

Need Definitin for Mutation in PL/SQL

Discussion in 'SQL PL/SQL' started by RG Hegde, May 18, 2015.

  1. RG Hegde

    RG Hegde Forum Advisor

    Likes Received:
    Trophy Points:
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    The definition is present in that post:

    "Re: Mutating Concepts in PL/SQL

    [​IMG] Originally Posted by kiran.marla [​IMG]
    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

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

    I was the one who posted that definition and it's as exact as you will get for that error. If you need it explained further then explain what it is in that definition that isn't clear.