Trigger 介紹

2012-10-22

20898
0
DB(Oracle)

Trigger 介紹

相關應用

    after insert or update of column1, column2, column3 on table名稱
    for each row
begin

    IF inserting THEN --insert即執行, 除了inserting外, 還可用updating
        …
    END IF;
end;

    before update on tableName
    for each row
declare
    start_dt timestamp;
    end_dt timestamp;
begin
    start_dt := :old.column1; --將update前的row column1值放入start_dt
    end_dt := :new.column1; --將update前的row column1值放入end_dt
    :new.column2 := start_dt; --update column2欄位
    :new.column3 := end_dt; --update column3欄位
end test;

轉貼 : http://oracled2k.pixnet.net/blog/post/19131028

Oracle觸發器開發與設計
一、什麼是觸發器？
數據庫觸發器是一個存儲的PL/SQL程序塊，它與一個基表聯系，當在表上執行特定的數據庫維護（插入、刪除、更新這三種操作）時，隱含地執行一個PL/SQL程序塊。
二、觸發器的作用：
。防止非法的數據庫操縱、維護數據庫安全
。對數據庫的操作進行審計，存儲歷史數據
。完成數據庫初始化處理
。控制數據庫的數據完整性
。進行相關數據的修改
。完成數據復制
。自動完成數據庫統計計算
。限制數據庫操作的時間、權限等，控制實體的安全性
三、觸發器的組成：
1、觸發時間：觸發器事件的時間次序（before, afer)[2]
2、觸發事件：什麼SQL語句會引起觸發器觸發(Insert, delete, update)[3]
3、觸發子體：觸發器觸發時要執行的操作(一個完整的PL/SQL程序)
4、觸發類型：觸發器被執行的次數(語句級、行級）[2] //語句級只執行一次，行級會執行多次。
[*]一個表上最多可以創建12個不同類型的觸發器：3*2*2 = 12
四、創建觸發器注意事項：
1、在觸發器中可以調用存儲過程、包；在存儲過程中不得調用觸發器。
2、在觸發器中不得使用commit, rollback, savepoint語句。
3、在觸發器中不得間接調用含有commit, rollback, savepoint的語句的存儲過程及函數。
五、創建語句級觸發器：
語句級觸發器：請參考PowerPoint教程：存儲過程1.ppt[Page19] 該觸發器在數據庫操作時只執行一次。
說明：
。update中的of是可選項，用于指定語句要修改的列
。要創建的觸發器已經存在時，使用replace選項
//例1：before型觸發器：

  before delete on emp
  Begin
   if (To_Char(sysdate,'dy') in ('星期六','星期日') or
        To_number(To_Char(sysdate,'hh24'))  not between  8   and 18)
   then dbms_output.put_line('現在是非工作時間,請退出!!!');
   end if;
  End;

[觸發器數據字典]
SQL> select table_owner, table_name,trigger_body from user_triggers where trigger_name='DELEMP';
//例2：After型觸發器：

    after insert on emp  // 如果是before，就會比after的結果少一名。
Declare
    v_empcount number(7);
Begin
    select count(*) into v_empcount from emp;
    dbms_output.put_line('目前員工總數已達到：'|| v_empcount|| '名。');
End;

//例3：多個觸發條件

    before delete or insert or update on emp
Begin
   if (To_Char(sysdate,'dy') in ('星期六','星期日') or
        To_number(To_Char(sysdate,'hh24'))  not between 8 and 18)
   then dbms_output.put_line('現在是非工作時間,請不要修改數據!!!');
   end if;
End;

// 更完善的寫法：

  before delete or insert or update  on emp
  Begin
   if (DELETING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
        To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))
   then dbms_output.put_line('現在是非工作時間,不要刪除數據！');
   elsif (UPDATING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
        To_number(To_Char(sysdate,'hh24'))  not between  8 and 18))
   then dbms_output.put_line('現在是非工作時間,不要更新數據！');
   elsif (INSERTING and (To_Char(sysdate,'dy') in ('星期六','星期日') or
        To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))
   then dbms_output.put_line('現在是非工作時間,不要插入數據！');
   end if;
  End;

六、創建行級觸發器：
等級觸發器：增加選項for each row, 使觸發器在每一行上觸發。
1、創建行級觸發器注意事項：
(1) 在行級觸發器中，在列名前增加old表示該列修改前值，增加new表示該列修改後值。
(2) 在PL/SQL中引用時，前邊增加冒號。
[例4: 行級觸發器] //必須是對所有的行進行操作才行。

    Before update on emp 
    for each row 
Begin
    dbms_output.put_line(:old.sal||'--------->'||:new.sal);
End;

[例5：保存歷史數據，這種使用方法很重要，用來保存關鍵表的歷史數據]

    Before update or delete on emp
    for each row
Begin
    Insert into oldemp(empno, ename,job,hiredate,sal)
        values(:old.empno,:old.ename,:old.job,sysdate,:old.sal);
End;

SQL> create table oldemp as select empno, ename,job,hiredate,sal from emp where 1>2;
[例6：修改外鍵]

    after update on dept
    for each row
Begin
update emp
    set emp.deptno = :new.deptno
    where emp.deptno = :old.deptno;
End;

[例7：刪除外鍵、刪除相關數據]

    before delete on dept
    for each row
Begin
    delete from emp where deptno = :old.empno;
End;

七、觸發器管理
1、使觸發器失效：
SQL> alter trigger 觸發器名稱 disable; // 失效
SQL> Alter Trigger 觸發器名稱 enable; // 生效
SQL> Alter table 表名 DISABLE all triggers; // 一個表上的所有觸發器失效
SQL> Alter table 表名 ENABLE all triggers; // 使一個表上的所有觸發器生效
SQL> Drop Trigger 觸發器名; // 刪除觸發器;

回首頁