用触发器实现数据简单同步

 

表t1和t1_temp有同样的结构和数据：

create table t1 as select * from emp where 1=0;
create table t1_temp as select * from emp where 1=0;
alter table t1 add constraint pk_empno1 primary key (empno);
alter table t1_temp add constraint pk_empno2 primary key (empno);

把表t1的操作同步到表t1_temp,触发器脚本如下：
create or replace trigger tgr_t1
 after insert or update or delete on t1
 for each row
 begin
   if (inserting)
   then
     insert into t1_temp
     values (:new.EMPNO,
     :new.ENAME,
     :new.JOB,
     :new.MGR,
     :new.HIREDATE,
     :new.SAL,
     :new.COMM,
     :new.DEPTNO);
   end if;

   if :new.empno!=:old.empno
   then
     raise_application_error(-20001,'con not modify primary key');
   end if;
   if (deleting)
   then
     delete t1_temp
     where empno=:old.empno;
   end if;
   if (updating)
   then
     update t1_temp
     set
         ENAME=:new.ENAME,
         JOB=:new.JOB,
         MGR=:new.MGR,
         HIREDATE=:new.HIREDATE,
         SAL=:new.SAL,
         COMM=:new.COMM,
         DEPTNO=:new.DEPTNO
     where EMPNO=:old.EMPNO;
   end if;  
 end;

在这里假设不能修改被同步表的主键（主要是为了同步简单），如果要修改主键，可以拆分成两条（一是主键删除，另一是主键插入）。
以上触发器是个简单的样本，实际工作中可能要比这复杂的多，可以根据需要增加功能。

下面验证正确性。


SQL> select * from t1;

no rows selected

SQL> select * from t1_temp;

no rows selected

SQL> insert into t1 select * from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

SQL> select * from t1_temp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10
     
14 rows selected.

insert 操作后数据被同步。

SQL> update t1 set sal=sal+1 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           801                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

SQL> select * from t1_temp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           801                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.    

update后数据同步成功。

SQL> delete t1 where empno=7369;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

13 rows selected.

SQL> select * from t1_temp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

13 rows selected.

delete操作后数据同步成功。