《Oracle Database 11g PL/SQL程序设计》触发器介绍

数据库触发器是特殊的存储程序。通常不直接调用它们，而要由数据库中的事件触发。它们在执行命令时和执行数据库管理系统动作之间运行。触发器可以用PL/SQL或Java编写。触发器可以捕获创建、修改或删除对象的事件，也可以捕获表或视图中的插入、更新或删除操作。它们还可以监控数据库或模式的状态变化及用户动作的变化。

本章包括如下主题：

●       触发器简介

●       触发器体系结构

●       数据定义语言触发器

●       数据操作语言触发器

●       复合触发器

●       instead-of触发器

●       系统或数据库事件触发器

●       触发器限制

这些部分首先介绍基础知识，然后提出一些思想。如果想马上开始编写具体类型的触发器，也可以将它们作为快速参考。例如，可以翻到本章10.4节了解如何编写插入、更新和删除的触发器。

 

 

10.1  触发器简介

 

数据库触发器是特殊的存储程序。因此，它们的定义与DDL规则非常相似。同样，触发器可以调用SQL语句和PL/SQL函数与过程。可以选择用PL/SQL或Java实现触发器。可以参见第14章和附录D了解编写Java库支持触发器的语法。

数据库触发器不同于存储函数和过程，因为不能直接调用它们。当数据库中发生触发事件时会激发数据库触发器。所以它们是管理数据库的有力工具。可以通过触发器来限制或重定向程序的行为。

触发器的用途有：

●       控制DDL语句的行为，如通过更改、创建或重命名对象

         （DDL ：数据定义语言及翻译程序）

●       控制DML语句的行为，如插入、更新和删除

         （DML ：数据操纵语言及编译（解释）程序）

●       实施参照完整性、复杂业务规则和安全性策略

●       在修改视图中的数据时控制和重定向DML语句

●       通过创建透明日志来审核系统访问和行为的信息

另一方面，由于不能控制对触发器的同步调用序列，所以如果过分依赖触发器的话，这可能成为问题。您唯一能控制的是让它们在某个事件之前还是之后触发。Oracle 11g提供了复合触发器来辅助管理较大的事件，比如需要排序的触发器事件。

使用触发器有一定的风险。风险比较复杂，因为当SQL语句激发触发器时，触发器会调用SQL语句。一个触发器可能调用一个会激发另一个触发器的SQL语句。后来的触发器可能重复这一行为并激发另一个触发器。这样就创建了连锁触发(cascading trigger)。Oracle 11g和更早的版本将连锁触发的数量限制为32，超过这个数时就会抛出异常。

下面概括了5种触发器类型及它们的用途。

●       数据定义语言触发器  当创建、修改或删除数据库模式中的对象时会激发这些触发器。它们有助于控制或监控DDL语句。instead-of create表触发器提供了一个工具，它可以确保表的创建符合开发标准，比如包括存储或分区子句。也可以用它们监控不良的编程行为，比如当程序创建和删除临时表而不是使用Oracle集合时。临时表会分割磁盘空间，随着时间的推移而降低数据库的性能。

●       数据操作语言触发器  当在表中执行插入、更新或删除数据的操作时激活这些触发器。可以分别用语句级或行级触发器类型对表上的所有修改或每行的修改激发一次触发器。DML触发器可用来控制DML语句。在修改值之前可以用这些触发器来审核、检查、保存和替换值。数值主键的自动编号经常通过一个行级DML触发器来完成。

●       复合触发器  当在表中执行插入、更新或删除数据的操作时，这些触发器同时充当语句级和行级触发器的角色。该触发器可以捕获关于4个计时点的信息：(a)激发语句前；(b)激发语句中的每一行变化前；(c)激发语句中的第一行变化后；(d)激发语句后。当需要在语句和行事件级别中采取动作时，可以用这些类型的触发器来审核、检查、保存和替换值。

●       instead-of触发器  这些触发器可以停止DML语句的执行，并重定向DML语句。INSTEAD OF触发器常用于管理编写不可更新的视图的方式。INSTEAD OF触发器向定义可更新的视图的表中应用业务规则和直接插入、更新或删除行。另外，INSTEAD OF触发器在与视图相关的指定表中插入、更新或删除行。

●       系统或数据库事件触发器  当数据库中的系统活动发生时激发这些触发器，比如登录和退出事件触发器。它们对于审核系统访问信息比较有用。这些触发器可以用来跟踪系统事件并将它们反映给用户。

触发器有一些重要的限制要注意。最大的限制是触发器主体绝不能大于32 760字节。这是因为触发器主体存储在LONG数据类型的列中。这意味着应当尽量保持触发器主体比较小。可以将编码逻辑放在其他模式级组件(比如函数、过程和包)中来做到这一点。将编码逻辑移出触发器主体中的另一个优点是：当它位于触发器主体中时不能包装它，这将在附录F中解释。

每一个触发器都有一套管理它用法的规则。我们将用5个小节来介绍这5个触发器。下一节将描述数据库触发器的体系结构。

 

使用触发器所需的权限

必须具有CREATE TRIGGER系统权限才能在自己拥有的对象上创建触发器。如果对象由另一名用户拥有，就需要那一名用户授予您对该对象的ALTER权限。还有一种办法，拥有权限的用户可以授予您ALTER ANY TABLE和CREATE ANY TRIGGER权限。

虽然您有自己的模式级组件上的定义者权限，但当调用另一个用户拥有的模式级组件时必须执行EXECUTE权限。在开发期间应当列出所有必需的权限，才能使接下来的实现更顺利。

 

 

10.2  数据库触发器体系结构

 

数据库触发器是在数据库(比如在包)中定义的。它们由两块组成：数据库触发器声明和主体。声明指出了如何以及何时调用触发器。不能直接调用触发器。它们通过激发事件触发(调用)。激发事件是DDL或DML语句，或者数据库或系统事件。数据库触发器实现一种面向对象的观测器模式，这意味着它们监听事件然后采取行动。

触发器声明由4个部分组成：触发器名、语句、限制和动作。前3个部分定义了触发器声明，最后一个部分定义触发器主体。触发器名必须是独一无二的，但是可以与模式中的其他任何对象的名称重名，因为触发器有自己的命名空间。触发器语句标识激发触发器的事件或语句类型。触发器限制(比如WHEN子句或INSTEAD OF子句)用来限制触发器何时运行。触发器动作是触发器主体。

 

注意： 命名空间是数据库目录中维护的独有标识符列表。

 

除非删除了数据库触发器观测到的对象，否则数据库触发器声明是有效的。当某个事件激发数据库触发器时，数据库触发器声明还会创建一个运行时进程。触发器主体并不简单。触发器主体可能依赖于表、视图或存储程序。这意味着去掉依赖关系会使触发器主体无效。虽然依赖关系是局部模式对象，但是它们包括可能需要跨网络解析的同义词。当触发器主体变得无效时，触发器就会变得无效。触发器主体是特殊的匿名块程序。只能通过触发器调用和传递参数给它们。

当在创建事件上定义一个DDL触发器时，连接就变更敏锐。正如本章10.3节将讨论的，CREATE触发器的无效触发器主体会使您失去重新创建丢失的依赖关系的能力。ALTER和DROP等其他DDL事件也会发生类似的行为。

替换任何丢失的依赖关系后可以重新编译触发器。语法为：

                          ALTER TRIGGER trigger_name COMPILE;

触发器事件直接与触发器通信。通信的发生过程不可控制或不可见。除了通过系统定义的事件属性可用的数据库外，您没有其他数据(可参见本章后面的10.3.1节了解关于DDL、语句级DML及系统与数据库事件触发器)。但您具有对行级DML或INSTEAD OF触发器中的new和old伪记录类型的访问权限。这些类型的结构是动态的，在运行时定义它们。触发器声明从激发它的DML语句中继承了这些值的声明。

DML行级和INSTEAD OF触发器对它们的触发器主体的调用不同于语句级的触发器。当一个事件激发这种类型的触发器时，触发器声明产生了一个运行时程序单元。这个运行时单元是该进程中“真正的”触发器。这个触发器通过与激发它的DML语句通信来使new和old伪记录结构可用。触发器代码块可以通过将伪记录结构作为绑定变量调用来访问这些伪记录结构。触发器代码块是匿名PL/SQL块，只能通过触发器声明访问。

正如第3章的表3-1中讨论的，绑定变量允许延伸到程序的作用域之外。可以访问在调用程序的作用域中定义的变量。:in和:out变量是触发器主体中的绑定变量。它们让触发器代码块与触发器会话通信。只有行级触发器可以引用这些伪记录结构绑定变量。行级触发器代码块可以通过这些绑定变量来读写，如图10-1所示。

