Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Unanswered: Mutating table error

    I have a table that contains several fields including a few "approval" fields. When a specific field changes I want to clear the approval fields because the record needs to be re-approved. I tried to do this through a trigger but I got the mutating table error because I am trying to update the table that the trigger belongs to. Is there a simple solution to clear out those approval fields when another field in the table changes?

    Thank you for any help.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dreamaway
    I have a table that contains several fields including a few "approval" fields. When a specific field changes I want to clear the approval fields because the record needs to be re-approved. I tried to do this through a trigger but I got the mutating table error because I am trying to update the table that the trigger belongs to. Is there a simple solution to clear out those approval fields when another field in the table changes?

    Thank you for any help.
    Show us your code.

    In general you should be able to change individual fields of the row updated/inserted in a row level trigger.
    Most probably you are using the wrong syntax.

  3. #3
    Join Date
    Mar 2008
    Posts
    11
    Here is what I originally tried:

    Code:
    if :old.total_qty_discrepant <> :new.total_qty_discrepant AND :new.status = 'Approved' then
    
    /* Remove the approvals from the table - will need to be re-approved */
    	 BEGIN
    	      	update clt_mdr_headers_all mha
    	          set mha.eng_disp_approver = null,
    		mha.eng_disp_approval_date = null,
    		mha.qa_disp_approver = null,
    		mha.qa_disp_approval_date = null,
    		mha.bp_disp_approver = null,
    		mha.bp_disp_approval_date = null,
    		mha.cus_mrb_approver = null,
    		mha.cus_mrb_approval_date = null,
    		mha.gov_mrb_approver = null,
    		mha.gov_mrb_approval_date = null
    	      	where mha.mdr_number = l_mdrNumber;
    	     END; 
    
    /* Notifications */
             save_threshold := wf_engine.threshold;
             wf_engine.threshold := -1;
    	     l_itemkey := str_headerID || '-E8-' || l_itemseq;
             wf_engine.createProcess(l_itemtype,l_itemkey,'MDR_APPROVED');
    	     wf_engine.setItemAttrText(l_itemtype,l_itemkey,'PART_NUMBER',l_partNumber);
    	     wf_engine.setItemAttrText(l_itemtype,l_itemkey,'PART_NAME',l_partName);
    	     wf_engine.setItemAttrText(l_itemtype,l_itemkey,'PART_REV',l_partRev);
    		 wf_engine.setItemAttrText(l_itemtype,l_itemkey,'BUYER_PLANNER',l_bp);
    		 wf_engine.setItemAttrText(l_itemtype,l_itemkey,'QA_ENGINEER',l_qe);
    	     wf_engine.setItemAttrText(l_itemtype,l_itemkey,'ENGINEER',l_eng);
    		 wf_engine.setItemAttrText(l_itemtype,l_itemkey,'MTL_SUPERVISOR',l_mtl);
    		 wf_engine.setItemAttrText(l_itemtype,l_itemkey,'ENG_MGR',l_em);
    		 wf_engine.setItemAttrText((l_itemtype,l_itemkey,'TECH_WRITER',l_tw);
    	     wf_engine.setItemAttrNumber(l_itemtype,l_itemkey,'MDR_NUMBER',l_mdrNumber);
             wf_engine.startProcess(l_itemtype,l_itemkey);
             wf_engine.threshold := save_threshold;
    	  end if;
    I thought maybe I could put this in a before or after trigger but I get the same result.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You need to replace the UPDATE statement with something like:
    Code:
    :new.mha.eng_disp_approver = null;
    :new.eng_disp_approval_date = null;
    :new.qa_disp_approver = null;
    ...
    (provided that you do have a row level trigger)

  5. #5
    Join Date
    Mar 2008
    Posts
    11
    Yes that worked beautifully. Thank you so much!!

Posting Permissions

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