Oracle备份表数据通过触发器，把A表中进行新增修改或删除 的数据同步到b表

简介:

主表A : CUSTOM_CABINETS_ORDERLIST_SIRE

备份/同步表B : CUSTOM_CABINETS_CHECKING

如果主表A  进行 insert  update delete   就对备份/同步表进行操作，具体如何触发同步 根据自己业务去修改触发器。

触发器如下:

CREATE OR REPLACE TRIGGER CUSTOM_CABINETS_CHECKING
  AFTER INSERT OR UPDATE OR DELETE ON CUSTOM_CABINETS_ORDERLIST_SIRE -- 当CUSTOM_CABINETS_ORDERLIST_SIRE表无论是INSERT、UPDATE还是DELETE都会触发
  -- AFTER UPDATE OF CLASS_ID ON CLASS 也可以直接指定作用于CUSTOM_CABINETS_ORDERLIST_SIRE表中的PARENT_ID字段有变更时触发，其他字段变更不触发
  FOR EACH ROW -- 行级触发
DECLARE
  -- local variables here局部变量设置
  V_INTRGRITY_ERROR EXCEPTION;
  V_ERROR_NO  NUMBER(10);
  V_ERROR_MSG VARCHAR(200);
BEGIN
  IF INSERTING THEN
    -- 当CUSTOM_CABINETS_ORDERLIST_SIRE表新增时，添加一条log记录到表CUSTOM_CABINETS_CHECKING
    INSERT INTO CUSTOM_CABINETS_CHECKING
      (PARENT_ID,CUSTOMERSNO,CREATOR,REMARKS,CREATE_TIME,FLOW_ID,DISCOUNTS_MONEY,DISCOUNTS_EXPLAIN,STATE,ORDER_TIME,TOTAL_PRICES,PROVE_IMAGE,SUPPLIER,CABINETS_ONE_CODE,COLLATE,COLLATESTARTTIME,COLLATEENDTIME,CREATEDBY,CREATEDWHEN,UPDATEDBY,UPDATEDWHEN,SHIPMENTST_TIME,DELIVERY_TIME,RECEPTION_TIME,SHIPMENTS_STATE,CATEGORY,MAJOR_UID,PREDICT_SETTLEACCOUNTS_TIME)
    VALUES
      (:NEW.PARENT_ID,:NEW.CUSTOMERSNO,:NEW.CREATOR,:NEW.REMARKS,:NEW.CREATE_TIME,:NEW.FLOW_ID,:NEW.DISCOUNTS_MONEY,:NEW.DISCOUNTS_EXPLAIN,:NEW.STATE,:NEW.ORDER_TIME,:NEW.TOTAL_PRICES,:NEW.PROVE_IMAGE,:NEW.SUPPLIER,:NEW.CABINETS_ONE_CODE,:NEW.COLLATE,:NEW.COLLATESTARTTIME,:NEW.COLLATEENDTIME,:NEW.CREATEDBY,:NEW.CREATEDWHEN,:NEW.UPDATEDBY,:NEW.UPDATEDWHEN,:NEW.SHIPMENTST_TIME,:NEW.DELIVERY_TIME,:NEW.RECEPTION_TIME,:NEW.SHIPMENTS_STATE,:NEW.CATEGORY,:new.MAJOR_UID,:NEW.PREDICT_SETTLEACCOUNTS_TIME+numtoyminterval(1,'month'));
  ELSIF UPDATING THEN
    -- 根据旧PARENT_ID作为条件，以新PARENT_ID替换之前的旧数据
    UPDATE CUSTOM_CABINETS_CHECKING
       SET STATE = :NEW.STATE
     WHERE PARENT_ID = :OLD.PARENT_ID;
  ELSE
    DELETE FROM CUSTOM_CABINETS_CHECKING
     WHERE PARENT_ID = :OLD.PARENT_ID;
  END IF;
EXCEPTION
  -- 异常捕获处理
  WHEN V_INTRGRITY_ERROR THEN
    RAISE_APPLICATION_ERROR(V_ERROR_NO, V_ERROR_MSG);
  
END CUSTOM_CABINETS_CHECKING;

 

获取A表某个字段当前日期、同步到B表时该字段、 "延后推迟1个月" 

重点: 日期字段+函数 PREDICT_SETTLEACCOUNTS_TIME+numtoyminterval(1,'month')

INSERT INTO CUSTOM_CABINETS_CHECKING
      (PARENT_ID,CUSTOMERSNO,CREATOR,REMARKS,CREATE_TIME,FLOW_ID,DISCOUNTS_MONEY,DISCOUNTS_EXPLAIN,STATE,ORDER_TIME,TOTAL_PRICES,PROVE_IMAGE,SUPPLIER,CABINETS_ONE_CODE,COLLATE,COLLATESTARTTIME,COLLATEENDTIME,CREATEDBY,CREATEDWHEN,UPDATEDBY,UPDATEDWHEN,SHIPMENTST_TIME,DELIVERY_TIME,RECEPTION_TIME,SHIPMENTS_STATE,CATEGORY,MAJOR_UID,PREDICT_SETTLEACCOUNTS_TIME)
VALUES
      (:NEW.PARENT_ID,:NEW.CUSTOMERSNO,:NEW.CREATOR,:NEW.REMARKS,:NEW.CREATE_TIME,:NEW.FLOW_ID,:NEW.DISCOUNTS_MONEY,:NEW.DISCOUNTS_EXPLAIN,:NEW.STATE,:NEW.ORDER_TIME,:NEW.TOTAL_PRICES,:NEW.PROVE_IMAGE,:NEW.SUPPLIER,:NEW.CABINETS_ONE_CODE,:NEW.COLLATE,:NEW.COLLATESTARTTIME,:NEW.COLLATEENDTIME,:NEW.CREATEDBY,:NEW.CREATEDWHEN,:NEW.UPDATEDBY,:NEW.UPDATEDWHEN,:NEW.SHIPMENTST_TIME,:NEW.DELIVERY_TIME,:NEW.RECEPTION_TIME,:NEW.SHIPMENTS_STATE,:NEW.CATEGORY,:new.MAJOR_UID,:NEW.PREDICT_SETTLEACCOUNTS_TIME+numtoyminterval(1,'month'));

解释:   获取A表中的 PREDICT_SETTLEACCOUNTS_TIME 日期， 在同步到B表(备份表)时，把A表当前日期延后推迟1个月，存储到B表。(如: A表 2023/3/21   B表 2023/4/21)

 

 

 在PLSQ找到Triggers 找对应的名字   

 触发器执行后 ，主表A 进行添加操作

insert into CUSTOM_CABINETS_ORDERLIST_SIRE(PARENT_ID,REMARKS,STATE) select '001','备注','11' from dual;

查询主表A记录如下:

 查询备份表B记录如下: