oracle监控某表变动触发器例子(监控增,删,改)

刺骨的言语ヽ痛彻心扉 2022-12-19 00:42 129阅读 0赞

最新项目遇到一个需求，将一张表的新增、修改、删除（逻辑删除）的数据，推送给外部系统，不能整个表推送，只能增量的推送，提高数据的准确性，避免大量无变化的数据推送给外部系统，设计想法是利用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;