oracle中的行触发器,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;

注意：本文归作者所有，未经作者允许，不得转载