首页 > 代码库 > ORACLE触发器的管理与实际应用【weber出品】
ORACLE触发器的管理与实际应用【weber出品】
一、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 rowdeclare 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 级别的最大值 50ORA-06512: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 4ORA-04088: 触发器 ‘SCOTT.TR_INSTEAD_DEPT_EMP‘ 执行过程中出错ORA-06512: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 6ORA-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/tigercreate table event_table(event varchar2(30),time date);
在建立了事件表event_table之后，就可以在触发器中引用该表了，注意，实例启动触发器和实例关闭触发器只有特权用户才能建立，并且实例启动触发器只能使用after关键字，而实例关闭触发器只能使用before关键字，示例如下：
SQL> show userUSER 为 "SCOTT"SQL> create table event_table(event varchar2(100),time date);表已创建。
创建打开数据库触发器：
SQL> conn /as sysdbacreate or replace trigger tr_startupafter startup on databasebegininsert into scott.event_table values(ora_sysevent,sysdate);end;SQL> /触发器已创建
创建关闭数据库触发器：
create or replace trigger tr_shutdownbefore shutdown on databasebegininsert into scott.event_table values(ora_sysevent,sysdate);end;/触发器已创建
查看数据：
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 595591168 bytesFixed Size 1275032 bytesVariable Size 163580776 bytesDatabase Buffers 423624704 bytesRedo Buffers 7110656 bytesDatabase mounted.Database opened.SQL> conn scott/tigerConnected.SQL> select * from event_table;EVENT TIME---------- ---------STARTUP 04-SEP-14SHUTDOWN 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 databasebegin 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 databasebegin 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_ddlafter ddl on scott.schemabegininsert 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月 -14DROP SCOTT SCOTT D TABLE 03-9月 -14CREATE SCOTT SCOTT E TABLE 03-9月 -14
三、管理触发器
1.显示触发器
建立触发器时，oracle会将触发器信息写入到数据字典中，通过查询数据字典视图user_triggers可以显示当前用户所包含的所有触发器信息，示例如下：
conn scott/tigerselect trigger_name,status from user_triggerswhere table_name=‘EMP‘;
2.禁止触发器
禁止触发器是指使触发器临时失效，当触发器处于enable状态时，如果在表上执行DML操作则就会触发相应的触发器，如果基于insert操作建立了触发器，当使用SQL*loader装载大批
量数据时会触发触发器，为了加快数据装载速度，应该在装载数据之前禁止触发器
方法如下：
conn /as sysdbaalter 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 empbegin 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 rowdeclare 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 3ORA-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 rowbegin update e set deptno = :new.deptno where deptno = :old.deptno;end;
ORACLE触发器的管理与实际应用【weber出品】