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.
Aucun commentaire:
Enregistrer un commentaire