locked
oracle trigger RRS feed

  • Question

  • User-2084712455 posted

    I have applied a trigger on a table whose task is to update one column of that inserted row and then insert the same updated row in another table. But once I execute insert query get this error.

    Error starting at line 1 in command:
    insert into INT_APPLICATIONDETAILS(sapplicationno,bbalancede,baddonforms,bmer,sdocumentid,bcompleted,ibusinesstype,iproductid,dtreceiptdate,dttrigger)
    values('3333333333',0,1,0,'090003e88000c05b',0,1,11011,'5-APR-13','5-APR-13')
    Error report:
    SQL Error: ORA-04091: table MDE_UAT.INT_APPLICATIONDETAILS is mutating, trigger/function may not see it
    ORA-06512: at "MDE_UAT.TR_MOVEAPPLICATIONNO", line 6
    ORA-04088: error during execution of trigger 'MDE_UAT.TR_MOVEAPPLICATIONNO'
    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
    *Cause: A trigger (or a user defined plsql function that is referenced in
    this statement) attempted to look at (or modify) a table that was
    in the middle of being modified by the statement which fired it.
    *Action: Rewrite the trigger (or function) so it does not read that table.

    Please help me out...

    Thursday, May 2, 2013 8:12 AM

Answers

  • User-2005691517 posted

    Your error is due to the following:

    update INT_APPLICATIONDETAILS set bcompleted =1 where bcompleted=0 and sapplicationno=papplicationno;

    Rewrite the trigger as follows (not tested):

    CREATE OR REPLACE
    TRIGGER "TR_MOVEAPPLICATIONNO" 
    BEFORE INSERT ON INT_APPLICATIONDETAILS 
    FOR EACH ROW
    
    BEGIN
    
    IF :new.BCOMPLETED=0 then
    :new.BCOMPLETED :=1;
    end if;
    
    INSERT into TBLMDEWORKSTATUS_HST (IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE)
    SELECT IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE
    from TBLMDEWORKSTATUS where SAPPLICATIONNO = :new.SAPPLICATIONNO;
    
    DELETE from TBLMDEWORKSTATUS where SAPPLICATIONNO = :new.SAPPLICATIONNO;
    
    INSERT into TBLMDEWORKSTATUS
    (IMDEID,SAPPLICATIONNO,BBALANCEDE,BADDONFORM,BMER,SDOCUMENTID,BCOMPLETE,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE) 
    values
    (:new.ISEQID,:new.SAPPLICATIONNO,:new.BBALANCEDE,:new.BADDONFORMS,:new.BMER,:new.SDOCUMENTID,:new.BCOMPLETED,:new.IBUSINESSTYPE,:new.IPRODUCTID,:new.DTRECEIPTDATE);
    
    END;

    You need to use a before insert trigger and use the row values in :new.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 3, 2013 10:23 AM
  • User-2005691517 posted

    Try this

    CREATE or REPLACE TRIGGER TR_MOVEMEMBER
    
    AFTER INSERT ON INT_MEMBER 
    FOR EACH ROW
    
    BEGIN
    
    MERGE INTO TBLMEMBER T
    USING DUAL
    ON (T.SAPPLICATIONNO =:old.SAPPLICATIONNO and T.IMEMBERID = :old.IMEMBERID)
    
    WHEN MATCHED THEN
    UPDATE SET SFIRSTNAME = :old.SFIRSTNAME, SMIDDLENAME =:old.SMIDDLENAME,SLASTNAME =:old.SLASTNAME, DTDOB= :old.DTDOB,IGENDERID= :old.IGENDERID,IMARITALSTATUSID= :old.IMARITALSTATUSID
    
    WHEN NOT MATCHED THEN
    INSERT (SAPPLICATIONNO,IMEMBERID,SFIRSTNAME,SMIDDLENAME,SLASTNAME,DTDOB,IGENDERID,IMARITALSTATUSID)
    values
    (:old.SAPPLICATIONNO,:old.IMEMBERID,:old.SFIRSTNAME, :old.SMIDDLENAME, :old.SLASTNAME, :old.DTDOB, :old.IGENDERID, :old.IMARITALSTATUSID);
    
    END;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 17, 2013 9:27 AM

