DML事件
DDL事件
數據庫事件
DML事件觸發器可以是語句或行級觸發器。DML語句觸發器在觸發語句之前或之後觸發DML行級觸發器在語句影響的行變化之前或之後觸發。
用戶可以給單一事件和類型定義多個觸發器，但沒有任何方法可以增強多觸發器觸發的命令。下表列出了用戶可以利用的觸發器事件：
事件 觸發器描述
INSERT 當向表或視圖插入一行時觸發觸發器
UPDATE 更新表或視圖中的某一行時觸發觸發器
DELETE 從表或視圖中刪除某一行時觸發觸發器
CREATE 當使用CREATE語句為數據庫或項目增加一個對象時觸發觸發器
ALTER 當使用ALTER語句為更改一個數據庫或項目的對象時觸發觸發器
DROP 當使用DROP語句刪除一個數據庫或項目的對象時觸發觸發器
START 打開數據庫時觸發觸發器，在事件後觸發
SHUTDOWN 關閉數據庫時觸發，事件前觸發
LOGON 當一個會話建立時觸發，事件前觸發
LOGOFF 當關閉會話時觸發，事件前觸發
SERVER 服務器錯誤發生時觸發觸發器，事件後觸發
創建觸發器的語法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
只有DML觸發器（INSERT、UPDATE、DELETE)語句可以使用INSTEAD OF觸發器並且只有表的DML觸發器可以是BEFORE或AFTER觸發器。
象約束一樣觸發器可以被設置為禁用或啟用來關閉或打開他們的執行體(EXECUTE),將觸發器設置為禁用或啟用使用ALTER TRIGGER語句：
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
要禁用或啟用表的所有觸發器，使用ALTER TABLE語句
ALTER TRIGGER table_name DISABLE ALL TRIGGER;
ALTER TRIGGER table_name ENABLE ALL TRIGGER;
刪除觸發器使用DROP TRIGGER
DROP TRIGGER trigger_name;
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 觸發器名; // 刪除觸發器;
declare
Begin
IF :NEW.YEAR_DISPLAY = 'N' THEN
:NEW.CLV_FLAG := 'Y';
ELSE
:NEW.CLV_FLAG := 'N';
END IF;
If Updating Then
-- A : ??????, B: ??? => ?? , C : ?? => ???, D: ????????
-- E :???? =>????
if :new.prt_type <> :old.prt_type then
insert into bnd_prtmst_log(maint_user,maint_date,prt_no,status,org_date,new_date,type,cur_bnd_ctrl)
values(userenv('CLIENT_INFO'),sysdate,:new.prt_no,:old.prt_type||'=>'||:new.prt_type,:old.beg_date,:new.beg_date,'A',:old.bnd_ctrl);
end if;
if :new.bnd_ctrl ='Y' and :old.bnd_ctrl='N' then
insert into bnd_prtmst_log(maint_user,maint_date,prt_no,status,org_date,new_date,type,cur_bnd_ctrl)
values(userenv('CLIENT_INFO'),sysdate,:new.prt_no,:old.bnd_ctrl||'=>'||:new.bnd_ctrl,:old.beg_date,:new.beg_date,'B',:old.bnd_ctrl);
elsif :new.bnd_ctrl ='N' and :old.bnd_ctrl='Y' then
insert into bnd_prtmst_log(maint_user,maint_date,prt_no,status,org_date,new_date,type,cur_bnd_ctrl)
values(userenv('CLIENT_INFO'),sysdate,:new.prt_no,:old.bnd_ctrl||'=>'||:new.bnd_ctrl,:old.beg_date,:new.beg_date,'C',:old.bnd_ctrl);
end if;
if :new.beg_date <> :old.beg_date then
insert into bnd_prtmst_log(maint_user,maint_date,prt_no,status,org_date,new_date,type,cur_bnd_ctrl)
values(userenv('CLIENT_INFO'),sysdate,:new.prt_no,'ORG_DATE=>NEW_DATE',:old.beg_date,:new.beg_date,'D',:old.bnd_ctrl);
else
insert into bnd_prtmst_log(maint_user,maint_date,prt_no,status,org_date,new_date,type,cur_bnd_ctrl)
values(userenv('CLIENT_INFO'),sysdate,:new.prt_no,' =>NEW_DATE',:old.beg_date,:new.beg_date,'E',:old.bnd_ctrl);
end if;
if :new.prt_type <> :old.prt_type or :new.bnd_ctrl <> :old.bnd_ctrl or :new.prt_edesc <> :old.prt_edesc or :new.prt_uom <> :old.prt_uom or
:new.tube_prod <> :old.tube_prod or :new.beg_date <> :old.beg_date then
:new.up_wnc_flag:='Y';
:new.up_wnc_date:=SYSDATE;
end if;
End if;
End;
沒有留言:
張貼留言