11 Replies Latest reply: Oct 9, 2012 7:27 AM by Paul Horth RSS

    Error :Mutating Trigger

    Vedant
      Dear Friend,

      i have created trigger
      create or replace TRIGGER  "AFT_CRM_SALES_ASSIGN" 
         after update ON TATEST.CRM_SALES_DEAL
         FOR EACH ROW
      
      declare
      
      i_id varchar2(30);
      t_id varchar2(30);
      e_id varchar2(30);
      a timestamp;
      ABC NUMBER;
      begin
      --select lpad ( CRMDEAL.nextval, 8, '0' ) into i_id from dual;
      
      select ASSIGNSEQ.NEXTVAL into t_id from DUAL;
      
      select to_char(sysdate, 'DD-MON-YYYY HH:MIPM') into a from dual;
      
      SELECT COUNT(*) INTO ABC FROM TATEST.CRM_SALES_ASSIGN_OPPORTUNITY WHERE DEAL_ID=:NEW.ID AND USER_ID=:NEW.ASSIGN_TO;
      
      IF ABC=0 THEN
      
      INSERT INTO TATEST.CRM_SALES_ASSIGN_OPPORTUNITY(ID,DEAL_ID,USER_ID) 
      SELECT t_id,ID,ASSIGN_TO FROM TATEST.CRM_SALES_DEAL WHERE ASSIGN_TO IS NOT NULL;
      
      END IF;
      end;
      
      
      Show me error 
      
      
      1 error has occurred
      ORA-04091: table TATEST.CRM_SALES_DEAL is mutating, trigger/function may not see it ORA-06512: at "TATEST.AFT_CRM_SALES_ASSIGN", line 19 ORA-04088: error during execution of trigger 'TATEST.AFT_CRM_SALES_ASSIGN' (Row 1)
      How to resolve it.

      Thanks
        • 1. Re: Error :Mutating Trigger
          Chanchal Wankhade
          Hi,

          It seems your are creating trigger on the table which you are using in the executable section
          after update ON TATEST.CRM_SALES_DEAL --You are using this table
          and you have below query in executable part
          SELECT t_id,ID,ASSIGN_TO FROM TATEST.CRM_SALES_DEAL WHERE ASSIGN_TO IS NOT NULL;
          Sollution ..

          you need to use other table instate of TATEST.CRM_SALES_DEAL table otherwise you cannot create trigger on this table.

          Edited by: Chanchal Wankhade on Oct 8, 2012 2:06 AM
          • 2. Re: Error :Mutating Trigger
            RamaKrishna.CH
            You are tying to read the table that is in change mode. if you want to solve the problem write autonomous transaction in declare section and commit at end of the transaction.
            • 3. Re: Error :Mutating Trigger
              ranit B
              Please try -(any one from below)-

              1. Change trigger from Row Level to Statement Level.
              2. Use PRAGMA AUTONOMOUS_TRANSACTION along with Commit.

              Please let me know if you have any concerns.

              Ranit B.
              • 4. Re: Error :Mutating Trigger
                yoonas
                Hi,


                http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

                Regards
                Yoonas
                • 5. Re: Error :Mutating Trigger
                  966744
                  -- First create a procedure which will contain the Insert statement with select statement on CRM_SALES_DEAL Table
                  -- Procedure Code

                  CREATE OR REPLACE PROCEDURE P_INSERT_CRM_SALES_DEAL
                  PRAGMA     AUTONOMOUS_TRANSACTION
                  BEGIN
                       --
                       INSERT INTO TATEST.CRM_SALES_ASSIGN_OPPORTUNITY(ID,DEAL_ID,USER_ID)
                       SELECT t_id,ID,ASSIGN_TO FROM TATEST.CRM_SALES_DEAL WHERE ASSIGN_TO IS NOT NULL;
                       --
                       COMMIT;
                  EXCEPTION
                       WHEN OTHERS
                       NULL --<You can define exception here if you want>
                  END;

                  -- Now re-write the trigger code as below -

                  create or replace TRIGGER "AFT_CRM_SALES_ASSIGN"
                  after update ON TATEST.CRM_SALES_DEAL
                  FOR EACH ROW

                  declare

                  i_id varchar2(30);
                  t_id varchar2(30);
                  e_id varchar2(30);
                  a timestamp;
                  ABC NUMBER;
                  begin

                  select ASSIGNSEQ.NEXTVAL into t_id from DUAL;

                  select to_char(sysdate, 'DD-MON-YYYY HH:MIPM') into a from dual;

                  SELECT COUNT(*) INTO ABC FROM TATEST.CRM_SALES_ASSIGN_OPPORTUNITY WHERE DEAL_ID=:NEW.ID AND USER_ID=:NEW.ASSIGN_TO;

                  IF ABC=0 THEN
                       P_INSERT_CRM_SALES_DEAL; -- Call the procedure to insert records.
                  END IF;
                  end;

                  Regards,
                  Rahul Tiwari
                  • 6. Re: Error :Mutating Trigger
                    Sven W.
                    ranit B wrote:
                    Please try -(any one from below)-

                    2. Use PRAGMA AUTONOMOUS_TRANSACTION along with Commit.
                    This is terribly terribly wrong! Never do that!
                    • 7. Re: Error :Mutating Trigger
                      ranit B
                      Why so Sven...?
                      Please rectify me there.
                      • 8. Re: Error :Mutating Trigger
                        9876564
                        rt9oct wrote:
                        -- First create a procedure which will contain the Insert statement with select statement on CRM_SALES_DEAL Table
                        -- Procedure Code

                        CREATE OR REPLACE PROCEDURE P_INSERT_CRM_SALES_DEAL
                        PRAGMA     AUTONOMOUS_TRANSACTION
                        BEGIN
                             --
                             INSERT INTO TATEST.CRM_SALES_ASSIGN_OPPORTUNITY(ID,DEAL_ID,USER_ID)
                             SELECT t_id,ID,ASSIGN_TO FROM TATEST.CRM_SALES_DEAL WHERE ASSIGN_TO IS NOT NULL;
                             --
                             COMMIT;
                        EXCEPTION
                             WHEN OTHERS
                             NULL --<You can define exception here if you want>
                        END;

                        -- Now re-write the trigger code as below -

                        create or replace TRIGGER "AFT_CRM_SALES_ASSIGN"
                        after update ON TATEST.CRM_SALES_DEAL
                        FOR EACH ROW

                        declare

                        i_id varchar2(30);
                        t_id varchar2(30);
                        e_id varchar2(30);
                        a timestamp;
                        ABC NUMBER;
                        begin

                        select ASSIGNSEQ.NEXTVAL into t_id from DUAL;

                        select to_char(sysdate, 'DD-MON-YYYY HH:MIPM') into a from dual;

                        SELECT COUNT(*) INTO ABC FROM TATEST.CRM_SALES_ASSIGN_OPPORTUNITY WHERE DEAL_ID=:NEW.ID AND USER_ID=:NEW.ASSIGN_TO;

                        IF ABC=0 THEN
                             P_INSERT_CRM_SALES_DEAL; -- Call the procedure to insert records.
                        END IF;
                        end;

                        Regards,
                        Rahul Tiwari
                        This is the only solution, By making it a row level trigger will not solve the mutating problem.

                        Edited by: AbSHeik on Oct 9, 2012 4:58 AM
                        • 9. Re: Error :Mutating Trigger
                          6363
                          ranit B wrote:
                          Why so Sven...?
                          Please rectify me there.
                          It is a row level update trigger in the middle of an update with NEW and OLD values available at the same time. Do you know which values you will get if you issue a select in the trigger during the update against the same table in an autonomous transaction?

                          Autonomous transactions should never be used to disable the mutating table error, particularly not by developers who do not know that the error is protecting them from getting the wrong data.
                          • 10. Re: Error :Mutating Trigger
                            Ora
                            I doubt your trigger got complied. This stmt, is it valid in plsql block?
                            SELECT t_id,ID,ASSIGN_TO FROM TATEST.CRM_SALES_DEAL WHERE ASSIGN_TO IS NOT NULL; 
                            Why do you want to hit the table TATEST.CRM_SALES_DEAL in the trigger?

                            Mutating table generally occurs if the implemention of logic is flawed.

                            Using Autonomous transaction tends to corrupt the data, and hence it must be used cautiously.
                            • 11. Re: Error :Mutating Trigger
                              Paul  Horth
                              ranit B wrote:
                              Why so Sven...?
                              Please rectify me there.
                              Because you subvert transactional consistency. What happens if you rollback the original transaction that caused the trigger to fire
                              but you've committed whatever the autonomous trigger did? Totally violates ACID.

                              Also in some circumstances Oracle can fire the trigger more than once.

                              The only time I would consider autonomous triggers is writing error details to a log file before rolling back in the main transaction.