oracle 触发器全扫描

第二章 触发器
2.1 触发器的创建
CREATE TRIGGER [schema.]trigger_name
{BEFORE|AFTER} {UPDATE|INSERT|DELETE} ON [schema.]table_name
[ [REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)] ]
DECLARE
declaration
BEGIN
pl/sql code
END;
/
2.2 触发器的管理
1) 查看触发器：
SQL> desc ALL_TRIGGERS;
名称 空? 类型
-------------------- -------- ----------
owner VARCHAR2(30)
trigger_name VARCHAR2(30)
trigger_type VARCHAR2(16)
triggering_event VARCHAR2(216)
table_owner VARCHAR2(30)
base_object_type VARCHAR2(16)
table_name VARCHAR2(30)
column_name VARCHAR2(4000)
referencing_names VARCHAR2(128)
when_clause VARCHAR2(4000)
status VARCHAR2(8)
description VARCHAR2(4000)
action_type VARCHAR2(11)
trigger_body LONG
2) 查看触发器的代码：
触发器的源代码被存贮在 ALL_TRIGGERS 的 trigger_body 字段中。抽取触发器定义的命令：
SET ECHO OFF
SET MAXDATA 20000
SET LONG 20000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 0
SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET RECSEP OFF
ACCEPT trigger_name CHAR PROMPT 'please input the trigger to lookup:'
ACCEPT trigger_owner CHAR PROMPT 'please input the trigger owner:'
ACCEPT file_name CHAR PROMPT 'please enter the output file path:'
SET TERMOUT OFF
SET FEEDBACK OFF
-- 设置when_clause字段格式并使之折行
COLUMN when_clause FORMAT A60 WORD_WRAPPED
SPOOL &file_name
SELECT 'CREATE OR REPLACE TRIGGER ' || description FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner');
SELECT 'WHEN (' || when_clause || ')' when_caluse FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner')
AND when_clause IS NOT NULL;
SELECT trigger_body FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner');
SELECT '/' FROM DUAL;
SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON
SET HEADING ON
SET PAGESIZE 24
3) 打开和关闭触发器：
-- 打开触发器
ALTER TRIGGER trigger_name DISABLE;
-- 关闭触发器
ALTER TRIGGER trigger_name ENABLE;
2.3 触发器的新功能
创建作用于特定数据库与数据定义事件的触发器。事件包括：
?? 关闭数据库(startup)；
?? 启动数据库(shutdown)；
?? 登陆数据库(logon)；
?? 退出数据库(logoff)；
?? 发生服务器错误(server error)；
?? 创建对象(create命令)；
?? 修改对象(alter命令)；
?? 删除对象(delete命令)；
1) 数据库事件触发器：
数据库事件触发器可以定义为数据库级和模式级。
要创建数据库级的触发器，必须有administrator database trigger 的系统权限，该权限提供
给DBA角色，通常只有数据库管理员有此权限。
事 件 触发器关键词 模 式 级 数据库级
启动数据库 STARTUP 否 是
关闭数据库 SHUTDOWN 否 是
服务器错误 SERVERERROR 否 是
登陆数据库 LOGON 是 是
退出数据库 LOGOFF 是 是
创建对象 CREATE 是 否
修改对象 ALTER 是 否
删除对象 DELETE 是 否
2) 事件属性：
编写触发器时，可以象变量一样使用这些属性。
事件属性 描 述 适应的触发器类型
sys.sysevent 返回一个20个字符的字符串，描述导致触发器
激发的事件 所有类型
sys.intance_num 返回Oracle 实例编号 所有类型
sys.database_name 返回数据库品牌，通常为"Oracle" 所有类型
sys.server_error
(stack_position)
从错误堆栈的指定位置返回错误号，使用
sys.server_error(1) 查找最近的错误 SERVERERROR
sys.login_user 返回导致触发器激发的用户名 所有类型
sys.dictionary_obj_
type
返回当DDL触发器激发时涉及到的对象类型 CREATE、ALTER、DROP
sys.dictionary_obj_
name
返回当DDL触发器激发时涉及到的对象名称 CREATE、ALTER、DROP
sys.des_encrypted_p
assword
创建或修改用户时，返回加密后的该用户的密
码 CREATE、ALTER、DROP
3) 创建数据库事件触发器实例：
CREATE TABLE uptime_log(
database_name VARCHAR2(30),
event_name VARCHAR2(30),
event_time DATE,
triggered_by_user VARCHAR2(30));
-- 创建启动数据库时的事件触发器。
CREATE OR REPLACE TRIGGER log_startup
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO uptime_log VALUES(sys.database_name,
sys.sysevent, sysdate, sys.login_user);
END;
/
-- 创建关闭数据库时的事件触发器。
CREATE OR REPLACE TRIGGER log_shutdown
AFTER SHUTDOWN ON DATABASE
BEGIN
INSERT INTO uptime_log VALUES(sys.database_name,
sys.sysevent, sysdate, sys.login_user);
END;
/
4) 创建DDL事件触发器实例：
CREATE TABLE alter_audit_trail(
object_owner VARCHAR2(30),
object_name VARCHAR2(30),
object_type VARCHAR2(30),
alter_by_user VARCHAR2(30),
sys_event VARCHAR2(30),
alteration_time DATE);
-- 当SCOTT用户创建、修改或删除时的模式触发器。
CREATE OR REPLACE TRIGGER tri_alter_audit_trail
AFTER ALTER OR CREATE OR DROP ON scott.SCHEMA
BEGIN
IF sys.sysevent = 'ALTER' THEN
INSERT INTO alter_audit_trail VALUES
(sys.dictionary_obj_owner, sys.dictionary_obj_name,
sys.dictionary_obj_type, sys.login_user, 'my_alter', sysdate);
ELSIF sys.sysevent = 'CREATE' THEN
NULL;
ELSIF sys.sysevent = 'DROP' THEN
NULL;
END IF;
END;
/
2.4 替代触发器
语法同建立表触发器，使用关键词INTEAD_OF代替BEFORE或AFTER。
替代触发器解决了不能修改视图的问题，它只能在视图上创建，执行一个PL/SQL代码块，而不是一条DML语句
(如INSERT、UPDATE)。
编写替代触发器时，主要是编写更新视图底层的表的代码。同时也可以针对视图中的某个字段或所有字段。语
法：
CREATE OR REPLACE TRIGGER tri_emp_view
INSTEAD OF UPDATE ON emp_view
BEGIN
PL/SQL code;
END;
2.5 触发器的局限性
1. 触发器无法完成的工作：
1) 查询或修改变化的表；
2) 执行数据定义语言(CREATE、DROP、ALTER)的语句；
3) 执行COMMIT、ROLLBACK、SAVEPOINT 语句；
注：变化的表是指执行触发器时正在被修改的表。
触发器中不能执行COMMIT、ROLLBACK、SAVEPOINT 语句，以确保对事务控制的有效性及数据的完整性。
2. 如果使用触发器来实现商务规则或引用完整性规则，不会影响在触发器创建之前的记录。创建声明性约束实
际上是指出数据必须为真，如果出现了违反了约束的数据，则不能建立约束。
3. 不能对特定模式对象(例如：表)来定义DDL触发器。
 来自Oracle PL/SQL高级编程 作者对Oracle开发管理有多年的经验，并在Oracle 数据库的基础上开发了自己的交易控制中间件，适用于
融、电信、交通等多个行业，现就主要开发资料参考资料共享给大家。
祝大家在Oracle平台上更上一层楼，共同进步。
[e-mail] 2006-2-14