替代觸發器
?
??? 替代觸發器的意義在于：如果觸發了觸發器，則原先的DML操作不再進行，而是執行觸發器中的操作。
?
??? 替代觸發器最常見的應用是在最view的操作時。由于不能對view直接進行操作，所以可以用替代觸發器來替代。
?
??? 1、創建一個簡單的視圖：
?
??? create
view
v_test
as
????
?
select
t11.a a1,t11.b b1,t22.a a2,t22.b b2
???????
from
t11 ,t22
????
?
where
t11.a = t22.a;
?
??? 2、創建替代觸發器
?
??? create
or
replace
trigger
view_trigger
????
?
instead
of?--等同于after
????
?
update
????
?
on
v_test
????
?
for
each
row
??? begin
????
?
update
t11
set
b=:new.b1
where
a=:new.a1;
??? end
;
?
??? 注：具體邏輯不是很正確，只為舉例方便。
?
??? 3、不能修改的view類別：
?
??? * 含有表連接
??? * 有集合運算符(union、minus、intersect等)
??? * 含有集合函數(sum、count等)
??? * group by connect by start with字句
??? * 包含distinct運算符
?
?
系統事件觸發器
?
??? 1、系統事件觸發器包括：
?
??? * 數據庫的啟動(STARTUP)
??? * 數據庫的關閉(SHUTDOWN)
??? * 數據庫服務器出錯(SERVERERROR)
?
??? 注：STARTUP和SERVERERROR只能使用BEFORE類型，只有SHUTDOWN才能用AFTER
?
??? 2、創建語法：
?
??? CREATE OR REPALCE TRIGGER trigger_name
??? { BEFORE | AFTER }
??? { DATABASE _EVENT_LIST }
??? ON [ DATABASE | SCHEMA ]
??? trigger body;
?
??? 注：SERVERERROR可以與SCHEMA關聯，表示在該模式上發生錯誤時才觸發。
?
??? 3、舉例：
?
??? create
or
replace
teigger dbstart_trigger
????
?
after
startup
????
?
on
database
??? begin
????
?
insert
into
t1_log
values
(
sysdate
);
??? end
;
?
?
用戶事件觸發器
?
??? 1、包括以下幾類：
?
??? * CREATE
??? * ALTER
??? * DROP
??? * ANALYZE
??? * ASSOCIATE STATISTICS
??? * DISASSOCIATE STATISTICS
??? * AUDIT
??? * NOTAUDIT
??? * COMMENT
??? * GRANT
??? * REVOKE
??? * RENAME
??? * TRUNCATE
??? * LOGON? --只能用AFTER
??? * LOGOFF --只能用BEFORE
?
??? 2、舉例：
?
??? create
or
replace
teigger logon_trigger
????
?
after
logon
????
?
on
schema??--若用on database則任何登陸均會觸發
??? begin
????
?
insert
into
t1_log
values
(user,
sysdate
);
??? end
;
?
?
ALTER語句
?
??? 1、重新編譯
?
??? ALTER TRIGGER [schema.] trigger_name COMPILE;
?
??? 作用：如果觸發器調用了函數或過程，當函數或過程被刪除或修改后，觸發器被標記為INVALID，必須重新編譯。
?
??? 2、啟用/禁用觸發器
?
??? ALTER TRIGGER [schema.] trigger_name DISABLE | ENABLE;
?
??? 作用：DBA要插入大量數據時，確定數據的正確性，禁用觸發器后可節省大量時間。
?
??? 3、啟用/禁用某個表的所有觸發器
?
??? ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS; --注意S
?
?
觸發器數據字典
?
?
??? 使用USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS查看觸發器信息
?
SQL
>
desc
user_triggers
?
Name
?????????????
Type
?????????? Nullable
Default
Comments??????????????? ????????????????????????????????????????????????????
----------------- -------------- -------- ------- ---------------------------------------------------------------------------
TRIGGER_NAME?????
VARCHAR2
(
30
)?? Y???????????????
Name
of
the
trigger
????????????????????????????????????????????????????????
TRIGGER_TYPE?????
VARCHAR2
(
16
)?? Y???????????????
Type
of
the
trigger
(
when
it fires) -
BEFORE
/
AFTER
and
STATEMENT
/
ROW
???????
TRIGGERING_EVENT?
VARCHAR2
(
227
)? Y???????????????
Statement
that will fire
the
trigger
-
INSERT
,
UPDATE
and
/
or
DELETE
????????
TABLE_OWNER??????
VARCHAR2
(
30
)?? Y??????????????? Owner
of
the
table
that this
trigger
is
associated
with
????????????????????
BASE_OBJECT_TYPE?
VARCHAR2
(
16
)?? Y?????????????????????????????????????? ?????????????????????????????????????????????????????
TABLE_NAME???????
VARCHAR2
(
30
)?? Y???????????????
Name
of
the
table
that this
trigger
is
associated
with
?????????????????????
COLUMN_NAME??????
VARCHAR2
(
4000
) Y???????????????
The
name
of
the
column
on
which
the
trigger
is
defined over????????????????
REFERENCING_NAMES
VARCHAR2
(
128
)? Y??????????????? Names used
for
referencing
to
OLD
,
NEW
and
PARENT
values
within
the
trigger
WHEN_CLAUSE??????
VARCHAR2
(
4000
) Y???????????????
WHEN
clause must evaluate
to
true
in
order
for
triggering
body
to
execute
??
STATUS???????????
VARCHAR2
(
8
)??? Y???????????????
If
DISABLED
then
trigger
will
not
fire?????????????????????????????????????
DESCRIPTION
??????
VARCHAR2
(
4000
) Y???????????????
Trigger
description
, useful
for
re-creating
trigger
creation
statement
?????
ACTION_TYPE??????
VARCHAR2
(
11
)?? Y???????????????????????????????????????????????????????????????????????????????????????????
TRIGGER_BODY?????
LONG
?????????? Y??????????????? Action taken
by
this
trigger
when
it fires?????????????????????????????????
?
?
?
?
?
-The End-