Oracle觸發器詳細介紹

觸發器

是特定事件出現的時候，自動執行的程式碼塊。類似於儲存過程，但是使用者不能直接呼叫他們。

功能：

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、使用者事件觸發器

語句觸發器

是在表上或者某些情況下的檢視上執行的特定語句或者語句組上的觸發器。能夠與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
本文來自: IXPUB技術社群(www.ixpub.net) 詳細出處參考：http://www.ixpub.net/thread-866283-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-563197/，如需轉載，請註明出處，否則將追究法律責任。

Oracle觸發器詳細介紹

相關文章