开发触发器

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注