开发触发器
触发器是指存放在数据库中,并被隐含执行的存储过程。在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 编程( 王海亮)

最后修改:2022 年 02 月 27 日
如果觉得我的文章对你有用,请随意赞赏