Results 1 to 9 of 9

Thread: Mutating error

  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Unanswered: Mutating error

    I have a specific requirement as stated below for which i have written a trigger so when user manipulates the column p, q values in table b the corrresponding columns in table a ( x ) should be updated accordingly.

    a - b
    -- --
    x p+q

    CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
    ON B
    FOR EACH ROW
    BEGIN
    PKG_FCI_APP.update_fmly_income_variables;
    DBMS_OUTPUT.PUT_LINE('TRIGGER FIRED UPON THE UPDATION OF THE COLUMNS ');
    END ;


    UPDATE B SET SALARYX = 5000 WHERE FAMID = 110904434407 AND MEMBNO = 2;



    SQL Error: ORA-20006: An error was encountered - -4091 -ERROR- ORA-04091: table B is mutating, trigger/function may not see it

    Pls let me know .

    thanks/kumar

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MIKELALA View Post
    the corrresponding columns in table a ( x ) should be updated accordingly.
    You say you want to update table A, but in the trigger you update table b: UPDATE B ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    within trigger you are precluded from doing any SQL against the table upon which the trigger is based.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2009
    Posts
    56

    Mutating error !!

    As you see the requirement,

    when user manipulates the column p, q values in table b the corrresponding columns in table a ( x ) should be updated accordingly.

    a - b
    -- --
    x p+q

    So the trigger should be on B table only right ? so that when the user tries to update the columns p, or q then the trigger has to fire and the procudure should update the table table a.x with the modified values of p and q .

    The below statement is a part of procedure

    UPDATE B SET SALARYX = 5000 WHERE FAMID = 110904434407 AND MEMBNO = 2;


    so when the user updates he table B, the trgger fires and updates with column x with the modified values of p and q .


    let me know whether my view of understanding is correct or not .

    thanks/mike

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >let me know whether my view of understanding is correct or not .
    ESCHEW OBFUSCATION!

    >CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
    ON B

    >when user manipulates the column p, q values in table b

    Please post DDL for both tables
    I have no idea what columns exist where.

    Please post code for PKG_FCI_APP.update_fmly_income_variables;

    Simply put, when Oracle throws error(s) your code needs to be changed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MIKELALA View Post
    when user manipulates the column p, q values in table b the corrresponding columns in table a ( x ) should be updated accordingly.
    ...
    UPDATE B SET SALARYX = 5000 WHERE FAMID = 110904434407 AND MEMBNO = 2;
    Again my question:
    you are telling us you want to update table A but your update statement is against table B.

    Could you please use real table names?

    If you want to modify columns from the same row from the same table in a trigger, you don't need an UPDATE statement. Simply assign the value to the "new" row:


    so when the user updates he table B, the trgger fires and updates with column x with the modified values of p and q .
    Again you are only mentioning table B but not table A.
    If you want to change table b, then simply do:

    new.SALARYX = 5000;

  7. #7
    Join Date
    Nov 2009
    Posts
    56

    mutating error

    Here is the requirement.. when the user updates any of the columns of MINC the trigger should fire and update the FINC columns

    LHS = RHS .

    note:-

    The tables FINC and MINC are having the columns as stated below .. except that both the tables have FAMID as a column which is primary key


    In RHS , The user is updating the values provided in brackets () of MINC table
    once updated, the trigger should fire and calculates the summation and updates the columns of FINC. This is my requirement.

    note:-

    The column FAMID is the key for both the tables.

    =========================================
    FSALARYX (FINC) = (MINC)sum(SALARYX + SALARYBX)
    FNONFRMX (FINC) = (MINC)sum (NONFARMX + NONFRMBX)
    FFRMINCX (FINC) = (MINC)sum (FARMINCX + FRMINCBX)
    FRRETIRX (FINC) = (MINC)sum (RRRETIRX + RRRETRBX + SOCCRX)
    FINDRETX (FINC) = (MINC)sum (INDRETX)
    FJSSDEDX (FINC) = (MINC)sum (JSSDEDX)
    FSSIX (FINC) = (MINC)sum (SSIX + SSIBX)
    =========================================
    The trigger i have written is as follows :-

    ---
    CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
    ON MINC
    FOR EACH ROW
    DECLARE
    -- the right hand side parameters are from user interface
    gv_temp_famid number:= :new.famid;
    gv_temp_salaryx number:= :new.salaryx;
    gv_temp_salarybx number:= :new.salarybx;
    gv_temp_nonfarmx number:= :new.nonfarmx;
    gv_temp_nonfrmbx number:= :new.nonfrmbx;
    gv_temp_farmincx number:= :new.farmincx;
    gv_temp_frmincbx number:= :new.frmincbx;
    gv_temp_rrretirx number:= :new.rretirx;
    gv_temp_rrretrbx number:= :new.rretrbx;
    gv_temp_socrrx number:= :new.socrrx;
    gv_temp_indretx number:= :new.indretx;
    gv_temp_jssdedx number:= :new.jssdedx;
    gv_temp_ssix number:= :new.ssix;
    gv_temp_ssibx number:= :new.ssibx;

    BEGIN
    PKG_FCI_APP.update_fmly_income_variables(gv_temp_f amid ,
    gv_temp_salaryx ,
    gv_temp_salarybx,
    gv_temp_nonfarmx,
    gv_temp_nonfrmbx,
    gv_temp_farmincx,
    gv_temp_frmincbx,
    gv_temp_rrretirx,
    gv_temp_rrretrbx,
    gv_temp_socrrx,
    gv_temp_indretx,
    gv_temp_jssdedx,
    gv_temp_ssix ,
    gv_temp_ssibx ) ;
    DBMS_OUTPUT.PUT_LINE('TRIGGER FIRED UPON THE UPDATION OF THE COLUMNS ');
    END ;
    ----

    The procedure is as follows:-
    ---
    create or replace
    PACKAGE BODY PKG_FCI_APP AS

    function chk_notnull_blank ( colname IN number ) return number is
    BEGIN
    if ( colname is NOT NULL and colname not in ( -8E14, -7E14, -6E14, -5E14, -4E14, -3E14, -2E14, -1E14, -1E9 )) then
    RETURN colname ;
    else
    RETURN 0;
    end if;
    END chk_notnull_blank;

    procedure update_fmly_income_variables
    (gv_temp_famid number,
    gv_temp_salaryx number,
    gv_temp_salarybx number,
    gv_temp_nonfarmx number,
    gv_temp_nonfrmbx number,
    gv_temp_farmincx number,
    gv_temp_frmincbx number,
    gv_temp_rrretirx number,
    gv_temp_rrretrbx number,
    gv_temp_socrrx number,
    gv_temp_indretx number,
    gv_temp_jssdedx number,
    gv_temp_ssix number,
    gv_temp_ssibx number) is

    cursor c1 is select FAMID, SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX from MINC where famid = gv_temp_famid ;
    cursor c2 is select FAMID, FSALARYX, FNONFRMX, FFRMINCX, FRRETIRX, FINDRETX, FJSSDEDX, FSSIX from FINC where famid = gv_temp_famid;
    v_flag_boolean boolean := false;
    v_famid number := 0 ;
    v_temp_sum_fsalaryx number := 0;
    v_temp_sum_fnonfrmx number := 0;
    v_temp_sum_ffrmincx number := 0;
    v_temp_sum_frretirx number := 0;
    v_temp_sum_findretx number := 0;
    v_temp_sum_fjssdedx number := 0;
    v_temp_sum_fssix number := 0;
    BEGIN

    for i in c2 loop

    if ( i.famid = gv_temp_famid ) then
    v_flag_boolean := true;
    v_famid := gv_temp_famid;
    v_temp_sum_fsalaryx := v_temp_sum_fsalaryx + chk_notnull_blank (gv_temp_salaryx) + chk_notnull_blank (gv_temp_salarybx);
    v_temp_sum_fnonfrmx := v_temp_sum_fnonfrmx + chk_notnull_blank(gv_temp_nonfarmx)+ chk_notnull_blank (gv_temp_nonfrmbx);
    v_temp_sum_ffrmincx := v_temp_sum_ffrmincx + chk_notnull_blank(gv_temp_farmincx) + chk_notnull_blank(gv_temp_frmincbx);
    v_temp_sum_frretirx := v_temp_sum_frretirx + chk_notnull_blank (gv_temp_rrretirx) + chk_notnull_blank(gv_temp_rrretrbx) + chk_notnull_blank(gv_temp_socrrx);
    v_temp_sum_findretx := v_temp_sum_findretx + chk_notnull_blank(gv_temp_indretx);
    v_temp_sum_fjssdedx := v_temp_sum_fjssdedx + chk_notnull_blank(gv_temp_jssdedx);
    v_temp_sum_fssix := v_temp_sum_fssix + chk_notnull_blank(gv_temp_ssix ) + chk_notnull_blank(gv_temp_ssibx);

    end if ;
    end loop ;

    update FINC set fsalaryx = v_temp_sum_fsalaryx WHERE famid = v_famid ;
    update FINC set fnonfrmx = v_temp_sum_fnonfrmx WHERE famid = v_famid ;
    update FINC set ffrmincx = v_temp_sum_ffrmincx WHERE famid = v_famid ;
    update FINC set frretirx = v_temp_sum_frretirx WHERE famid = v_famid ;
    update FINC set findretx = v_temp_sum_findretx WHERE famid = v_famid ;
    update FINC set fjssdedx = v_temp_sum_fjssdedx WHERE famid = v_famid ;
    update FINC set fssix = v_temp_sum_fssix WHERE famid = v_famid ;


    v_temp_sum_fsalaryx := 0;
    v_temp_sum_fnonfrmx := 0;
    v_temp_sum_ffrmincx := 0;
    v_temp_sum_frretirx := 0;
    v_temp_sum_findretx := 0;
    v_temp_sum_fjssdedx := 0;
    v_temp_sum_fssix := 0;



    EXCEPTION
    when others then
    raise_application_error(-20006,' An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    v_err_code := SQLCODE;
    v_err_msg := substr(SQLERRM, 1, 200);
    INSERT INTO audit_table (error_number, error_message) VALUES (v_err_code, v_err_msg);

    end update_fmly_income_variables ;


    ---

    Hope the code helps ..

    Let me know where i am going wrong ..
    thanks/mike
    Last edited by MIKELALA; 08-26-10 at 13:18.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please edit your post and format the SQL using [code] tags.

    The way you posted it, it is unreadable.

  9. #9
    Join Date
    Nov 2009
    Posts
    56

    Mutating trigger

    Hello shammat ,

    I have modified the code .

    Please review and let me know .

    thanks/mike

Posting Permissions

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