Oracle Database 11g PLSQL程序设计 101 触发器简介

Oracle Database 11g PLSQL程序设计 101 触发器简介 数据库触发器是特殊的存储程序。通常不直接调用它们，而要由数据库中的事件触发。它们在执行命令时和执行数据库管理系统动作之间运行。触发器可以用PL/SQL或Java编写。触发器可以捕获创建、修改或删除对象的事件，也可以捕获表或视图中的插入、更新或删除操作。它们还可以监控数据库或模式的状态变化及用户动作的变化。 本章包括如下主题: ? 触发器简介 ? 触发器体系结构 ? 数据定义语言触发器 ? 数据操作语言触发器 ? 复合触发器 ? instead-of触发器 ? 系统或数据库事件触发器 ? 触发器限制 这些部分首先介绍基础知识，然后提出一些思想。如果想马上开始编写具体类型的触发器，也可以将它们作为快速参考。例如，可以翻到本章10.4节了解如何编写插入、更新和删除的触发器。 10.1 触发器简介 数据库触发器是特殊的存储程序。因此，它们的定义与DDL规则非常相似。同样，触发器可以调用SQL语句和PL/SQL函数与过程。可以选择用PL/SQL或Java实现触发器。可以参见第14章和附录D了解编写Java库支持触发器的语法。 数据库触发器不同于存储函数和过程，因为不能直接调用它们。当数据库中发生触发事件时会激发数据库触发器。所以它们是管理数据库的有力工具。可以通过触发器来限制或重定向程序的行为。 触发器的用途有: ? 控制DDL语句的行为，如通过更改、创建或重命名对象 ? 控制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没有提供同步触发器的方式，即不能确定何时激发第一个、第二个或最后一个触发器。这一限制是因为触发器是交叉存取的，也就是说程序单元是作为离散的进程彼此无关地工作的。 触发器可能使应用程序接口变慢，尤其是行级语句。应当注意何时及在何处部署 触发器来解决问题。 -1 触发器体系结构 图10 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中选择了以区分大小写的 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 保存所有表，就需要匹配目录的大小写。该示例用了一个区分大小写的字符串作为 实参: 用法如下: 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包含触发事件授予的权限或角色的列表。