5 Replies Latest reply: Aug 5, 2014 11:13 AM by Mike Kutz RSS

    ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?

    Amr Abdeen

      HI ALL,

       

      I CREATED TRIGGER TO CHECK ITEM_NAME BEFORE UPDATING ON TABLE T_ITEMS IF ANOTHER ITEM_NAME LIKE CURRENT ITEM_NAME OR NOT, IF LIKE ANOTHER ITEM_NAME DON'T SAVE CURRENT UPDATED ITEM_NAME.

       

      MY STEPS IS:

      1- I CREATED FUNCTION CALL CH_UNIQUE_ITEM_NAME

      CREATE OR REPLACE FUNCTION CH_UNIQUE_ITEM_NAME
      ( P_ID NUMBER, P_ITEM_NAME IN VARCHAR2 )
      RETURN NUMBER
      AS
      X_COUNT NUMBER;
      --  FUNCTION "CH_UNIQUE_ITEM_NAME" TO CHECK ITEM_NAME LIKE OTHER ITEM_NAME OR NOT.
      BEGIN
              SELECT COUNT(ITEM_NAME)
              INTO X_COUNT
              FROM T_ITEMS
              WHERE ID != P_ID
              AND UPPER(ITEM_NAME) LIKE UPPER(P_ITEM_NAME);
        RETURN X_COUNT;
      END CH_UNIQUE_ITEM_NAME;

       

      2- I CREATED TRIGGER CALL TRG_CH_UNIQUE_T_ITEMS

      CREATE OR REPLACE TRIGGER "TRG_CH_UNIQUE_T_ITEMS" BEFORE
        UPDATE
        ON T_ITEMS FOR EACH ROW
        DECLARE
        X_CHECK NUMBER;
      BEGIN
      SELECT CH_UNIQUE_ITEM_NAME(:OLD.ID, :NEW.ITEM_NAME) INTO  X_CHECK FROM DUAL;
      IF (:NEW.ITEM_NAME NOT LIKE :OLD.ITEM_NAME AND  X_CHECK > 0)
      THEN RAISE_APPLICATION_ERROR (-20007, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
      END IF;
      END;

       

      NOW WHEN UPDATE ANY ITEMS I FOUND ERROR "ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512"

      Form on T_ITEMS22222.png

       

      PLEASE HELP ME TO SOLVE THIS ERROR . I TIRED TO SOLVE IT ..

      THANKS ,

        • 1. Re: ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?
          AndyH

          This isn't an APEX question, however.... you trigger is mutating because you are querying the same table that you are attempting to change the data in. There isn't an easy fix, if you really need to do this have a look at the AskTom site.

           

          In your case, it looks like you just need to have a unique functional index based on upper(item_name).

           

          --

          Andy

          • 2. Re: ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?
            Amr Abdeen

            @AndyH

             

            PLEASE, SIMPLIFYING YOUR ADVICE "In your case, it looks like you just need to have a unique functional index based on upper(item_name)."

             

            THANKS,

            • 3. Re: Re: ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?
              Tom Petrus

              Please do not use capital letters to communicate. On the net, caps are used to represent "shouting". It doesn't read easy and screams "PAY THIS ATTENTION". When opening this thread the first reaction would be to simply back out, as it's not easy enough on the eye to take the time to read.

              Similarly, you can put your actual code in a code-like block by using the syntax highlighting (Advanced editor, use the double blue arrow to get the option).

              Keeping these things in mind will make for a much more appreciated post.

               

              What Andy meant with unique index is: use a unique constraint to force uniqueness on the item name on the table. You do not need to use triggers and PLSQL to get this functionality since all you do is check this one column in your table for uniqueness, and that is what unique constraints are used for.

              By "functional" is meant that by default a unique constraint will simply check the values of the column for uniqueness, capitalization included, which is not what you want. Therefor, you'd need to create a constraint which is just slightly advanced in that it would be based on a function performed on the column, this being UPPER().

              With that constraint/index in place you won't need to have that trigger and plsql code to verify data integrity, as oracle will do it right away. You will then receive a unique constraint violation error in your application. (DUP_VAL_ON_INDEX)

              Using Indexes in Database Applications

               

              An example would be:

              CREATE UNIQUE INDEX fn_unique_idx  ON table1 (UPPER(ITEM_NAME);
              

              (note that unique constraints create unique indexes/constraints can be linked up with an index, hence me talking constraints and linking indexes. )

              • 4. Re: Re: Re: ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?
                Amr Abdeen

                I'm sorry for capital letters & i will be use syntax highlighting.

                 

                Sorry i found miss understanding for why i use trigger to check unique item_name column instead of UNIQUE INDEX column.

                 

                T_ITEMS is table multiple users use it with different data @ same table, Sure more than one user insert items with same item_name with different USER# in table.

                USER#1 add item_name "Dell PC VISTRO" in column item_name ,  I want create trigger before inserting or updating to check unique another item_name with same item_name in same USER#1.

                 

                For that i created function and trigger to do it.

                But when update or insert in the table i found That ERROR TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512.

                 

                Thanks,

                • 5. Re: Re: Re: Re: ERROR/  TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512  ..?
                  Mike Kutz

                  You do realize you have just defined a multi-column UNIQUE INDEX?

                   

                  create unique index index_name_uq on table_name( user#, upper(item_name) );
                  

                   

                  Also, from a GUI Design stand point, (using APEX terms) you want to Validate that the data BEFORE you Submit.

                  (use a Page Level Validation)

                   

                  MK