也可以从触发器主体中调用外部独立或包函数和过程。当从触发器主体中调用程序时，调用的程序是黑盒。这意味着外部存储程序不能访问:new和:old绑定变量。可以选择按值或按引用将它们传递给存储函数和过程。

Oracle 11g引入了复合触发器。这种新触发器改变了触发器编写的前景。现在可以激发复合触发器，捕获行级语句信息，在全局触发器集合中累积，并在AFTER STATEMENT计时块中访问数据。在本章10.5节将详细介绍这些内容。

可以在任何对象或事件上定义多个触发器。Oracle 11g没有提供同步触发器的方式，即不能确定何时激发第一个、第二个或最后一个触发器。这一限制是因为触发器是交叉存取的，也就是说程序单元是作为离散的进程彼此无关地工作的。触发器可能使应用程序接口变慢，尤其是行级语句。应当注意何时及在何处部署触发器来解决问题。

 

 

10.3  数据定义语言触发器

 

当创建、修改或删除数据库模式中的对象时，会激发数据定义语言触发器。它们可以用来控制或监控DDL语句。表10-1列出了使用DDL触发器的数据定义事件。这些触发器都支持BEFORE和AFTER事件触发器，并在数据库或模式级运行。

通常，DDL触发器用于监控数据库中的重要事件。有时用它们来监控错误代码。错误代码可能会执行破坏数据库或使数据库不稳定的活动。更常见的情况是，在开发、测试和stage系统中用它们来了解和监控数据库活动的动态。

 

注意： stage系统用于在部署到生产中之前进行终端用户测试和负载平衡度量。

 

为应用程序打补丁时，DDL触发器非常有用。通过它们可以发现不同版本之间的潜在变化。也可以在升级过程中用instead-of create触发器来实施表创建存储子句或分区规则。

 

警告： 在生产系统中应当小心监控此类触发器的开销。

 

这些触发器也可以通过导致数据库碎片的应用程序来跟踪表的创建和修改。当监控GRANT和REVOKE权限语句时，它们也是有效的安全工具。下面几节列出并详细描述了可以用来补充DDL触发器的事件属性函数。

表10-1  可用的数据定义事件

DDL 事 件	说    明
ALTER	可以通过对对象的某些部分进行一些修改来ALTER(更改)对象，比如它们的约束、名称、存储子句或结构
ANALYZE	ANALYZE(分析)对象用来计算基于代价的优化器的统计信息
ASSOCIATE STATISTICS	ASSOCIATE STATISTICS(关联统计信息)用来将统计类型链接到列、函数、包、类型、域索引或索引类型
AUDIT	AUDIT(审核)用来启用对象或系统上的审核
COMMENT	COMMENT(注释)用来说明列或表的作用
CREATE	在数据库中CREATE(创建)对象，比如对象、权限、角色、表、用户和视图
DDL	用DDL事件来表示任一主要数据定义事件。它有效地说明了DDL事件可以作用于任何事情
DISASSOCIATE STATISTICS	DISASSOCIATE STATISTICS(取消统计信息的关联)用来取消统计信息类型与列、函数、包、类型、域索引或索引类型之间的链接
DROP	DROP(删除)数据库中的对象，比如对象、权限、角色、表、用户和视图
GRANT	向数据库中的用户GRANT(授予)权限或角色。权限使用户可以执行一些对象，比如对象、权限、角色、表、用户和视图
NOAUDIT	NOAUDIT(禁用审核)可以禁用对对象或系统的审核
RENAME	RENAME数据库中的对象，比如列、约束、对象、权限、角色、同义词、表、用户和视图
REVOKE	REVOKE(取消)数据库用户的权限或角色。该权限使用户可以对一些对象起作用，比如对象、权限、角色、表、用户和视图
TRUNCATE	TRUNCATE(清空)表，它删除表中的所有行，并将高水位标记重置为原始存储子句最初的扩展值。与DML的DELETE语句不同，TRUNCATE命令不能用ROLLBACK命令恢复。可以用这种新的闪回技术来取消修改

 

10.3.1  事件属性函数

下面是系统定义的事件属性函数列表：

●       ORA_CLIENT_IP_ADDRESS

●       ORA_DATABASE_NAME

●       ORA_DES_ENCRYPTED_PASSWORD

●       ORA_DICT_OBJ_NAME

●       ORA_DICT_OBJ_NAME_LIST

●       ORA_DICT_OBJ_OWNER

●       ORA_DICT_OBJ_OWNER_LIST

●       ORA_DICT_OBJ_TYPE

●       ORA_GRANTEE

●       ORA_INSTANCE_NUM

●       ORA_IS_ALTER_COLUMN

●       ORA_IS_CREATING_NESTED_TABLE

●       ORA_IS_DROP_COLUMN

●       ORA_IS_SERVERERROR

●       ORA_LOGIN_USER

●       ORA_PARTITION_POS

●       ORA_PRIVILEGE_LIST

●       ORA_REVOKEE

●       ORA_SERVER_ERROR

●       ORA_SERVER_ERROR_DEPTH

●       ORA_SERVER_ERROR_MSG

●       ORA_SERVER_ERROR_NUM_PARAMS

●       ORA_SERVER_ERROR_PARAM

●       ORA_SQL_TXT

●       ORA_SYSEVENT

●       ORA_WITH_GRANT_OPTION

●       SPACE_ERROR_INFO

 

1. ORA_CLIENT_IP_ADDRESS

ORA_CLIENT_IP_ADDRESS函数不接受形参。它返回的客户端IP地址为VARCHAR2数据类型。

用法如下：

DECLARE

ip_address VARCHAR2(11);

BEGIN

IF ora_sysevent = 'LOGON' THEN

ip_address := ora_client_ip_address;

 END IF;

END;

2. ORA_DATABASE_NAME

ORA_DATABASE_NAME函数不接受形参。它返回的数据库名称为VARCHAR2数据类型。

用法如下：

DECLARE

database VARCHAR2(50);

BEGIN

database := ora_database_name;

END;

3. ORA_DES_ENCRYPTED_PASSWORD

ORA_DES_ENCRYPTED_PASSWORD函数不接受形参。它返回的DES加密口令为VARCHAR2数据类型。这等价于Oracle 11g中SYS.USER$表的PASSWORD列中的值。不再能够从DBA_USERS或ALL_USERS视图中访问口令。

用法如下：

DECLARE

password VARCHAR2(60);

BEGIN

IF ora_dict_obj_type = 'USER' THEN

password := ora_des_encrypted_password;

END IF;

END;

4. ORA_DICT_OBJ_NAME

ORA_DICT_OBJ_NAME函数不接受形参。它返回的对象名为VARCHAR2数据类型。对象名表示了DDL语句的目标。

用法如下：

DECLARE

database VARCHAR2(50);

BEGIN

database := ora_obj_name;

END;

5. ORA_DICT_OBJ_NAME_LIST

ORA_DICT_OBJ_NAME_LIST函数接受一个形参。返回该形参也是因为它是作为VARCHAR2变量的OUT模式列表按引用传递的。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。name_list包含触发事件所触发的对象名列表。

用法如下：

DECLARE

name_list DBMS_STANDARD.ORA_NAME_LIST_T;

counter PLS_INTEGER;

BEGIN

IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN

counter := ora_dict_obj_name_list(name_list);

END IF;

END;

6. ORA_DICT_OBJ_OWNER

ORA_DICT_OBJ_OWNER函数不接受形参。它返回事件相关的对象的拥有者，数据类型为VARCHAR2。

用法如下：

DECLARE

owner VARCHAR2(30);

BEGIN

database := ora_dict_obj_owner;

END;

7. ORA_DICT_OBJ_OWNER_LIST

ORA_DICT_OBJ_OWNER_LIST函数接受一个形参。该形参也返回是因为它是按引用传递的OUT模式的VARCHAR2变量。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。

在本例中，owner_list包含对象拥有者的列表，其中它们的统计信息由触发事件分析。用法如下：

DECLARE

owner_list DBMS_STANDARD.ORA_NAME_LIST_T;

counter PLS_INTEGER;

BEGIN

IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN

counter := ora_dict_obj_owner_list(owner_list);

END IF;

END;

8. ORA_DICT_OBJ_TYPE

ORA_DICT_OBJ_TYPE函数不接受形参。它返回事件修改的字典对象的数据类型，数据类型为VARCHAR2。

用法如下：

DECLARE

type VARCHAR2(19);

BEGIN

database := ora_dict_obj_type;

END;

9. ORA_GRANTEE

ORA_GRANTEE函数接受一个形参。该形参也返回是因为它是按引用传递的OUT模式的VARCHAR2变量。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。user_list包含触发事件授予了权限或角色的用户列表。

用法如下：

DECLARE

user_list DBMS_STANDARD.ORA_NAME_LIST_T;

counter PLS_INTEGER;

BEGIN

IF ora_sysevent = 'GRANT' THEN

counter := ora_grantee(user_list);

END IF;

END;

10. ORA_INSTANCE_NUM

ORA_INSTANCE_NUM函数不接受形参。它返回当前数据库实例编号，数据类型为NUMBER。

用法如下：

DECLARE

instance NUMBER;

BEGIN

instance := ora_instance_num;

END;

11. ORA_IS_ALTER_COLUMN

ORA_IS_ALTER_COLUMN函数接受一个形参，它是一个列名。该函数返回BOOLEAN数据类型的真或假值。当列被更改时返回真，列未被更改时返回假。该函数使用传统的大写目录信息，但是如果在Oracle 11g中选择了以区分大小写的格式保存所有表，就需要匹配目录的大小写。该示例用了一个区分大小写的字符串作为实参：

用法如下：

DECLARE

TYPE column_list IS TABLE OF VARCHAR2(32);

columns COLUMN_LIST := column_list('CREATED_BY','LAST_UPDATED_BY');

BEGIN

IF ora_sysevent = 'ALTER' AND

ora_dict_obj_type = 'TABLE' THEN

FOR i IN 1..columns.COUNT THEN

IF ora_is_alter_column(columns(i)) THEN

INSERT INTO logging_table

VALUES (ora_dict_obj_name||'.'||columns(i)||' changed.');

END IF;

END LOOP;

END IF;

END;

如果要防止修改标准who-audit列，比如CREATED_BY、CREATION_DATE、 LAST_UPDATED_BY或LAST_UPDATE_DATE，这会非常有用。这些是安全列，通常用来标识谁最后通过标准应用程序接口(API)接触数据。对这些列的任何修改都可能使API不稳定。

12. ORA_IS_CREATING_NESTED_TABLE

ORA_IS_CREATING_NESTED_TABLE函数不接受形参。当创建一个带嵌套表的表时，它返回BOOLEAN数据类型的真或假值。

用法如下：

BEGIN

IF ora_sysevent = 'CREATE' AND

ora_dict_obj_type = 'TABLE' AND

ora_is_creating_nested_table THEN

INSERT INTO logging_table

VALUES (ora_dict_obj_name||'.'||' created with nested table.');

END IF;

END;

13. ORA_IS_DROP_COLUMN

ORA_IS_DROP_COLUMN函数接受一个形参，它是一个列名。该函数返回BOOLEAN数据类型的真或假值。当列被删除时返回真，当列没有被删除时它返回假。虽然这个函数使用传统的大写目录信息，但是在Oracle 11g中如果选择以区分大小写的格式保存所有表，则需要匹配目录的大小写。本例使用一个不区分大小写的字符串作为实参。

用法如下：

DECLARE

TYPE column_list IS TABLE OF VARCHAR2(32);

columns COLUMN_LIST := column_list('CREATED_BY','LAST_UPDATED_BY');

BEGIN

IF ora_sysevent = 'DROP' AND

ora_dict_obj_type = 'TABLE' THEN

FOR i IN 1..columns.COUNT THEN

IF ora_is_drop_column(columns(i)) THEN

INSERT INTO logging_table

VALUES (ora_dict_obj_name||'.'||columns(i)||' changed.');

END IF;

END LOOP;

END IF;

END;

如果要防止修改who-audit列，比如这个表中前面讨论的ORA_IS_DROP_COLUMN函数的那些列，则该函数非常有用。

14. ORA_IS_SERVERERROR

ORA_IS_SERVERERROR函数接受一个形参，它是一个错误号。当该错误在错误堆栈上时，它返回BOOLEAN数据类型的真或假值。

用法如下：

BEGIN

IF ora_is_servererror(4082) THEN

INSERT INTO logging_table

VALUES ('ORA-04082 error thrown.');

END IF;

END;

15. ORA_LOGIN_USER

ORA_LOGIN_USER函数不接受形参。该函数返回当前模式名，数据类型为VARCHAR2。

用法如下：

BEGIN

INSERT INTO logging_table

VALUES (ora_login_user||' is the current user.');

END;

16. ORA_PARTITION_POS

ORA_PARTITION_POS函数不接受形参。该函数返回带SQL文本的数值位置，表示插入分区子句的地方。本函数仅用于INSTEAD OF CREATE触发器。

如果添加了自己的分区子句，可以使用下面的代码：

DECLARE

sql_text ORA_NAME_LIST_T;

sql_stmt VARCHAR2(32767);

partition VARCHAR2(32767) := 'partitioning_clause';

BEGIN

FOR i IN 1..ora_sql_txt(sql_text) LOOP

sql_stmt := sql_stmt || sql_text(i);

END LOOP;

sql_stmt := SUBSTR(sql_text,1,ora_partition_pos – 1)||' '

|| partition||' '||SUBSTR(sql_test,ora_partition_pos);

-- Add logic to prepend schema because this runs under SYSTEM.

sql_stmt := REPLACE(UPPER(sql_stmt),'CREATE TABLE '

,'CREATE TABLE '||ora_login_user||'.');

EXECUTE IMMEDIATE sql_stmt;

END;

这段编码示例要求授予触发器的拥有者CREATE ANY TRIGGER权限。应当为应用程序安排一个主权限用户，并且要避免使用SYSTEM模式。

17. ORA_PRIVILEGE_LIST

ORA_PRIVILEGE_LIST函数接受一个形参。该形参也返回是因为它是按引用传递的OUT模式的VARCHAR2变量。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。priv_list包含触发事件授予的权限或角色的列表。

用法如下：

DECLARE

priv_list DBMS_STANDARD.ORA_NAME_LIST_T;

counter PLS_INTEGER;

BEGIN

IF ora_sysevent = 'GRANT' OR

ora_sysevent = 'REVOKE' THEN

counter := ora_privilege_list(priv_list);

END IF;

END;

18. ORA_REVOKEE

ORA_REVOKEE函数接受一个形参。该形参也返回是因为它是按引用传递的OUT模式的VARCHAR2变量。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。priv_list包含触发事件授予的权限或角色列表。

用法如下：

DECLARE

revokee_list DBMS_STANDARD.ORA_NAME_LIST_T;

counter PLS_INTEGER;

BEGIN

IF ora_sysevent = 'REVOKE' THEN

counter := ora_revokee(priv_list);

END IF;

END;

19. ORA_SERVER_ERROR

ORA_SERVER_ERROR函数接受一个形参，它是错误堆栈上的位置，其中1是错误堆栈的顶端。它返回NUMBER数据类型的错误号。

用法如下：

DECLARE

error NUMBER;

BEGIN

FOR i IN 1..ora_server_error_depth LOOP

error := ora_server_error(i);

END LOOP;

END;

20. ORA_SERVER_ERROR_DEPTH

ORA_SERVER_ERROR_DEPTH函数不接受形参。该函数返回的错误堆栈上的错误号为PLS_INTEGER数据类型。ORA_SERVER_ERROR和ORA_SERVER_ERROR_MSG函数的代码示例演示了本函数的用法。

21. ORA_SERVER_ERROR_MSG

ORA_SERVER_ERROR_MSG函数接受一个参数，它是错误堆栈上的位置，其中1是错误堆栈的顶端。它返回VARCHAR2数据类型的错误消息文本。

用法如下：

DECLARE

error VARCHAR2(64);

BEGIN

FOR i IN 1..ora_server_error_depth LOOP

error := ora_server_error_msg(i);

END LOOP;

END;

22. ORA_SERVER_ERROR_NUM_PARAMS

ORA_SERVER_ERROR_NUM_PARAMS函数不接受形参。该函数返回错误消息中所有替代字符串的数目，数据类型为PLS_INTEGER。例如，错误格式可以是“Expected %s, found %s.” ORA_SERVER_ERROR_PARAM函数的代码示例显示了本函数的用法。

23. ORA_SERVER_ERROR_PARAM

ORA_SERVER_ERROR_PARAM函数接受一个形参，它是错误消息中的位置，其中1是错误消息中出现的第一个字符串的位置。它返回VARCHAR2数据类型的错误消息文本。

用法如下：

DECLARE

param VARCHAR2(32);

BEGIN

FOR i IN 1..ora_server_error_depth LOOP

FOR j IN 1..ora_server_error_num_params(i) LOOP

param := ora_server_error_param(j);

END LOOP;

END LOOP;

END;

24. ORA_SQL_TXT

ORA_SQL_TXT函数接受一个形参。该形参也返回是因为它是按引用传递的OUT模式的VARCHAR2变量。DBMS_STANDARD包中的形参数据类型被定义为ORA_NAME_LIST_T。ORA_NAME_LIST_T是一个VARCHAR2(64)数据类型的表。该函数返回列表中的元素个数，数据类型为PLS_INTEGER。该列表包含触发事件的SQL语句处理的子串。代码示例见ORA_PARTITION_POS函数。

25. ORA_SYSEVENT

ORA_SYSEVENT函数不接受形参。该函数返回负责激发触发器的系统事件，数据类型为VARCHAR2。

用法如下：

BEGIN

INSERT INTO logging_table

VALUES (ora_sysevent||' fired the trigger.');

END;

26. ORA_WITH_GRANT_OPTION

ORA_WITH_GRANT_OPTION函数不接受形参。该函数返回BOOLEAN数据类型的真或假值。当用授权选项授予权限时它返回真值。

用法如下：

BEGIN

IF ora_with_grant_option THEN

INSERT INTO logging_table

VALUES ('ORA-04082 error thrown.');

END IF;

END;

27. SPACE_ERROR_INFO

SPACE_ERROR_INFO函数使用6个按引用传递的形参。它们都是OUT模式参数。原型为：

space_error_info( error_number OUT NUMBER

, error_type OUT VARCHAR2

, object_owner OUT VARCHAR2

, table_space_name OUT VARCHAR2

, object_name OUT VARCHAR2

, sub_object_name OUT VARCHAR2)

当触发事件与out-of-space条件相关时，该函数返回真，它填充所有输出参数。它至少用支持6个参数的日志表实现。当函数返回假时，OUT模式变量为空。

用法如下：

DECLARE

error_number      NUMBER;

error_type        VARCHAR2(12);

object_owner      VARCHAR2(30);

tablespace_name VARCHAR2(30);

object_name       VARCHAR2(128);

subobject_name   VARCHAR2(30);

BEGIN

IF space_error_info( error_number, error_type

, object_owner, tablespace_name

, object_name, subobject_name) THEN

INSERT INTO logging_table

VALUES ( … implementation_dependent … );

END IF;

END;

 

10.3.2  构建DDL触发器

构建DDL触发器的原型为：

CREATE [OR REPLACE] TRIGGER trigger_name  -- trigger_name：触发器名称

{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA} --触发语句。

--before和after：指在事件发生之前或之后激活触发器;

--instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。 

    --ddl_event：如10.3.1所示

    --DATABASE：表示创建数据库级触发器，数据库级要给出数据库事件，则ddl_event 就是 {DDL事件1 [DDL事件2...]| 数据库事件1 [数据库事件2...]} 

    --SCHEMA：表示创建模式级触发器，模式级要给出模式事件或DDL事件

                            

[WHEN (logical_expression)] --WHEN 触发器限制: 限制不是必需的

[DECLARE]

declaration_statements;

BEGIN   --触发操作，是触发器的主题

execution_statements;

END [trigger_name];

/

 

只有当审核创建事件时才使用INSTEAD OF子句。在触发前，确保触发器主体的内容在触发DDL命令之前和触发器最后一次运行之后发生。可参见本章前面的“ORA_ PARTITION_POS”一节了解附加在分区表后面的INSTEAD OF CREATE触发器的实现。

DDL示例触发器要求在触发之前创建autid_creations表和audit_creations_sl序列。如果忘记了创建其中之一或者两者都忘记了，则在试图编译数据库触发器之后就不能创建任何一个。之所以存在这个限制是因为虽然触发器声明有效，但是触发器主体是无效的。必须删除或禁用触发器(声明)才能在该模式中创建任何内容。

应当注意，表和触发器采用的是相同的名称。之所以可以这样是因为Oracle数据库中有两个命名空间，一个用于触发器，另一个用于其他对象。

可按照如下代码所示创建表和序列：

CREATE TABLE audit_creation

( audit_creation_id NUMBER

, audit_owner_name   VARCHAR2(30) CONSTRAINT audit_creation_nn1 NOT NULL

, audit_obj_name     VARCHAR2(30) CONSTRAINT audit_creation_nn2 NOT NULL

, audit_date         DATE           CONSTRAINT audit_creation_nn3 NOT NULL

, CONSTRAINT audit_creation_p1     PRIMARY KEY (audit_creation_id));

CREATE SEQUENCE audit_creation_s1;

现在可以创建audit_creation系统触发器了。这个触发器显示了当DDL触发器无法使用依赖关系时的行为：

CREATE OR REPLACE TRIGGER audit_creation

BEFORE CREATE ON SCHEMA

BEGIN

INSERT INTO audit_creation VALUES

(audit_creation_s1.nextval,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,SYSDATE);

END audit_creation;

/

下面的DDL语句触发这个系统触发器，它将触发器属性函数中的数据插入。它创建了一个名为mythology的同义词，虽然不能将它翻译成任何有实际意义的词，但是它创建了一个激发触发器的事件。

DDL语句为：

CREATE SYNONYM mythology FOR plsql.some_myth;

可以用下面的SQL*Plus格式和语句查询触发器的结果：

COL audit_creation_id FORMAT 99999999 HEADING "Audit|Creation|ID #"

COL audit_owner_name FORMAT A6 HEADING "Audit|Owner|Name"

COL audit_obj_name FORMAT A8 HEADING "Audit|Object|Name"

COL audit_obj_name FORMAT A9 HEADING "Audit|Object|Name"

SELECT * FROM audit_creation;

该查询返回：

Audit Audit Audit

Creation Owner   Object     Audit

ID #   Name     Name        Date

---------- ------ --------- ---------

21   PLSQL   MYTHOLOGY 17-NOV-08

现在已经介绍了如何实现DDL触发器。下一节将介绍DML触发器。

 

 

10.4  数据操作语言触发器

DML触发器可以在INSERT、UPDATE和DELETE语句之前或之后激发。DML触发器可以是语句级或行级活动。无论有多少行受DML事件影响，语句级触发器都只激发和执行一个语句或一组语句一次。行级触发器为DML语句修改的每一行激发和执行一个语句或一组语句。

有关管理数据修改的触发器的重要一点是不能对它们使用SQL Data Control Language(DCL)，除非将触发器声明为自治。当触发器在一个事务的作用域中运行时，它们不允许设置SAVEPOINT，也不能执行ROLLBACK或COMMIT语句中的任何一句。同样地，它们不能在所调用的任何函数或过程的执行路径中有DCL(也称为TCL)语句。

构建DML触发器的原型为：

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER}

{INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}

ON table_name

[FOR EACH ROW]

[WHEN (logical_expression)]

[DECLARE]

[PRAGMA AUTONOMOUS_TRANSACTION;]

declaration_statements;

BEGIN

execution_statements;

END [trigger_name];

/

BEFORE或AFTER子句决定了是在数据的本地副本被修改之前还是之后激发触发器。可以在表上但不能在视图上定义BEFORE或AFTER子句。尽管原型显示了一个插入、更新、(列的)更新或删除，但也可以在事件之间使用包含运算符OR。在两个事件之间用一个OR将创建一个触发器，它参照两个事件而运行。用多个包含运算符可以创建支持所有4个可能事件的触发器。

DML触发器有两个选项。可以将它们声明为语句级触发器，也称为表级触发器；或者，也可以将它们声明为行级触发器。

行级触发器中有一个FOR EACH ROW子句、一个WHEN子句及new和old伪记录。不同于每个语句激发一次，FOR EACH ROW子句指定触发器为每行激发。WHEN子句起过滤器的作用，它指定何时激发触发器。与使用其他存储程序单元时不同，在触发器中声明局部变量、类型或游标时必须限定一个DECLARE块。

触发器要求在触发器主体中有DECLARE块，因为触发器的声明是与主体分离的。触发器主体就像匿名块PL/SQL程序。它们由触发器调用，触发器隐式地管理参数传递。触发器主体不支持替代变量，比如匿名块。它们支持绑定变量，不过仅在行级触发器的上下文中是这样。另外，没有传递给语句级触发器的参数。

语句级和行级触发器的作用和方法各不相同。这两种类型的触发器将在下两节介绍。

 

10.4.1  语句级触发器

语句级触发器也称为表级触发器，因为它们通过对表的修改来触发。当用户插入、更新或删除表中的一行或多行时，语句级触发器捕获并处理信息。也可以通过将UPDATE语句触发器约束为仅当特定列的值发生变化时才激活，从而对它们进行限制(过滤)。可以通过用一个UPDATE OF子句来限制触发器。该子句可应用于列名或用逗号分隔的列名列表。

在语句级触发器中不能使用WHEN子句。也不能引用new或old伪记录，否则会引发ORA-04082异常。这个异常是一个编译时错误，它指出表级触发器中不允许new或old引用。

