跨库触发器问题：基于OrderNo修正Ord_Detail触发器代码

原触发器核心问题 #

1. WHERE条件逻辑错误：UPDATE语句使用OR会匹配到非目标行（如同一订单下的其他明细），导致误更新。
2. OrderNo关联逻辑混乱：子查询试图将本地Ord_Detail的ORDERS_ID与本地Orders的OrderNo直接匹配，违背表结构关联规则（ORDERS_ID是Orders表主键ID，而非OrderNo）。
3. 缺少事务提交：使用了自治事务PRAGMA AUTONOMOUS_TRANSACTION但未添加COMMIT，同步操作无法生效。
4. DELETE未遵循OrderNo匹配要求：仅通过主键和ORDERS_ID匹配，未关联OrderNo确保同步准确性。

修正后的触发器代码 #

CREATE OR REPLACE TRIGGER trg_orddetail_locals
AFTER UPDATE OR DELETE ON ORD_DETAIL
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_local_order_id NUMBER;
BEGIN
  IF UPDATING THEN
    -- 获取本地库中对应OrderNo的Orders记录ID
    SELECT ID INTO v_local_order_id
    FROM ORDERS@local_link
    WHERE ORDERNO = (SELECT ORDERNO FROM ORDERS WHERE ID = :NEW.ORDERS_ID);

    -- 更新本地库中匹配的Ord_Detail记录
    UPDATE ORD_DETAIL@local_link dst
    SET 
      ID = :NEW.ID, 
      ORDERS_ID = v_local_order_id,
      ARINVT_ID = :NEW.ARINVT_ID, 
      ORD_DET_SEQNO = :NEW.ORD_DET_SEQNO, 
      TOTAL_QTY_ORD = :NEW.TOTAL_QTY_ORD, 
      CUMM_SHIPPED = :NEW.CUMM_SHIPPED, 
      ONHOLD = :NEW.ONHOLD, 
      TAX_CODE_ID = :NEW.TAX_CODE_ID, 
      MISC_ITEM = :NEW.MISC_ITEM, 
      COMMENT1 = :NEW.COMMENT1, 
      UNIT_PRICE = :NEW.UNIT_PRICE, 
      SALESPEOPLE_ID = :NEW.SALESPEOPLE_ID, 
      COMM_PCT = :NEW.COMM_PCT, 
      PRICE_PER_1000 = :NEW.PRICE_PER_1000, 
      LIST_UNIT_PRICE = :NEW.LIST_UNIT_PRICE, 
      DISCOUNT = :NEW.DISCOUNT, 
      ECODE = :NEW.ECODE, 
      EID = :NEW.EID, 
      EDATE_TIME = :NEW.EDATE_TIME, 
      ECOPY = :NEW.ECOPY, 
      EPLANT_ID = :NEW.EPLANT_ID, 
      COST_OBJECT_ID = :NEW.COST_OBJECT_ID, 
      COST_OBJECT_SOURCE = :NEW.COST_OBJECT_SOURCE, 
      UNIT = :NEW.UNIT, 
      UOM_FACTOR = :NEW.UOM_FACTOR, 
      GLACCT_ID = :NEW.GLACCT_ID, 
      DOCKID = :NEW.DOCKID, 
      LINEFEED = :NEW.LINEFEED, 
      RESERVELOCATION = :NEW.RESERVELOCATION, 
      KBTRIGGER = :NEW.KBTRIGGER, 
      AGGREGATE_DISCOUNT = :NEW.AGGREGATE_DISCOUNT, 
      CUST_CUM_START = :NEW.CUST_CUM_START, 
      LAST_RECEIPT_QTY = :NEW.LAST_RECEIPT_QTY, 
      LAST_RECEIPT_DATE = :NEW.LAST_RECEIPT_DATE, 
      RMA_DETAIL_ID = :NEW.RMA_DETAIL_ID, 
      REF_CODE_ID = :NEW.REF_CODE_ID, 
      FAB_QTY = :NEW.FAB_QTY, 
      RAW_MT_QTY = :NEW.RAW_MT_QTY, 
      FAB_START_DATE = :NEW.FAB_START_DATE, 
      FAB_END_DATE = :NEW.FAB_END_DATE, 
      CONTAINERS = :NEW.CONTAINERS, 
      FROM_SALES_OPTION = :NEW.FROM_SALES_OPTION, 
      SHIP_TO_ID_FROM = :NEW.SHIP_TO_ID_FROM, 
      IN_TRANSIT_WORKORDER_ID = :NEW.IN_TRANSIT_WORKORDER_ID, 
      IN_TRANSIT_PARTNO_ID = :NEW.IN_TRANSIT_PARTNO_ID, 
      IS_DROP_SHIP = :NEW.IS_DROP_SHIP, 
      IS_MAKE_TO_ORDER = :NEW.IS_MAKE_TO_ORDER, 
      MFG_QUAN = :NEW.MFG_QUAN, 
      PO_INFO = :NEW.PO_INFO, 
      MAKE_TO_ORDER_PS_TICKET_DTL_ID = :NEW.MAKE_TO_ORDER_PS_TICKET_DTL_ID, 
      CAMPAIGN_ID = :NEW.CAMPAIGN_ID, 
      CTP = :NEW.CTP, 
      CRM_QUOTE_DETAIL_ID = :NEW.CRM_QUOTE_DETAIL_ID, 
      SALES_OPTION_CHOICE_ID = :NEW.SALES_OPTION_CHOICE_ID, 
      CUSER1 = :NEW.CUSER1, 
      CUSER2 = :NEW.CUSER2, 
      CUSER3 = :NEW.CUSER3, 
      REBATE_PARAMS_ID = :NEW.REBATE_PARAMS_ID, 
      PHANTOM_ORD_DETAIL_ID = :NEW.PHANTOM_ORD_DETAIL_ID, 
      PHANTOM_PTSPER = :NEW.PHANTOM_PTSPER, 
      HIDE = :NEW.HIDE, 
      OUTSOURCE_PO_DETAIL_ID = :NEW.OUTSOURCE_PO_DETAIL_ID, 
      LOT_CHARGE_ARINVT_ID = :NEW.LOT_CHARGE_ARINVT_ID, 
      STANDARD_ID = :NEW.STANDARD_ID, 
      DIVISION_ID = :NEW.DIVISION_ID, 
      AKA_KIND = :NEW.AKA_KIND, 
      MSDS_UPLOAD = :NEW.MSDS_UPLOAD, 
      SHIPHOLD = :NEW.SHIPHOLD, 
      MILK_RUN_LOCATIONS_ID = :NEW.MILK_RUN_LOCATIONS_ID, 
      LOT_CHARGE_ORD_DETAIL_ID = :NEW.LOT_CHARGE_ORD_DETAIL_ID, 
      AUTO_INVOICE = :NEW.AUTO_INVOICE, 
      C_PO_MISC_ID = :NEW.C_PO_MISC_ID, 
      MISC_ITEMNO = :NEW.MISC_ITEMNO, 
      SAMPLE = :NEW.SAMPLE, 
      PHANTOM_DTL_MODIFIED = :NEW.PHANTOM_DTL_MODIFIED, 
      BOGO_ORD_DETAIL_ID = :NEW.BOGO_ORD_DETAIL_ID, 
      BOGO_AR_DISCOUNT_TIERS_ID = :NEW.BOGO_AR_DISCOUNT_TIERS_ID, 
      BOGO_EXCEPTION = :NEW.BOGO_EXCEPTION, 
      PRICE_BOOK_ID = :NEW.PRICE_BOOK_ID, 
      PRICE_BOOK_PRICE_TYPE_ID = :NEW.PRICE_BOOK_PRICE_TYPE_ID, 
      NEW_SEQUENCE_COLUMN = :NEW.NEW_SEQUENCE_COLUMN
    WHERE dst.ID = :NEW.ID
      AND dst.ORDERS_ID = v_local_order_id;

  ELSIF DELETING THEN
    -- 通过OrderNo关联删除本地对应明细
    DELETE FROM ORD_DETAIL@local_link dst
    WHERE dst.ID = :OLD.ID
      AND EXISTS (
        SELECT 1
        FROM ORDERS@local_link lo
        JOIN ORDERS ro ON lo.ORDERNO = ro.ORDERNO
        WHERE ro.ID = :OLD.ORDERS_ID
          AND lo.ID = dst.ORDERS_ID
      );
  END IF;

  COMMIT; -- 提交自治事务
END;
/

关键修改说明 #

1. 新增本地OrderID变量：通过远程Orders的ID获取对应OrderNo，再匹配本地Orders的OrderNo得到本地ID，确保关联准确性。
2. 修正UPDATE条件：用AND同时匹配主键和本地OrderID，避免误更新其他行。
3. 完善DELETE逻辑：通过EXISTS子查询关联OrderNo，保证删除的是对应订单下的明细。
4. 添加COMMIT语句：自治事务必须显式提交，否则同步操作不生效。
5. 修正ORDERS_ID赋值：更新本地Ord_Detail时，将ORDERS_ID设为本地Orders的ID，保证本地表关联正确。

内容的提问来源于stack exchange，提问作者Arulselvam