觸發器是特定事件出現的時候，自動執行的代碼塊。類似于存儲過程，但是用戶不能直接調用他們。?


觸發器的功能：?


1、?允許/限制對表的修改?
2、?自動生成派生列，比如自增字段?
3、?強制數據一致性?
4、?提供審計和日志記錄?
5、?防止無效的事務處理?
6、?啟用復雜的業務邏輯?

觸發器的組成：?

create?trigger?biufer_employees_department_id?
before?insert?or?update?
of?department_id?
on?employees?
referencing?old?as?old_value?
new?as?new_value?
for?each?row?
when?(new_value.department_id<>80?)?
begin?
:new_value.commission_pct?:=0;?
end;?
/?

觸發器的組成部分：?
1、?觸發器名稱?
2、?觸發語句?
3、?觸發器限制?
4、?觸發操作?

1、?觸發器名稱?

create?trigger?biufer_employees_department_id?
命名習慣：?
biufer（before?insert?update?for?each?row）?
employees?表名?
department_id?列名?

2、?觸發語句?

比如：?
表或視圖上的DML語句?
DDL語句?
數據庫關閉或啟動,startup?shutdown?等等?
before?insert?or?update?
of?department_id?
on?employees?
referencing?old?as?old_value?
new?as?new_value?
for?each?row?
說明：?
1、?無論是否規定了department_id?，對employees表進行insert的時候?
2、?對employees表的department_id列進行update的時候?

3、?觸發器限制?

when?(new_value.department_id<>80?)?
限制不是必須的。此例表示如果列department_id不等于80的時候，觸發器就會執行。?
其中的new_value是代表跟新之后的值。?

4、?觸發操作?

