Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: Error in Trigger

    Hi

    I am getting this error when I am calling this trigger (deleting a record in t_user)

    ORA-04091: table FOLDERDBA01.T_USER is mutating, trigger/function may not see it
    ORA-06512: at "FOLDERDBA01.TR_TRANSFER_FOLDER_TO_MGR", line 20
    ORA-04088: error during execution of trigger 'FOLDERDBA01.TR_TRANSFER_FOLDER_TO_MGR'

    The trigger is as follows:

    CREATE OR REPLACE TRIGGER FOLDERDBA01.TR_TRANSFER_FOLDER_TO_MGR
    BEFORE DELETE
    ON FOLDERDBA01.T_user
    REFERENCING OLD AS OLD
    FOR EACH ROW
    DECLARE
    var_folder_id NUMBER(8);
    var_server_id NUMBER(8);
    var_all_folder_id NUMBER(8);
    var_all_server_id NUMBER(8);
    var_Manager_id NUMBER(8);



    cursor user_folders is
    select folder_id,server_id from t_folder where created_by = :old.GEORACLEHRID;

    cursor user_all_access_folders is
    Select FOLDER_ID,SERVER_ID FROM T_FOLDER START With FOLDER_ID = var_folder_id AND SERVER_ID = var_server_id
    CONNECT BY PRIOR FOLDER_ID = PARENT_FOLDER_ID;

    BEGIN


    select gehrsupervisorid into var_manager_id from t_user where georaclehrid = :old.georaclehrid;

    open user_folders;
    loop
    fetch user_folders into var_folder_id, var_server_id ;

    open user_all_access_folders;
    loop
    fetch user_all_access_folders into var_all_folder_id, var_all_server_id ;
    --Give same access to manager in all the sub folder
    INSERT INTO T_OBJECT_ACCESS(object_id,object_type,member_id,me mber_type,access_type,server_id,date_regd)
    Select object_id,object_type,var_Manager_id,member_type,a ccess_type,server_id,sysdate from t_object_access where member_id = :old.georaclehrid and object_id = var_all_folder_id and server_id = var_all_server_id;

    end loop;
    close user_all_access_folders;


    update t_folder set created_by = var_manager_id where folder_id= var_folder_id and server_id = var_server_id;

    end loop;
    close user_folders;
    END ;


    Would really appreciate if you can help me on this.

    Thanks
    Smit

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    The error is on the line "select gehrsupervisorid into var_manager_id from t_user where georaclehrid = ld.georaclehrid;"
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try:
    Code:
    var_manager_id := :old.gehrsupervisorid;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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