oracle 11g PL/SQL Programming学习七

isu2558mue

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

-----------------PL/SQL学习笔记系列 By Cryking-----------------

------------------------转载请注明出处,谢谢!------------------------
　　
　　第十章 触发器(Triggers)

数据库触发器是一种特殊的存储程序。它们不能直接调用,而是由数据库里的事件触发来执行.

它运行在你执行SQL命令的时候和你执行数据库系统管理操作的时候.

触发器定义有着DDL相似的规则.触发器可以调用SQL语句、PL/SQL函数或存储.你可以在PL/SQL或JAVA里来实现触发器.

你可以通过触发器来做以下的事情:

1.控制DDL语句的行为,如改变、创建、重命名对象

2.控制DML语句的行为,如插入、更新、删除

3.执行参照完整性,实现复杂的商业规则、执行安全策略

4.控制视图的DML操作

5.审计系统访问信息并创建事务日志.

但是你不能控制同时触发时触发器执行事件的顺序.所以你不能过多的依赖于触发器.
oracle 11g提供了复合触发器来帮你管理大事件,像那些你需要顺序触发的事件.
触发器存在级联触发的风险.11g以及早前版本都限制级联触发器的数量为32,超过32个将抛出异常.
五种触发器及其使用:

1.DDL触发器(Data Definition Language triggers)

当你创建、更改、移除数据库对象时触发.它们常用于控制或监视DDL语句.

譬如可以建立CREATE TABLE的触发器,来确保所创建的表满足开发标注,如包含存储或分区子句等.

2.DML触发器(Data Manipulation Language triggers)

当你向表插入数据、更新数据、删除数据时触发.你可以一次触发表上所有的更改,或者每次触发一行(行级触发).

DML触发器用来控制DML语句.你可以使用它在发生改变前来审计、检查、保存、替换值.常用行级DML触发器来生成

主键编号.

3.组合触发器(Compound triggers)

当你插入、更新、删除表数据时,它就像是一个语句级触发和行级触发器结合的触发器.它让你在4个时间点捕获信息:

a,在语句触发前;b,在每行改变发生前;c,在每行发生改变后;d,在语句触发后.你可以使用它在发生改变前来审计、检查、保存、替换值

4.INSTEAD OF触发器(Instead-of triggers)

它使你停止执行一个DML语句,然后重定向这个DML语句.通常用来管理如何写入不能进行更新的视图.

5.系统或数据库事件触发器(System or database event triggers)

当一个系统行为发生时触发,如登入或登出数据库.通常用来审计系统访问信息.可以让你跟踪系统事件,并映射到用户.
　　使用触发器要注意:
触发器本身不能超过32760个字节.这是因为触发器本身是存储在LONG类型的列中的.当你的触发器体比较大的时候,你可以使用函数/存储或者包来存放逻辑处理的代码,这样可以大大减少触发器本身的大小.还有一个原因是触发器不能进行加密,而函数/存储可以加密.
使用触发器需要的权限:

你需要有CREATE TRIGGER的权限来创建触发器.如果你在其他用户的对象上创建触发器,你需要这个用户赋予给你更改该对象的

权限(ALTER对象权限).可选的,该用户可以赋予你ALTER ANY TABLE和CREATE ANY TRIGGER权限.


数据库触发器结构

数据库触发器像包一样定义在数据库.它由触发器声明部分和触发器主体部分组成.

声明部分包含了触发器怎么被触发(触发器类型)以及何时触发.你不能直接调用触发器.它们只能被触发器事件触发(调用).

触发器实现了一个观察者模式,即它们在监听事件,并可以在事件发生后采取相应的行动.

触发器通常包含4部分:触发器名,一个语句、一个限制、一个行动.前3个在声明部分,最后一个在触发器体.触发器名在触发器中

应当是唯一的,但可以和别的类型的对象重名.触发器限制通常在WHEN子句或INSTEAD OF子句中.

