0

What happen with my trigger command?

I am getting the 'SQL Error: ORA-04091: table is mutating, trigger/function may not see it" when i am updating the data in the table.

Could someone please me?

Error:

One error saving changes to table "WSPADMIN"."WSP_T_REQ_MASTER":
Row 22: ORA-04091: table WSPADMIN.WSP_T_REQ_MASTER is mutating, trigger/function may not see it
ORA-06512: at "WSPADMIN.TG_AFTER_UPDATE_MASTER", line 4
ORA-04088: error during execution of trigger 'WSPADMIN.TG_AFTER_UPDATE_MASTER'

Trigger : 

create or replace TRIGGER "WSPADMIN"."TG_AFTER_UPDATE_MASTER"
    AFTER UPDATE 
    OF WSP_REQ_STATUS ON "WSPADMIN"."WSP_T_REQ_MASTER"
    FOR EACH ROW
    WHEN (NEW.WSP_REQ_STATUS = 'CCC')
    DECLARE REQ_NO varchar2(20);
    BEGIN
        REQ_NO := :old.WSP_REQ_NO;
        INSERT INTO WSP_T_SALE_ORDER ( WSP_REQ_NO, BU_SAP_PAYER_CODE, DEP_CODE, DEP_NAME, WSP_ITEM_CODE, WSP_QTY, UOM, TRANS_TYPE, STATUS, REQUEST_DATE, CREATE_DATE, GENERATE_DATE) 
        select A.WSP_REQ_NO, null, C.US_DEPT, D.DEP_NAME, B.WSP_ITEM_CODE, B.WSP_ITEM_QTY, 'EA', 'I', 'W', A.WSP_REQ_CREATE_DT, SYSDATE, SYSDATE  
        from WSP_T_REQ_MASTER A 
        left join WSP_T_REQ_DETAIL B on A.WSP_REQ_NO = B.WSP_REQ_NO
        left join CT_M_USER C on A.WSP_REQ_CONTACT_ID = C.US_USER_APPCODE
        left join CT_M_DEPARTMENT D on C.US_BU = D.BU_CODE and C.US_DEPT = D.DEP_CODE
        where A.WSP_REQ_NO = REQ_NO;
    END;
Yo Akarapol Imphrom
  • 55
  • 1
  • 8
  • I think the issue is that your trigger fires after update on the `WSP_T_REQ_MASTER` table, but the trigger itself is then selecting from this same table. – Tim Biegeleisen Jun 17 '18 at 02:42
  • 1
    Thank you Tim, I remove selecting from same table. it's working ^_^ – Yo Akarapol Imphrom Jun 17 '18 at 03:39
  • Glad to hear that. I avoided an answer because I'm not so strong with Oracle, and also I had no idea what your business logic was. In general, triggers should avoid modifying the table which caused them to fire. – Tim Biegeleisen Jun 17 '18 at 03:40

0 Answers0