oracle 行级触发器的使用

1、通过“MATERIALIZED”视图，监管远程数据变化，更新本地数据

    a、创建数据库连接：

    -- Drop existing database link
    drop database link 数据库连接名称;
    -- Create database link
    create database link 数据库连接名称

      connect to 用户名
      using 'IP地址:1521/orcl';

    b、创建“MATERIALIZED”视图和刷新方式

    CREATE MATERIALIZED VIEW SN_VEH_IS_COMM_EXCH_BLOB_OUT
    REFRESH FAST ON DEMAND
    START WITH TO_DATE('30-12-2016 12:28:24', 'DD-MM-YYYY HH24:MI:SS')     NEXT SYSDATE+1/(24*60)
    AS
    SELECT "VEH_IS_COMM_EXCH_BLOB"."BH"                 "BH","VEH_IS_COMM_EXCH_BLOB"."SJLX"     "SJLX","VEH_IS_COMM_EXCH_BLOB"."ZJ" "ZJ","VEH_IS_COMM_EXCH_BLOB"."ZJZ" "ZJZ","VEH_IS_COMM_EXCH_BLOB"."SJNR1" "SJNR1","VEH_IS_COMM_EXCH_BLOB"."SJNR2" "SJNR2","VEH_IS_COMM_EXCH_BLOB"."SJNR3" "SJNR3","VEH_IS_COMM_EXCH_BLOB"."SJNR4" "SJNR4","VEH_IS_COMM_EXCH_BLOB"."SJNR5" "SJNR5","VEH_IS_COMM_EXCH_BLOB"."SJDX1" "SJDX1","VEH_IS_COMM_EXCH_BLOB"."SJDX2" "SJDX2","VEH_IS_COMM_EXCH_BLOB"."SJDX3" "SJDX3","VEH_IS_COMM_EXCH_BLOB"."SJDX4" "SJDX4","VEH_IS_COMM_EXCH_BLOB"."SJDX5" "SJDX5","VEH_IS_COMM_EXCH_BLOB"."CLBJ" "CLBJ","VEH_IS_COMM_EXCH_BLOB"."CJSJ" "CJSJ","VEH_IS_COMM_EXCH_BLOB"."GXSJ" "GXSJ" FROM "VEH_IS_COMM_EXCH_BLOB"@"数据库连接名称"

"VEH_IS_COMM_EXCH_BLOB";

    手动刷新

    begin
    DBMS_SNAPSHOT.REFRESH('SN_VEH_IS_COMM_EXCH_BLOB_OUT','F');
    end;

    c、创建触发器

    CREATE OR REPLACE TRIGGER TRI_VEH_IS_COMM_EXCH_BLOB_AFR
    AFTER DELETE OR INSERT OR UPDATE
    ON SN_VEH_IS_COMM_EXCH_BLOB_OUT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW

declare
        tmp_id number(30):=-1;
    begin
      --dbms_output.put_line('begin');
      if inserting then
          --select bh into tmp_id from veh_is_comm_exch_blob where bh=:new.bh;
          for p in(select bh from veh_is_comm_exch_blob where bh=:new.bh)
          loop
            tmp_id:=p.bh;
          end loop;

          --dbms_output.put_line(tmp_id||'===------------');
          if (tmp_id=-1) then
              insert into veh_is_comm_exch_blob (BH, SJLX, ZJ, ZJZ, SJNR1, SJNR2, SJNR3, SJNR4, SJNR5, SJDX1, SJDX2, SJDX3, SJDX4, SJDX5, CLBJ, CJSJ, GXSJ)
values (:new.BH,:new.SJLX,:new.ZJ,:new.ZJZ,:new.SJNR1,:new.SJNR2,:new.SJNR3,:new.SJNR4,:new.SJNR5,:new.SJDX1,:new.SJDX2,:new.SJDX3,:new.SJDX4,:new.SJDX5,:new.CLBJ,:new.CJSJ,:new.GXSJ);
          end if;
      end if;

      if updating then
         --dbms_output.put_line('updated');
         for p in(select bh from veh_is_comm_exch_blob where bh=:old.bh)
         loop
             if (p.bh=:new.bh)then
                  update veh_is_comm_exch_blob set SJLX=:new.SJLX, ZJ=:new.ZJ, ZJZ=:new.ZJZ, SJNR1=:new.SJNR1, SJNR2=:new.SJNR2, SJNR3=:new.SJNR3, SJNR4=:new.SJNR4, SJNR5=:new.SJNR5, SJDX1=:new.SJDX1, SJDX2=:new.SJDX2, SJDX3=:new.SJDX3, SJDX4=:new.SJDX4, SJDX5=:new.SJDX5, CLBJ=:new.CLBJ, CJSJ=:new.CJSJ, GXSJ=:new.GXSJ where bh=:old.bh;
             end if;
         end loop;
      end if;

      if deleting then
          --dbms_output.put_line('deleted');
          delete from veh_is_comm_exch_blob where bh=:old.bh;
      end if;
      --dbms_output.put_line('end');
    end TRI_VEH_IS_COMM_EXCH_BLOB_AFR;

2、更新或插入数据之前，更改数据的触发器

CREATE OR REPLACE TRIGGER TRI_FRM_WS_CONTROL_AFR
    BEFORE INSERT OR UPDATE
    ON FRM_WS_CONTROL
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    begin
      if inserting OR updating then
          if (:NEW.JKXLH='数据') then
              :NEW.IPDZ1:='更新的数据';
          end if;
          if (:NEW.JKXLH='数据') then
              :NEW.IPDZ1:='更新的数据';
          end if;
          if (:NEW.JKXLH='数据') then
              :NEW.IPDZ1:='更新的数据';
          end if;
      end if;
    end FRM_WS_CONTROL_AFR;