oracle inside(2)

第二章 触发器
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;
名称 空? 类型
-------------------- -------- ----------
PL/SQL 高级编程
第 8 页 共 8 页
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
PL/SQL 高级编程
第 9 页 共 9 页
SET VERIFY ON
SET HEADING ON
SET PAGESIZE 24
3) 打开和关闭触发器：
-- 打开触发器
ALTER TRIGGER trigger_name DISABLE;
-- 关闭触发器
ALTER TRIGGER trigger_name ENABLE;
2.3 触发器的新功能
创建作用于特定数据库与数据定义事件的触发器。事件包括：