在插入、更新或删除事件时可以实现语句级触发器。语句级触发器不能用来收集事务的详细信息。您仅具有对事件属性函数返回的事件和值类型的访问权限。UPDATE OF子句将触发器事件过滤为特定列发生变化时才触发。

语句级示例用一个UPDATE OF Column name事件。使用该触发器需要从本书Web站点下载create_store.sql脚本。本书前言中列出了该站点地址。

这个触发器记录price_type_log表中的事件。它必须在编译触发器前创建。下面的语句将创建表：

-- This is found in create_price_type_trigger.sql on the publisher's web site.

CREATE TABLE price_type_log

( price_id     NUMBER             CONSTRAINT price_type_log_nn1 NOT NULL

, user_id      VARCHAR2(32)       CONSTRAINT price_type_log_nn2 NOT NULL

, action_date DATE CONSTRAINT    price_type_log_nn3 NOT NULL

, CONSTRAINT   price_type_log_p1 PRIMARY KEY (price_id))

/

创建了表之后就可以创建触发器。如果在另一个表上声明了另一个price_t1触发器，则触发器可能失效。只有对相同的表执行CREATE OR REPLACE TRIGGER命令时，REPLACE命令才会生效。当另一个表已经存在与触发器名相同的名称时，会引发ORA-04095异常。

下面的触发器在Oracle 10g或11g中运行。Oracle 10g不支持对SQL语句中的序列.nextval或.currval伪列的引用(当它们在PL/SQL块中时)。

在本书的Web站点上没有发现向后兼容的触发器脚本。

CREATE OR REPLACE TRIGGER price_t1

AFTER UPDATE OF price_type ON price

DECLARE

price_id NUMBER;

BEGIN

SELECT price_log_s1.nextval INTO price_id FROM dual;

INSERT INTO price_type_log

VALUES (price_log_s1.nextval,USER,SYSDATE);

END price_t1;

/

Oracle 11g不支持对SQL语句中的序列.nextval或.currval伪列的引用(当它们在PL/SQL块内时)。代码如下：

-- This is found in create_price_type_trigger.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER price_t1

AFTER UPDATE OF price_type ON price

BEGIN

-- This statement only works in Oracle 11g forward.

INSERT INTO price_type_log VALUES (price_log_s1.nextval,USER,SYSDATE);

END price_t1;

/

可以通过运行下面的UPDATE语句来触发它，这个语句什么都没有修改，因为它直接将price_type列的当前值重新赋给了自身：

UPDATE price p

SET p.price_type = p.price_type

WHERE EXISTS (SELECT NULL

FROM price q

WHERE q.price_id = p.price_id);

下面的查询显示了触发器被触发并将信息写入price_type_log表：

SELECT * FROM price_type_log;

本小节介绍了如何使用语句级DML触发器。下一节将介绍如何编写行级触发器。

 

10.4.2  行级触发器

行级触发器可以从每一行中捕获新值和以前的值。该信息可用来审核修改、分析行为和在不执行数据库恢复操作的情况下恢复以前的数据。

在行级触发器中使用FOR EACH ROW子句时有两个伪记录。它们都是指在DML语句中引用的列。这些伪记录是组合变量；new或old是WHEN子句中的伪记录变量名，:new和:old是触发器主体中的绑定变量。它们之所以不同是因为触发器声明和主体是两个独立的PL/SQL块。new和old伪记录在行级触发器的作用域中声明。触发器声明是调用块，触发器主体是被调用的块。当事件激发数据库会话中的触发器时，在PL/SQL块之间按引用传递绑定变量。伪记录的元素是伪字段。

new或old伪记录是会话级组合变量。它们是在触发事件的作用域中隐式声明的，这些触发事件是DML语句。触发器没有像独立的函数或过程那样的形式签名，但是它们对DML语句修改的列值有访问权限。这些列值是伪记录的元素，或伪字段。伪字段的值是由INSERT语句插入、由UPDATE语句设置或者由DELETE语句删除的列。

在WHEN子句中，可通过引用new或old伪记录、一个组件选择符和一个列名来访问伪字段。在触发器主体内，应在伪记录前面加上冒号(:)作前缀。冒号用来引用触发器主体中的外部作用域伪记录。DML语句声明列名的列表(伪字段)。

下面的示例演示了一个触发器，对于带连字符号的名字，用一个短划线代替姓氏中的空白符。

 

WHEN子句检查contact表中last_name伪字段的值是否包含空白。如果符合条件，则触发器将控制传递给触发器主体。触发器主体中有一个语句；REGEXP_REPLACE函数接受伪字段的一个副本作为实参。REGEXP_REPLACE将字符串中的所有空白改为短划线，它返回修改后的值作为结果。结果被赋予伪字段，并成为INSERT语句中的值。这是一个用DML触发器将所有姓以带连字符的形式输入的示例。

使用触发器需要运行前言介绍的站点提供create_store.sql脚本。编译了测试模式中的触发器后，就可以通过运行下面的插入来测试触发器：

INSERT INTO contact

VALUES (contact_s1.nextval, 1001, 1003

,       'Zeta Jones','Catherine',NULL

,        3, SYSDATE, 3, SYSDATE);

它将姓转换为带连字符的姓。从contact表中查询last_name可以查看实际插入值：

SELECT last_name FROM contact WHERE last_name LIKE 'Zeta%';

结果应当如下：

LAST_NAME

--------------------

Zeta-Jones

触发器的唯一问题是用户可以简单地更新列以从last_name列中删除短划线。可以用包含运算符OR来阻止单个触发器中发生这种情况，比如：

CREATE OR REPLACE TRIGGER contact_insert_t1

BEFORE INSERT OR UPDATE OF last_name ON contact

FOR EACH ROW

WHEN (REGEXP_LIKE(new.last_name,' '))

BEGIN

:new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1);

END contact_insert_t1;

/

触发器现在在所有INSERT语句上激发，并且只针对修改last_name列的UPDATE语句。当采用相同类型的动作时，最好构建使用多个DML语句的触发器。

DML触发器的另一个常见用法是自动为主键列编号。Oracle不像Microsoft Access或SQL Server那样自动编码。所以需创建一个序列和触发器来管理自动编号。

尽管可选用WHEN子句来创建这种类型的触发器，但是WHEN子句可以过滤触发器何时应当或不应运行。WHEN子句可以插入手工主键值，它可以在单个事务插入期间同步主键和外键的伪列.nexttval和.currval。

与其构建一个多表示例，不如从对数据库的新连接和断开连接的日志中发现自动编号的规律。本例代码的对比可参见“数据定义语言触发器”一节。监控登录和退出事件的DDL触发器调用记录在connection_log表的user_connection包中。该表定义为：

CREATE TABLE connection_log

( event_id          NUMBER(10)

, event_user_name   VARCHAR2(30)   CONSTRAINT log_event_nn1 NOT NULL

, event_type        VARCHAR2(30)   CONSTRAINT log_event_nn2 NOT NULL

, event_date        DATE            CONSTRAINT log_event_nn3 NOT NULL

, CONSTRAINT connection_log_p1     PRIMARY KEY (event_id));

行级触发器connection_log_t1演示了在Oracle 10g中编写伪自动编号触发器的正确方式：

-- This is found in create_signon_trigger.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER connection_log_t1

BEFORE INSERT ON connection_log

FOR EACH ROW

WHEN (new.event_id IS NULL)

BEGIN

SELECT connection_log_s1.nextval

INTO    :new.event_id

FROM    dual;

END;

/

connection_log_t1触发器演示了如何管理一个序列，但它也说明了如何SELECT INTO一个伪字段变量。当在Oracle 11g数据库上部署触发器时应当真正地修改它，因为不再需要从伪表dual中选择一个序列值到变量中。

行级触发器connection_log_t2演示了在Oracle 11g中编写伪自动编号触发器的正确方式：

CREATE OR REPLACE TRIGGER connection_log_t1

BEFORE INSERT ON connection_log

FOR EACH ROW

WHEN (new.event_id IS NULL)

BEGIN

:new.event_id := connection_log_s1.nextval;

END;

/

只有当INSERT语句没有提供主键值时，connection_log_t1和connection_log_t2触发器才会激发。

这些行级触发器说明了两个处理规则。一个规则是可以引用一个伪行列作为WHEN子句中的普通变量，因为实际触发器在与DML事务相同的内存作用域中激发。另一个规则是必须引用一个伪行列作为实际触发器作用域中的绑定变量，且它在不同的内存空间运行。伪行NEW和OLD是按引用传递的结构。new和old伪记录变量也接收触发器主体中的任何修改(当它们返回到活动DML会话中时)。

