CODEWITHSUNDEEP
sqloracle-databasetriggersprocedure
Iommiund
Iommiund

Reputation: 15

Table not updated by trigger and procedure

I have a system which people do orders on, each order has actions, and a table exists called cm_ord_order_action. Sometimes actions fail, so I need to make a trigger that gets information for the failed order action and populates a table called cm_ord_failed_order.

the trigger is shown below:

CREATE OR REPLACE TRIGGER CM.TRGID_CM_ORD_FAILED_ORDER
AFTER UPDATE ON CM.CM_ORD_ORDER_ACTION
FOR EACH ROW
BEGIN 
   IF (:new.STATUS = 'FA') THEN
        CM.CM_FAILED_ORDER_MLT(:new.order_unit_id, :new.order_id, :new.action_type);
   END IF;
END;
/

This trigger passes parameters to a procedure which updates the table:

CREATE OR REPLACE PROCEDURE CM_FAILED_ORDER_MLT(
v_order_unit_id NUMBER,
v_order_id in NUMBER,
v_action_type in VARCHAR)

AS
v_lob varchar(100);
v_step varchar(100);
v_error varchar(200);

BEGIN

SELECT
    ITEM.LOB_NAME, ST.STEP_NAME, ASS.STEP_ERROR
    INTO v_lob, v_step, v_error           
FROM
    CM.CM_ORD_ORDER_ACTION OA 
    INNER JOIN CM.CM_ORD_ASSIGNMENTS ASS 
        ON OA.ORDER_UNIT_ID = ASS.ORDER_ACTION_ID 
    INNER JOIN CM.CM_ORD_PROCESS_STEP ST
        ON ST.ORD_PROCESS_STEP_ID = ASS.STEP_ID 
    INNER JOIN CM.CM_ORD_AP_ITEM ITEM
        ON ITEM.AP_SUBSCRIBER_ID = OA.AP_SUBSCRIBER_ID
WHERE ASS.COMPLETION_STATUS = 'FA'
AND OA.ORDER_ID = v_order_id
AND OA.ORDER_UNIT_ID = v_order_unit_id
GROUP BY OA.ORDER_UNIT_ID, ITEM.LOB_NAME, ST.STEP_NAME, ASS.STEP_ERROR;

INSERT INTO CM_ORD_FAILED_ORDER (ORDER_ID, FAILED_DATE, ORDER_ACTION_ID, ACTION_TYPE, LOB, STEP, ERROR)
  VALUES (v_order_id, sysdate, v_order_unit_id, v_action_type, v_lob, v_step, v_error);

END CM_FAILED_ORDER_MLT;
/

There is probably something wrong here because: A - Even though the trigger is for after update on cm_ord_order_action, when the trigger is enabled, the status is not being updated, but when I disable the trigger the status is updated.

B - the table cm_ord_failed_order is not being populated with the information.

Thanks in advance.

Upvotes: 0

Views: 100

Answers (3)

Boneist
Boneist

Reputation: 23578

An alternative to Alex's solution that avoids the need for a cross join would be to change the procedure to:

create or replace procedure cm_failed_order_mlt (v_order_unit_id number,
                                                 v_order_id in number,
                                                 v_action_type in varchar,
                                                 v_ap_subscriber_id in cm.cm_ord_order_action.ap_subscriber_id%type)

as
  v_lob varchar(100);
  v_step varchar(100);
  v_error varchar(200);
begin
  select distinct lob_name
  into   v_lob
  from   cm.cm_ord_ap_item
  where ap_subscriber_id = v_ap_subscriber_id;

  select distinct st.step_name, ass.step_error
  into   v_step, v_error           
  from   cm.cm_ord_assignments ass
         inner join cm.cm_ord_process_step st on st.ord_process_step_id = ass.step_id
  where  ass.completion_status = 'FA'
  and    ass.order_action_id = v_order_id
  and    oa.order_unit_id = v_order_unit_id;

  insert into cm_ord_failed_order (order_id, failed_date, order_action_id, action_type, lob, step, error)
    values (v_order_id, sysdate, v_order_unit_id, v_action_type, v_lob, v_step, v_error);

end cm_failed_order_mlt;
/

Or, to remove the cross join in Alex's solution, simply replace it with a scalar subquery, e.g.:

select (select distinct lob_name from cm.cm_ord_ap_item where ap_subscriber_id = v_ap_subscriber_id), ...

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

You can avoid the mutating table error your script is somehow ignoring or discarding by doing the insert directly in the trigger, where you have the details from the row being updated in the :NEW pseudorecord and don't have to query it again. You can also do an insert...select without needing local variables.

I think this is a rough translation:

CREATE OR REPLACE TRIGGER CM.TRGID_CM_ORD_FAILED_ORDER
AFTER UPDATE ON CM.CM_ORD_ORDER_ACTION
FOR EACH ROW
WHEN (new.STATUS = 'FA')
BEGIN 

  INSERT INTO CM_ORD_FAILED_ORDER (ORDER_ID, FAILED_DATE, ORDER_ACTION_ID, ACTION_TYPE,
    LOB, STEP, ERROR)
  SELECT
    DISTINCT :new.ORDER_ID, sysdate, :new.Order_Unit_Id, :new.Action_Type,
      ITEM.LOB_NAME, ST.STEP_NAME, ASS.STEP_ERROR
  FROM
    CM.CM_ORD_ASSIGNMENTS ASS
    INNER JOIN CM.CM_ORD_PROCESS_STEP ST
        ON ST.ORD_PROCESS_STEP_ID = ASS.STEP_ID 
    CROSS JOIN CM.CM_ORD_AP_ITEM ITEM
  WHERE ASS.ORDER_ACTION_ID = :new.ORDER_UNIT_ID
  AND ASS.COMPLETION_STATUS = :new.STATUS
  AND ITEM.AP_SUBSCRIBER_ID = :new.AP_SUBSCRIBER_ID;

END CM_FAILED_ORDER_MLT;
/

The DISTINCT (instead of grouping) and CROSS JOIN suggest you're missing a join condition in your original query, but without your table structures and data that may not be the case.

Alternatively you could keep the insert in a procedure, but pass :newAP_SUBSCRIBER_ID` as another argument, since that seems to be the only column you need from the mutating table that you aren't already passing in.

Your trigger could also be a BEFORE UPDATE rather than AFTER UPDATE.

Upvotes: 1

vercelli
vercelli

Reputation: 4757

Like @JustinCave said, it is clear that you have mutating table error:

Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code

On a trigger on CM_ORD_ORDER_ACTION you are selecting from that same table. Try to redo the query in the procedure without referencing CM_ORD_ORDER_ACTION.

Upvotes: 0

Related Questions