触发器事件直接与触发器传递信息.你不能看到这个交流如何发生.你没有数据除了系统预定义的一些事件属性.

你可以在DML行级触发器和INSTEAD OF触发器中通过new和old伪列来访问数据.

DML行级触发器和INSTEAD OF触发器与语句级触发器不同,当一个事件触发该类型的触发器时,触发器声明生成一个运行时程序单元.

在这个过程中,这个程序单元才是真正的"触发器".这个触发器创造有效的new和old伪列结构来与DML语句进行交流.

触发器体可以通过绑定变量方式访问这些伪列结构.

注意:触发器会降低应用访问接口的性能,特别是行级触发器.
　　DDL触发器触发事件汇总

事件 描述

ALTER 更改对象属性,像约束、对象名、存储、结构等

ANALYZE 为优化器计算成本而做的对象分析统计

ASSOCIATE STATISTICS 关联统计信息链接到一个统计类型到列,函数、包、类型、域索引、索引类型

AUDIT 对某个对象或整个系统启用审计

COMMENT 给列或表加的注释信息

CREATE 在数据库创建对象,像对象、权限、角色、表、用户、视图等

DDL 任何主数据定义事件

DISASSOCIATE 撤销关联统计信息链接到一个统计类型到列,函数、包、类型、域索引、索引类型

STATISTICS

DROP 在数据库中删除对象

GRANT 给用户、角色赋予某种权限

NOAUDIT 对某个对象或整个系统撤销审计

RENAME 重命名数据库对象,如列、约束‘对象、权限、角色、同义词、表、用户、视图等.

REVOKE 在用户、角色撤销某种权限

TRUNCATE 截断表(重新设置高水位线,不能ROLLBACK)
　　系统预定义触发函数汇总

ORA_CLIENT_IP_ADDRESS

功能:以VARCHAR2类型返回客户端IP地址

注:ORA_CLIENT_IP_ADDRESS只在系统触发器中有效,DBMS_OUTPUT.PUT_LINE()在系统触发器中无效
　　ORA_DATABASE_NAME

功能:以VARCHAR2类型返回数据库名


12:38:37 SYS@orcl> select ORA_DATABASE_NAME from dual;
ORA_DATABASE_NAME
--------------------------------------------------------
ORCL
已选择 1 行。

　　

ORA_DES_ENCRYPTED_PASSWORD

功能:以VARCHAR2类型返回DES加密的密码,在11g中它和SYS.USER$表的PASSWORD字段一致,11g密码不再保存在DBA_USERS、ALL_USERS视图中
　　ORA_DICT_OBJ_NAME

功能:以VARCHAR2类型返回事件操作的对象
　　ORA_DICT_OBJ_NAME_LIST

功能:该函数带有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);，该参数返回统计信息分析的对象名列表

整个函数返回统计分析的对象个数。

ORA_DICT_OBJ_NAME_LIST的定义：

CREATE PUBLIC SYNONYM ORA_DICT_OBJ_NAME_LIST FOR SYS.DICTIONARY_OBJ_NAME_LIST

/
　　CREATE OR REPLACE FUNCTION SYS.DICTIONARY_OBJ_NAME_LIST

(object_list out ora_name_list_t)

return binary_integer is

begin

return dbms_standard.dictionary_obj_name_list(object_list);

end;

/

示例:

CREATE OR REPLACE TRIGGER tri_ddl_test02   
AFTER ddl ON DATABASE  
DECLARE  
nl ora_name_list_t;   
i          PLS_INTEGER;   
BEGIN  
IF (ora_sysevent = 'ASSOCIATE STATISTICS' or  
ora_sysevent = 'DISASSOCIATE STATISTICS')   
THEN  
i := ORA_DICT_OBJ_NAME_LIST(nl);   
FOR j IN 1 .. i   
LOOP   
dbms_output.put_line(
ora_sysevent || ' : ORA_DICT_OBJ_NAME_LIST--' || nl(j));   
END LOOP;   
END IF;   
END;  
/
21:21:09 SCOTT@orcl> create or replace function GET_now
21:21:22   2  return date
21:21:28   3  as
21:21:29   4  begin
21:21:30   5    return sysdate;
21:21:31   6  end;
21:21:32   7  /
Elapsed: 00:00:00.04
21:21:33 SCOTT@orcl> ASSOCIATE STATISTICS WITH FUNCTIONS GET_now DEFAULT SELECTIVITY 100;
ASSOCIATE STATISTICS : ORA_DICT_OBJ_NAME_LIST--GET_NOW
Elapsed: 00:00:00.12
21:21:50 SCOTT@orcl> DISASSOCIATE STATISTICS FROM functions GET_now;
DISASSOCIATE STATISTICS : ORA_DICT_OBJ_NAME_LIST--GET_NOW
Elapsed: 00:00:00.06

　　

ORA_DICT_OBJ_OWNER

功能:以VARCHAR2类型返回事件操作的对象的拥有者
　　ORA_DICT_OBJ_OWNER_LIST

功能:该函数带有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);，该参数返回统计信息分析的对象拥有者列表

整个函数返回统计分析的拥有者数。

示例:


--修改上面的触发器为
CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
DECLARE
nl ora_name_list_t;
i  PLS_INTEGER;
j  PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'ASSOCIATE STATISTICS' or
ora_sysevent = 'DISASSOCIATE STATISTICS') THEN
i := ORA_DICT_OBJ_NAME_LIST(nl);
FOR x IN 1 .. i LOOP
dbms_output.put_line(ora_sysevent || ' : ORA_DICT_OBJ_NAME_LIST--' || nl(x));
END LOOP;
j := ORA_DICT_OBJ_OWNER_LIST(nl);
FOR x IN 1 .. j LOOP
dbms_output.put_line(ora_sysevent || ' : ORA_DICT_OBJ_OWNER_LIST--' || nl(x));
END LOOP;
END IF;
END;
21:24:26 SCOTT@orcl> ASSOCIATE STATISTICS WITH FUNCTIONS GET_now DEFAULT SELECTIVITY 100;
ASSOCIATE STATISTICS : ORA_DICT_OBJ_NAME_LIST--GET_NOW
ASSOCIATE STATISTICS : ORA_DICT_OBJ_OWNER_LIST--SCOTT
Elapsed: 00:00:00.03

　　


ORA_DICT_OBJ_TYPE

功能:以VARCHAR2类型返回事件改变的字典对象的数据类型
　　ORA_GRANTEE

功能:该函数有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);，该参数返回赋权限事件影响的用户列表

整个函数返回被赋权限或角色的用户数。

示例:


CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
DECLARE
nl ora_name_list_t;
i  PLS_INTEGER;
BEGIN
i := ORA_GRANTEE(nl);
if i>=1 then
FOR x IN 1 .. i LOOP
dbms_output.put_line(ora_sysevent || ' : ORA_GRANTEE--' ||nl(x));
END LOOP;
end if;
END;
21:42:44 SCOTT@orcl> grant select on sc_01 to cry;
GRANT : ORA_GRANTEE--CRY
Grant succeeded.
Elapsed: 00:00:00.11
21:42:46 SCOTT@orcl> revoke select on sc_01 from cry;
Revoke succeeded.
Elapsed: 00:00:00.11

　　


ORA_INSTANCE_NUM
　　

功能:以NUMBER类型返当前数据库的实例数

如:

21:43:15 SCOTT@orcl> SELECT ORA_INSTANCE_NUM FROM DUAL;

ORA_INSTANCE_NUM
----------------
1
1 row selected.

　　
　　ORA_IS_ALTER_COLUMN

功能:判断列名是否被更改,更改了返回TRUE,没更改返回FALSE

示例:


