Oracle觸發器詳細介紹

ophoper 13年前發布 | 2K 次閱讀 項目經理
觸發器
是特定事件出現的時候，自動執行的代碼塊。類似于存儲過程，但是用戶不能直接調用他們。
功能：
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

 本文由用戶 ophoper 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！