[size=large][b]1.触发器的创建[/b][/size]
[quote]
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;
/
[/quote]
[size=large][b]2.触发器的管理[/b][/size]
1) 查看触发器:
[quote]
SQL> desc ALL_TRIGGERS;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
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
[/quote]
2)查看触发器的代码:
触发器的源代码被存贮在 ALL_TRIGGERS 的 trigger_body 字段中。抽取触发器定义的命令:
3) 打开和关闭触发器:
[size=large][b]3触发器的新功能[/b][/size]
创建作用于特定数据库与数据定义事件的触发器。事件包括:
[quote]
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;
/
[/quote]
[size=large][b]2.触发器的管理[/b][/size]
1) 查看触发器:
[quote]
SQL> desc ALL_TRIGGERS;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
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
[/quote]
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;
[size=large][b]3触发器的新功能[/b][/size]
创建作用于特定数据库与数据定义事件的触发器。事件包括: