Oracle触发器结构,Oracle触发器详细介绍

行级触发器

本章介绍行级触发器机制。大部分例子以INSERT出发器给出，行级触发器可从insert update delete语句触发。

1、介绍

触发器是存储在数据库已编译的存储过程，使用的语言是PL/SQL，用编写存储过程一样的方式编写和编译触发器。下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS_OUTPUT在每插入一行数据时打印“executing temp_air”

SQL> set feedback off

SQL> CREATE TABLE temp (N NUMBER);

SQL> CREATE OR REPLACE TRIGGER temp_air

2  AFTER INSERT ON TEMP

3  FOR EACH ROW

4  BEGIN

5  dbms_output.put_line('executing temp_air');

6  END;

7   /

8   SQL> INSERT INTO temp VALUES (1);     -- insert 1 row

executing temp_air

SQL> INSERT INTO temp SELECT * FROM temp; -- insert 1 row

executing temp_air

SQL> INSERT INTO temp SELECT * FROM temp; -- inserts 2 rows

executing temp_air

executing temp_air

SQL>

尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。

2、行级触发器语法：

CREATE OR REPLACE TRIGGER trigger_name

AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name

FOR EACH ROW

[WHEN (Boolean expression)]

DECLARE

Local declarations

BEGIN

Trigger Body written PL/SQL

END;

·Trigger_name

用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息，涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。

ORA-01476: divisor is equal to zero

ORA-06512: at "SCOTT.STUDENTS_AIR", line 5

ORA-04088: error during execution of trigger

'SCOTT.STUDENTS_AIR'

行记数从关键字DECLARE行开始，如果没有DECLARE部分，BEGIN语句是第一行。触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。触发器名一般由表名、触发器类型、触发事件，语法如下：

trigger_name = table_name_[A|B] [I|U|D] [R|S]

trigger_name 最长30个字符，所以有时不得不使用表名缩写。常表名一般要有一个规则的缩写。这样可以减少故障分析处理时间。

[A|B]        表示是AFTER 或 BEFORE 触发器类型

[I|U|D]      表示触发事件，可能是 insert ,update 或者delete

[R|S]        表示行级(row)或语句级(statement)触发器类型。

·BEFORE|AFTER insert on table_name

这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE 完整性约束检查前或后执行，可以指定一个Before或after触发器在多语句操作类型上触发，如：

BEFORE INSERT OR UPDATE on table_name

BEFORE INSERT OR UPDATE OR DELETE on table_name

AFTER INSERT OR DELETE on table_name

DBMS_STANDARD 包提供了四个boolean函数来区分SQL语句类型。

PACKAGE DBMS_STANDARD IS

FUNCTION inserting RETURN BOOLEAN;

FUNCTION updating RETURN BOOLEAN;

FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN;

FUNCTION deleting RETURN BOOLEAN;

etc,

END DBMS_STANDARD;

在触发器中可以直接使用函数名称，不需要指定包名：

CREATE OR REPLACE TRIGGER temp_aiur

AFTER INSERT OR UPDATE ON TEMP

FOR EACH ROW

BEGIN

CASE

WHEN inserting THEN

dbms_output.put_line

('executing temp_aiur - insert');

WHEN updating THEN

dbms_output.put_line

('executing temp_aiur - update');

END CASE;

END;

对于Update行级触发器，可以指定被更新的列作为触发器触发条件。

CREATE OR REPLACE TRIGGER temp_aur

AFTER INSERT OR UPDATE OF M, P ON TEMP

FOR EACH ROW

BEGIN

dbms_output.put_line

('after insert or update of m, p');

END;

·WHEN(BOOLEAN EXPRESSION)

这是个可选语句，用来过滤触发触发器的条件。

CREATE OR REPLACE TRIGGER temp_air

AFTER INSERT ON TEMP

FOR EACH ROW

WHEN (NEW.N = 0)

BEGIN

dbms_output.put_line('executing temp_air');

END;

上例中表示AFTER INSERT行触发器触发的条件是：N字段的值等于0.

NEW.COLUMN_NAME : INSERT或UPDATE触发器中WHEN语句中引用字段的语法。

OLD.COLUMN_NAME : 用于UPDATE或DELETE行级触发器中WHEN语句中。在INSERT语句中为Null。

Oracle触发器详细介绍四--INSTEAD OF触发器

