ORACLE触发器的管理与实际应用【weber出品】
2014-09-03 21:37 yaoweber 阅读(420) 评论(0) 编辑 收藏 举报一、INSTEAD OF触发器
对于简单的视图可以执行INSERT,UPDATE和DELETE操作，但是对于复杂视图，不允许直接执行INSERT,UPDATE,DELETE操作，当视图出现以下任何一种情况时，都不允许直接执行DML操作，如下：
具有集合操作符（UNION,UNION ALL,INTERSECT,MINUS） 具有分组函数（MIN,MAX,SUM,AVG,COUNT等） 具有GROUP BY,CONNECT BY或START WITH等子句 具有DISTINCT关键字 具有连接查询
为了在具有以上情况的复杂视图上执行DML操作，必须要基于视图建立INSTEAD-OF触发器
在建立了INSTEAD-OF触发器之后，就可以基于复杂视图执行INSERT,UPDATE,DELETE,但建立INSTEAD-OF触发器有以下注意事项：
INSTEAD-OF选项只适用于视图 当基于视图建立触发器时，不能指定BEFORE和AFTER选项 在建立视图时没有指定WITH CHECK OPTION选项 当建立INSTEAD OF触发器时，必须指定FOR EACH ROW 选项
现在进行演示instead of 触发器的使用：
我们现在创建一个表：
SQL> conn /as sysdba 已连接。 SQL> grant create view to scott; 授权成功。 SQL> conn scott/tiger 已连接。 create or replace view dept_emp as select b.deptno,b.dname,a.empno,a.ename from emp a,dept b where a.deptno=b.deptno; 视图已创建。
插入数据：
SQL> desc dept_emp 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) SQL> insert into dept_view values(11,'dname','ename',120); insert into dept_view values(11,'dname','ename',120) ORA-01779: 无法修改与非键值保存表对应的列 SQL> insert into dept_emp values(50,'ADMIN',1223,'MARY'); insert into dept_emp values(50,'ADMIN',1223,'MARY') * 第 1 行出现错误: ORA-01779: 无法修改与非键值保存表对应的列
错误原因：这是个包含了两张表的负责视图，不可以进行dml操作。现在进行创建触发器：
create or replace trigger tr_instead_of_dept_emp instead of insert on dept_emp for each row declare v_temp int; begin select count(*) into v_temp from dept where deptno = :new.deptno; if v_temp = 0 then insert into dept (deptno, dname) values (:new.deptno, :new.dname); end if; select count(*) into v_temp from emp where empno = :new.empno; if v_temp = 0 then insert into emp (empno, ename) values (:new.empno, :new.ename); end if; end;
注意：
这里千万不要在select count(*) from dept_view where deptno=:new.deptno;不能从视图只查找内容
否则会报错：
SQL> insert into dept_view values(20,'dname','ename',7777); insert into dept_view values(20,'dname','ename',7777) ORA-00036: 超过递归 SQL 级别的最大值 50 ORA-06512: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 4 ORA-04088: 触发器 'SCOTT.TR_INSTEAD_DEPT_EMP' 执行过程中出错 ORA-06512: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 6 ORA-04088: 触发器 'SCOTT.TR_INSTEAD_DEPT_EMP' 执行过程中出错
正确插入的结果：
QL> insert into dept_emp values(50,'ADMIN',1223,'MARY'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ----- -------------- ----- ---------- ------ 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 4000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 1223 MARY 已选择15行。 SQL> select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 ADMIN
二、数据库事件触发器
系统事件触发器是指基于oracle系统事件(例如LOGON和STARTUP)所建立的触发器，通过使用系统事件触发器提供了跟踪系统或数据库变化的机制
下面介绍一些常用的系统事件属性函数，以及建立各种事件触发器的方法
1. 系统事件属性函数：
ora_client_ip_address:用于返回客户端的IP地址
ora_database_name:用于返回当前数据库名
ora_dict_obj_name:：用于返回DDL操作所对应的数据库对象名
ora_dict_obj_name_list(name_list out ora_name_list_t):用于返回在事件中被修改的对象名列表
ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名
ora_dict_obj_owner_list(owner_list out ora_name_list_t):用于返回在事件中被修改对象的所有者列表
ora_dict_obj_type:用于返回DDL操作对应的对象的类型
ora_grantee(user_list out ora_name_list_t):用于返回授权事件的授权者
ora_instance_num:用于返回实例号
ora_is_alter_column(cloumn_name in varchar2):用于检测特定列是否被修改
ora_is_creating_nested_table:用于检测是否正在建立嵌套表
ora_is_drop_column(column_name in varchar2):用于检测特定列是否被删除
ora_is_servererror(error_number):用于检测是否返回了特定oracle错误
ora_login_user:用于返回登录用户名
ora_sysevent:用于返回触发触发器的系统事件名
2.建立实例启动和关闭触发器
实例启动触发器和实例关闭触发器只有特权用户才能建立，实例启动触发器只能使用after关键字而实例关闭触发器只能使before关键字，示例如下：
为了跟踪实例启动和关闭事件，可以分别建立示例启动和示例关闭触发器，为了记载实例启动和关闭事件和时间，首先建立事件表event_table，示例如下：
conn scott/tiger create table event_table(event varchar2(30),time date);
在建立了事件表event_table之后，就可以在触发器中引用该表了，注意，实例启动触发器和实例关闭触发器只有特权用户才能建立，并且实例启动触发器只能使用after关键字，而实例关闭触发器只能使用before关键字，示例如下：
SQL> show user USER 为 "SCOTT" SQL> create table event_table(event varchar2(100),time date); 表已创建。
创建打开数据库触发器：
SQL> conn /as sysdba create or replace trigger tr_startup after startup on database begin insert into scott.event_table values(ora_sysevent,sysdate); end; SQL> / 触发器已创建
创建关闭数据库触发器：
create or replace trigger tr_shutdown before shutdown on database begin insert into scott.event_table values(ora_sysevent,sysdate); end; / 触发器已创建
查看数据：
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 595591168 bytes Fixed Size 1275032 bytes Variable Size 163580776 bytes Database Buffers 423624704 bytes Redo Buffers 7110656 bytes Database mounted. Database opened. SQL> conn scott/tiger Connected. SQL> select * from event_table; EVENT TIME ---------- --------- STARTUP 04-SEP-14 SHUTDOWN 04-SEP-14
3.建立登录和退出触发器
为了记录用户登录和退出事件，可以分别建立登录和退出触发器，为了记载登录用户和退出用户的名称时间和IP地址
应该首先建立专门存放登录和退出的信息表LOG_TABLE示例如下：
SQL> conn scott/tiger 已连接。 SQL> create table log_table( 2 username varchar2(10), 3 logontime date, 4 logofftime date, 5 address varchar2(20)); 表已创建。
创建登入触发器：
SQL> conn /as sysdba 已连接。 create or replace trigger tr_on after logon on database begin insert into scott.log_table (username, logontime, address) values (ora_login_user, sysdate, ora_client_ip_address); end;
触发器已创建
创建退出触发器：
create or replace trigger tr_off before logoff on database begin insert into scott.log_table (username, logofftime, address) values (ora_login_user, sysdate, ora_client_ip_address); end; 触发器已创建
测试触发器：
SQL> select * from scott.log_table;
USERNAME LOGONTIME LOGOFFTIME ADDRESS
---------- -------------- -------------- --------------------
SCOTT 03-9月 -14 192.168.0.5
exit
SQL> select * from scott.log_table;
USERNAME LOGONTIME LOGOFFTIME ADDRESS
---------- -------------- -------------- --------------------
SCOTT 03-9月 -14 192.168.0.5
SCOTT 03-9月 -14
4. 建立DDL触发器
为了记录系统所发生的DDL事件（CREATE,ALTER,DROP等），可以建立DDL触发器为了记载DDL时间信息，应该建立专门的表，以便存放DDL事件信息
示例如下：
SQL> conn /as sysdba 已连接。 create table event_ddl( event varchar2(20), username varchar2(10), owner varchar2(10), objname varchar2(10), objtype varchar2(10), time date); 表已创建。
创建记录DDL事件触发器：
create or replace trigger tr_ddl after ddl on scott.schema begin insert into event_ddl values(ora_sysevent, ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, sysdate); end; SQL> /
执行结果：
SQL> select * from event_ddl; 未选定行 SQL> conn scott/tiger 已连接。 SQL> drop table e purge; 表已删除。 SQL> drop table d purge; 表已删除。 SQL> create table e as select * from emp; 表已创建。 SQL> conn /as sysdba 已连接。 SQL> select * from event_ddl; EVENT USERNAME OWNER OBJNAME OBJTYPE TIME -------------------- ---------- ---------- ---------- ---------- -------------- DROP SCOTT SCOTT E TABLE 03-9月 -14 DROP SCOTT SCOTT D TABLE 03-9月 -14 CREATE SCOTT SCOTT E TABLE 03-9月 -14
三、管理触发器
1.显示触发器
建立触发器时，oracle会将触发器信息写入到数据字典中，通过查询数据字典视图user_triggers可以显示当前用户所包含的所有触发器信息，示例如下：
conn scott/tiger select trigger_name,status from user_triggers where table_name='EMP';
2.禁止触发器
禁止触发器是指使触发器临时失效，当触发器处于enable状态时，如果在表上执行DML操作则就会触发相应的触发器，如果基于insert操作建立了触发器，当使用SQL*loader装载大批
量数据时会触发触发器，为了加快数据装载速度，应该在装载数据之前禁止触发器
方法如下：
conn /as sysdba
alter trigger tr_ddl disable;
3.激活触发器
激活触发器是指使用触发器重新生效，当使用SQL*Loader转载完了数据之后为了使被禁止的触发器生效，应该激活触发器
方法如下：
alter trigger tr_ddl enable;
4.禁止或激活表的所有触发器
如果在表上同时存在多个触发器，那么使用alter table命令可以一次禁止或激活所有触发器，示例如下：
alter table emp disable all triggers;
alter table emp enable all triggers;
5.重新编译触发器
当使用alter table命令修改表结构（例如增加列、删除列）时，会使得其触发器转变为invalid状态在这种情况下为了使得触发器继续生效，需要重新编译触发器，示例如下：
alter trigger tr_check_sal compile;
6.删除触发器
drop trigger tr_ddl;
四、DML触发器在实际中的应用
为了确保数据库满足特定的商业规则或企业逻辑，可以使用约束，触发器和子程序实现由于约束性能最好，实现最简单，所以首选约束，如果使用约束不能实现特定规则
那么因该选择触发器，如果触发器仍然不能实现特定规则，那么应该选择子程序（过程和函数）DML触发器可以用于实现数据库安全，数据审计，数据完整性，参照完整性
等功能
1. 控制数据安全
create or replace trigger tr_emp_time before insert or delete or update on emp begin if to_number(to_char(sysdate, 'HH24')) not between 9 and 17 then raise_application_error(-20001, '不能在休息时间更改员工信息'); end if; end;
建立了触发器tr_emp_time之后，只能在9:00-17:00之间在emp表上执行DML操作，如果不在该时间段则会报错
2. 实现数据审计
如果要在emp上执行insert，update，delete操作，oracle只会记录sql操作，不会记录数据变化，用dml审计可以记录变化
create or replace trigger tr_sal_change after update of sal on emp for each row declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name = :old.ename; if v_temp = 0 then insert into audit_emp_change values (:old.ename, :old.sal, :new.sal, sysdate); else update audit_emp_change set oldsal = :old.sal, newsal = :new.sal, time = sysdate where name = :old.ename; end if; end; /
执行结果：
SQL> update emp set sal=sal+100 where ename='ywb'; update emp set sal=sal+100 where ename='ywb' ORA-20001: 不能在员工休息的时间修改信息 ORA-06512: 在 "SCOTT.TR_EMP_TIME", line 3 ORA-04088: 触发器 'SCOTT.TR_EMP_TIME' 执行过程中出错
在建立了触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中
3. 实现数据完整性
数据的完整性用于确保数据库数据满足特定的商业逻辑或企业规则，数据完整性可以通过触发器和子程序约束来实现，因为约束的实现最简单，性能也好，所以实现数据完整性首选约束
例如为了限制雇员工资不能低于800元，可以选用check约束，示例如下：
alter table emp add constraint ck_sal check(sal>=800);
但某些情况下使用约束无法实现特定的商业规则，此时可以使用触发器来实现数据完整性，例如，假定希望雇员的新工资不能低于其原工资，但也不能高出原工资的20%，使用约束显然无
法实现该规则，但通过触发器却可以实现该项规则
示例如下：
create or replace trigger tr_check_sal before update of sal on emp for each row when (new.sal <= old.sal or new.sal > 1.2 * old.sal) begin raise_application_error(-20001, '工资只能升不能降，并且不能超过20%'); end;
4. 参照完整性
QL> create table e as select * from emp; 表已创建。 SQL> create table d as select * from dept; 表已创建。 SQL> alter table d add constraint pk_d primary key(deptno); 表已更改。 SQL> alter table e add constraint fk_e foreign key(deptno) references d on delete cascade; 表已更改。 SQL> delete d where deptno=10; 已删除 1 行。 SQL> roll 回退已完成。
更新后报错：
SQL> update d set deptno=50 where deptno=10; update d set deptno=50 where deptno=10 * 第 1 行出现错误: ORA-02292: 违反完整约束条件 (SCOTT.FK_E) - 已找到子记录
通过触发器来解决级联update
create or replace trigger tr_update_cascade after update of deptno on d for each row begin update e set deptno = :new.deptno where deptno = :old.deptno; end;
