一、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()
DNAME VARCHAR2()
EMPNO NOT NULL NUMBER()
ENAME VARCHAR2() SQL> insert into dept_view values(,'dname','ename',);
insert into dept_view values(,'dname','ename',)
ORA-: 无法修改与非键值保存表对应的列
SQL> insert into dept_emp values(,'ADMIN',,'MARY');
insert into dept_emp values(,'ADMIN',,'MARY')
*
第 行出现错误:
ORA-: 无法修改与非键值保存表对应的列
错误原因：这是个包含了两张表的负责视图，不可以进行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 = 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 = 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(,'dname','ename',);
insert into dept_view values(,'dname','ename',)
ORA-: 超过递归 SQL 级别的最大值
ORA-: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 4
ORA-: 触发器 'SCOTT.TR_INSTEAD_DEPT_EMP' 执行过程中出错
ORA-: 在 "SCOTT.TR_INSTEAD_DEPT_EMP", line 6
ORA-: 触发器 'SCOTT.TR_INSTEAD_DEPT_EMP' 执行过程中出错
正确插入的结果：
QL> insert into dept_emp values(,'ADMIN',,'MARY'); 已创建 行。 SQL> commit; 提交完成。 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ----- -------------- ----- ---------- ------
SMITH CLERK -12月-
ALLEN SALESMAN -2月 -
WARD SALESMAN -2月 -
JONES MANAGER -4月 -
MARTIN SALESMAN -9月 -
BLAKE MANAGER -5月 -
CLARK MANAGER -6月 -
SCOTT ANALYST -4月 -
KING PRESIDENT -11月-
TURNER SALESMAN -9月 -
ADAMS CLERK -5月 -
JAMES CLERK -12月-
FORD ANALYST -12月-
MILLER CLERK -1月 -
MARY 已选择15行。 SQL> select * from dept; DEPTNO DNAME LOC
------ -------------- -------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
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(),time date);
在建立了事件表event_table之后，就可以在触发器中引用该表了，注意，实例启动触发器和实例关闭触发器只有特权用户才能建立，并且实例启动触发器只能使用after关键字，而实例关闭触发器只能使用before关键字，示例如下：
SQL> show user
USER 为 "SCOTT"
SQL> create table event_table(event varchar2(),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 bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> select * from event_table; EVENT TIME
---------- ---------
STARTUP -SEP-
SHUTDOWN -SEP-
3.建立登录和退出触发器
为了记录用户登录和退出事件，可以分别建立登录和退出触发器，为了记载登录用户和退出用户的名称时间和IP地址
应该首先建立专门存放登录和退出的信息表LOG_TABLE示例如下：
SQL> conn scott/tiger
已连接。
SQL> create table log_table(
username varchar2(),
logontime date,
logofftime date,
address varchar2()); 表已创建。
创建登入触发器：
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(),
username varchar2(),
owner varchar2(),
objname varchar2(),
objtype varchar2(),
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 -9月 -
DROP SCOTT SCOTT D TABLE -9月 -
CREATE SCOTT SCOTT E TABLE -9月 -
三、管理触发器
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 and then
raise_application_error(-, '不能在休息时间更改员工信息');
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 = 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+ where ename='ywb';
update emp set sal=sal+ where ename='ywb'
ORA-: 不能在员工休息的时间修改信息
ORA-: 在 "SCOTT.TR_EMP_TIME", line 3
ORA-: 触发器 'SCOTT.TR_EMP_TIME' 执行过程中出错
在建立了触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中
3. 实现数据完整性
数据的完整性用于确保数据库数据满足特定的商业逻辑或企业规则，数据完整性可以通过触发器和子程序约束来实现，因为约束的实现最简单，性能也好，所以实现数据完整性首选约束
例如为了限制雇员工资不能低于800元，可以选用check约束，示例如下：
alter table emp add constraint ck_sal check(sal>=);
但某些情况下使用约束无法实现特定的商业规则，此时可以使用触发器来实现数据完整性，例如，假定希望雇员的新工资不能低于其原工资，但也不能高出原工资的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(-, '工资只能升不能降，并且不能超过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=; 已删除 行。 SQL> roll
回退已完成。
更新后报错：
SQL> update d set deptno= where deptno=;
update d set deptno= where deptno=
*
第 行出现错误:
ORA-: 违反完整约束条件 (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;