create or replace trigger tri_ddl_test02
after ddl ON DATABASE
DECLARE
TYPE column_list IS TABLE OF VARCHAR2(32);
v_col column_list := column_list('ID', 'NA');--列名
BEGIN
IF ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE' THEN
FOR i IN 1 .. v_col.COUNT loop
IF ora_is_alter_column(v_col(i)) THEN
dbms_output.put_line(ora_dict_obj_name || '.' || v_col(i) ||' 改变了.');
END IF;
END LOOP;
END IF;
END;
00:37:07 SCOTT@orcl> create table sc_01 as select 1 id from dual;
Table created.
Elapsed: 00:00:00.06
00:38:46 SCOTT@orcl> alter table sc_01 add na varchar2(10);
Table altered.
Elapsed: 00:00:00.01
00:38:55 SCOTT@orcl> alter table sc_01 modify na varchar2(20);
SC_01.NA 改变了.
Table altered.
Elapsed: 00:00:00.03

　　

ORA_IS_CREATING_NESTED_TABLE

功能:当创建的表带嵌套表时,返回TRUE,否则返回FALSE

示例:

create or replace trigger tri_ddl_test02
after ddl ON DATABASE
BEGIN
IF ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND
ora_is_creating_nested_table THEN
dbms_output.put_line(ora_dict_obj_name || '.' ||' created with nested table.');
END IF;
END;
/
00:54:56 SCOTT@orcl> create table addre
00:55:19   2  (address_id  integer,
00:55:19   3  street_address  address_table,
00:55:19   4  city  varchar2(20))
00:55:19   5  nested table street_address store as cc;
CC. created with nested table.
Table created.
Elapsed: 00:00:00.47

　　

ORA_IS_DROP_COLUMN

功能:判断列是否被删除,删除了返回TRUE,没删除返回FALSE

示例:

create or replace trigger tri_ddl_test02
AFTER ddl ON DATABASE  
DECLARE
TYPE column_list IS TABLE OF VARCHAR2(32);
v_col column_list := column_list('ID', 'NA');--列名
BEGIN
FOR i IN 1 .. v_col.COUNT loop
IF ORA_IS_DROP_COLUMN(v_col(i)) THEN
dbms_output.put_line(ora_dict_obj_name || '.' || v_col(i) ||' 删除了.');
END IF;
END LOOP;
END;
/
01:06:02 SCOTT@orcl> create table sc_01 as select 1 id,'A' NA from dual;
Table created.
Elapsed: 00:00:00.12
01:06:13 SCOTT@orcl> alter table sc_01 drop column na;
SC_01.NA 删除了.
Table altered.
Elapsed: 00:00:00.15

　　

ORA_IS_SERVERERROR

功能:判断是否发生指定错误(传入错误号),当发生了指定错误号的错误时返回TRUE,否则返回FALSE
　　ORA_LOGIN_USER

功能:以VARCHAR2类型返回当前的模式名

如:

01:36:05 SYS@cry> select ORA_LOGIN_USER from dual;
ORA_LOGIN_USER
---------------------
SYS
Elapsed: 00:00:00.03

　　


ORA_PARTITION_POS

只能用在INSTEAD OF CREATE触发器中 (不要在SYSTEM模式中使用此触发器)

功能:该函数返回带SQL文本的数值位置,表示插入分区子句的地方

如:

09:54:44 SCOTT@orcl> CREATE OR REPLACE TRIGGER tri_insteadof
09:56:43   2    INSTEAD OF CREATE ON SCHEMA
09:56:43   3  BEGIN
09:56:43   4    dbms_output.put_line('位置:' || ORA_PARTITION_POS);
09:56:43   5  END;
09:56:44   6  /
触发器已创建
已用时间:  00: 00: 00.01
09:59:54 SCOTT@orcl> create table s_01(id number);
位置:28
表已创建。
已用时间:  00: 00: 00.01
10:00:04 SCOTT@orcl> create table s_01(id number)
10:00:36   2  partition by range(id)(
10:00:37   3  partition t_p1 values less than (20),
10:00:37   4  partition t_p2 values less than (40)
10:00:38   5  );
位置:0
表已创建。
已用时间:  00: 00: 00.01

　　

ORA_PRIVILEGE_LIST

功能:该函数有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);，该参数返回事件中授予或者回收的权限数量。

如:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
DECLARE
nl ora_name_list_t;
i  PLS_INTEGER;
BEGIN
i := ora_privilege_list(nl);
dbms_output.put_line('COUNT:' || I);
IF i >= 1 THEN
FOR x IN 1 .. i
LOOP
dbms_output.put_line(ora_sysevent || ' : ora_privilege_list--' ||nl(x));
END LOOP;
END IF;
END;
12:35:12 SYS@orcl> grant insert,update on t_test to scott;
COUNT:2
GRANT : ora_privilege_list--INSERT
GRANT : ora_privilege_list--UPDATE
授权成功。
已用时间:  00: 00: 00.01
14:44:48 SYS@orcl> revoke insert,update on t_test from scott;
COUNT:2
REVOKE : ora_privilege_list--INSERT
REVOKE : ora_privilege_list--UPDATE
撤销成功。
已用时间:  00: 00: 00.01

　　

ORA_REVOKEE

功能:该函数有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);

该参数返回事件中回收权限的用户数量。

如:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
DECLARE
nl ora_name_list_t;
i  PLS_INTEGER;
BEGIN
i := ORA_REVOKEE(nl);
dbms_output.put_line('COUNT:' || I);
IF i >= 1 THEN
FOR x IN 1 .. i
LOOP
dbms_output.put_line(ora_sysevent || ' : ORA_REVOKEE--' || nl(x));
END LOOP;
END IF;
END;
14:50:32 SYS@orcl> grant insert,update on t_test to scott;
COUNT:
授权成功。
已用时间:  00: 00: 00.01
14:51:34 SYS@orcl> revoke insert,update on t_test from scott;
COUNT:1
REVOKE : ORA_REVOKEE--SCOTT
撤销成功。
已用时间:  00: 00: 00.01

　　


ORA_SERVER_ERROR

功能:根据指定的错误堆栈位置(1为错误堆栈的顶部),返回错误号
　　ORA_SERVER_ERROR_DEPTH

功能:该函数返回错误堆栈上的错误号,没有参数
　　ORA_SERVER_ERROR_MSG

功能:根据指定的错误堆栈位置(1为错误堆栈的顶部),返回错误信息
　　ORA_SERVER_ERROR_NUM_PARAMS

功能:返回错误堆栈中错误位置被替换为错误消息的所有替代符数量.如:(“Expected %s, found %s.”)

它有一个参数,返回类型为PLS_INTEGER
　　ORA_SERVER_ERROR_PARAM

功能:返回在错误堆栈中特定错误位置特定参数号所对应的字符串替代值

它有两个输入参数.

示例:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER SERVERERROR ON DATABASE
DECLARE
param VARCHAR2(32);
BEGIN
dbms_output.put_line('ora_server_error_depth:' || ora_server_error_depth);
dbms_output.put_line('ora_server_error_num_params:' ||ora_server_error_depth);
param := ora_server_error_param(2,3);
dbms_output.put_line('ora_server_error_param:' || param);
END;
15:21:44 SYS@orcl> BEGIN
15:24:56   2    raise_application_error(-20001, '异常测试001');
15:24:56   3  EXCEPTION
15:24:56   4    WHEN OTHERS THEN
15:24:56   5      BEGIN
15:24:56   6        SELECT COUNT(1) FROM CCCC;
15:24:56   7      EXCEPTION
15:24:56   8        WHEN OTHERS THEN
15:24:56   9          raise_application_error(-20002, '异常测试002');
15:24:56  10      END;
15:24:56  11  END;
15:24:57  12  /
ora_server_error_depth:2
ora_server_error_num_params:2
ora_server_error_param:PL/SQL: SQL Statement ignored
SELECT COUNT(1) FROM CCCC;
*
第 6 行出现错误:
ORA-06550: 第 6 行, 第 28 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 6 行, 第 7 列:
PL/SQL: SQL Statement ignored

