简介:
主表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记录如下: