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!

ORA-04091: mutating, trigger/function may not see it..

Discussion in 'SQL PL/SQL' started by Vicky, Mar 19, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I've created the below trigger.,

    create or replace trigger TRG_EMP_PROCESS
    after insert
    ON EMPLOYEES REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    BEGIN
    IF :new.STATUS ='Process' THEN
    update
    EMPLOYEES_1
    SET
    MOBILE_NO=:new.MOBILE_NO,
    EMAIL_ID =:new.EMAIL_ID
    WHERE
    EMP_ID=:new.EMP_ID;
    update
    EMPLOYEES_2
    SET
    STATUS='PROCESSED'
    WHERE
    EMP_ID=:new.EMP_ID;
    end if;
    UPDATE
    EMPLOYEES
    SET
    STATUS='PROCESSED'
    WHERE
    EMP_ID=:new.EMP_ID;
    END TRG_EMP_PROCESS;

    It got executed successfully.,
    But while inserting records into employees table, I'm getting the below error.,


    One error saving changes to table "EMPLOYEES":
    Row 10: ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
    ORA-06512: at "TRG_EMP_PROCESS", line 16
    ORA-04088: error during execution of trigger 'TRG_EMP_PROCESS'
    ORA-06512: at line 1

    Even though I'm using AFTER INSERT timing, It's not allowing me to update the :new value 'Process' to 'PROCESSED'..
    Cud U tel me tat, Is der any alternative way to updated :new values.. while using trigger.,?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    As a rule, the trigger it is used for implementation of expanded rules of integrity.

    Here possible candidate solutions of a problem:

    1) this logic can be carried out in procedure

    2) Compound DML Triggers (11r)

    3) to use autonomous_transaction. In this task it is better not to use it


    See links :

    PL/SQL Triggers
    Mutating Table Exceptions
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Refer to above link indicated by Sergey to get more details on why you're getting this error.

    Only thing to add is that you can still achieve what you're trying by some even simpler changes:

    1) Replace:

    with
    2) Replace:

    with

    N.B: Changing/Assigning values to ":NEW" is not allowed in "AFTER INSERT" triggers - that's why you need change 1).
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi rajenb,

    If I keep "before" insert, the updates are not happening.,
    The updates other then :new value update are not happening././
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    This works fine !
    Are you sure you already have the record for the EMP_ID :)new.EMP_ID) in the 2 tables (EMPLOYEES_1 and EMPLOYEES_2) when you insert a record with :new.EMP_ID in EMPLOYEES, i.e., if you are inserting a record with EMP_ID=12345 in EMPLOYEES, you should already be having such a record in the other 2 tables as you're doing an Update (not an Insert).
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes rajen,

    The respective records are there in Employee_1 and Employees_2, But, it's not updating only while keeping 'Before' insert alone., But the :new value got updated successfully..

    The other updates except :new value works fine with 'After' insert event.,
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    In the absence of your table structures and data, here's what I've done as POC:

    And as I mentioned earlier, this works fine ... if it doesn't work in your instance, most probably there are some issues in your data or table definitions...

    That's the best I can do with the info you've provided ...:p
     
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Rajen., It's working fine now., Its the problem with the sequence.,,.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO