Trigger與Sequence應用(包含Trigger detail介紹)－Programs Knowledge

Trigger與Sequence應用

Trigger

create or replace trigger 名稱

before insert on table名稱

for each row

begin

--呼叫sequence將數字插入Table的某個column中

select sequence名稱.NEXTVAL into :NEW.Column名稱　from dual;

end;

Sequence

create sequence 名稱

minvalue 0　--最小數字

maxvalue 99999999999999　--最大數字

start with 1　--下一個數字

increment by 1　--每次遞增1

cache 20

order;

==============================================================

其他應用

create or replace trigger 名稱

after insert or update of column1, column2, column3 on table名稱

for each row

begin

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

…

END IF;

end;

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

create or replace trigger test
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型觸發器：
Create or replace trigger DelEmp
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型觸發器：
Create or replace trigger InsertEmp
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：多個觸發條件
Create or replace trigger ChangeEmp
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;

// 更完善的寫法：
Create or replace trigger ChangeEmp
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: 行級觸發器] //必須是對所有的行進行操作才行。
Create or Replace trigger UpdateEmp
Before update on emp
for each row
Begin
dbms_output.put_line(:old.sal||'--------->'||:new.sal);
End;

[例5：保存歷史數據，這種使用方法很重要，用來保存關鍵表的歷史數據]
CReate or Replace trigger ChangeEmp
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：修改外鍵]
Create or Replace trigger UpdateDept
after update on dept
for each row
Begin
update emp
set emp.deptno = :new.deptno
where emp.deptno = :old.deptno;
End;

[例7：刪除外鍵、刪除相關數據]
Create or Replace trigger DeleteDept
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 觸發器名; // 刪除觸發器;