在简单视图上往往可以执行INSERT、UPDATE和DELETE操作，但是在复杂视图上执行INSERT、UPDATE和DELETE操作是有限的。如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询，那么将禁止在该视图上执行DML操作。为了在这些复杂视图上执行操作，需要建立INSTEAD-OF触发器。INSTEAD-OF触发器具有以下限制：

INSTEAD OF触发器只适用于视图。

INSTEAD OF触发器不能指定BEFORE和AFTER选项。

不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。

INSTEAD OF触发器必须包含有FOR EACH ROW选项。

复杂视图DEPT_EMP用于显示部门号、部门名、雇员号以及雇员名，并且在该复杂视图上不能执行任何DML操作。为了在该视图上执行DML操作，必须建立INSTEAD OF触发器。下面以完成该认务为例，说明建立INSTEAD OF触发器的方法。在建立INSTEAD OF触发器之前，首先建立视图DEPT_ENP。

create or replace view dept_emp as

select a.deptno,a.dname,b.empno,b.ename from department a,employee b

where a.deptno=b.deptno;

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 department where deptno=:new.deptno;

if v_temp=0 then

insert into department(deptno,dname) values(:new.deptno,:new.dname);

end if;

select count(*) into v_temp from employee where empno=:new.empno;

if v_temp=0 then

insert into employee(empno,ename,deptno) values(:new.emptno,:new.deptno);

end if;

end;

/

Oracle触发器详细介绍五--系统事件触发器

oracle的系统事件触发器：系统事件触发器是指基于oracle系统事件(如logon和startup)所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。

create table jax_event_table(eventname varchar2(30),time date);createtrigger tr_startup

after startup ondatabase

begin

insertinto jax_event_table values(ora_sysevent,sysdate);

end;

createtrigger tr_shutdown

beforeshutdownondatabase

begin

insertinto jax_event_table values(ora_sysevent,sysdate);

end;

在建立如上所示的两个触发器后，使用shutdown和startup关闭开启数据库会往表jax_event_table中记录一条记录，但 shutdown abort则不会触发该触发器，而startup nomount后使用alter database将数据库更改为mount或者open都只会触发一次。

1          SHUTDOWN     2008-3-20 14:29:472          STARTUP         2008-3-20 14:42:523          SHUTDOWN     2008-3-20 14:43:064          STARTUP         2008-3-20 14:45:34登录和退出触发器用来记载登录用户名称、时间和ip地址

createtable jax_log_table(

username varchar2(20), log_time date, onoff varchar(6),address varchar2(30));

createtrigger tr_logon

after logon ondatabase

begin

insertinto jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address);

end;

createtrigger tr_logoff

before logoff ondatabase

begin

insertinto jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address);

end;

select * from jax_log_table;

1          SYS       2008-3-20 14:55:17       logon2          SYSMAN           2008-3-20 14:55:21       logon3          SYS       2008-3-20 14:55:45       logon    127.0.0.14          SYS       2008-3-20 14:56:07       logoff5          SYSMAN           2008-3-20 14:56:26       logon6          SYSMAN           2008-3-20 14:56:27       logoff7          ZHANGLEI       2008-3-20 14:56:35       logon    127.0.0.18          ZHANGLEI       2008-3-20 14:57:01       logoff9          SYS       2008-3-20 14:57:12       logon    127.0.0.110         SYSMAN           2008-3-20 14:57:31       logon11         SYSMAN           2008-3-20 14:57:32       logoffDDL触发器记录系统所发生的DDL事件(create，alter，drop等)

createtable jax_event_ddl_table(event varchar2(20),

username varchar2(10),owner varchar2(10),objname varchar2(20),

objtype varchar2(10),timedate);

createtrigger tr_ddl

afterddlondatabase

begin

insertinto jax_event_ddl_table values(ora_sysevent,ora_login_user,

ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate)

end;

1          CREATE           SYS       SYS       T2        TABLE 2008-3-20 15:05:412          CREATE           SYS       SYS       VIEW_T2          VIEW    2008-3-20 15:06:53由上面的描述看到，在编写系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下：

Ora_client_ip_address 返回客户端的ip地址

Ora_database_name 返回当前数据库名

Ora_des_encrypted_password 返回des加密后的用户口令

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(column_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 返回触发器的系统事件名。