7 Replies Latest reply: May 1, 2012 3:40 PM by damorgan RSS

    Mutation while Calling Function from Trigger

    Eclipse01
      Hi,

      I would really appreciate if anyone can point me to the right direction. So I have a trigger (TableAR_BU) on a table TableAR. On that Trigger, I have a logic to call the function and inside that Function, I am just doing select statement from few other tables including the table TableAR.
      Function is supposed to return me 'Y' and it does correctly returns me 'Y' if I select the function from dual. - Select Function1(100) from dual;

      But when I call the function from the trigger, it returned me 'N'. So after investigation I found that the select statement ( select from tableAR) inside the function returns null when called from trigger.
       
      DROP TRIGGER TableAR_BU;
      
      CREATE OR REPLACE TRIGGER TableAR_BU
      BEFORE UPDATE
      ON TableAR_BU REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      declare
       localcontext varchar2(32000) := '; Context : ';
       sqlstmt varchar2 (2000);
      
       Status varchar2(1);
      
      Status := Function1(:new.Id);
      
      
      End;
      /
      
      
      function Function1(al_Id in TableAR.Id%type)
      RETURN  varchar2 is
      
      lsItemId TableAR.itemId%type;
      
      Begin
      
      Select itemId into lsItemId
      from TableAR
      where id = al_Id;
      
      
      
      Exception
      
      End Function1;
      Any suggestions/comments appreciated.

      Edited by: Eclipse01 on May 1, 2012 11:29 AM
        • 1. Re: Mutation while Calling Function from Trigger
          JustinCave
          A row-level trigger on TableAR cannot, in general, query TableAR or call anything that queries TableAR.

          In your case, it would appear that you could just reference :new.itemId rather than calling the function. It's unclear to me, though, how it makes sense to assign an itemId to a status column-- it seems unlikely that itemId is a varchar2(1) so your function (or at least the function call) doesn't seem to make sense. That makes me suspect that there is more to the problem than you are providing here. The fact that you have a local variable sqlStmt in a trigger, implying that you're doing dynamic SQL in a trigger, has me even more concerned.

          Justin
          • 2. Re: Mutation while Calling Function from Trigger
            Etbin
            Usually when updating you don't check whether or not the key exists (it might have been deleted meanwhile).
            You just update and check sql%rowcount to see if anything was updated.

            Regards

            Etbin
            • 3. Re: Mutation while Calling Function from Trigger
              damorgan
              What is the point of the code:
              localcontext varchar2(32000) := '; Context : ';
               sqlstmt varchar2 (2000);
              as neither is used?

              It seems to me very unlikely that this is the real code ... so please post the real code and database version number.
              • 4. Re: Mutation while Calling Function from Trigger
                Eclipse01
                Thank you All for your input. I appreciate that.
                Sorry to be little vague.
                So, Let me add more details. The Function Funcion1 has many select statement from number of tables and those values are used to decide whether the return value for this function is 'Y' or 'N'.

                DROP TRIGGER TableAR_BU;
                 
                CREATE OR REPLACE TRIGGER TableAR_BU
                BEFORE UPDATE
                ON TableAR_BU REFERENCING NEW AS NEW OLD AS OLD
                FOR EACH ROW
                declare
                
                 Status varchar2(1);
                 
                Status := Function1(:new.Id);
                
                 if  Status = 'Y' then 
                  :new.ItemFlag := 11;
                 end if;
                 
                End;
                / 
                 
                 
                function Function1(al_Id in TableAR.Id%type)
                RETURN  varchar2 is
                 
                lsItemId TableAR.itemId%type;
                lsValid varchar2(1);
                
                Begin
                 
                lsValid :='N'
                
                Select itemId into lsItemId
                from TableAR
                where id = al_Id;
                
                Select key, name into lsKey, lsName
                from tableB 
                where id = al_id;
                ..................
                
                ................
                
                If lsItemId = "SomeValue" and lsKey = 22 and .....
                
                IsValid :='Y'
                
                End if;
                 
                
                return IsValid;
                
                Exception
                 When others then 
                 return IsValid;
                End Function1;
                I have cleaned up the code a bit to remove irrelevant.

                Edited by: Eclipse01 on May 1, 2012 11:58 AM
                • 5. Re: Mutation while Calling Function from Trigger
                  JustinCave
                  The problem remains that a row-level trigger on TableAR cannot query TableAR or call anything that does query TableAR. In the middle of a SQL statement, the table is potentially in an invalid intermediate state so Oracle can't guarantee that queries against the table return consistent or reasonable results so it disallows such queries.

                  In 99+% of the cases, this is a good thing because it means that you are trying to use triggers for something they are not designed for (in addition to creating applications that are terribly difficult to maintain because one action like updating a row ends up creating a huge cascade of side effects). Most of the time, you're much better off reconsidering the architecture and moving the function call out of the trigger and into some sort of API that wraps the UPDATE statement (i.e. a stored procedure that does the UPDATE and calls the procedure).

                  In the <1% of the cases where you really want to do this in a trigger, you'd need a combination of objects (or a compound trigger with multiple sections in 11g). You would need
                  - A package with a collection of TableAR primary keys
                  - A before statement trigger that clears out this collection
                  - A row-level trigger that inserts the :new.Id into the collection
                  - An after statement trigger that iterates over the collection and calls the function.

                  That, however, is a lot of moving parts to deal with so it radically increases complexity. So you'd only want to consider it if you are really confident that you need to use triggers in the first place.

                  Justin
                  • 6. Re: Mutation while Calling Function from Trigger
                    Eclipse01
                    Thanks Justin For the detail Explanation. I sincerely appreciate your help. I was under the impression that only Update would cause the mutation or create any issue. I was not aware that the table is in invalid state when it's in the middle of a SQL statement.

                    Thanks Again Everyone.
                    • 7. Re: Mutation while Calling Function from Trigger
                      damorgan
                      Actually the row level trigger can perform the select ... if the trigger is defined with PRAGMA AUTONOMOUS_TRANSACTION but, having said that, I would not advise that you do it.

                      The best thing to do is just let it happen and trap any resulting error if one is generated.