当执行INSERT语句时，所有old伪记录列都是空值，当运行DELETE语句时，new伪记录列是空值。在执行UPDATE语句期间，new和old伪记录都会出现，但仅指那些由SET子句引用的列。

本小节说明了如何写行级触发器，演示了如何在WHEN子句和触发器主体中使用新、老伪记录。

本节介绍了如何使用DML触发器，并分析了语句级和行级触发器的实现。学完本节后应当能够使用DML触发器。

 

 

10.5  复合触发器

在表中进行插入、更新或删除操作时，复合触发器既是语句级又是行级触发器。可以用复合触发器捕获4个计时点的信息：(a)激发语句之前；(b)激发语句中的每一行发生变化前；(c)激发语句中的第一行变化后；(d)激发语句之后。当要在语句和行事件级别都要采取行动时，可以用这些类型的触发器来审核、检查、保存和替换值。

在复合触发器面世之前，模拟这一行为需要很多努力，在语句触发器失效之后还要冒内存泄漏的风险。复合触发器函数就像一个多线程的进程。这种触发器作为一个整体有一个声明部分，每个计时点部分有自己的局部声明部分。计时点部分是复合触发器的次级触发器块。

当同时想要语句级和行级触发器的行为时，可以使用复合触发器。可以在表上也可以在视图上定义它们。复合触发器不支持用WHEN子句进行的过滤动作，或者自治事务PRAGMA。在更新中可以用UPDATE OF列名过滤器作为管理事件。同样，复合触发器的激发顺序不是一定的，因为它们可能被独立触发器的激发交叉存取(在两者之间混合)。

 

提示： 总是可以调出自治运行的存储函数或过程。

 

虽然复合触发器不支持EXCEPTION块，但是可以在任何次级计时点块中实现EXCEPTION块。GOTO命令被限制为单个计时点块，这意味着不能在计时块之间调用。可以在行级语句块中使用:new和:old伪记录，但不能在其他地方使用。

复合触发器的最小实现要求至少实现一个计时点块。只有DML语句能触发复合触发器。同样，当(a)DML语句没有修改任何行，和(b)触发器没有至少实现BEFORE STATEMENT或AFTER STATEMENT块时，复合触发器不会激发。当DML主句使用批(bulk)操作时，复合触发器有很大的性能优势。

复合触发器的原型为：

CREATE [OR REPLACE] TRIGGER trigger_name

FOR {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}

ON table_name

COMPOUND TRIGGER

[BEFORE STATEMENT IS

[declaration_statement;]

BEGIN

execution_statement;

END BEFORE STATEMENT;]

[BEFORE EACH ROW IS

[declaration_statement;]

BEGIN

execution_statement;

END BEFORE EACH ROW;]

[AFTER EACH ROW IS

[declaration_statement;]

BEGIN

execution_statement;

END AFTER EACH ROW;]

[AFTER STATEMENT IS

[declaration_statement;]

BEGIN

execution_statement;

END AFTER STATEMENT;]

END [trigger_name];

/

本例将“行级触发器”一节中的插入事件行级触发器重写为复合触发器。代码如下：

-- This is found in create_signon_trigger.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER compound_connection_log_t1

FOR INSERT ON connection_log

COMPOUND TRIGGER

BEFORE EACH ROW IS

BEGIN

IF :new.event_id IS NULL THEN

:new.event_id := connection_log_s1.nextval;

END IF;

END BEFORE EACH ROW;

END;

/

关于复合触发器应当注意3个关键元素。在这种触发器类型中不能通过WHEN子句过滤事件。正如前面所提到的，:new和:old伪记录只能在BEFORE EACH ROW和AFTER EACH ROW计时块中使用。在全局声明块中声明的变量对实现的所有计时块的执行都保持它们的值。

可以收集BEFORE EACH ROW或AFTER EACH ROW计时点中的行级信息，并将该信息传输到在触发器主体中声明的全局集合。然后，可以在AFTER STATEMENT计时点中用集合内容执行批操作。如果没有向另一个表中写数据，可能会引发一个编号最大的递归调用错误：ORA-00036。

下面的示例演示了收集行级计时点中的信息，将它传递到全局集合中，并将它作为AFTER STATEMENT计时块中的批事务来处理。本示例需要运行本书站点中提供的create_store.sql脚本。第一步要求创建一个日志储存库，它通过创建下面的表和序列来完成：

-- This is found in create_compound_trigger.sql on the publisher's web site.

CREATE TABLE price_event_log

( price_log_id      NUMBER

, price_id          NUMBER

, created_by        NUMBER

, creation_date     DATE

, last_updated_by   NUMBER

, last_update_date DATE );

CREATE SEQUENCE price_event_log_s1;

触发器填充created_by和last_updated_by列作为应用程序“who-audit”信息的一部分。它假设您在将数据分片，这意味着需要为会话设置一个CLIENT_INFO值。物理CLIENT_INFO部分位于V$SESSION视图中。从本章后面“读、写会话元数据”中的说明可以了解关于这些概念的更多信息。

下面的代码将CLIIENT_INFO值设置为3，它是system_user表中的一个有效system_user_id：

EXEC dbms_application_info.set_client_info('3');

虽然该触发器依赖于CLENT_INFO列的状态，但是您可能想像得出，状态不能控制触发器。因此，当执行过程中少了CLIENT_INFO值时，该触发器该会指定a–1。

下面用BEFORE EACH ROW和AFTER STATEMENT计时块定义复合触发器：

-- This is found in create_compound_trigger on the publisher's web site.

CREATE OR REPLACE TRIGGER compound_price_update_t1

FOR UPDATE ON price

COMPOUND TRIGGER

-- Declare a global record type.

TYPE price_record IS RECORD

( price_log_id      price_event_log.price_log_id%TYPE

, price_id          price_event_log.price_id%TYPE

, created_by        price_event_log.created_by%TYPE

, creation_date     price_event_log.creation_date%TYPE

, last_updated_by   price_event_log.last_updated_by%TYPE

, last_update_date  price_event_log.last_update_date%TYPE );

-- Declare a global collection type.

TYPE price_list IS TABLE OF PRICE_RECORD;

-- Declare a global collection and initialize it.

price_updates PRICE_LIST := price_list();

BEFORE EACH ROW IS

-- Declare or define local timing point variables.

c NUMBER;

user_id NUMBER:= NVL(TO_NUMBER(SYS_CONTEXT('userenv','client_info')),-1);

BEGIN

-- Extend space and assign dynamic index value.

price_updates.EXTEND;

c := price_updates.LAST;

price_updates(c).price_log_id := price_event_log_s1.nextval;

price_updates(c).price_id := :old.price_id;

price_updates(c).created_by := user_id;

price_updates(c).creation_date := SYSDATE;

price_updates(c).last_updated_by := user_id;

price_updates(c).last_update_date := SYSDATE;

END BEFORE EACH ROW;

AFTER STATEMENT IS

BEGIN

-- Bulk insert statement.

FORALL i IN price_updates.FIRST..price_updates.LAST

INSERT INTO price_event_log

VALUES

( price_updates(i).price_log_id

, price_updates(i).price_id

, price_updates(i).created_by

, price_updates(i).creation_date

, price_updates(i).last_updated_by

, price_updates(i).last_update_date );

END AFTER STATEMENT;

END;

/

BEFORE EACH ROW计时块收集行级数据并将它存储在全局集合中，然后可以从另一个计时块中读取它。该集合的数值索引是动态的，并利用了Collection API的LAST方法。如果要了解它的工作方式，请参见第7章。

AFTER STATEMENT计时块读取全局集合并向日志表中执行数据的一个批

插入。下次激发触发器时，全局集合为空，因为复合触发器是连续实现的。

可以通过运行下面的UPDATE语句来测试触发器：

UPDATE price

SET last_updated_by=NVL(TO_NUMBER(SYS_CONTEXT('userenv','client_info')),-1);

然后，可以查询price_event_log表：

SELECT * FROM price_event_log;

本例说明了如何捕获行级数据，将它保存在全局集合中，并在语句级语句中重用它。

读、写会话元数据

在会话CLIENT_INFO列中读、写的过程都需要使用DBMS_APPLICATION_INFO包。在DBMS_APPLICATION_INFO包中要用SET_CLIENT_INFO过程将数据写到V$SESSION视图中的64个字符的CLIENT_INFO列中。下面的匿名PL/SQL块假设CREATED_BY和LAST_UPDATED_BY列都应为3：

BEGIN

-- Write value to V$SESSION.CLIENT_INFO column.

DBMS_APPLICATION_INFO.SET_CLIENT_INFO('3');

END;

/

