Oracle触发器的分类和使用

Oracle触发器的分类和使用

摘要：在Oracle中，触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle 系统自动触发。触发器通常用于加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更为复杂的约束。本文介绍了Oracle触发器的概念，功能，优缺点，组成等方面。详细解读了Oracle触发器在各方面的应用并配合相关实例。

关键字：Oracle、触发器、介绍、应用

一、触发器的介绍

1.1 触发器的概念

在Oracle中，触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发发器，通常用于加强数据的完整性约束和业务规则等。

触发器与存储过程的区别在于：存储过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的，而是由一个事件来启动运行。即触发器是当某个时间发生时自动地隐式运行。

1.2 触发器的功能

●允许/限制对表的修改

●自动生成派生列，比如自增字段

●强制数据一致性

●提供审计和日志记录

●防止无效的事务处理

●启用复杂的业务逻辑

1.3 触发器的优缺点

优点：触发器可通过数据库中的相关表实现级联更改，不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用CHECK 约束定义的约束更为复杂的约束。与CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDA TE 或DELETE）允许采取多个不同的对策以响应同一个修改语句。

缺点：触发器功能强大，轻松可靠地实现许多复杂的功能，但是它也具有一些缺点那就是由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作。同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。

1.4 触发器的组成

1. 触发事件：引起触发器被触发的事件。例如：DML语句(INSERT, UPDATE, DELETE 语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

2. 触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER的操作顺序。

3. 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。

4. 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

5. 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

6. 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

1.5 触发器触发次序

I.执行BEFORE语句级触发器;

II.对与受语句影响的每一行

●执行BEFORE行级触发器

●执行DML语句

●执行AFTER行级触发器

I.执行AFTER语句级触发器

二、触发器的种类

Oracle中的触发器的类型主要有DML触发器、替代触发器、系统事件触发器和DDL 触发器。

2.1 DML触发器

DML触发器由DML语句触发，例如INSERT、UPDATE和DELETE语句。针对所有的DML事件，按触发的时间可以将DML触发器分为BEFORE触发器与AFTER触发器，分别表示在DML事件发生之前与之后采取行动。另外，DML触发器也可以分为语句级触发器与行迹触发器，其中，语句级触发器针对某一条语句触发一次，而行级触发器则针对语句所影响的每一行都触发一次。例如：某条UPDATE语句修改了表中的100行数据，那么针对该UPDATE事件的语句级触发器将被触发一次，而行级触发器将被触发100次。

例1: 建立一个触发器, 当职工表emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER tr_del_emp

BEFORE DELETE --指定触发时机为删除操作前触发

ON scott.emp

FOR EACH ROW --说明创建的是行级触发器

BEGIN --将修改前数据插入到日志记录表del_emp，以供监督使用。

INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

V ALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :https://www.360docs.net/doc/2d12884030.html,m, :old.h iredate );

END;

--测试触发器

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;

2.2 替代触发器

INSTEAD OF触发器又称为替代触发器，用于执行一个替代操作来代替触发事件的操作。例如：针对INSERT事件的INSTEAD OF触发器，它由INSERT语句触发，当出现INSERT 语句时，该语句不会被执行，而是执行INSTEAD OF触发器中定义的语句。

创建INSTEAD OF触发器需要注意以下几点：

●只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

●不能指定BEFORE 或AFTER选项。

●FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是

行级触发器，没有必要指定。

●没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可

以了。

2.3 系统事件触发器

系统事件触发器在发生如数据库启动或者关闭等系统事件时触发，包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。

例2：创建登录、退出触发器。

CREATE TABLE log_event

(user_name VARCHAR2(10),

address V ARCHAR2(20),

logon_date timestamp,

logoff_date timestamp);

--创建登录触发器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logon_date)

V ALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--创建退出触发器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DA TABASE

BEGIN

INSERT INTO log_event (user_name, address, logoff_date)

V ALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logoff;

2.4 DDL触发器

DDL触发器由DDL语句触发，例如：CREATE、ALTER和DROP语句。DDL触发器同样可以分为BEFORE触发器与AFTER触发器。

三、触发器的应用

Oracle触发器一般应用在以下方面：

3.1 确保数据库的安全性

●可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

●可以基于数据库中的数据限制用户的操作，例如不允许价格的升幅一次超过10%。

3.2 实施复杂的安全性授权

●利用触发器控制实体的安全性，可以将权限藉于各种数据库的值。

3.3 提供复杂的审计功能

●审计用户操作数据库的语句。

●把用户对数据库的更新写入审计表。

3.4 维护不同数据库之间同步表

●在不同的数据库之间可以利用快照来实现数据的复制，但有些系统要求两个数据库数据

实时同步，就必须利用触发器从一个数据库中向另一个数据库复制数据。

3.5 实现复杂的数据完整性规则

●实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不

同，触发器可以引用列或数据库对象。

●提供可变的缺省值。

3.6 实现复杂的非标准的数据库相关完整性规则

●触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的

删除触发器可导致相应删除在其它表中的与之匹配的行。

●触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当

插入一个与其主健不匹配的外部键时，这种触发器会起作用。

四、编写触发器注意事项

●触发器不接受参数。

●一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一

个。并各触发器之间不能有矛盾。

●在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

●触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进

行调用。

●在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），

不能使用DDL语句（CREATE、ALTER、DROP）。

●触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器

是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

●在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

●在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的

任何long和blob列。

●不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和

作用有较大区别。

●触犯器声明变量赋值方式和dephi类似，使用:= 符号来赋值。新值new, 旧值old前面

不要忘记：符号。

参考文献

[1] 《Oracle11g 数据库应用简明教程》, 杨少敏王红敏, 清华大学出版社, 2010

[2] 《Oracle触发器在信息系统安全方面的应用》, 谢梅源, 温州职业技术学院学报, 2004

[3] 《基于Oracle数据库存储过程与触发器的应用》, 刘小生张世良, 江西有色金属, 2007

[4] 《Oracle中特殊类型触发器的使用》, 王二暖, 华南金融电脑应用技术, 2007

[5] 《Oracle数据库触发器及其应用》, 王杰文李赫男, 计算机应用研究, 2001

[6] 《Oracle触发器的功能与应用》, 梁桦, 北京电脑技术应用研究所, 2004