All replies

  • User-2005691517 posted

    Can you post the trigger script?

    Thursday, May 2, 2013 10:37 AM
  • User-2084712455 posted

    Here is my trigger:

    create or replace

    TRIGGER "TR_MOVEAPPLICATIONNO"
    AFTER INSERT ON INT_APPLICATIONDETAILS
    FOR EACH ROW
    DECLARE
    PApplicationNo varchar2(20) := :new.SAPPLICATIONNO;
    BEGIN
    update INT_APPLICATIONDETAILS set bcompleted =1 where bcompleted=0 and sapplicationno=papplicationno;

    insert into TBLMDEWORKSTATUS_HST (IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE)
    select IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE from TBLMDEWORKSTATUS where SAPPLICATIONNO = PApplicationNo;

    delete from TBLMDEWORKSTATUS where SAPPLICATIONNO = PApplicationNo;

    insert into TBLMDEWORKSTATUS (
    IMDEID,SAPPLICATIONNO,BBALANCEDE,BADDONFORM,BMER,SDOCUMENTID,BCOMPLETE,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE
    )

    select ISEQID,SAPPLICATIONNO,BBALANCEDE,BADDONFORMS,BMER,SDOCUMENTID,BCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE from int_applicationdetails where sapplicationno=PApplicationNo;

    END;

    Friday, May 3, 2013 5:28 AM
  • User-2005691517 posted

    Your error is due to the following:

    update INT_APPLICATIONDETAILS set bcompleted =1 where bcompleted=0 and sapplicationno=papplicationno;

    Rewrite the trigger as follows (not tested):

    CREATE OR REPLACE
    TRIGGER "TR_MOVEAPPLICATIONNO" 
    BEFORE INSERT ON INT_APPLICATIONDETAILS 
    FOR EACH ROW
    
    BEGIN
    
    IF :new.BCOMPLETED=0 then
    :new.BCOMPLETED :=1;
    end if;
    
    INSERT into TBLMDEWORKSTATUS_HST (IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE)
    SELECT IMDEID,SAPPLICATIONNO,ILOCKBYUSER,DTLOCKTAKEN,BADDONFORM,BBALANCEDE,BMER,BDOCTORSREFERRAL,BCOMPLETE,BSTATUS,DTLASTUPDATED,BDOCTORSCOMPLETE,SDOCUMENTID,BBALANCEDECOMPLETED,BADDONFORMCOMPLETED,BMERCOMPLETED,DTLOCKRELEASE,DTBALANCEDECOMPLETE,DTMERCOMPLETE,DTADDONFORMCOMPLETE,DTCOMPLETED,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE,DTDOCTORSCOMPLETE
    from TBLMDEWORKSTATUS where SAPPLICATIONNO = :new.SAPPLICATIONNO;
    
    DELETE from TBLMDEWORKSTATUS where SAPPLICATIONNO = :new.SAPPLICATIONNO;
    
    INSERT into TBLMDEWORKSTATUS
    (IMDEID,SAPPLICATIONNO,BBALANCEDE,BADDONFORM,BMER,SDOCUMENTID,BCOMPLETE,IBUSINESSTYPE,IPRODUCTID,DTRECEIPTDATE) 
    values
    (:new.ISEQID,:new.SAPPLICATIONNO,:new.BBALANCEDE,:new.BADDONFORMS,:new.BMER,:new.SDOCUMENTID,:new.BCOMPLETED,:new.IBUSINESSTYPE,:new.IPRODUCTID,:new.DTRECEIPTDATE);
    
    END;

    You need to use a before insert trigger and use the row values in :new.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 3, 2013 10:23 AM
  • User-2084712455 posted

    Thanks prashant its working........

    Tuesday, May 7, 2013 12:39 AM
  • User-2084712455 posted

    Hey Prashant,

    Here is my anohter trigger giving same type of errro.

    create or replace
    TRIGGER TR_MOVEMEMBER

    AFTER INSERT ON INT_MEMBER
    FOR EACH ROW
    DECLARE
    rowcnt number(20);
    BEGIN
    SELECT COUNT(SAPPLICATIONNO) into rowcnt FROM INT_MEMBER WHERE SAPPLICATIONNO = :new.SAPPLICATIONNO and IMEMBERID = :new.IMEMBERID;
    IF (rowcnt = 0) THEN
    insert into TBLMEMBER (SAPPLICATIONNO,IMEMBERID,SFIRSTNAME,SMIDDLENAME,SLASTNAME,DTDOB,IGENDERID,IMARITALSTATUSID)
    values
    (:new.SAPPLICATIONNO,:new.IMEMBERID,:new.SFIRSTNAME, :new.SMIDDLENAME, :new.SLASTNAME, :new.DTDOB, :new.IGENDERID, :new.IMARITALSTATUSID);
    else
    update TBLMEMBER set SFIRSTNAME = :new.SFIRSTNAME, SMIDDLENAME =:new.SMIDDLENAME,SLASTNAME =:new.SLASTNAME, DTDOB= :new.DTDOB,IGENDERID= :new.IGENDERID,IMARITALSTATUSID= :new.IMARITALSTATUSID
    WHERE SAPPLICATIONNO = :new.SAPPLICATIONNO and IMEMBERID = :new.IMEMBERID;
    END IF ;
    END;

    Error:

    Error starting at line 7 in command:
    insert into int_member(SAPPLICATIONNO,IMEMBERID,SFIRSTNAME,SMIDDLENAME,SLASTNAME,DTDOB,IGENDERID,IMARITALSTATUSID)
    values('1233211213',111,'pravesh','','dobhal','18-Apr-1987',1,1)
    Error report:
    SQL Error: ORA-04091: table MDE_UAT.INT_MEMBER is mutating, trigger/function may not see it
    ORA-06512: at "MDE_UAT.TR_MOVEMEMBER", line 4
    ORA-04088: error during execution of trigger 'MDE_UAT.TR_MOVEMEMBER'
    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
    *Cause: A trigger (or a user defined plsql function that is referenced in
    this statement) attempted to look at (or modify) a table that was
    in the middle of being modified by the statement which fired it.
    *Action: Rewrite the trigger (or function) so it does not read that table.

    Tuesday, May 7, 2013 1:07 AM
  • User-2084712455 posted

    Anybody....help me out

    Thursday, May 9, 2013 2:21 AM
  • User-2005691517 posted

    SELECT COUNT(SAPPLICATIONNO) into rowcnt FROM INT_MEMBER WHERE SAPPLICATIONNO = :new.SAPPLICATIONNO and IMEMBERID = :new.IMEMBERID;

    The above statement is your problem.

    From your code it appears to me that you are inserting a record into TBLMEMBER if it does not already exist but update the exising row if it already exists. I think you don't really need to check for existence of row in INT_MEMBER.

    Remove the existing SELECT, INSERT and UPDATE statements from your trigger and then use a MERGE statement to take care of the INSERT/UPDATE into TBLMEMBER.

    Saturday, May 11, 2013 12:40 AM
  • User-2084712455 posted

    Right Prashant but I am not familiar with merge statement.

    Please write merging code 

    Friday, May 17, 2013 1:48 AM
  • User-2005691517 posted

    Try this

    CREATE or REPLACE TRIGGER TR_MOVEMEMBER
    
    AFTER INSERT ON INT_MEMBER 
    FOR EACH ROW
    
    BEGIN
    
    MERGE INTO TBLMEMBER T
    USING DUAL
    ON (T.SAPPLICATIONNO =:old.SAPPLICATIONNO and T.IMEMBERID = :old.IMEMBERID)
    
    WHEN MATCHED THEN
    UPDATE SET SFIRSTNAME = :old.SFIRSTNAME, SMIDDLENAME =:old.SMIDDLENAME,SLASTNAME =:old.SLASTNAME, DTDOB= :old.DTDOB,IGENDERID= :old.IGENDERID,IMARITALSTATUSID= :old.IMARITALSTATUSID
    
    WHEN NOT MATCHED THEN
    INSERT (SAPPLICATIONNO,IMEMBERID,SFIRSTNAME,SMIDDLENAME,SLASTNAME,DTDOB,IGENDERID,IMARITALSTATUSID)
    values
    (:old.SAPPLICATIONNO,:old.IMEMBERID,:old.SFIRSTNAME, :old.SMIDDLENAME, :old.SLASTNAME, :old.DTDOB, :old.IGENDERID, :old.IMARITALSTATUSID);
    
    END;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 17, 2013 9:27 AM
  • User-2084712455 posted

    Hey Prashat it work after replacing old with new keyword..

    Thankyou Dear...

    Saturday, May 18, 2013 7:07 AM