第 13 章 开发触发器
触发器是指存放在数据库中，并被隐含执行的存储过程。在ORACLE8i之前，只允许基于表或视图的DML操作(INSERT,UPDATE和DELETE)建立触发器；而从ORACLE8i开始，不仅支持DML触发器，也允许基于系统事件(启动数据库、关闭数据库、登录)和DDL操作建立触发器。
13.1 触发器简介
触发器由触发事件、触发条件和触发操作三部分组成。
1 触发事件
触发事件是指引起触发器被触发的SQL语句、数据库事件或用户事件。在Oracle8i之前，触发事件只能是DML操作；在8i开始，不仅支持原有的DML事件，而且还增加了其他触发器事件。具体触发事件如下：
(1)启动和关闭例程；
(2)Oracle错误信息；
(3)用户登录和断开会话；
(4)特定表或视图的DML操作；
(5)在任何方案上的DDL语句。
2 触发条件(可选)
触发条件是指使用WHEN子句指定一个BOOLEAN表达式，当布尔表达式返回值为TRUE时，会自动执行触发器相应代码；当布尔表达式返回值为FALSE或UNKNOWN时，不会执行触发器。
3 触发操作
触发操作是指包含SQL语句和其他执行代码的PL/SQL块，不仅可以使用PL/SQL进行开发，也可以使用JAVA语言和C语言进行开发。当触发条件为TRUE时，会自动执行触发器操作的相应代码。但编写触发器执行代码时，需要注意一下限制：
(1) 触发器代码大小不能超过32K。如果确实需要使用大量代码建立触发器，应该首先建立存储过程，然后在触发器中使用CALL语句调用存储过程。
(2) 触发器只能包含SELECT,INSERT,UPDATE和DELETE语句，而不能包含DDL语句(CREATE,ALTER,DROP)和事务控制语句(COMMIT,ROLLBACK和SAVEPOINT)。
13.2 建立DML触发器
在Oracle8i之前，只能基于DML事件建立触发器。在建立了DML触发器后，如果发生了相应的DML操作，就会自动执行触发器的代码。当建立DML触发器时，需要指定触发时机(BEFORE或AFTER)、触发事件(INSERT,UPDATE,DELETE)、表名、触发类型、触发条件以及触发操作。
1 触发时机
触发时机用于指定触发器的触发事件。当指定BEFORE关键字时，表示在执行DML操作之前触发触发器；当指定AFTER关键字，表示在执行DML操作之后触发触发器。
2 触发事件
触发事件用于指定导致触发器执行的DML操作，也即INSERT,UPDATE和DELETE操作。既可以使用单个触发事件，也可以组合多个触发事件。
3 表名
因为DML触发器是针对特定表进行的，所以必须指定DML操作所对应的表。
4 触发类型
触发类型用于指定当触发事件发生后，需要执行几次触发操作。如果指定语句触发类型(默认)，那么只会执行一次触发器代码；如果指定行触发类型，则会在每个被作用行上执行一次触发器代码。
5 触发条件
触发条件用于指定执行触发器代码的条件，只有条件为TRUE时才会执行触发器代码。注意，当编写DML触发器时，只允许在行触发器上指定触发条件。
6 触发操作
触发操作作用于指定触发器执行代码。如果使用PL/SQL存储过程、JAVA存储过程或外部存储过程来实现触发器代码，那么在触发操作部分可直接使用CALL语句调用相应过程。如果使用PL/SQL匿名块编写触发器操作，则应该按照以下格式进行编写：
[DECLARE]
________定义变量、常量等
BEGIN
________编写SQL语句和PL/SQL语句
EXCEPTION
________编写例外处理语句
END;
7 DML触发器触发顺序
(1) DML触发器在单行数据上的触发顺序
当针对某一表的相同DML操作而建立了多个DML触发器(BEFORE/AFTER语句触发器、BEFORE/AFTER行触发器)时，如果在单行数据上执行了该种DML操作，则触发器会按照以下顺序执行：
对应单行数据而言，无论是语句触发器，还是行触发器，触发器代码实际只执行一次，并且执行顺序为BEFORE语句触发器、BEFORE行触发器、DML操作、AFTER行触发器、AFTER语句触发器。
(2) DML触发器在多行数据上的触发顺序
在多行数据而言，语句触发器只能执行一次，而行触发器在每个作用行上都执行一次。
13.2.1 语句触发器
语句触发器是指当执行DML语句时被隐含执行的触发器。如果在表上针对某种DML操作建立了语句触发器，那么当执行DML操作时会自动执行触发器的相应代码。当审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器。注意，使用语句触发器，不能记录列数据的变化。建立语句触发器的语法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
PL/SQL block;
如上所示，trigger_name用于指定触发器名；timint用于指定触发器时机(BEFORE或AFTER)；event用于指定触发器事件(INSERT、UPDATE和DELETE)；table_name用于指定DML操作所对应的表名。
1 建立BEFORE语句触发器
为了确保DML操作在正常情况下执行，可以基于DML操作建立BEFORE语句触发器。例如，为了禁止工作人员在休息日改变雇员信息，开发人员可以建立BEFORE语句触发器，以实现数据的安全保护。示例如下：
SQL> create or replace trigger tr_sec_emp
2 before insert or update or delete on emp
3 begin
4 if to_char(sysdate,'DY','nls_date_language=AMERICAN')
5 in('SAT','SUN') THEN
6 raise_application_error(-20001,'bu neng zai xiu xi ri xiu gai gu yuan xin xi');
7 end if;
8 end;
9 /
Trigger created.
在建立了触发器tr_sec_emp之后，如果星期六、星期日在EMP表上执行DML操作，则会显示错误信息。示例如下：
[root@ogg1 ~]# date -s "20150531 14:05:30"
Sun May 31 14:05:30 CST 2015
[root@ogg1 ~]# date
Sun May 31 14:05:31 CST 2015
SQL> delete emp where deptno=10;
delete emp where deptno=10
*
ERROR at line 1:
ORA-20001: bu neng zai xiu xi ri xiu gai gu yuan xin xi
ORA-06512: ?"CHEN.TR_SEC_EMP", line 4
ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?
2 使用条件谓语
当触发器中同时包含多个触发事件(INSERT,UPDATE,DELETE)时，为了在触发器代码中区分具体的触发器事件，可以使用以下三个条件谓语：
(1) INSERTINT：当触发器事件是INSERT时，该条件谓语返回值为TRUE，否则为FALSE。
(2) UPDATING：当触发器事件是UPDATE时，该条件谓语返回值为TRUE，否则为FALSE。
(3) DELETING：当触发器事件是DELETE时，该条件谓语返回值为TRUE，否则为FALSE。
下面举例说明在触发器中使用这三个条件谓语的方法，示例如下：
SQL> create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN('SAT','SUN') THEN
CASE
WHEN INSERTING THEN
raise_application_error(-20001,'not add people in rest day');
WHEN UPDATING THEN
raise_application_error(-20002,'not modify people in rest day');
WHEN DELETING THEN
raise_application_error(-20003,'not delete people in rest day');
end case;
end if;
end;
/
Trigger created.
SQL> insert into emp(empno,ename) values(123,'abc');
insert into emp(empno,ename) values(123,'abc')
*
ERROR at line 1:
ORA-20001: not add people in rest day
ORA-06512: ?"CHEN.TR_SEC_EMP", line 6
ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?
SQL> update emp set sal=5000 where deptno=10;
update emp set sal=5000 where deptno=10
*
ERROR at line 1:
ORA-20002: not modify people in rest day
ORA-06512: ?"CHEN.TR_SEC_EMP", line 8
ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?
SQL> delete emp where deptno=20;
delete emp where deptno=20
*
ERROR at line 1:
ORA-20003: not delete people in rest day
ORA-06512: ?"CHEN.TR_SEC_EMP", line 10
ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?
3 建立AFTER语句触发器
为了审计DML操作，或者在DML操作之后执行汇总运算，可以使用AFTER语句触发器。例如，为了审计在EMP表上INSERT,UPDATE和DELETE的操作次数，可以建立AFTER触发器。在建立AFTER触发器之前，首先建立审计表audit_table。示例如下：
SQL> create table audit_table(
2 name varchar2(20),ins int,upd int,del int,starttime date,endtime date);
Table created.
为了审计EMP表上DML操作执行的次数、最早执行时间和最近执行时间，需要建立AFTER语句触发器。示例如下：
SQL> create or replace trigger tr_audit_emp
2 after insert or update or delete on emp
3 declare
4 v_temp int;
5 begin
6 select count(*) into v_temp from audit_table where name='EMP';
7 if v_temp=0 then
8 insert into audit_table values('EMP',0,0,0,SYSDATE,NULL);
9 end if;
10 case
11 when inserting then
12 update audit_table set ins=ins+1,endtime=SYSDATE where name='EMP';
13 when updating then
14 update audit_table set upd=upd+1,endtime=sysdate where name='EMP';
15 when deleting then
16 update audit_table set del=del+1,endtime=sysdate where name='EMP';
17 end case;
18 end;
19 /
Trigger created.
SQL> update emp set sal=5000 where empno=7788;
1 row updated.
SQL> update emp set sal=8000 where empno=7369;
1 row updated.
SQL> delete emp where empno=7369;
1 row deleted.
SQL> insert into emp(empno,ename) values(1234,'aaa');
1 row created.
SQL> select * from audit_table;
NAME INS UPD DEL STARTTIME ENDTIME
-------------------- ---------- ---------- ---------- ---------- ----------
EMP 1 2 1 02-6? -15 02-6? -15
13.2.2 行触发器
行触发器是指执行DML操作时，每作用一行就触发一次的触发器。审计数据变化时，可以使用行触发器。建立行触发器的语法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
Timing event1 [OR event12OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS NEW]
FOR EACH ROW
[WHEN condition]
PL/SQL block;
其中：REFERENCING子句用于指定引用新、旧数据的方式，默认情况使用OLD修饰符引用旧数据，使用NEW修饰符引用新数据；FOR EACH ROW表示建立行触发器；
1 建立BEFORE行触发器
在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制，但某些情况下使用约束可能无法实现复杂的商业逻辑或企业规则，此时可以考虑使用BEFORE行触发器。下面以确保雇员工资不能低于其原有工资为例，说明建立BEFORE行触发器。示例如下：
SQL> create or replace trigger tr_emp_sal
before update of sal on emp for each row
begin
if :new.sal<:old.sal then>
raise_application_error(-20010,'Bu neng jiang sal!');
end if;
end;
/
Trigger created.
在建立触发器tr_emp_sal之后，如果雇员新工资低于其原工资，则会提示错误信息。示例如下：
SQL> update emp set sal=1000 where empno=7788;
update emp set sal=1000 where empno=7788
*
ERROR at line 1:
ORA-20010: Bu neng jiang sal!
ORA-06512: ?"CHEN.TR_EMP_SAL", line 3
ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?
2 建立AFTER行触发器
为了审计DML操作，可以使用语句触发器或ORACLE系统提供的审计功能；而为了审计数据变化，则应该使用AFTER行触发器。下面以审计雇员工资变化为例，说明使用AFTER行触发器的方法。在建立触发器之前，首先应建立存放审计数据的表audit_emp_change，示例如下：
SQL> create table audit_emp_change(
2 name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);
Table created.
为了审计所有雇员的工资变化和雇员工资的更新日期，必须要建立AFTER行触发器。示例如下：
SQL> 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;
/
Trigger created.
在建立触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中。示例如下
SQL> update emp set sal=sal*1.5 where deptno=20;
5 rows updated.
SQL> select * from audit_emp_change;
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ----------
SMITH 800 1200 03-6? -15
JONES 2975 4462.5 03-6? -15
SCOTT 3000 4500 03-6? -15
ADAMS 1100 1650 03-6? -15
FORD 3000 4500 03-6? -15
3 限制行触发器
当使用行触发器时，默认情况下会在每个被作用行行执行一次触发器代码。为了使得在特定条件下执行行触发器代码，就需要使用WHEN子句对触发器条件加以限制。下面以审计岗位“SALESMAN”的雇员工资变化为例，说明限制行触发器的方法。示例如下：
SQL>
create or replace trigger tr_sal_change
after update of sal on emp
for each row
when(old.job='CLERK')
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;
/
Trigger created.
SQL> update emp set sal=sal*1.5 where deptno=20;
5 rows updated.
SQL> select * from audit_emp_change;
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ----------
SMITH 800 1200 03-6? -15
ADAMS 1100 1650 03-6? -15
SQL> select ename,job from emp where deptno=20;
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
SCOTT ANALYST
ADAMS CLERK
FORD ANALYST
4 DML触发器使用注意事项
当编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据。例如，如果要基于EMP表建立触发器，那么该触发器的执行代码不能包含对EMP表的查询操作。尽管在建立触发器时不会出现任何错误，但在执行相应触发器操作时出显示错误信息。假定希望雇员工资不能超过当前雇员最高工资，并使用触发器实现该规则。示例如下：
SQL> create or replace trigger tr_emp_sal
2 before update of sal on emp
3 for each row
4 declare
5 maxsal number(6,2);
6 begin
7 select max(sal) into maxsal from emp;
8 if :new.sal>maxsal then
9 raise_application_error(-20010,'chao chu shang xian');
10 end if;
11 end;
12 /
Trigger created.
如上所示，当建立触发器tr_emp_sal时，不会显示任何错误。但因为触发器代码引用了基表emp，所以在执行UPDATE操作时会显示如下错误信息：
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> update emp set sal=3000 where empno=7369;
update emp set sal=3000 where empno=7369
*
ERROR at line 1:
ORA-04091: ?CHEN.EMP 靠靠? 靠?靠靠靠
ORA-06512: ?"CHEN.TR_EMP_SAL", line 4
ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?
SQL> update emp set sal=8000 where empno=7369;
update emp set sal=8000 where empno=7369
*
ERROR at line 1:
ORA-04091: ?CHEN.EMP 靠靠? 靠?靠靠靠
ORA-06512: ?"CHEN.TR_EMP_SAL", line 4
ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?
13.2.3 使用DML触发器
为了确保数据库数据满足特定的商业规则或企业逻辑，可以使用约束、触发器和子程序实现。因为约束性能最好，实现最简单，所有首先约束；如果使用约束不能实现特定规则，那么应该选择触发器；如果触发器仍然不能实现特定规则，那么应该选择子程序(过程和函数)。DML触发器可以用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能，下面通过示例给大家说明如何实现这些功能。
1 控制数据安全
在服务器级控制数据安全是通过授予和收回对象权限来实现的，例如为了使得CHEN用户可以在SCOTT.EMP表上执行DML操作和SELECT操作，必须要为CHEN用户授予相应的对象权限。如下所示：
SQL> conn scott/tiger
Connected.
SQL> grant select,insert,update,delete on emp to chen;
Grant succeeded.
当用户有了以上权限之后 ，就可以随时在EMP表上执行相应的SQL操作。为了实现更复杂的安全模式(例如现在要修改的数据，修改时间等)，就需要使用DML触发器了。下面以限制用户在正常工作时间(9:00~18:00)改变EMP表数据为例，说明使用DML触发器控制数据安全的方法。示例如下：
SQL> create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
if to_char(sysdate,'hh24') not between '9' and '18' then
raise_application_error(-20101,'fei gong zuo shi jian');
end if;
end;
/
Trigger created.
建立触发器tr_temp_time之后，只能在9:00~18:00之间在EMP表上执行DML操作。如果不在该时间段，则会显示错误信息：
SQL> update emp set sal=1200 where empno=7788;
update emp set sal=1200 where empno=7788
*
ERROR at line 1:
ORA-20101: fei gong zuo shi jian
ORA-06512: ?"CHEN.TR_EMP_TIME", line 3
ORA-04088: 靠?'CHEN.TR_EMP_TIME' 靠靠靠?
SQL> select to_char(sysdate,'hh24') from dual; --------Why???
TO
--
11
2 实现数据审计
审计用于监视非法和可疑的数据库活动。Oracle数据库本身提供了审计功能，例如，如果要对EMP表上的DML操作进行审计，可以执行如下命令：
SQL> audit insert,update,delete on emp by access;
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ogg1/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL> conn /as sysdba
Connected.
SQL> alter system set audit_trail=db_extended scope=spfile;
System altered.
SQL> startup force
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ogg1/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB_EXTENDED
SQL> conn chen/chen
Connected.
SQL> audit insert,update,delete on emp by access;
Audit succeeded.
SQL> update emp set sal=8000 where empno=7788;
1 row updated.
SQL> delete emp where depnto=10;
delete emp where depnto=10
*
ERROR at line 1:
ORA-00904: "DEPNTO": 靠靠?
SQL> delete emp where deptno=20;
5 rows deleted.
SQL> col sql_text for a45
SQL> select username,sql_text from dba_audit_trail where sql_text is not null;
USERNAME SQL_TEXT
------------------------------ ---------------------------------------------
CHEN update emp set sal=8000 where empno=7788
CHEN delete emp where depnto=10
CHEN delete emp where deptno=20
如上所示，在设置了审计选项之后，如果在EMP表上执行INSERT、UPDATE和DELETE操作，ORACLE会将关于SQL操作的信息(用户、时间等)写入到数据字典中。注意，使用数据库审计只能审计SQL操作，而不会记载数据变化。为了审计SQL操作所引起的数据变化，必须要使用DML触发器。示例如下：
SQL> 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;
/
Trigger created.
在建立了触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中。示例如下：
SQL> update emp set sal=sal*1.1 where deptno=30;
6 rows updated.
SQL> select * from audit_emp_change;
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ----------
ALLEN 1600 1760 03-6? -15
WARD 1250 1375 03-6? -15
MARTIN 1250 1375 03-6? -15
BLAKE 2850 3135 03-6? -15
TURNER 1500 1650 03-6? -15
JAMES 950 1045 03-6? -15
6 rows selected.
SQL> select username,sql_text from dba_audit_trail where sql_text is not null;
USERNAME SQL_TEXT
------------------------------ ---------------------------------------------
CHEN update emp set sal=8000 where empno=7788
CHEN delete emp where depnto=10
CHEN delete emp where deptno=20
CHEN update emp set sal=sal*2 where deptno=10
CHEN update emp set sal=sal*1.1 where deptno=30
3 实现数据完整性
数据完整性用语确保数据库数据满足特定的商业逻辑或企业规则，数据完整性可以使用约束，触发器和子程序实现。因为约束的实现最简单，性能也最好，所以实现数据完整性首先约束。例如，为了限制雇员工资不能低于800元，可以选用CHENCK约束。示例如下：
SQL> alter table emp add constraint ck_sal check (sal>=800);
Table altered.
SQL> update emp set sal=8000 where empno=7369;
1 row updated.
SQL> update emp set sal=700 where empno=7788;
update emp set sal=700 where empno=7788
*
ERROR at line 1:
ORA-02290: 靠靠靠靠 (CHEN.CK_SAL)
但某些情况下使用约束无法实现特定的商业规则，此时可以使用触发器来实现完整性。例如，假定希望雇员的新工资不能低于其原工资，并且不能高于原工资20%，使其约束显然无法实现该规则，但通过触发器却可以实现该规则。示例如下：
SQL> create or replace trigger tr_check_sal
2 before update of sal on emp
3 for each row
4 when (new.sal1.2*old.sal)
5 begin
6 raise_application_error(-20931,'sal bu neng jiang,bu neng chao guo 20%');
7 end;
8 /
Trigger created.
SQL> update emp set sal=7999 where empno=7369;
update emp set sal=7999 where empno=7369
*
ERROR at line 1:
ORA-20931: sal bu neng jiang,bu neng chao guo 20%
ORA-06512: ?"CHEN.TR_CHECK_SAL", line 2
ORA-04088: 靠?'CHEN.TR_CHECK_SAL' 靠靠靠?
SQL> update emp set sal=16000 where empno=7369;
update emp set sal=16000 where empno=7369
*
ERROR at line 1:
ORA-20931: sal bu neng jiang,bu neng chao guo 20%
ORA-06512: ?"CHEN.TR_CHECK_SAL", line 2
ORA-04088: 靠?'CHEN.TR_CHECK_SAL' 靠靠靠?
SQL> update emp set sal=8500 where empno=7369;
1 row updated.
4 实现参照完整性
参照完整性是指若两个表之间具有主从关系(也即主外键关系)，当删除主表数据时，必须确保相关的从表数据已经被删除；当修改主表的主键列数据时，必须确保相关从表数据已经被修改。为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。示例如下：
SQL>alter table emp drop constraint FK_DEPTNO;
SQL>alter table EMP
add constraint FK_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO) on delete cascade;
当用如上方式建立了外部键fk_deptno之后，在删除表DEPT的数据时，会同时删除从表EMP的所有相关数据。但使用约束却不能实现级联更新，如果要更新DEPT表的部门号，则会显示如下错误信息：
SQL> update dept set deptno=50 where deptno=10;
update dept set deptno=50 where deptno=10
*
ERROR at line 1:
ORA-02292: integrity constraint (CHEN.FK_DEPTNO) violated - child record found
如上所示，错误原因是emp表包含有该部门的相应雇员。为了实现级联更新，可以使用触发器。示例如下：
SQL> create or replace trigger tr_update_cascade
2 after update of deptno on dept
3 for each row
4 begin
5 update emp set deptno=:new.deptno where deptno=:old.deptno;
6 end;
7 /
Trigger created.
在建立了触发器tr_update_cascade之后，当更新DEPT表的部门号时，会级联更新EMP表的相应雇员的部门号。示例如下：
SQL> update dept set deptno=50 where deptno=10;
1 row updated.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
50 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select ename,deptno from emp where deptno=50;
ENAME DEPTNO
-------------------- ----------
CLARK 50
KING 50
MILLER 50
13.3 建立INSTEAD OF 触发器
对于简单视图，可以直接执行INSERT,UPDATE和DELETE操作。但对于复杂视图，不允许直接执行INSERT,UPDATE和DELETE操作。当视图符合以下任何一种情况时，都不允许直接执行DML操作。具体情况如下：
1 具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS)；
2 具体分组函数(MIN,MAX,SUM,AVG,COUNT等)；
3 具体GROUP BY,CONNECT BY或START WITH等子句；
4 具有DISTINCT关键字；
5 具有连接查询。
为了在具有以上情况查询的复杂视图上执行DML操作，必须要基于视图建立INSTEAD-OF触发器。在建立INSTEAD-OF触发器之后，就可以基于复杂视图执行INSERT,UPDATE和DELETE语句。但建立INSTEAD-OF触发器有以下注意事项：
1 INSTEAD OF 选项只适用于视图；
2 当基于视图建立触发器时，不能指定BEFORE和AFTER选项；
3 在建立视图时没有指定WITH CHECK OPTION选项；
4 当建立INSTEAD OF触发器时，必须指定FOR EACH ROW选项。
下面举例说明复杂视图建立INSTEAD-OF触发器的方法。
1 建立复杂视图dept_emp
视图是逻辑表，本身没有任何数据。视图只是对于一条SELECT语句，当查询视图时，其数据实际是从视图基表上取得。为了简化部门及其雇员信息的查询，应建立复杂视图dept_emp。示例如下：
SQL> create or replace view dept_emp as
2 select a.deptno,a.dname,b.empno,b.ename from dept a,emp b where a.deptno=b.deptno;
View created.
SQL> select * from dept_emp where deptno=10;
DEPTNO DNAME EMPNO ENAME
---------- ---------------------------- ---------- --------------------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7839 KING
10 ACCOUNTING 7934 MILLER
SQL> insert into dept_emp values(50,'ADMIN','1223','MARY');
insert into dept_emp values(50,'ADMIN','1223','MARY')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
2 建立INSTEAD-OF触发器
为了在复杂视图上执行DML操作，必须要基于复杂视图建立INSTEAD-OF触发器。下面以在复杂视图dept_emp上执行INSERT操作为例，说明建立INSETEAD-OF触发器的方法。示例如下：
SQL> 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,deptno) values(:new.empno,:new.ename,:new.deptno);
end if;
end;
/
Trigger created.
当建立了INSTEAD-OF触发器tr_instead_dept_emp之后，就可以在复杂视图dept_emp上执行INSERT操作了。示例如下：
SQL> insert into dept_emp values(50,'ADMIN','1223','MARY');
1 row created.
SQL> insert into dept_emp values(10,'ADMIN','1224','BAKE');
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ADMIN
SQL> select empno,ename,deptno from emp where empno in('1223','1224');
EMPNO ENAME DEPTNO
---------- -------------------- ----------
1223 MARY 50
1224 BAKE 10
13.4 建立系统事件触发器
系统事件触发器是指基于Oracle系统事件(例如LOGON和STARTUP)所建立的触发器。通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制。下面介绍一下常用的系统事件属性函数，以及建立各种事件触发器的方法。
1 常用事件属性函数
建立系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下：
(1) ora_client_ip_address:用于返回客户端的IP地址；
(2) ora_database_name:用于返回当前数据库名；
(3) ora_des_encrypted_password:用于返回DES加密后的用户口令；
(4) ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名；
(5) ora_dict_obj_name_list(name_list OUT ora_name_list_t):用于返回在事件中被修改的对象列表；
(6) ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名；
(7) ora_dict_obj_owner_list(owner_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表；
(8) ora_dict_obj_type:用于返回DDL操作所对应的数据对象的类型；
(9) ora_grantee(user_list OUT ora_name_list_t):用于返回授权事件的授权者；
(10)ora_instance_num:用于返回例程号；
(11)ora_is_alter_column(column_name IN VARCHAR2):用于检测定列是否被删除；
(12)ora_is_creating_nested_table:用于检测是否正在建立嵌套表；
(13)ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除；
(14)ora_is_servererro(error_number):用于检测是否返回特定ORACLE错误；
(15)ora_login_user:用于返回登录用户名；
(16)ora_sysevent:用于返回触发器的系统事件名。
2 建立例程启动和关闭触发器
为了跟踪例程启动和关闭事件，可以分别建立例程启动触发器和例程关闭触发器。为了记载例程启动和关闭的事件和时间，首先建立事件表event_table。示例如下：
SQL> show user
USER is "CHEN"
SQL> create table event_table(event varchar2(20),time date);
Table created.
在建立了事件表event_table之后，就可以在触发器中引用该表了。注意，例程启动触发器和例程关闭触发器只能在特权用户下才能建立，并且例程启动触发器只能使用AFTER关键字，而例程关闭触发器只能使用BEFORE关键字，示例如下：
SQL> create or replace trigger tr_startup
2 after startup on database
3 begin
4 insert into event_table values(ora_sysevent,sysdate);
5 end;
6 /
Trigger created.
SQL> create or replace trigger tr_shutdown
2 before shutdown on database
3 begin
4 insert into event_table values(ora_sysevent,sysdate);
5 end;
6 /
Trigger created.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
SQL> startup
SQL> conn chen/chen
SQL> select event,to_char(time,'yyyy-mm-dd hh24:mi:ss') time from event_table;
EVENT TIME
---------------------------------------- --------------------------------------
SHUTDOWN 2015-06-04 14:45:42
STARTUP 2015-06-04 14:45:58
[oracle@ogg1 ~]$ vim /u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/alert_ogg1.log
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
…………………
Thu Jun 04 14:45:51 2015
PMON started with pid=2, OS id=7159
…………………
3 建立登录和退出触发器
为了记载用户登录和退出事件，可以分别建立登录和退出触发器。为了记载登录用户和退出用户的名称、事件和IP地址，应该首先建立专门存放登录和退出的信息表LOG_TABLE。示例如下：
SQL> create table log_table(
2 username varchar2(20),logon_time date,logoff_time date,address varchar2(20));
Table created.
在建立了LOG_TABLE表之后，就可以在触发器中引用该表了。注意，登录触发器和退出触发器一定要以特权用户身份建立，并且登录触发器只能使用AFTER关键字，而退出触发器只能使用BEFORE关键字。示例如下：
SQL> create or replace trigger tr_logon
2 after logon on database
3 begin
4 insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
5 end;
6 /
Trigger created.
SQL> create or replace trigger tr_logoff
2 before logoff on database
3 begin
4 insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
5 end;
6 /
Trigger created.
在建立了触发器tr_logon之后，当用户登录到数据库之后，会执行触发器代码；在建立了触发器tr_logoff之后，当用户断开数据库连接之前，会执行其触发器代码。示例如下：
SQL> conn scott/tiger
Connected.
SQL> conn oe/oe
Connected.
SQL> conn / as sysdba
Connected.
SQL> conn system/oracle
Connected.
SQL> conn chen/chen
Connected.
SQL> select * from log_table;
SQL> col username for a8
SQL> col address for a20
SQL> select * from log_table order by username;
USERNAME LOGON_TIME LOGOFF_TIME ADDRESS
-------- ------------ ------------ --------------------
CHEN 04-JUN-15
CHEN 04-JUN-15
OE 04-JUN-15
OE 04-JUN-15
SCOTT 04-JUN-15
SCOTT 04-JUN-15
SYS 04-JUN-15
SYS 04-JUN-15
SYSTEM 04-JUN-15
SYSTEM 04-JUN-15
4 建立DDL触发器
为了记载系统所发生的DDL事件(CREATE,ALTER,DROP等)，可以建立DDL触发器。为了记载DDL事件信息，应该建立专门的表，以便存放DDL事件信息。示例如下：
SQL> create table event_ddl(
2 event varchar2(20),username varchar2(10),
3 owner varchar2(10),objname varchar2(20),
4 objtype varchar2(10),time date);
Table created.
在建立表EVENT_DDL之后，就可以在触发器中引用该表了。为了记载DDL事件，应该建立DDL触发器。注意，当建立DDL触发器时，必须要使用AFTER关键字。示例如下：
SQL> create or replace trigger tr_ddl
2 after ddl on chen.schema
3 begin
4 insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
5 end;
6 /
Trigger created.
在建立了触发器tr_ddl之后，如果在CHEN方案对象上执行DDL操作，则会将该信息记载到表event_table中。示例如下：
SQL> create table t1(id number);
SQL> alter table t1 add(name varchar2(20));
SQL> drop table t1 purge;
SQL> col event for a8
SQL> col owner for a8
SQL> col objname for a8
SQL> col objtype for a8
SQL> select * from event_ddl;
EVENT USERNAME OWNER OBJNAME OBJTYPE TIME
-------- -------- -------- -------- -------- ------------
CREATE CHEN CHEN T1 TABLE 04-JUN-15
ALTER CHEN CHEN T1 TABLE 04-JUN-15
DROP CHEN CHEN T1 TABLE 04-JUN-15
13.5 管理触发器
1 显示触发器信息
建立触发器时，oracle会将触发器信息写入到数据字典中，通过查询数据字典视图user_triggers,可以显示当前用户所包含的所有触发器信息。示例如下：
SQL> select trigger_name,status from user_triggers;
TRIGGER_NAME STATUS
------------------------------------------------------------ ----------------
TR_INSTEAD_OF_DEPT_EMP ENABLED
TR_UPDATE_CASCADE ENABLED
TR_LOGON ENABLED
TR_STARTUP ENABLED
TR_SHUTDOWN ENABLED
TR_LOGOFF ENABLED
TR_DDL ENABLED
7 rows selected.
2 禁止触发器
禁止触发器是指使触发器临时失效。当触发器处于ENABLE状态时，如果表上执行DML操作，则就会触发相应的触发器。如果基于INSERT操作建立触发器，当使用SQL*Loader装载大批量数据时会触发触发器。为了加快数据装载速度，应该在装载数据之前禁止触发器。方法如下：
SQL> alter trigger tr_ddl disable;
Trigger altered.
SQL> select trigger_name,status from user_triggers where trigger_name='TR_DDL';
TRIGGER_NAME STATUS
------------------------------------------------------------ ----------------
TR_DDL DISABLED
3 激活触发器
激活触发器是指使触发器重新生效。当使用SQL*Loader装载了数据之后，为了使被禁止的触发器生效，应该激活触发器。方法如下：
SQL> alter trigger tr_ddl enable;
4 禁止或激活表的所有触发器
SQL> alter table emp disable all triggers;
SQL> alter table emp enable all triggers;
5 重新编译触发器
当使用ALTER TABLE命令修改表结构(例如增加列、删除列)时，会使得其触发器转变为INVALID状态。在这种情况下，为了使得触发器继续生效，需要重新编译触发器。示例如下：
SQL> alter trigger tr_ddl compile;
6 删除触发器
当触发器不在需要时，可以使用drop trigger命令删除触发。注意，在表上的触发器越多，对应DML操作的性能影响越大，所以一定要适度使用触发器。删除触发器的示例如下：
SQL> drop trigger tr_ddl;
-----本文摘抄自《精通oracle 10g PL/SQL编程(王海亮)》
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1687817/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1687817/
扫一扫
490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
