|
最新项目遇到一个需求，将一张表的新增、修改、删除（逻辑删除）的数据，推送给外部系统，不能整个表推送，只能增量的推送，提高数据的准确性，避免大量无变化的数据推送给外部系统，设计想法是利用oracle自身的触发器机制，监控目标表的变化，将变化的记录行ID记录到另一张日志表，程序定时扫描这张日志表即可，从而实现增量的数据同步问题。现在将触发器部分脚本，整理如下，有需要的小伙伴可以参考： 1、创建日志表，BUSINESS_ID表示监控的数据表主键，DATA_TYPE是数据类型的区分，为了支持后续其他数据扩展
DROP TABLE "EQU_BASE_INFO_HIS";
CREATE TABLE "EQU_BASE_INFO_HIS" (
"ID" NUMBER(30) NOT NULL ,
"BUSINESS_ID" VARCHAR2(64 BYTE) NULL ,
"IS_SYNC" NUMBER DEFAULT 0 NULL ,
"INPUT_TIME" DATE NULL ,
"MOD_TIME" DATE NULL ,
"DATA_TYPE" VARCHAR2(64 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."ID" IS '主键';
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."BUSINESS_ID" IS '台账表EQU_BASE_INFO主键ID';
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."IS_SYNC" IS '是否同步（0:未同步；1:已同步）';
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."INPUT_TIME" IS '录入时间';
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."MOD_TIME" IS '修改时间';
COMMENT ON COLUMN "EQU_BASE_INFO_HIS"."DATA_TYPE" IS ' 数据类型（01：台账；02：资产分类）';
-- ----------------------------
-- Indexes structure for table EQU_BASE_INFO_HIS
-- ----------------------------
-- ----------------------------
-- Checks structure for table EQU_BASE_INFO_HIS
-- ----------------------------
ALTER TABLE "EQU_BASE_INFO_HIS" ADD CHECK ("ID" IS NOT NULL);
-- ----------------------------
-- Primary Key structure for table EQU_BASE_INFO_HIS
-- ----------------------------
ALTER TABLE "EQU_BASE_INFO_HIS" ADD PRIMARY KEY ("ID");
2、创建自增序列
CREATE SEQUENCE EQU_BASE_INFO_HIS_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCACHE;
3、新建触发器，监控目标表，将变化信息插入上述新建的日志表
CREATE OR REPLACE TRIGGER "EQU_BASE_INFO_TRIGGER" AFTER INSERT OR DELETE OR UPDATE ON "EQU_BASE_INFO" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW DECLARE
v_id NUMBER (30) ;
v_business_id VARCHAR2 (64) ;
v_data_type VARCHAR2 (64) ;
v_input_time DATE ;
BEGIN
SELECT
EQU_BASE_INFO_HIS_SEQ.nextval INTO v_id FROM dual ; -- 利用seq生成主键
SELECT SYSDATE INTO v_input_time FROM dual ;
v_data_type := '01' ;
IF inserting THEN
v_business_id:=:NEW.ID;
INSERT INTO SF_EAM.EQU_BASE_INFO_HIS (ID, BUSINESS_ID, IS_SYNC, INPUT_TIME,DATA_TYPE) values (v_id, v_business_id, 0, v_input_time,v_data_type );
ELSIF updating THEN
v_business_id:=:OLD.ID;
INSERT INTO SF_EAM.EQU_BASE_INFO_HIS (ID, BUSINESS_ID, IS_SYNC, INPUT_TIME,DATA_TYPE) values (v_id, v_business_id, 0, v_input_time,v_data_type );
ELSE
v_business_id:=:OLD.ID;
INSERT INTO SF_EAM.EQU_BASE_INFO_HIS (ID, BUSINESS_ID, IS_SYNC, INPUT_TIME,DATA_TYPE) values (v_id, v_business_id, 0, v_input_time,v_data_type );
END IF ;
END;
|