oracle 触发器实例参考

 使用触发器实现 A单据下推B单据    反写A单据已下推标志

1、配置转换规则 把  A单据的单据id 携带到B单据上的FSourceBillID 字段上

2、在B 表上写以下触发器实现

create or replace trigger mz_TEST
  AFTER insert or update OR DELETE OF FSourceBillID ON CT_HOS_YIYUANFLZC --下游单据
  for each row
declare

begin
  IF INSERTING or UPDATING THEN
    update CT_XZK_SYBYYECTJFL
       set CFISFL = '是'
     where fid = :new.FSourceBillID;
  END IF;

  IF DELETING THEN
    update CT_XZK_SYBYYECTJFL
       set CFISFL = '否'
     where fid = :old.FSourceBillID;
  
  END IF;

end mz_TEST;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER "EAS_GOODS_BIZACCOUNT"
  BEFORE INSERT OR UPDATE OR DELETE ON T_BC_BIZACCOUNTBILL
  FOR EACH ROW
DECLARE
BEGIN
  IF INSERTING THEN
    UPDATE T_GOO_WAREHOUSINGOFGOODS
       SET FIDENTITYSTATUS = '已报销'
     WHERE FID IN
           (

            SELECT REGEXP_SUBSTR(:NEW.CFCUSTOMERIDS, '[^,]+', 1, ROWNUM)
              FROM DUAL
            CONNECT BY ROWNUM <=
                       LENGTH(:NEW.CFCUSTOMERIDS) -
                       LENGTH(REPLACE(:NEW.CFCUSTOMERIDS, ',', '')) + 1

            );

  ELSIF UPDATING THEN
    IF (:new.FSTATE = 27 or :new.FSTATE = 40) then
      UPDATE T_GOO_WAREHOUSINGOFGOODS
         SET FIDENTITYSTATUS = NULL
       WHERE FID IN
             (

              SELECT REGEXP_SUBSTR(:OLD.CFCUSTOMERIDS, '[^,]+', 1, ROWNUM)
                FROM DUAL
              CONNECT BY ROWNUM <=
                         LENGTH(:OLD.CFCUSTOMERIDS) -
                         LENGTH(REPLACE(:OLD.CFCUSTOMERIDS, ',', '')) + 1

              );
    END IF;

  ELSIF DELETING THEN
    UPDATE T_GOO_WAREHOUSINGOFGOODS
       SET FIDENTITYSTATUS = NULL
     WHERE FID IN
           (

            SELECT REGEXP_SUBSTR(:OLD.CFCUSTOMERIDS, '[^,]+', 1, ROWNUM)
              FROM DUAL
            CONNECT BY ROWNUM <=
                       LENGTH(:OLD.CFCUSTOMERIDS) -
                       LENGTH(REPLACE(:OLD.CFCUSTOMERIDS, ',', '')) + 1

            );
  END IF;

END;