1 数据定义语言触发器
通常，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命令恢复。可以用这种新的闪回技术来取消修改
2 事件属性函数
下面是系统定义的事件属性函数列表：
● 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
{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA}
[WHEN (logical_expression)]
[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;
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子句和触发器主体中使用新、老伪记录。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
