11 数据库的触发器trigger
定义：根据发生的事件而执行的一种存储子程序
数据库事件：DML事件、DDL事件、用户事件、系统事件
触发器分类：DML触发器、DDL触发器、系统用户事件触发器
11.1 触发器的创建
(1) DML触发器
A insert事件
create or replace trigger ai_org_trig
before insert on org_tab
for each row
begin
update sec_hrc_audit set num_rows=num_rows+1 where hrc_code=:NEW.hrc_code;
if sql%notfound then
insert into sec_hrc_audit values(:NEW.hrc_code,1);
end if;
exception when others then
null;
end;
测试：
SQL> insert into org_tab values(4,1008,‘test1‘,‘test2‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sec_hrc_audit;
HRC_CODE NUM_ROWS
---------- ----------
4 1
1 3
2 3
SQL> insert into org_tab values(4,1009,‘test1‘,‘test2‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sec_hrc_audit;
HRC_CODE NUM_ROWS
---------- ----------
4 2
1 3
2 3
注意：
触发器也是一种对象
select * from user_objects where object_type=‘TRIGGER‘;
触发器的字典视图：
select * from user_triggers; --有整个触发器的详细介绍
(2) update事件的捕获
创建一个监控表
create table tr_org_tab as select * from org_tab where 1=2;
create or replace trigger ai_org_trig
before update on org_tab
create or replace trigger ai_org_trig
before delete or update or insert on org_tab
for each row
begin
if inserting then --insert事件对应inserting操作
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘NEW‘,sysdate,‘INSERT‘);
elsif updating then
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate,‘UPDATE‘);
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘NEW‘,sysdate,‘UPDATE‘);
elsif deleting then
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate,‘DELETE‘);
end if;
exception when others then
null;
end;
for each row
begin
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name);--更新之前的值
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name);--更新之后的值
exception when others then
null;
end;
注意：:OLD跟:NEW只要修改了一个值，就能够获取到所修改值那一行的记录的所有字段的值。主键在生产机上只能删除，不能修改，不用考虑主键更新的问题。
测试：
SQL> update org_tab set org_long_name=‘update long‘,org_short_name=‘update short‘ where hrc_code=4;
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from tr_org_tab;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME
-------- --------- ------------------------------ ------------------------------------------------------------
4 1008 test1 test2
4 1008 update short update long
4 1009 test1 test2
4 1009 update short update long
加两个字段：值的类型：NEW OLD、update的时间
SQL> alter table tr_org_tab add on_flag varchar2(10); --NEW和OLD字段存储
Table altered.
SQL> alter table tr_org_tab add oper_date date; --操作的时间
Table altered.
SQL> truncate table tr_org_tab;
Table truncated.
修改上面的程序
create or replace trigger ai_org_trig
before update on org_tab
for each row
begin
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate); --更新之前的值
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘NEW‘,sysdate);
exception when others then
null;
end;
SQL> update org_tab set org_long_name=‘update long‘,org_short_name=‘update short‘ where hrc_code=4;
2 rows updated.
SQL> commit;
Commit complete.
验证：
SQL> select * from tr_org_tab;
(3) delete事件的捕获
要求捕获到原值，再加一个字段，写入事件类型：INSERT、UPDATE、DELETE
SQL> alter table tr_org_tab add oper_flag varchar2(10); --操作的类型
Table altered.
create or replace trigger ai_org_trig
before delete on org_tab
for each row
begin
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate,‘DELETE‘); --更新之前的值
exception when others then
null;
end;
测试：
SQL> delete from org_tab where hrc_code=4;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from tr_org_tab;
(4)将上面的三个触发器合并
create or replace trigger ai_org_trig
before delete or update or insert on org_tab
for each row
begin
if inserting then --insert事件对应inserting操作
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘NEW‘,sysdate,‘INSERT‘);
elsif updating then
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate,‘UPDATE‘);
insert into tr_org_tab values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘NEW‘,sysdate,‘UPDATE‘);
elsif deleting then
insert into tr_org_tab values(:OLD.hrc_code,:OLD.org_id,:OLD.org_short_name,:OLD.org_long_name,‘OLD‘,sysdate,‘DELETE‘);
end if;
exception when others then
null;
end;
(5)测试
SQL> insert into org_tab values(4,1008,‘test1‘,‘test2‘);
1 row inserted
SQL> commit;
Commit complete
SQL> update org_tab set org_long_name=‘update long 1‘,org_short_name=‘update short 1‘ where hrc_code=4;
1 row updated
SQL> commit;
Commit complete
SQL> delete from org_tab where org_id=1008;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from tr_org_tab;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME ON_FLAG OPER_DATE OPER_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- ----------
4 1008 update short update long OLD 2014-06-16 DELETE
4 1009 update short update long OLD 2014-06-16 DELETE
4 1008 test1 test2 NEW 2014-06-16 INSERT
4 1008 test1 test2 OLD 2014-06-16 UPDATE
4 1008 update short 1 update long 1 NEW 2014-06-16 UPDATE
4 1008 update short 1 update long 1 OLD 2014-06-16 DELETE
实现触发器的时候要注意：
A 触发器的实现主体中不能够出现commit或者rollback，触发器是自动提交的
B 触发器内部是不能出现DDL语句。
案例1：限制对departments修改，提示：不允许在非工作时间修改
create or replace trigger tr_dept_time
before insert or update or delete on departments
for each row
begin
if (to_char(sysdate,‘DAY‘) in (‘SATURDAY‘,‘SUNDAY‘)) or (to_char(sysdate,‘HH24:MI‘) not between ‘08:30‘ and ‘18:00‘) then
raise_application_error(-20001,‘不是上班时间，不能修改‘);
end if;
exception when others then
dbms_output.put_line(sqlerrm);
end;
SQL> update departments set department_name=department_name||‘XX‘ where department_id=80;
ORA-20001: 不是上班时间，不能修改
1 row updated.
SQL> select department_name from departments where department_id=80;
DEPARTMENT_NAME
------------------------------
SalesXXXX
仅仅只能发出一个提示，限制不了用户修改表的数据，不能够达到目的
限制用户操作表：用到替代触发器
替代触发器需要建立在视图上面
create or replace view v_dept as select * from departments;
create or replace trigger tr_dept
instead of insert or update or delete on v_dept
for each row
begin
if (to_char(sysdate,‘DAY‘) in (‘SATURDAY‘,‘SUNDAY‘)) or (to_char(sysdate,‘HH24:MI‘) not between ‘08:30‘ and ‘18:00‘) then
dbms_output.put_line(‘不是上班时间，不能修改‘);
else
update departments set department_name=department_name||‘XX‘ where department_id=80;
end if;
exception when others then
dbms_output.put_line(sqlerrm);
end;
update v_dept set department_name=department_name||‘XX‘ where department_id=80;
(2) 触发器对字段的捕获
create or replace trigger tr_emp_sal_comm
before update of salary,commission_pct or delete on employees
for each row
when(old.department_id=80) --对触发条件进行过滤，只针对部门80的员工
begin
case when updating(‘salary‘) then
if :new.salary < :old.salary then
raise_application_error(-20001,‘部门80员工的工资不能降‘);
end if;
when updating(‘commission_pct‘) then
if :new.commission_pct < :old.commission_pct then
raise_application_error(-20002,‘部门80员工的奖金不能降‘);
end if;
when deleting then
raise_application_error(-20003,‘部门80员工的数据不能删除‘);
end case;
exception when others then
dbms_output.put_line(sqlerrm);
end;
SQL> update employees set salary=13000 where employee_id=145;
ORA-20001: 部门80员工的工资不能降
1 row updated.
(3)级联修改表
利用行级触发器实现级联更新，修改主表regions中region_id之后，级联地，自动更新字表中countries表中原来在该地区的region_id
create or replace trigger tr_reg_coun
after update of region_id on regions --字段用of,表用on
for each row
begin
update countries set region_id=:new.region_id where region_id=:old.region_id;
exception when others then
null;
end;
测试：
SQL> update regions set region_id=5 where region_id=4;
1 row updated.
SQL> commit;
Commit complete.
但是没有触发器直接改会报错，因为破坏了主外键参考关系
SQL> alter trigger tr_reg_coun disable; --禁用触发器的命令
Trigger altered.
SQL> update regions set region_id=7 where region_id=5; --不用触发器，外键约束冲突
update regions set region_id=7 where region_id=5
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.COUNTR_REG_FK) violated - child record found
SQL> alter trigger tr_reg_coun enable;
Trigger altered.
SQL> update regions set region_id=4 where region_id=5;
1 row updated.
SQL> commit;
Commit complete.
触发时机：
before：表示执行DML之前触发点火，以防止某些错误的操作发生或者实现某些业务规则
after：表示执行DML之后触发点火，在执行trigger的过程中，这个行会被暂时锁定
触发器可以创建在表或者视图上。
条件谓词：inserting表示insert操作，updating表示update操作、deleting表示delete操作
触发对象：行、字段
##########################################################################################
练习：做一个触发器
捕获org_tab上变化的数据，DML操作都要捕获，捕获每个字段的变化情况，将变化后的值存储在另外一张表中
当在org_tab插入数据的时候，将所有的NEW值捕获到，插入表org_tab_1
当在org_tab修改数据的时候，用NEW值覆盖掉监控表中的OLD值，保留最新的值
当在org_tab删除数据的时候，将对应OLD值删除，逻辑上删除
SQL> desc org_tab_1;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
HRC_CODE NUMBER(4) Y
ORG_ID NUMBER(8)
ORG_SHORT_NAME VARCHAR2(30) Y
ORG_LONG_NAME VARCHAR2(60) Y
OPER_TYPE VARCHAR2(10) Y --操作的类型
OPER_DATE DATE Y --时间
OPER_IDX VARCHAR2(20) Y --NEW OLD
DEL_FLAG VARCHAR2(2) Y --‘Y‘ 删除 ‘N‘ 不删除
create table org_tab_1(
hrc_code number(4),
org_id number(8) primary key,
org_short_name varchar2(30),
org_long_name varchar2(60),
oper_type varchar2(10),
oper_date date,
oper_idx varchar2(20),
del_flag varchar2(2)
);
alter table org_tab_1 add constraint FK_ORG foreign key(hrc_code) references hrc_tab(hrc_code);
create or replace trigger tr_org_tab
before insert or update or delete on org_tab
for each row
begin
if inserting then
insert into org_tab_1 values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘I‘,sysdate,‘NEW‘,‘N‘);
elsif updating then
update org_tab_1
set hrc_code=:new.hrc_code,
org_short_name=:new.org_short_name,
org_long_name=:new.org_long_name,
oper_type=‘U‘,oper_date=sysdate
where org_id=:new.org_id;
elsif deleting then
update org_tab_1
set oper_type=‘D‘,
oper_date=sysdate,
oper_idx=‘OLD‘,
del_flag=‘Y‘
where org_id=:old.org_id;
end if;
exception when others then
null;
end;
测试：
SQL> insert into org_tab values(2,1009,‘test1‘,‘test2‘);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 test1 test2 I 2014-06-16 NEW N
SQL> update org_tab set org_short_name=‘xxxx‘ where org_id=1009;
1 row updated
SQL> commit;
Commit complete
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 xxxx test2 U 2014-06-16 NEW N
SQL> delete from org_tab where org_id=1009;
1 row deleted
SQL> commit;
Commit complete
SQL> insert into org_tab values(2,1009,‘test1‘,‘test2‘);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 test1 test2 I 2014-06-16 NEW N
SQL> update org_tab set org_short_name=‘xxxx‘ where org_id=1009;
1 row updated
SQL> commit;
Commit complete
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 xxxx test2 U 2014-06-16 NEW N
SQL> delete from org_tab where org_id=1009;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 xxxx test2 D 2014-06-16 OLD Y
SQL> select * from org_tab_1;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME OPER_TYPE OPER_DATE OPER_IDX DEL_FLAG
-------- --------- -------------------- -------------------- ---------- ----------- -------------------- --------
2 1009 xxxx test2 D 2014-06-16 OLD Y
###########################################################################################
11.2 用触发器做数据同步、数据镜像、数据更新
数据同步--逻辑上的数据同步
数据复制--需要额外的工具或者组件(goldengate)
数据接口--特殊的数据同步、将数据的一部分传输到临时表上，另外的库定时从临时表上面抓取数据
(1) 数据链路
A 授权创建
SQL> conn /as sysdba
Connected.
SQL> grant create public database link to plsql; --所有的用户都能使用,公共数据链路
Grant succeeded.
B 建立数据链路
SQL> conn plsql/plsql
Connected.
SQL> create public database link DBL connect to plsql identified by plsql using ‘ORCL159‘;
Database link created.
C 本地的表和远端的表作联合查询
SQL> select * from hrc_tab o,org_tab@DBL h where o.hrc_code=h.hrc_code;
HRC_CODE HRC_DESCR HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME
-------- -------------------- -------- --------- ------------------------------ ------------------------------
1 CEO/COO 1 1003 Office of CEO DataPro Inc. Office of CEO DataPro Inc.
1 CEO/COO 1 1002 Office of CEO XYZ Inc. Office of CEO XYZ Inc.
1 CEO/COO 1 1001 Office of CEO ABC Inc. Office of CEO ABC Inc.
2 VP 2 1006 Office of VP Tech ABC Inc. Office of VP Tech ABC Inc.
2 VP 2 1005 Office of VP Mktg ABC Inc. Office of VP Mktg ABC Inc.
2 VP 2 1004 Office of VP Sales ABC Inc. Office of VP Sales ABC Inc.
D 对远端的表做数据修改：
SQL> update org_tab@DBL set org_short_name=org_short_name||‘ZZZ‘ where org_id=1006;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from org_tab@DBL where org_id=1006;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME
-------- --------- ------------------------------ ------------------------------
2 1006 Office of VP Tech ABC Inc.ZZZ Office of VP Tech ABC Inc.
SQL> select * from org_tab where org_id=1006;
HRC_CODE ORG_ID ORG_SHORT_NAME ORG_LONG_NAME
-------- --------- ------------------------------ ------------------------------
2 1006 Office of VP Tech ABC Inc. Office of VP Tech ABC Inc.
这种数据链路也是分布式数据库的基础，数据链路能够平摊磁盘IO，分摊压力，但是这种收到网络带宽的限制
(2) 数据同步
数据同步可以使用两种发发来实现，第一种使用触发器，第二种使用时间戳的方式
触发器方法：
A 创建另外一个数据库实例LAW
B 在TNS中添加到这个数据库的连接，测试访问
C 在LAW中创建PLSQL用户，和ORCL实例创建过程一样
[oracle@oracle3 ~]$ export ORACLE_SID=LAW
[oracle@oracle3 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 16 15:17:54 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
LAW
SQL> create tablespace plsql datafile ‘/u01/oracle/oradata/LAW/plsql01.dbf‘ size 1G; --创建表空间
Tablespace created.
SQL> create user plsql identified by plsql default tablespace plsql; --创建用户并指定表空间
User created.
SQL> grant connect,resource to plsql;
Grant succeeded.
SQL> conn plsql/plsql;
Connected.
D 在ORCL实例上讲PLSQL用户下的org_tab同步到LAW实例的PLSQL用户下
1) 在LAW的PLSQL用户下创建目标表：ORG_TAB
创建的脚本可以通过developer工具导出
create table ORG_TAB
(
hrc_code NUMBER(4) not null,
org_id NUMBER(8) not null,
org_short_name VARCHAR2(30),
org_long_name VARCHAR2(60)
);
alter table ORG_TAB
add constraint PK_ORG_TAB primary key (HRC_CODE, ORG_ID);
alter table ORG_TAB
add constraint PK_ORG_TAB_UK unique (ORG_ID);
alter table ORG_TAB
add constraint ORG_TAB_FK foreign key (HRC_CODE)
references HRC_TAB (HRC_CODE);
2) 创建数据链路，在ORCL上面
SQL> conn /as sysdba
Connected.
SQL> grant create public database link to plsql;
Grant succeeded.
SQL> conn plsql/plsql
Connected.
SQL> create public database link DBLX connect to plsql identified by plsql using ‘LAW‘;
Database link created.
注意：‘LAW‘--TNSNAME,要配置，因为ORCL和LAW之间要互访(数据链路建立在哪个数据库上，就在哪个数据库的服务器上面配置)
3) 测试数据链路
SQL> select * from org_tab@DBLX;
no rows selected
4) 在原库上的表上面创建触发器 -- 抓取表的增量
全量 ： 只要加载一次就可以
create or replace procedure SYN_Q(v_retcd out number,v_errmsg out varchar2) is
v_time date;
begin
insert into org_tab@DBLX
select * from org_tab;
v_retcd:=0;
v_errmsg:=‘successful!‘;
v_time:=trunc(sysdate,‘dd‘);
insert into sync_audit values(v_time,‘Y‘);
commit;
exception when others then
v_retcd:=sqlcode;
v_errmsg:=sqlerrm;
end;
增量：使用触发器来实现增量的抓取，一天同步一次
create or replace trigger tr_org_tab
before insert or update or delete on org_tab
for each row
begin
if inserting then
insert into org_tab_1 values(:NEW.hrc_code,:NEW.org_id,:NEW.org_short_name,:NEW.org_long_name,‘I‘,sysdate,‘NEW‘,‘N‘);
elsif updating then
update org_tab_1
set hrc_code=:new.hrc_code,
org_short_name=:new.org_short_name,
org_long_name=:new.org_long_name,
oper_type=‘U‘,oper_date=sysdate
where org_id=:new.org_id;
elsif deleting then
update org_tab_1
set oper_type=‘D‘,
create or replace procedure SYN_Q(v_retcd out number,v_errmsg out varchar2) is
v_time date;
begin
insert into org_tab@DBLX
select * from org_tab;
v_retcd:=0;
v_errmsg:=‘successful!‘;
v_time:=trunc(sysdate,‘dd‘);
insert into sync_audit values(v_time,‘Y‘);
commit;
exception when others then
v_retcd:=sqlcode;
v_errmsg:=sqlerrm;
end;
oper_date=sysdate,
oper_idx=‘OLD‘,
del_flag=‘Y‘
where org_id=:old.org_id;
end if;
exception when others then
null;
end;
问题：
怎样抓取过去一天的数据：
select * from org_tab_1 where oper_date>=trunc(sysdate,‘dd‘)-1 and oper_date<trunc(sysdate,‘dd‘);
抓取前一个小时
select * from org_tab_1 where oper_date>=trunc(sysdate,‘hh‘)-1/24 and oper_date<trunc(sysdate,‘hh‘);
5) 做时间拉链：创建一个数据处理登记表
create table SYNC_AUDIT
(
AS_DATE DATE not null,
AS_FLAG varchar2(2)
);
alter table sync_audit add constraint PK_AS_FLAG primary key(AS_DATE);
6)分析如何处理DML操作
I --只要找一个逐渐的只有I一次的，直接插入
I--U(n) --找到最后一次U的，插入
I--U(n)--D --不处理
U--U(n)--D --找到对应的主键删除
U--U(n) --找到最后一次U，到目标库上U
D --只要找到一个主键只有D一次，直接删除
7）总结思路：
1、将所有I的直接插入到目标表
2. 将所有U标记的，找到每个主键修改的U标记的数据，拿到目标库上，存在update，不存在insert
3、所有D拿出来，直接到目标表上D
8）编写SP
create or replace procedure SYN_Q(v_retcd out number,v_errmsg out varchar2) is
v_time date;
begin
insert into org_tab@DBLX
select * from org_tab;
v_retcd:=0;
v_errmsg:=‘successful!‘;
v_time:=trunc(sysdate,‘dd‘);
insert into sync_audit values(v_time,‘Y‘);
commit;
exception when others then
v_retcd:=sqlcode;
v_errmsg:=sqlerrm;
end;
create or replace procedure sp_sync is
v_time1 date;
v_time2 date;
v_time3 date;
x number;
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
/*将sync_audit表上的数据增加到今天*/
select max(as_date) into v_time3 from sync_audit;
if trunc(v_time3,‘dd‘)<trunc(sysdate,‘dd‘) then
loop
exit when v_time3=trunc(sysdate,‘dd‘);
insert into sync_audit values(v_time3+1,‘N‘);
commit;
v_time3:=v_time3+1;
end loop;
end if;
/*获取增量处理的开始时间和结束时间*/
select count(1) into x from sync_audit where as_flag=‘Y‘;
if x <> 0 then
select as_date into v_time1 from (select as_date
from sync_audit
where as_flag=‘Y‘
order by as_date desc)
where rownum=1;
else
select min(as_date) into v_time1 from sync_audit;
end if;
v_time2:=trunc(sysdate,‘dd‘);
/*处理增量*/
/*处理物理删除操作的*/
delete from org_tab@dblx a
where exists (select null
from org_tab_1 b
where b.del_flag=‘Y‘
and b.oper_date>=v_time1
and b.oper_date<v_time2
and a.org_id=b.org_id);
commit;
/*处理插入和修改操作的*/
merge into org_tab@DBLX a1
using(select * from org_tab_1
where del_flag=‘N‘ and oper_date>=v_time1 and oper_date<v_time2) a2
on (a1.org_id=a2.org_id)
when matched then
update set
a1.hrc_code=a2.hrc_code,
a1.org_short_name=a2.org_short_name,
a1.org_long_name=a2.org_long_name
when not matched then
insert (a1.hrc_code,a1.org_id,a1.org_short_name,a1.org_long_name)
values (a2.hrc_code,a2.org_id,a2.org_short_name,a2.org_long_name);
commit;
/*如果成功没有异常，处理增量时间段的时间拉链全部置为Y*/
loop
exit when (v_time1>v_time2);
update sync_audit set as_flag=‘Y‘ where as_date=v_time1;
commit;
v_time1:=v_time1+1;
end loop;
exception when others then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into exception_monitor values(‘ORG_TAB‘,NULL,upper(‘sp_sync‘),‘OTHERS‘,v_sqlcode,v_sqlerrm,sysdate);
end;
测试：
A 全量加载
declare
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
syn_q(v_sqlcode,v_sqlerrm);
dbms_output.put_line(v_sqlerrm);
end;
B 插入、删除、修改原库
insert into org_tab values(4,1007,‘test1‘,‘test2‘);
insert into org_tab values(4,1008,‘test3‘,‘test4‘);
insert into org_tab values(4,1009,‘test5‘,‘test6‘);
commit;
update org_tab set org_short_name=‘test short name‘ where org_id=1008;
commit;
delete from org_tab where org_id=1009;
commit;
create or replace procedure SYN_Q(v_retcd out number,v_errmsg out varchar2) is
v_time date;
begin
insert into org_tab@DBLX
select * from org_tab;
v_retcd:=0;
v_errmsg:=‘successful!‘;
v_time:=trunc(sysdate,‘dd‘);
insert into sync_audit values(v_time,‘Y‘);
commit;
exception when others then
v_retcd:=sqlcode;
v_errmsg:=sqlerrm;
end;
注意系统时间
begin
sp_sync;
end;
select * from org_tab@dblx;
9)创建后台作业
declare
job number;
begin
dbms_job.submit(job,‘sp_sync;‘,next_date=>to_date(‘20140620000001‘,‘yyyy-mm-dd hh24:mi:ss‘),interval => ‘sysdate +1‘);
commit;
end;
确认：
select * from user_jobs;
删除掉后台作业
begin
dbms_job.remove(job_number);
end;
select * from org_tab@dblx;
以上使用触发器的最大风险是：
当修改了表的定义的时候，比如添加或者删除字段，此时触发器将失效，编译错误，此时将不点火，这段时间将丢失增量数据
可以使用时间戳的方式解决以上的风险
(2) 时间戳方式
A 在源库设计的时候每个表上加三个字段
1. 插入时间 2.最后修改的时间 3.删除标记
B 第一次插入的时候，插入时间=最后修改时间 以后修改只修改‘最后修改时间‘，数据可以逻辑删除
org_mater中createOrg存储过程要修改，updateOrg存储过程要修改
removeOrgSite存储过程的delete操作修改为：update org_tab set del_flag=‘1‘ where org_id=ip_org_id;
获取增量：在原表上按照时间范围就能找到数据，抛弃触发器，直接merge到目标表
如果删除操作：只能够用全表对比的方式进行更新，性能损耗很大，如果有删除标记，不存在delete操作，一个merge就可以同步。
先同步没有被删除的
再去同步被删除
###########################################################################################
11.3 语句级触发器
statement触发器--语句级触发器
(1)案例：
A 克隆一个空表
SQL> create table org_tab_2 as select * from org_tab where 1=3;
Table created
B 创建一个语句级别触发器 -- 没法办法获取行的属性 NEW OLD
create or replace trigger ai_org_trig_statement
after insert on org_tab_2 --语句级触发器的写法，没有for each row
begin
for idx in (select hrc_code,count(1) as cnt from org_tab_2 group by hrc_code) loop
update sec_hrc_audit set num_rows=idx.cnt where hrc_code=idx.hrc_code;
if sql%notfound then
insert into sec_hrc_audit values(idx.hrc_code,idx.cnt);
end if;
end loop;
exception when others then
null;
end;
C 测试
SQL> truncate table sec_hrc_audit;
Table truncated.
SQL> insert into org_tab_2 select * from org_tab;
8 rows created.
SQL> commit;
Commit complete.
SQL> select * from sec_hrc_audit;
HRC_CODE NUM_ROWS
---------- ----------
1 3
2 3
4 2
(2)行级触发器与语句级触发器的区别
ROW级别触发器 statement级别触发器
-----------------------------------------------------------------------------------------------
触发器每影响一行点火一次 只为一个SQL语句点火一次
如果触发器没有影响到任何行触发器不点火 没有影响到行也会点火
可以指定before或者after 可以指定before或者after
可以访问行数据OLD和NEW 没有办法访问行数据
after方法触发器锁定行数据 不会锁定行
##########################################################################################
11.4 一些字句的使用
referencing字句：只能出现在行级触发器中
create or replace trigger ai_trig_ref
after insert or update or delete on org_tab
referencing new as new_org old as old_org --取代:NEW和:OLD,相当于取别名
for each row
begin
update sec_hrc_audit set num_rows=num_rows+1 where hrc_code=:new_org.hrc_code; --相当于:new.hrc_code
if sql%notfound then
insert into sec_hrc_audit values(:new_org.hrc_code,1);
end if;
exception when others then
null;
end;
when字句：
create or replace trigger ai_trig_ref
after insert or update or delete on org_tab
referencing new as new_org old as old_org --取代:NEW和:OLD,相当于取别名
for each row
when(new_org.hrc_code<>1) --<>不等于符号，过滤触发条件，当不等于1时才触发
begin
update sec_hrc_audit set num_rows=num_rows+1 where hrc_code=:new_org.hrc_code;
if sql%notfound then
insert into sec_hrc_audit values(:new_org.hrc_code,1);
end if;
exception when others then
null;
end;
注意：new_org.hrc_code=1的话，此时触发器不点火
when字句必须放在for each row 的后面
when引用new_org.hrc_code前面是没有冒号的
###########################################################################################
11.5 触发器数据字典的结构
SQL> desc user_triggers;
Name Null? Type
----------------------------------------- -------- ----------------------------
TRIGGER_NAME VARCHAR2(30) --触发器的名称
TRIGGER_TYPE VARCHAR2(16)
--触发器的类型，可以看出触发的时机还有是什么类型的触发器，行级还是语句级的
TRIGGERING_EVENT VARCHAR2(227) --触发器事件类型
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16) --操作的对象(table)
TABLE_NAME VARCHAR2(30) --表名
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8) --触发器的状态，表示启用还是禁用
DESCRIPTION VARCHAR2(4000) --触发器的描述，也就是头部的语句
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG --触发器的内容
select * from user_objects where object_type=‘TRIGGER‘; --概要信息
触发器的状态：在user_objects这个视图中，触发器的状态表示有效的和无效的 VALID 和INVALID
在user_triggers视图中，触发器的状态表示启用还是禁用ENABLE 和 DISABLE
###########################################################################################
11.6 触发器的管理
(1) 启用、禁用、编译触发器
SQL> alter trigger tr_org_tab disable; --禁用触发器
Trigger altered
select * from user_triggers where trigger_name=‘TR_ORG_TAB‘; --status变为 DISABLE
SQL> alter trigger tr_org_tab enable; --启用触发器
Trigger altered
select * from user_triggers where trigger_name=‘TR_ORG_TAB‘; --status变为 ENABLE
当触发器失效的时候要重新编译，而且还是要能够编译通过，否则触发器不点火
情景：给org_tab_2表添加一个字段：
SQL> alter table org_tab_2 add x number;
Table altered.
select * from user_objectS where object_name=‘AI_ORG_TRIG_STATEMENT‘; --status为INVALID
SQL> alter trigger AI_ORG_TRIG_STATEMENT compile; --重新编译触发器，使触发器生效
Trigger altered.
select * from user_objectS where object_name=‘AI_ORG_TRIG_STATEMENT‘; --status为VALID
(2) 启用、禁用某个表上所有的触发器
SQL> alter table org_tab disable all triggers;
Table altered.
select * from user_triggers where table_name=‘ORG_TAB‘; --验证 DISABLE
SQL> alter table org_tab enable all triggers;
Table altered.
select * from user_triggers where table_name=‘ORG_TAB‘; --验证 ENABLE
(3) 关于触发器的权限
A 本用户下的触发器：create trigger、alter trigger、drop trigger
B 系统内： create any trigger、alter any trigger、drop any trigger
(4)触发器的限制：
A 触发器的脚本大小不能够超过32K
B 触发器内部不能够写DDL语句
C 不能出现commit或者rollback
D LONG字段不能用行级触发器
E 修改信息的时候违反了参考完整性，触发器会点火但是内部不执行
###########################################################################################
11.7 DDL事件触发器
捕获的事件是DDL事件
(1) 对用户授权
SQL> conn /as sysdba
Connected.
SQL> grant create any trigger to plsql;
Grant succeeded.
SQL> grant alter any trigger to plsql;
Grant succeeded.
SQL> grant drop any trigger to plsql;
Grant succeeded.
SQL> grant ADMINISTER DATABASE TRIGGER to plsql;
Grant succeeded.
(2) 创建触发器
方法1：通过sys系统变量创建DDL事件捕获触发器
创建一个监控表
create table ddl_audit(
object_name varchar2(30) not null, --DDL对象名
object_type varchar2(30) not null, --DDL的对象类型
when_created date not null, --DDL的时间
who_created varchar2(30) not null, --操作DDL的用户
op_type varchar2(30) not null --DDL类型
);
create or replace trigger after_ddl_creation
after create or alter or drop on schema
begin
insert into ddl_audit values(sys.dictionary_obj_name,sys.dictionary_obj_type,sysdate,sys.dictionary_obj_owner,sys.sysevent);
exception when others then
null;
end;
函数： 同义词
sys.dictionary_obj_name -- sys用户下的一个加密函数，返回操作的对象的名称 ora_dict_obj_name
sys.dictionary_obj_type -- 返回的是操作对象的对象类型 ora_dict_obj_type
sys.dictionary_obj_owner -- 返回的是操作DDL的用户 ora_dict_obj_owner
sys.sysevent -- 返回DDL的类型，create、alter、drop ora_sysevent
SQL> conn plsql/plsql
Connected.
SQL> create table x(u int);
Table created.
SQL> drop table x;
Table dropped.
SQL> create table y(u int);
Table created.
SQL> alter table y add yu int;
Table altered.
SQL> select * from ddl_audit;
OBJECT_NAME OBJECT_TYPE WHEN_CREATED WHO_CREATED OP_TYPE
------------------------------ ------------------------------ ------------ ------------------------------ -------
X TABLE 2014-06-19 1 PLSQL CREATE
X TABLE 2014-06-19 1 PLSQL DROP
Y TABLE 2014-06-19 1 PLSQL CREATE
Y TABLE 2014-06-19 1 PLSQL ALTER
方法2：使用同义词的方式
synonym 同义词
案例：将数据链路封装在同义词
先授权
SQL> conn /as sysdba
Connected.
SQL> grant create synonym to plsql;
Grant succeeded.
SQL> create or replace synonym "PLSQL"."SYNC" for "ORG_TAB"@"DBLX";
Synonym created
select * from org_tab@DBLX;
select * from sync;
create or replace trigger after_ddl_creation
after create or alter or drop on schema
begin
insert into ddl_audit values(ora_dict_obj_name,ora_dict_obj_type,sysdate,ora_dict_obj_owner,ora_sysevent);
exception when others then
null;
end;
###########################################################################################
11.8 系统事件触发器
(1) 捕获系统事件
普通用户想要捕获系统事件，必须要有dba权限
SQL> grant dba to plsql;
Grant succeeded.
after logon on database --登录系统后
before logoff on database --退出系统前
before shutdown on database --关机前
after startup on database --开机后
(2) 同义词
ora_client_ip_address --操作的客户端的ip地址
ora_database_name --操作的数据库的实例名
ora_login_user --登录操作的用户
ora_sql_txt --操作的文本
(3) 创建一个监控表
create table log_event(
user_name varchar2(10),
logon_date timestamp,
logoff_date timestamp
);
(4) 创建一个触发器
create or replace trigger tr_login
after logon on database
begin
insert into log_event values(ora_login_user,systimestamp,null);
exception when others then
null;
end;
Systimestamp 函数返回本机数据库上当前系统日期和时间(包括微秒和时区)
(5) 测试
SQL> conn plsql/plsql;
Connected.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is ""
SQL> conn scott/scott;
Connected.
SQL> conn plsql/plsql;
Connected.
查看：
SQL> select * from log_event;
练习1：
A 创建捕获用户退出事件的触发器
B 在监控表上加上ORA_DATABASE_NAME,ORA_CLIENT_IP_ADDRESS
SQL> conn plsql/plsql@ORCL
Connected.
SQL> select sys_context(‘userenv‘,‘ip_address‘) from dual;
SYS_CONTEXT(‘USERENV‘,‘IP_ADDRESS‘)
--------------------------------------------------------------------------------
10.1.3.3
练习2：
创建捕获数据库关闭的触发器
要求捕获关闭数据库的名称、执行关闭的用户、以及客户端的IP地址、另外还要捕获操作的时间
###########################################################################################
原文：http://fengsonglin.blog.51cto.com/9860507/1615261