程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle觸發器

Oracle觸發器

編輯：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,‘[email protected]’,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表
[試驗]

如下實驗環境OS: XP DB:Oracle 10G
對修改表的時間、人物進行日志記錄。
1、建立試驗表
create table scott.employees_copy as select *from scott.emp
2、建立日志表

create table scott.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 scott.employees_copy

Begin

Insert into scott.employees_log(

Who,when)

Values( user, sysdate);

End;

/
4、測試

update scott.employees_copy set sal= sal*1.1;

SQL> select * from scott.employees_log;

WHO WHEN

------------------------------ --------------

SCOTT                          23-11月-07
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