Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: ORA-04091:table is mutating, trigger/function may not see it

    Hello All!

    Calling the following statement:
    PHP Code:
    begin      
              INSERT INTO FEATURE
    (FEATURE_KEYID_FEATURE_BASEVERSIONEXPIRE_DATENUMLIC
                
    VALUES ('testKey6'1'2004.067''12-Jun-2004'28);    
    end
    i've got these errors:

    ORA-04091: table FEATURE_AVAILABLE is mutating, trigger/function may not see it
    ORA-06512: at "PRC_CHECK_AVAIL_EXPIRED", line 10
    ORA-06512: at "TRG_FEATURE_AVAILABLE_AFT_INS", line 7
    ORA-04088: error during execution of trigger 'TRG_FEATURE_AVAILABLE_AFT_INS'
    ORA-06512: at "TRG_FEATURE_AFT_INS", line 2
    ORA-04088: error during execution of trigger 'TRG_FEATURE_AFT_INS'
    ORA-06512: at line 2


    I cannont find my error, maybe it's something i've overlooked?

    It would be greate if somebody could help me!

    Thanks in advance!



    ###############################################
    ###############################################
    MY TABLES:
    ###############################################
    ###############################################

    ################
    FEATURE_AVAILABLE
    ################
    PHP Code:
    CREATE TABLE FEATURE_AVAILABLE
    (
      
    ID_FEATURE       INTEGER,
      
    FEATURE_KEY      VARCHAR2(25),
      
    ID_FEATURE_BASE  INTEGER,
      
    NUM_AVAIL        INTEGER,
      
    EXPIRE_DATE      DATE,
      
    CREATED_ON       DATE,
      
    CREATED_BY       VARCHAR2(20),
      
    CHANGED_ON       DATE,
      
    CHANGED_BY       VARCHAR2(20)
    );

    ALTER TABLE FEATURE_AVAILABLE ADD (
      
    CONSTRAINT PK_FEATURE_AVAILABLE PRIMARY KEY (ID_FEATURE));


    ALTER TABLE FEATURE_AVAILABLE ADD (
      
    CONSTRAINT FK_FAVAIL_FEATURE FOREIGN KEY (ID_FEATURE
        
    REFERENCES FEATURE (ID_FEATURE));



    CREATE OR REPLACE TRIGGER TRG_FEATURE_AVAILABLE_AFT_INS 
    AFTER INSERT ON FEATURE_AVAILABLE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    IdFeature INTEGER;
    BEGIN
         
    --IdFeature := :OLD.ID_FEATURE;
         
    IdFeature := :NEW.ID_FEATURE;
         
         
    PRC_CHECK_AVAIL_EXPIRED(IdFeature);
    END;
    /

    CREATE OR REPLACE TRIGGER TRG_FEATURE_AVAILABLE_BEF_UPD 
    BEFORE UPDATE ON FEATURE_AVAILABLE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;

    BEGIN
         tmpVar1 
    := 0;
         
      
    SELECT SEQ_FEATURE_AVAILABLE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;
    END;

    ################
    FEATURE
    ################

    PHP Code:
    CREATE TABLE FEATURE
    (
      
    ID_FEATURE        INTEGER,
      
    FEATURE_KEY       VARCHAR2(25CONSTRAINT NN_FEATURE_FKEY NOT NULL,
      
    ID_FEATURE_BASE   INTEGER CONSTRAINT NN_FEATURE_IDFBASE NOT NULL,
      
    VERSION           VARCHAR2(10CONSTRAINT NN_FEATURE_VERSION NOT NULL,
      
    EXPIRE_DATE       DATE CONSTRAINT NN_FEATURE_EXPDATE NOT NULL,
      
    NUMLIC            INTEGER CONSTRAINT NN_FEATURE_NUMLIC NOT NULL,
      
    CREATED_ON        DATE,
      
    CREATED_BY        VARCHAR2(20),
      
    CHANGED_ON        DATE,
      
    CHANGED_BY        VARCHAR2(20)
      --  ,  ...
    );

    ALTER TABLE FEATURE ADD (
      
    CONSTRAINT PK_FEATURE PRIMARY KEY (ID_FEATURE));

    ALTER TABLE FEATURE ADD (
      
    CONSTRAINT FK_FEATURE_FBASE FOREIGN KEY (ID_FEATURE_BASE
        
    REFERENCES FEATURE_BASE (ID_FEATURE_BASE));


    CREATE UNIQUE INDEX UK_FEATURE_FKEY ON FEATURE
    (FEATURE_KEY);


    CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_INS
    AFTER INSERT ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
        INSERT INTO FEATURE_AVAILABLE 
    (ID_FEATUREFEATURE_KEYID_FEATURE_BASE
                          
    NUM_AVAILEXPIRE_DATE
                    
    CREATED_ONCREATED_BYCHANGED_ONCHANGED_BY
                     

                  
    VALUES (SEQ_FEATURE.CURRVAL, :NEW.FEATURE_KEY, :NEW.ID_FEATURE_BASE
                       
    , :NEW.NUMLIC, :NEW.EXPIRE_DATE
                    
    SYSDATEUSERSYSDATEUSER
                     
    );
    END;
    /

    CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_UPD 
    AFTER UPDATE ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
     UPDATE FEATURE_AVAILABLE 
     SET FEATURE_KEY 
    = :NEW.FEATURE_KEY
       
    ID_FEATURE_BASE =:NEW.ID_FEATURE_BASE
       
    NUM_AVAIL =:NEW.NUMLIC
       
    EXPIRE_DATE =:NEW.EXPIRE_DATE
       
    CREATED_ON SYSDATE
       
    CREATED_BY USER 
       
    CHANGED_ON SYSDATE
       
    CHANGED_BY USER
       WHERE ID_FEATURE 
    = :NEW.ID_FEATURE;
    END;
    /


    CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_INS 
    BEFORE INSERT ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;
    BEGIN
         tmpVar1 
    := 0
      
    SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    ID_FEATURE := tmpVar1
        :NEW.
    CREATED_ON := SYSDATE;
        :NEW.
    CREATED_BY := USER;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;

    END;
    /

    CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_UPD 
    BEFORE UPDATE ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;
    BEGIN
         tmpVar1 
    := 0
      
    SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;
    END;

    Regards,
    Julia

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    The error is because of the DML operation on the same table that is having trigger on it. u can use autonomous transaction in the trigger
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Praveen,

    thank you for your answer.

    Can you explain please, what do you mean with "autonomous transaction in the trigger"?


    Thanks!
    Regards,
    Julia

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Initial insert into 'feature' table triggers database trigger 'trg_feature_aft_ins' which collects data from the same table ('feature') and does something with it.
    As inserting statement is not finished yet, state of data in 'feature' table is not known; such a thing is called a "mutating table". Annoying thing, really.

    Praveen suggested you to use a pragma - directive to the compiler. Pragma AUTONOMOUS_TRANSACTION makes a particular PL/SQL code autonomous from the calling environment. One of characteristical use is to log errors - you want to insert data into your error log table. If you omit this pragma, an error causes rollback so that your "main" procedure rolls back, but it rolls back insert into error log table. Therefore, the code would be:
    PHP Code:
    declare
      
    pragma autonomous_transaction,
    begin
      insert into error_log 
    (...);
      
    commit;
    end
    It will commit insert into error_log, but this commit won't affect your main transaction.

    However, I prefer use of INDEX-BY table (formerly known as PL/SQL table) during mutating tables problem ...

    I've got an example so I'll post it here. You should change it to fit your situation.

    First, create a type:
    PHP Code:
    CREATE OR REPLACE package pkg_mdate IS
       TYPE mdate_tab_type IS TABLE OF NUMBER
          INDEX BY BINARY_INTEGER
    ;
       
    mdate_tab     mdate_tab_type;
       
    mdate_index   BINARY_INTEGER;
    END pkg_mdate
    Now, create three triggers. First one initials index-by table. Second one will populate it, and third reads the table and executes the code (in this example calls a procedure within the package) that caused mutating table problem.
    PHP Code:
    CREATE OR REPLACE TRIGGER mdate_1_BEF_STM
      BEFORE UPDATE 
    OR INSERT ON fixed_deposit
    BEGIN
      pkg_mdate
    .mdate_index := 0;
    END;
    CREATE OR REPLACE TRIGGER mdate_2_AFT_ROW
      AFTER UPDATE 
    OR INSERT ON fixed_deposit
      
    FOR EACH ROW
    BEGIN
      pkg_mdate
    .mdate_index := pkg_mdate.mdate_index 1;
      
    pkg_mdate.mdate_tab(pkg_mdate.mdate_index) := :new.mdate_id;
    END;
    CREATE OR REPLACE TRIGGER mdate_3_AFT_STM
      AFTER UPDATE 
    OR INSERT ON fixed_deposit
    BEGIN
      
    FOR i IN 1 .. pkg_mdate.mdate_index LOOP
        
    -- this procedure does the job which (without use of PL/SQL tablecauses
        
    -- a mutating table problem
        pkg_mdate
    .obrangocit (pkg_mdate.mdate_tab(i));
      
    END LOOP;
      
    pkg_mdate.mdate_index := 0;
    END

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Littlefoot,
    thank you very much for your answer and help!

    But i have one question:

    What does pkg_mdate.obrangocit ?

    Thanks!
    Last edited by julla27; 06-04-04 at 05:46.
    Regards,
    Julia

  6. #6
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello!

    I've droped my procedure, changed my trigger into:

    PHP Code:

    CREATE 
    OR REPLACE TRIGGER TRG_FEATURE_AVAILABLE_AFT_INS_1 
    AFTER INSERT ON FEATURE_AVAILABLE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    IdFeature INTEGER;
         
    NumAvail  INTEGER;
         
         
    DateExpired DATE;
         
    NumDays     NUMBER;
         
    Message      VARCHAR2(250);
    BEGIN
              
         IdFeature 
    := :NEW.ID_FEATURE;
         
    NumAvail := :NEW.NUM_AVAIL;
         
    DateExpired := :NEW.EXPIRE_DATE;
         
         
    NumDays := round(DateExpired SYSDATE);
          
         IF 
    NumDays >= 0 THEN
             Message 
    := 'In ' || NumDays || ' Tagen läuft ab ';
         ELSE
            
    Message := 'Abgelaufen!';
         
    END IF;    
         

         IF 
    NumDays 3 Then
            
    --- do nothing 
                PRC_SEND_MAIL
    ('sender''recpt''OK!');
            
    ELSIF NumDays AND NumDays 0  THEN 
            
    -- MAIL 'In ' || (DateExpired SYSDATE) || '  Tagen läuft ab '
                
    PRC_SEND_MAIL('sender''recpt'Message);
               
    ELSIF Numdays 0  THEN
            
    -- MAIL 'HEUTE läuft ab ' 
                
    PRC_SEND_MAIL('sender''recpt'Message);
            
    ELSIF Numdays 0  THEN
            
    -- MAIL 'Abgelaufen'
               
    PRC_SEND_MAIL('sender''recpt'Message);
               
    NumAvail := 0;  
            ELSE
                
    RAISE_APPLICATION_ERROR(-12345,'Expired Date Definition Error');
            
    END IF;
    END
    and anything works!


    Thanks, Praveen and Littlefoot, for your help!!!
    Regards,
    Julia

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by julla27
    What does pkg_mdate.obrangocit ?
    It does nothing you should be concerned of ... it is procedure in my package; I just used copy-paste from my own mutating table solution.
    You would, instead of this procedure, write your INSERT INTO ... statement here.

    OK, never mind. I'm glad you fixed the problem

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank you!
    Have a nice day, Littlefoot!
    Regards,
    Julia

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You're welcome.
    The same goes to you too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •