表未按触发器和过程更新

Question

我有一个系统，人们可以在这个系统上下订单，每个订单都有操作，还有一个名为cm_ord_order_action的表。有时操作会失败，因此我需要创建一个触发器来获取失败订单操作的信息，并填充一个名为cm_ord_failed_order的表。

触发器如下所示：

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;
/

此触发器将参数传递给更新表的过程：

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;
/

这里可能有问题，因为:a-尽管触发器是用于对cm_ord_order_action进行更新之后，当触发器启用时，状态没有被更新，但是当我禁用触发器时，状态就被更新了。

B-表cm_ord_failed_order没有填充信息。

提前谢谢。

Answer 1

通过直接在触发器中执行插入操作，可以避免脚本以某种方式忽略或丢弃的表错误，在触发器中，您可以在:NEW伪记录中更新行的详细信息，而不必再次查询它。您也可以在不需要局部变量的情况下执行insert...select。

我认为这是一个粗略的翻译：

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;
/

DISTINCT (而不是分组)和CROSS JOIN表明您在原始查询中缺少一个联接条件，但如果没有表结构和数据，则可能不是这样。

或者，您可以将插入保留在过程中，但将:newAP_SUBSCRIBER_ID`作为另一个参数传递，因为这似乎是您需要从未传入的变异表中所需的唯一列。

您的触发器也可以是BEFORE UPDATE而不是AFTER UPDATE。

Answer 2

除了Alex的解决方案之外，避免交叉连接的另一种解决方案是将过程更改为：

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;
/

或者，要删除Alex解决方案中的交叉连接，只需用标量子查询替换它，例如：

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

Answer 3

就像@JustinCave说的那样，很明显你有一个变异表错误

当我们试图从行级触发器代码中引用查询中的触发表时，会发生表异常。

在CM_ORD_ORDER_ACTION上的触发器上，您要从同一个表中进行选择。尝试在不引用CM_ORD_ORDER_ACTION的情况下在过程中重新执行查询。