现在，可以通过调用READ_CLIENT_INFO过程来读取这个值。当运行下面的程序时，应当用SQL*Plus启用SERVEROUTPUT来查看显示的输出：

DECLARE

client_info VARCHAR2(64);

BEGIN

-- Read value from V$SESSION.CLIENT_INTO column.

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info);

DBMS_OUTPUT.PUT_LINE('[ '||client_info||']');

END;

/

用户自定义的会话列允许存储与Access Control List(ACL)中的用户证书相关的独有信息。首先，在用户身份验证期间指定一个会话列值。然后，会话CLIENT_INFO列允许在一个模式中管理多个用户交互。当表的会话CLIENT_INFO列值符合表中的分片列值时，通过身份验证的用户可以访问表中的行。

本节介绍了Oracle 11g新增的复合触发器，并说明了如何实现它们。它们允许在一个触发器中结合语句级和行级触发器的优点和操作。

 

 

 

10.6  Instead-of触发器

可以用INSTEAD OF触发器来解释INSERT、UPDATE和DELETE语句，并用备用的程序代码替换那些指令。不可更新的视图通常用INSTEAD OF触发器来接受输出并解决使视图不可更新的问题。

构建INSTEAD OF触发器的原型为：

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF {dml_statement }

ON {object_name | database | schema}

FOR EACH ROW

[WHEN (logical_expression)]

[DECLARE]

declaration_statements;

BEGIN

execution_statements;

END [trigger_name];

/

INSTEAD OF触发器是另一种强大的触发器，它解决了如何使用复杂且不可更新的视图。知道了SELECT语句的工作方式后，就可以写程序代码来更新不能直接通过不可更新视图访问的数据。

只能对视图部署INSTEAD OF DML触发器。虽然对视图是否可更新没有限制，但是一般INSTEAD OF触发器是为不可更新的视图构建的。

下面的视图由本书Web站点上提供的数据模型支持。它也是不可更新的视图，因为有DECODE语句，如下所示：

-- This is found in create_insteadof_trigger.sql on the publisher's web site.

CREATE OR REPLACE VIEW account_list AS

SELECT c.member_id

,      c.contact_id

,      m.account_number

,      c.first_name

||     DECODE(c.middle_initial,NULL,' ',' '||c.middle_initial||' ')

||     c.last_name FULL_NAME

FROM contact c JOIN member m ON c.member_id = m.member_id;

如果没有INSTEAD OF触发器，则针对该视图的DML语句可能会引发ORA-01776异常，它表示不允许通过一个连接修改多个基表。也可能会抛出ORA-01779异常，它表示禁止修改一个列，因为它没有成功地映射到non-key-preserved表。

可以创建一个允许从该视图中更新或删除的INSTEAD OF触发器。然而，该视图没有支持将语句插入到每个基表中的足够信息。如果不重新定义该视图，就不能通过写程序来修复这一缺陷。

下面是一个INSTEAD OF INSERT触发器。对于任何插入不可更新的视图的企图都会引发一个异常。

CREATE OR REPLACE TRIGGER account_list_insert

INSTEAD OF INSERT ON account_list

FOR EACH ROW

BEGIN

RAISE_APPLICATION_ERROR(-20000,'Not enough data for insert!');

END;

/

编译了该触发器以后，这时针对该视图运行INSERT语句将引发下面的异常堆栈：

INSERT INTO account_list

  *

ERROR at line 1:

ORA-20000: Not enough data for insert!

ORA-06512: at "PLSQL.ACCOUNT_LIST_INSERT", line 2

ORA-04088: error during execution of trigger 'PLSQL.ACCOUNT_LIST_INSERT'

这里的问题是，您要定义3个INSTEAD OF事件还是一个？有些开发人员选择用多个INSTEAD OF触发器，而不是用一个触发器完成所有事情。应当考虑分别为插入、更新和删除事件定义一个触发器。表10-2列出了DBMS_STANDARD包中的INSERTING、UPDATING和DELETING函数。这些函数能让您确定DML事件的类型，并可编写一个能管理所有3个DML事件的触发器。

视图中缺少一些向member或contact表中插入的必需字段。有一种编程技巧可用来修复这些缺陷。

表10-2  数据操作语言事件函数

函  数  名	返回数据类型	说    明
DELETING	BOOLEAN	当DML事件在删除时DELETING函数返回Boolean类型的真值
INSERTING	BOOLEAN	当DML事件在插入时INSERTING函数返回Boolean类型的真值
UPDATING	BOOLEAN	当DML事件在更新时UPDATING函数返回Boolean类型的真值

通过使用表10-2中的事件函数可以为所有DML语句构建一个完整的触发器。下面提供了一个示例INSTEAD OF触发器：

-- This is found in create_insteadof_trigger.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER account_list_dml

INSTEAD OF INSERT OR UPDATE OR DELETE ON account_list

FOR EACH ROW

DECLARE

-- Source variable.

source account_list.full_name%TYPE := :new.full_name;

-- Parsed variables.

fname VARCHAR2(43);

mname VARCHAR2(1);

lname VARCHAR2(43);

-- Check whether all dependents are gone.

FUNCTION get_dependents (member_id NUMBER) RETURN BOOLEAN IS

rows NUMBER := 0;

CURSOR c (member_id_in NUMBER) IS

SELECT COUNT(*) FROM contact WHERE member_id = member_id_in;

BEGIN

OPEN c (member_id);

FETCH c INTO rows;

IF rows > 0 THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

END get_dependents;

BEGIN

IF INSERTING THEN -- On insert event.

RAISE_APPLICATION_ERROR(-20000,'Not enough data for insert!');

ELSIF UPDATING THEN -- On update event.

-- Assign source variable.

source := :new.full_name;

-- Parse full_name for elements.

fname := LTRIM(REGEXP_SUBSTR(source,'(^|^ +)([[:alpha:]]+)',1));

mname := REGEXP_SUBSTR(

REGEXP_SUBSTR(

source,'( +)([[:alpha:]]+)(( +|. +))',1),'([[:alpha:]])',1);

lname := REGEXP_SUBSTR(

REGEXP_SUBSTR(

source,'( +)([[:alpha:]]+)( +$|$)',1),'([[:alpha:]]+)',1);

-- Update name change in base table.

UPDATE contact

SET    first_name = fname

,      middle_initial = mname

,      last_name = lname

WHERE contact_id = :old.contact_id;

ELSIF DELETING THEN -- On delete event.

DELETE FROM contact WHERE member_id = :old.member_id;

-- Only delete the parent when there aren't any more children.

IF get_dependents(:old.member_id) THEN

DELETE FROM member WHERE member_id = :old.member_id;

END IF;

END IF;

END;

/

使用这种触发器也有一些窍门或风险。风险在触发器中不是好事，因为它们本应是十分安全的。这种触发器中的一个潜在的缺陷是声明部分中:new.full_name的赋值。如果source变量足够大，可以处理所有可能的赋值，则编译触发器时数据库不会进行检查。这是要使用第9章讨论的类型锚定的关键地方。

account_list_dml触发器将源变量锚定到指定的列值，它能确保不会引发ORA-06502、ORA-06512和ORA-04088错误。像独立的匿名块程序一样，在触发器主体的DECLARE块中进行赋值会引发一个运行时异常。

这个触发器针对不可更改的视图激发所有DML事件，在适当的时候它处理对基表的插入、更新或删除。正如前面所提到的，没有对基表执行INSERT语句的足够信息。当有人试图通过该视图插入一个新记录时，触发器会引发一个用户定义的异常。虽然有足够的信息更新名字，但是颇费周折。应当知道，如果名字前面以空白开头，则中间名的正则表达式不会起作用。除非contact表中的所有依赖行先被删除掉，否则DELETE语句仅接触一个表，因为通常不在依赖表中留下孤立的行。

本节介绍了如何编写单个事件和多个事件的INSTEAD OF触发器。应当尝试在单个INSTEAD OF触发器中编写所有的DML事件，因为它们要容易维护得多。

不可更新的触发器

当视图中包含下面的结构之一时，就是不可更新的视图：

●  集合运算符

●  聚集函数

●  CASE或DECODE语句

●  CONNECT BY、GROUP BY、HAVING或START WITH子句

●  DISTINCT运算符

●  连接(当包含连接键时会引发异常)

在更新视图时也不能引用任何伪列或表达式。

 

 

 

10.7  系统或数据库事件触发器

系统触发器可用来审核服务器启动和关闭、服务器错误，以及用户登录与退出活动。它们对于在连接期间跟踪每个用户和数据库服务器的正常运行时间很方便。

构建数据库SYSTEM触发器的原型为：

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER} database_event ON {database | schema}

[DECLARE]