已用时间:  00: 00: 00.01

　　

ORA_SQL_TXT

功能:该函数有一个OUT模式的VARCHAR2参数,该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64);

该参数返回触发事件的sql语句。

如:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
DECLARE
V_COUNT  NUMBER;
sql_text ora_name_list_t;
BEGIN
v_count := ORA_SQL_TXT(sql_text);
IF v_count > 0 THEN
FOR i IN 1 .. v_count
LOOP
dbms_output.put_line(ora_sysevent || ' : ora_sql_text--' ||
sql_text(i));
END LOOP;
END IF;
END;
/
15:37:15 SYS@orcl> create table t02 as select 1 ID from dual;
CREATE : ora_sql_text--create table t02 as select 1 ID from dual
表已创建。
已用时间:  00: 00: 00.04

　　

ORA_SYSEVENT

功能:以VARCHAR2类型返回触发的事件,如:CREATE事件、REVOKE事件等
　　ORA_WITH_GRANT_OPTION

功能:以BOOLEAN类型返回赋权限事件中是否带grant option选项。

示例:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER ddl ON DATABASE
BEGIN
IF ora_with_grant_option THEN
dbms_output.put_line('USE:ora_with_grant_option');
END IF;
END;
15:37:21 SYS@orcl> grant insert,update on t_test to scott with grant option;
USE:ora_with_grant_option
授权成功。
已用时间:  00: 00: 00.03

　　

SPACE_ERROR_INFO

该函数有6个参数,原型为:

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)

当触发事件与空间不足的条件相关时返回真,否则为假.当函数返回为FALSE时,6个参数返回为NULL.

示例:

CREATE OR REPLACE TRIGGER tri_ddl_test02
AFTER SERVERERROR ON DATABASE
DECLARE
error_number    VARCHAR2(30);--原书为NUMBER类型,是错误的,需要改为VARCHAR2类型
error_type      VARCHAR2(30);
object_owner    VARCHAR2(30);
tablespace_name VARCHAR2(30);
object_name     VARCHAR2(128);
subobject_name  VARCHAR2(30);
V_B             BOOLEAN;
BEGIN
V_B := space_error_info(error_number
,error_type
,object_owner
,tablespace_name
,object_name
,subobject_name);
DBMS_OUTPUT.PUT_LINE('V_B:' || CASE WHEN V_B THEN 'TRUE' ELSE 'FALSE' END);
DBMS_OUTPUT.PUT_LINE('error_number:' || error_number);
DBMS_OUTPUT.PUT_LINE('error_type:' || error_type);
DBMS_OUTPUT.PUT_LINE('object_owner:' || object_owner);
DBMS_OUTPUT.PUT_LINE('tablespace_name:' || tablespace_name);
DBMS_OUTPUT.PUT_LINE('object_name:' || object_name);
DBMS_OUTPUT.PUT_LINE('subobject_name:' || subobject_name);
END;
/
16:39:14 SYS@orcl> CREATE TABLE T_BIG(ID NUMBER) tablespace cry_data storage(initial 25000M);
V_B:TRUE
error_number:NO MORE SPACE
error_type:
object_owner:
tablespace_name:CRY_DATA
object_name:
subobject_name:
CREATE TABLE T_BIG (ID NUMBER) tablespace cry_data storage(initial 25000M)
*
第 1 行出现错误:
ORA-01659: 无法分配超出 31 的 MINEXTENTS (在表空间 CRY_DATA 中)

已用时间:  00: 00: 00.40

　　

-----
　　如有问题,欢迎交流！