是觸發器的主體?
begin?
:new_value.commission_pct?:=0;?
end;?
主體很簡單，就是將更新后的commission_pct列置為0?
觸發：?
insert?into?employees(employee_id,?
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct?)?
values(?12345,'Chen','Donny',?sysdate,?12,?‘donny@hotmail.com',60,10000,.25);?
select?commission_pct?from?employees?where?employee_id=12345;?
觸發器不會通知用戶，便改變了用戶的輸入值。?


觸發器的類型：?

1、?語句觸發器?
2、?行觸發器?
3、?INSTEAD?OF?觸發器?
4、?系統條件觸發器?
5、?用戶事件觸發器?

1、?語句觸發器?

是在表上或者某些情況下的視圖上執行的特定語句或者語句組上的觸發器。能夠與INSERT、UPDATE、?
DELETE或者組合上進行關聯。但是無論使用什么樣的組合，各個語句觸發器都只會針對指定語句激活一次?
。比如，無論update多少行，也只會調用一次update語句觸發器。?

例子：?
需要對在表上進行DML操作的用戶進行安全檢查，看是否具有合適的特權。?
Create?table?foo(a?number);?
Create?trigger?biud_foo?
Before?insert?or?update?or?delete?
On?foo?
Begin?
If?user?not?in?(‘DONNY')?then?
Raise_application_error(-20001,?‘You?don't?have?access?to?modify?this?table.');?
End?if;?
End;?
/?
即使SYS，SYSTEM用戶也不能修改foo表?

[試驗]?
對修改表的時間、人物進行日志記錄。?
1、?建立試驗表?
create?table?employees_copy?as?select?*from?hr.employees?

2、?建立日志表?
create?table?employees_log(?
who?varchar2(30),?
when?date);?

3、?在employees_copy表上建立語句觸發器，在觸發器中填充employees_log?表。?
Create?or?replace?trigger?biud_employee_copy?
Before?insert?or?update?or?delete?
On?employees_copy?
Begin?
Insert?into?employees_log(?
Who,when)?
Values(?user,?sysdate);?
End;?
/?

4、?測試?
update?employees_copy?set?salary=?salary*1.1;?
select?*from?employess_log;?

5、?確定是哪個語句起作用？?
即是INSERT/UPDATE/DELETE中的哪一個觸發了觸發器？?
可以在觸發器中使用INSERTING?/?UPDATING?/?DELETING?條件謂詞，作判斷：?
begin?
if?inserting?then?
-----?
elsif?updating?then?
-----?
elsif?deleting?then?
------?
end?if;?
end;?
if?updating(‘COL1')?or?updating(‘COL2')?then?
------?
end?if;?

[試驗]?
1、?修改日志表?
alter?table?employees_log?
add?(action?varchar2(20));?

2、?修改觸發器，以便記錄語句類型。?
Create?or?replace?trigger?biud_employee_copy?
Before?insert?or?update?or?delete?
On?employees_copy?
Declare?
L_action?employees_log.action%type;?
Begin?
if?inserting?then?
l_action:='Insert';?
elsif?updating?then?
l_action:='Update';?
elsif?deleting?then?
l_action:='Delete';?
else?
raise_application_error(-20001,'You?should?never?ever?get?this?error.');?
Insert?into?employees_log(?
Who,action,when)?
Values(?user,?l_action,sysdate);?
End;?
/?

3、?測試?
insert?into?employees_copy(?employee_id,?last_name,?email,?hire_date,?job_id)?
values(12345,'Chen','Donny@hotmail',sysdate,12);?
select?*from?employees_log?
update?employees_copy?set?salary=50000?where?employee_id?=?12345;?


2、?行觸發器?


是指為受到影響的各個行激活的觸發器，定義與語句觸發器類似，有以下兩個例外：?
1、?定義語句中包含FOR?EACH?ROW子句?
2、?在BEFORE……FOR?EACH?ROW觸發器中，用戶可以引用受到影響的行值。?

比如：?
定義：?
create?trigger?biufer_employees_department_id?
before?insert?or?update?
of?department_id?
on?employees_copy?
referencing?old?as?old_value?
new?as?new_value?
for?each?row?
when?(new_value.department_id<>80?)?
begin?
:new_value.commission_pct?:=0;?
end;?
/?

Referencing?子句：?
執行DML語句之前的值的默認名稱是?:old?,之后的值是?:new?
insert?操作只有:new?
delete?操作只有?:old?
update?操作兩者都有?
referencing子句只是將new?和old重命名為new_value和old_value，目的是避免混淆。比如操作一個名為?
new的表時。?
作用不很大。?

[試驗]：為主健生成自增序列號?
drop?table?foo;?
create?table?foo(id?number,?data?varchar2(20));?
create?sequence?foo_seq;?
create?or?replace?trigger?bifer_foo_id_pk?
before?insert?on?foo?
for?each?row?
begin?
select?foo_seq.nextval?into?:new.id?from?dual;?
end;?
/?

insert?into?foo(data)?values(‘donny');?
insert?into?foo?values(5,'Chen');?
select?*?from?foo;?


3、?INSTEAD?OF?觸發器更新視圖?

Create?or?replace?view?company_phone_book?as?
Select?first_name||',?'||last_name?name,?email,?phone_number,?
employee_id?emp_id?
From?hr.employees;?

嘗試更新email和name?
update?hr.company_phone_book?
set?name='Chen1,?Donny1'?
where?emp_id=100?

create?or?replace?trigger?update_name_company_phone_book?
INSTEAD?OF?
Update?on?hr.company_phone_book?
Begin?
Update?hr.employees?
Set?employee_id=:new.emp_id,?
First_name=substr(:new.name,?instr(:new.name,',')+2),?
last_name=?substr(:new.name,1,instr(:new.name,',')-1),?
phone_number=:new.phone_number,?
email=:new.email?
where?employee_id=:old.emp_id;?
end;?



4、?系統事件觸發器?

系統事件：數據庫啟動、關閉，服務器錯誤?
create?trigger?ad_startup?
after?startup?
on?database?
begin?
--?do?some?stuff?
end;?
/?


5、?用戶事件觸發器?


用戶事件：用戶登陸、注銷，CREATE?/?ALTER?/?DROP?/?ANALYZE?/?AUDIT?/?GRANT?/?REVOKE?/?
RENAME?/?TRUNCATE?/?LOGOFF?
例子：記錄刪除對象?

1.?日志表?
create?table?droped_objects(?
object_name?varchar2(30),?
object_type?varchar2(30),?
dropped_on?date);?

2．觸發器?
create?or?replace?trigger?log_drop_trigger?
before?drop?on?donny.schema?
begin?
insert?into?droped_objects?values(?
ora_dict_obj_name,?--?與觸發器相關的函數?
ora_dict_obj_type,?
sysdate);?
end;?
/?

3.?測試?
create?table?drop_me(a?number);?
create?view?drop_me_view?as?select?*from?drop_me;?
drop?view?drop_me_view;?
drop?table?drop_me;?
select?*from?droped_objects?


其它：?

禁用和啟用觸發器?
alter?trigger?<trigger_name>?disable;?
alter?trigger?<trigger_name>?enable;?

事務處理：?
在觸發器中，不能使用commit?/?rollback?
因為ddl語句具有隱式的commit，所以也不允許使用?

視圖：?dba_triggers

總結

以上是生活随笔為你收集整理的oracle触发器的类型及使用方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。