declaration_statements;

BEGIN

execution_statements;

END [trigger_name];

/

登录(logon)和退出(logoff)触发器可实现对连接持续时间的监控。这些触发器的DML语句在user_connection包中。connecting_trigger和disconnecting_trigger都调用user_connection包中的过程来插入每个用户的登录和退出信息。

connection_trigger提供一个监控用户登录数据库的系统触发器示例，代码如下：

-- This is found in create_system_triggers.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER connecting_trigger

AFTER LOGON ON DATABASE

BEGIN

user_connection.connecting(sys.login_user);

END;

/

disconnection_trigger提供一个监控用户退出数据库的系统触发器示例，代码如下：

-- This is found in create_system_triggers.sql on the publisher's web site.

CREATE OR REPLACE TRIGGER disconnecting_trigger

BEFORE LOGOFF ON DATABASE

BEGIN

user_connection.disconnecting(sys.login_user);

END;

/

这两个触发器都是紧凑的，并调用user_connection包的方法。这个包需要connection_log表，即：

-- This is found in create_system_triggers.sql on the publisher's web site.

CREATE TABLE connection_log

( event_id          NUMBER

, event_user_name   VARCHAR2(30) CONSTRAINT log_event_nn1 NOT NULL

, event_type        VARCHAR2(14) CONSTRAINT log_event_nn2 NOT NULL

, event_date        DATE           CONSTRAINT log_event_nn3 NOT NULL

, CONSTRAINT connection_log_p1    PRIMARY KEY (event_id));

这个包主体声明了两个过程。一个过程支持登录触发器，另一个过程支持退出触发器。包规范为：

-- This is found in create_system_triggers.sql on the publisher's web site.

CREATE OR REPLACE PACKAGE user_connection AS

PROCEDURE connecting (user_name IN VARCHAR2);

PROCEDURE disconnecting (user_name IN VARCHAR2);

END user_connection;

/

user_connection包主体的实现为：

-- This is found in create_system_triggers.sql on the publisher's web site.

CREATE OR REPLACE PACKAGE BODY user_connection AS

PROCEDURE connecting (user_name IN VARCHAR2) IS

BEGIN

INSERT INTO connection_log (event_user_name, event_type, event_date)

VALUES (user_name,'CONNECT',SYSDATE);

END connecting;

PROCEDURE disconnecting (user_name IN VARCHAR2) IS

BEGIN

INSERT INTO connection_log (event_user_name, event_type, event_date)

VALUES (user_name,'DISCONNECT',SYSDATE);

END disconnecting;

END user_connection;

/

尽管connection_log表有4个列，但是INSERT语句仅使用了两个。之所以会这样，是因为connection_­log_t1触发器自动指定connection_log_s1序列中的下一个值。从在本章的“行级触发器”一节可以找到connection_log_t1触发器的来源。

本节介绍如了如何构建系统触发器。 

 

 

10.8  触发器限制

关于如何在Oracle 11g中实现触发器有几个限制。虽然它们在不同的版本之间相当一致，但是Oracle 11g放松了一些变异表的限制。前面几节应介绍过一些仅应用于一种触发器的限制。

下面几小节将介绍其余的限制。

 

10.8.1  最大触发器尺寸

触发器主体不能大于32 760个字节，如本章开头的“触发器简介”一节所示。这个尺寸限制意味着应尽量保持触发器主体较小。可以通过将编码逻辑移到其他模式级组件中(比如函数、过程和包)来完成这一点，而不会丢失任何实用程序。将编码逻辑移出触发器主体外的优点是可以重用编码。也可以包装模式级对象，而不能包装触发器主体。附录F讨论了如何将PL/SQL代码包装起来防止偷窥。

 

10.8.2  SQL语句

非系统触发器主体不能包含DDL语句。它们也不能包含Data Control Language(DCL)或Transaction Control Language(TCL)命令，比如ROLLBACK、SAVEPOINT或COMMIT。当触发器在触发语句的作用域中运行时，这个规则适用于可以从非系统触发器主体中调用的模式级组件。

如果将一个触发器声明为自治的，则非系统触发器主体可以包含Data Control Language命令，因为它们没有更改事务作用域。它们在作用域外面起作用。在DECLARE块中添加下面的代码可以使触发器在触发语句的作用域外起作用：

PRAGMA AUTONOMOUS_TRANSACTION;

SQL语句存在的一个较大的问题是远程事务。如果从一个触发器主体中调用一个远程模式级函数或过程，则可能会遇到时间戳或签名不匹配的问题。不匹配会使触发器无效，并导致触发SQL语句失效。

 

10.8.3  LONG和LONG RAW数据类型

LONG和LONG RAW数据类型是遗留组件。不要努力更新它们，应当一有机会就迁移到LOB中。

不能用LONG或LONG RAW数据类型在触发器中声明局部变量。然而，当值可以转换成受约束的数据类型，比如CHAR或VARCHAR2时，可以插入到LONG或LONG RAW列中。最大长度是32 000字节。

当列被声明为LONG或LONG RAW数据类型时，行级触发器不能使用:new或:old伪记录或者数据的行。

 

10.8.4  变异表

变异表是一个当前正在改变的表。改变可以是因为INSERT、UPDATE或DELETE语句，或者由于DELETE CASCADE约束。

这种错误类型只会在行级触发器上发生。

当表在改变时，不能对表进行查询或修改。细想一下就会发现它是有意义的。如果触发器因为表上的改变而激发，那么直到结束之前都看不到这种改变。尽管可以访问new和old伪记录，但是不能读取表的状态。任何这么做的企图都会引发ORA-04091异常。

下面演示了变异错误的发生过程。按照如下代码所示创建一个mutant表：

CREATE TABLE mutant

( mutant_id NUMBER

, mutant_name VARCHAR2(20));

然后可以插入4个主要“忍者神龟”：

INSERT INTO mutant VALUES (mutant_s1.nextval,'Donatello');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Leonardo');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Michelangelo');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Raphael');

插入数据以后，可以构建下面的触发器：

CREATE OR REPLACE TRIGGER mutator

AFTER DELETE ON mutant

FOR EACH ROW

DECLARE

rows NUMBER;

BEGIN

SELECT COUNT(*) INTO rows FROM mutant;

dbms_output.put_line('[rows] has '||rows||']');

END;

/

触发器主体试图得到行数，但是它得不到行数，因为记录集没有结束。存在这个限制是为了防止触发器看到不一致的数据。

可以通过运行下面的命令删除变异表中的Michelangelo来激发触发器。DELETE语句为：

DELETE FROM MUTANT WHERE mutant_name = 'Michelangelo';

运行了该语句后，DELETE语句会抛出下面的错误堆栈：

DELETE FROM mutant WHERE mutant_name = 'Michelangelo'

ERROR at line 1:

ORA-04091: table PLSQL.MUTANT is mutating, trigger/function may not see it

ORA-06512: at "PLSQL.MUTATOR", line 4

ORA-04088: error during execution of trigger 'PLSQL.MUTATOR'

当触发器遇到变异表时，它会回滚触发器主体指令和触发语句。知道了为什么会生生变异表错误，就应当小心地避免这种错误。

 

10.8.5  系统触发器

系统触发器可以提出有趣的问题。大多数问题与事件属性函数强加的限制或约束有关。对于某些DDL事件，有些事件属性函数可能是不确定的。最好回过头来参考本章前面的“事件属性函数”一节来确切地了解事件属性函数的要求。

事件属性函数在Oracle的STANDARD包中声明和实现。当系统触发器编译失败后，可能会遇到创建对象的问题。这在CREATE事件激发触发器，而因为缺少对象依赖关系，触发器主体是无效时会出现。缺少的依赖关系使触发器失效，并将它标记为无效。当尝试创建缺少的对象时，CREATE事件触发器会引发ORA-04098错误，并禁止DDL语句。必须删除无效触发器，修复对象依赖关系，并重新编译触发器才能继续。

可以用本章10.3节中定义的audit_creation触发器来说明这个限制。如果删除了audit_creation表，则audit_creation触发器会变得无效。随后，当试图创建这个缺少的表时，会引发ORA-04098错误。直到删除这个触发器或者禁用它后才能继续。可通过运行下面的命令来禁用触发器：

ALTER TRIGGER audit_creations DISABLE;

现在可以创建表，当调用触发器时，应当重新对它进行有效性验证。如果触发器仍然无效，可以用下面的语法编译它：

ALTER TRIGGER audit_new_stuff COMPILE;

本节介绍了一些触发器限制。您应当深入了解某个触发器类型特有的具体限制。

 

 

 

10.9  小结

本章介绍了5种类型的数据库触发器，还解释了触发器及其体系结构。