Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Red face Unanswered: SQL Error: 4091, SQLState:table is mutating, trigger/function may not see it

    I have a trigger written by someone else in my organization
    Am very new to PL/SQL
    It has a reference to the same table for which the trigger is made, as far as i've read i need to change that reference. ie the query which uses the same table.
    Can someone help me with that... ?
    Following is my trigger:

    create or replace
    TRIGGER OCEAN_ADM.TR_BUI_PSCLST BEFORE INSERT ON PARAMSPEC_LISTE
    FOR EACH ROW
    DECLARE

    v_jpaidobj JEUPARAM.JPA_IDOBJ%TYPE;
    v_modid MODELE.MOD_ID%TYPE;
    v_modlib MODELE.MOD_LIB%TYPE;
    v_ppalib PORTEEPARAM.PPA_LIB%TYPE;
    v_pallib PALIER.PAL_LIB%TYPE;
    v_parlib PARAMETRE.PAR_LIB%TYPE;
    v_jpaeswid JEUPARAM.ESW_ID%TYPE;
    v_direct NUMBER := 0;
    v_version NUMBER(9);
    no_vcount NUMBER;
    no_vcount_up NUMBER;
    str_tmp1 VARCHAR2(100):=null;
    str_tmp2 VARCHAR2(100):=null;
    max_flag number(5) :=1;
    max_count number(5);

    v_valstd varchar2(4000);

    valspec_old varchar2(4000);
    valspec_new varchar2(4000);

    v_parsp_arch varchar2(4000);

    cursor comma_seprate1 (valStdStrold varchar2) is
    select sz_text from TABLE(CAST(fn_comma_to_table(valStdStrold,',') as TYP_ARR_COMMA_SEP_STRING));

    cursor comma_seprate2 (valStdStrnew varchar2) is
    select sz_text from TABLE(CAST(fn_comma_to_table(valStdStrnew,',') as TYP_ARR_COMMA_SEP_STRING));


    /*================================================= =============*/
    /* Type : TRIGGER */
    /* Nom : TR_BUI_PSCLST */
    /*================================================= =============*/
    /* Projet : OCEAN */
    /* Auteur : ANOOP AWASTHI */
    /* Date : 16/03/2002 */
    /* Description : Trigger sur la table des parametres specifiques*/
    /* Il permet la mise a jour du QUAND de la table et la version */
    /* de la table permet l historisation des maj des parametrages */
    /* qui sont deja ou deviennent specifiques. */
    /*================================================= =============*/
    /* tables mises a jour : PARAMSPEC, ARCH_PARAM */
    /* tables utilisees : JEUPARAM, MODELE, PORTEEPARAM, PALIER, */
    /* PARAMETRE, PARAMSTDMOD. */
    /*================================================= =============*/
    /* Historique des modifications : */
    /*
    /*================================================= =============*/


    BEGIN

    BEGIN
    INSERT INTO PARAMSPEC_LISTE_NEWTMP
    VALUES (:NEW.JPA_ID,:NEW.PAR_ID,:NEW.PSCLST_VALSPEC,:NEW. FLAG);
    EXCEPTION
    WHEN OTHERS THEN RAISE;
    END;

    IF :NEW.PSCLST_QUAND IS NULL
    THEN
    :NEW.PSCLST_QUAND := SYSDATE;
    END IF;

    IF :NEW.PSCLST_VERSION IS NULL THEN

    :NEW.PSCLST_VERSION := 1;
    END IF;

    BEGIN
    SELECT PARAMSPEC_LISTE.PSCLST_VERSION INTO v_version
    FROM PARAMSPEC_LISTE
    WHERE PARAMSPEC_LISTE.JPA_ID = :NEW.JPA_ID
    AND PARAMSPEC_LISTE.PAR_ID = :NEW.PAR_ID
    GROUP by PARAMSPEC_LISTE.PSCLST_VERSION;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_version :=0;

    END;


    select count(*) into no_vcount from PARAMSPEC_LISTE_OLDTMP;
    select count(*) into no_vcount_up from PARAMSPEC_LISTE_OLDTMP_UP;

    BEGIN
    SELECT JEUPARAM.JPA_IDOBJ, JEUPARAM.ESW_ID, MODELE.MOD_ID, MODELE.MOD_LIB, PORTEEPARAM.PPA_LIB, PALIER.PAL_LIB, PARAMETRE.PAR_LIB
    INTO v_jpaidobj, v_jpaeswid, v_modid, v_modlib, v_ppalib, v_pallib, v_parlib
    FROM JEUPARAM, MODELE, PORTEEPARAM, PALIER, PARAMETRE
    WHERE JEUPARAM.JPA_ID = :NEW.JPA_ID
    AND PARAMETRE.PAR_ID = :NEW.PAR_ID
    AND PARAMETRE.PPA_ID = JEUPARAM.PPA_ID
    AND MODELE.MOD_ID = JEUPARAM.MOD_ID
    AND MODELE.PPA_ID = PARAMETRE.PPA_ID
    AND MODELE.PAL_ID = PARAMETRE.PAL_ID
    AND PORTEEPARAM.PPA_ID = MODELE.PPA_ID
    AND PALIER.PAL_ID = MODELE.PAL_ID;
    EXCEPTION
    WHEN OTHERS THEN RAISE;
    END;

    BEGIN

    IF ((no_vcount_up<>0) and (:new.FLAG=1)) THEN


    IF (v_jpaeswid = 0 AND v_ppalib NOT IN ('Cellule TRX', 'Cellule TDMA')) THEN


    BEGIN
    SELECT rtrim (xmlagg (xmlelement (e, PARAMSPEC_LISTE_OLDTMP_UP.PSCLST_VALSPEC || ',')).extract ('//text()'), ',') INTO valspec_old
    FROM PARAMSPEC_LISTE_OLDTMP_UP
    WHERE PARAMSPEC_LISTE_OLDTMP_UP.JPA_ID = :new.JPA_ID
    AND PARAMSPEC_LISTE_OLDTMP_UP.PAR_ID = :new.PAR_ID;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN

    RAISE;
    END;


    BEGIN
    SELECT rtrim (xmlagg (xmlelement (e, PARAMSPEC_LISTE_NEWTMP.PSCLST_VALSPEC || ',')).extract ('//text()'), ',') INTO valspec_new
    FROM PARAMSPEC_LISTE_NEWTMP
    WHERE PARAMSPEC_LISTE_NEWTMP.JPA_ID = :new.JPA_ID
    AND PARAMSPEC_LISTE_NEWTMP.PAR_ID = :new.PAR_ID;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN

    RAISE;
    END;



    IF NOT (Pc_Ocean_Fonctions.verif_equal(valspec_new,valspe c_old)) THEN

    :NEW.PSCLST_VERSION := v_version;


    BEGIN

    SELECT rtrim (xmlagg (xmlelement (e, ARCH_PARAM_LISTE.APM_LISTE_VALAPR || ',')).extract ('//text()'), ',') INTO v_parsp_arch
    FROM ARCH_PARAM_LISTE
    WHERE ARCH_PARAM_LISTE.APM_LISTE_IDOBJ = v_jpaidobj
    AND ARCH_PARAM_LISTE.PPA_LIB = v_ppalib
    AND ARCH_PARAM_LISTE.APM_LISTE_PALAVT = v_pallib
    AND ARCH_PARAM_LISTE.APM_LISTE_MODAVT = v_modlib
    AND ARCH_PARAM_LISTE.APM_LISTE_PARAMLIB = v_parlib
    AND ARCH_PARAM_LISTE.APM_LISTE_TYPEMODIF = PC_OCEAN_API_MAJ.cst_type_maj_spf_spf
    AND TO_CHAR(ARCH_PARAM_LISTE.APM_LISTE_QUAND,'dd/mm/yyyy hh:mi:ss') = TO_CHAR(sysdate,'dd/mm/yyyy hh:mi:ss');
    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    v_parsp_arch:=NULL;
    END;


    IF (v_parsp_arch is null) THEN

    BEGIN
    select max(APM_LISTE_FLAG) into max_flag from arch_param_liste
    WHERE ARCH_PARAM_LISTE.APM_LISTE_MODAVT = v_modlib
    AND ARCH_PARAM_LISTE.APM_LISTE_PARAMLIB = v_parlib;


    EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE;
    WHEN OTHERS THEN RAISE;
    END;

    delete ARCH_PARAM_LISTE WHERE ARCH_PARAM_LISTE.APM_LISTE_FLAG = max_flag;

    BEGIN
    select max(APM_LISTE_FLAG) into max_flag from arch_param_liste
    WHERE ARCH_PARAM_LISTE.APM_LISTE_MODAVT = v_modlib
    AND ARCH_PARAM_LISTE.APM_LISTE_PARAMLIB = v_parlib;

    if max_flag is null then
    max_flag:=1;
    else
    max_flag:=max_flag+1;
    end if;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE;
    WHEN OTHERS THEN RAISE;
    END;


    select max(cnt) into max_count
    from (
    select count(sz_text) as cnt from TABLE(CAST(fn_comma_to_table(valspec_old,',') as TYP_ARR_COMMA_SEP_STRING))
    UNION
    select count(sz_text) as cnt from TABLE(CAST(fn_comma_to_table(valspec_new,',') as TYP_ARR_COMMA_SEP_STRING))
    );

    BEGIN

    open comma_seprate1(valspec_old);
    open comma_seprate2(valspec_new);

    for i in 1..max_count
    loop

    fetch comma_seprate1 into str_tmp1;
    if( comma_seprate1%notfound ) then
    str_tmp1:=null;
    end if;

    fetch comma_seprate2 into str_tmp2;
    if(comma_seprate2%notfound) then
    str_tmp2:=null;
    end if;


    INSERT INTO ARCH_PARAM_LISTE(APM_LISTE_IDOBJ,
    PPA_LIB,
    APM_LISTE_PALAVT,
    APM_LISTE_PALAPR,
    APM_LISTE_MODAVT,
    APM_LISTE_MODAPR,
    APM_LISTE_RANG,
    APM_LISTE_PARAMLIB,
    APM_LISTE_VALAVT,
    APM_LISTE_VALAPR,
    APM_LISTE_FLAG,
    JUS_ID,
    APM_LISTE_COMMENT,
    APM_LISTE_TYPEMODIF,
    APM_LISTE_DIID,
    APM_LISTE_DMID,
    APM_LISTE_QUI,
    APM_LISTE_QUAND
    )
    VALUES (v_jpaidobj,
    v_ppalib,
    v_pallib,
    v_pallib,
    v_modlib,
    v_modlib,
    NULL,
    v_parlib,
    str_tmp1,
    str_tmp2,
    max_flag,
    :NEW.JUS_ID,
    :NEW.PSCLST_COMMENT,
    PC_OCEAN_API_MAJ.cst_type_maj_spf_spf,
    :NEW.PSCLST_NUMDI,
    NULL,
    :NEW.PSCLST_QUI,
    SYSDATE
    );
    end loop;

    close comma_seprate1;
    close comma_seprate2;

    DELETE PARAMSPEC_LISTE_OLDTMP_UP;
    DELETE PARAMSPEC_LISTE_NEWTMP;

    EXCEPTION
    WHEN OTHERS THEN
    DELETE PARAMSPEC_LISTE_OLDTMP_UP;
    DELETE PARAMSPEC_LISTE_NEWTMP;
    RAISE;
    END;

    END IF;
    END IF;

    END IF;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;

    BEGIN

    IF ((no_vcount=0) and (:new.FLAG=1) and (no_vcount_up=0)) THEN

    :NEW.PSCLST_VERSION := v_version +1;

    IF (v_jpaeswid = 0 AND v_ppalib NOT IN ('Cellule TRX', 'Cellule TDMA')) THEN

    BEGIN

    SELECT rtrim (xmlagg (xmlelement (e, PARAMSTDMOD_LISTE.PSMLST_VALSTD || ',')).extract ('//text()'), ',') INTO v_valstd
    FROM PARAMSTDMOD_LISTE
    WHERE PARAMSTDMOD_LISTE.PAR_ID = :NEW.PAR_ID
    AND PARAMSTDMOD_LISTE.MOD_ID = v_modid;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE;
    WHEN OTHERS THEN RAISE;
    END;



    ---------------------------------------------------------
    If you find some more problems please do help me.. with that as well ...http://www.dbforums.com/db_images_v3...milies/eek.gif

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    to avoid getting MUTATING TABLE error do not do SQL against same table that 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.

Posting Permissions

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