达梦触发器实现数据同步

达梦触发器实现数据同步

说明

仅限于触发器创建完成后的INSERT、UPDATE、DELETE操作，TRUNCATE无法触发同步数据，详见定义，可基于全字段触发或某个字段，根据需求修改

创建测试表

/*创建源表*/
CREATE TABLE "SYSDBA"."A001"
(	"C1" VARCHAR(50),
	"C2" VARCHAR(50),
	"C3" VARCHAR(50));
/*创建同步表*/
CREATE TABLE "SYSDBA"."A002"
(	"C1" VARCHAR(50),
	"C2" VARCHAR(50),
	"C3" VARCHAR(50));

创建触发器

/*创建触发器*/
CREATE OR REPLACE TRIGGER INSERT_UPDATE_DELETE
/*不可用于TRUNCATE,只适用于INSERT、UPDATE、DELETE*/
AFTER INSERT OR UPDATE OR DELETE ON A001
/*当SYSDBA.A001表无论是INSERT、UPDATE还是DELETE都会触发*/
FOR EACH ROW
/*行级触发*/
DECLARE
    /*局部变量设置*/
    V_INTRGRITY_ERROR EXCEPTION;
    V_ERROR_NO        NUMBER(10);
    V_ERROR_MSG       VARCHAR(200);
BEGIN
    IF INSERTING THEN
        /*当SYSDBA.A001表新增时，添加一条记录到表SYSDBA.A002*/
        INSERT INTO SYSDBA.A002 (C1,
               C2,
               C3)
               VALUES (:NEW.C1,
               :NEW.C2,
               :NEW.C3);
    
    ELSIF UPDATING THEN
        /*根据所有字段作为条件，替换之前的旧数据*/
        UPDATE
               SYSDBA.A002
           SET C1 = :NEW.C1,
               C2 = :NEW.C2,
               C3 = :NEW.C3
         WHERE C2 = :OLD.C2
           AND C1 = :OLD.C1
           AND C3 = :OLD.C3;
    
    ELSE
        DELETE
          FROM SYSDBA.A002
         WHERE C2 = :OLD.C2
           AND C1 = :OLD.C1
           AND C3 = :OLD.C3;
    
    END IF;
    /*        ELSIF UPDATING THEN
    --也可以直接指定作用于SYSDBA.A001表中的C2字段有变更时触发，其他字段变更不触发
    --根据旧C2作为条件，以新C2替换之前的旧数据
    UPDATE
    SYSDBA.A002
    SET C1 = :NEW.C1
    WHERE C2 = :OLD.C2;
    ELSE
    DELETE FROM SYSDBA.A002 WHERE C2 = :OLD.C2;
    END IF;*/
EXCEPTION
    -- 异常捕获处理
WHEN V_INTRGRITY_ERROR THEN
    RAISE_APPLICATION_ERROR(V_ERROR_NO, V_ERROR_MSG);
END A002 ;

插入提交测试数据

/*插入提交测试数据*/
INSERT INTO "SYSDBA"."A001" VALUES('1','1','1');
INSERT INTO "SYSDBA"."A001" VALUES('2','2','2');
INSERT INTO "SYSDBA"."A001" VALUES('3','3','3');
COMMIT;
/*查询数据已同步*/
SELECT * FROM "SYSDBA"."A001";
SELECT * FROM "SYSDBA"."A002";

修改数据测试，数据一致

UPDATE "SYSDBA"."A001" SET "C3"= '11' WHERE C1 ='1';
COMMIT;
SELECT * FROM "SYSDBA"."A002";

UPDATE "SYSDBA"."A001" SET "C2"= '22';
COMMIT;
SELECT * FROM "SYSDBA"."A002";

UPDATE "SYSDBA"."A001" SET "C1"= '666' WHERE C2 ='22' AND C3 ='11';
COMMIT;
SELECT * FROM "SYSDBA"."A002";

DELETE FROM "SYSDBA"."A001" WHERE C1='666';
COMMIT;
SELECT * FROM "SYSDBA"."A002";

TRUNCATE表无法触发

TRUNCATE TABLE "SYSDBA"."A001";
SELECT * FROM "SYSDBA"."A002";