第二章 觸發器
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 觸發器的新功能
建立作用於特定資料庫與資料定義事件的觸發器。事件包括：
