何謂觸發器？簡言之，是一段命名的PL/SQL代碼塊，隻不過該代碼塊在特定的條件下被觸發并且執行。對于這樣的代碼我們稱之為觸發器
。觸發器根據觸發類型的不同又分為不同級别的觸發器，下面将給出觸發器的分類，定義，以及使用的示例。
一、觸發器的相關概念
1.觸發器的分類
通常根據觸發條件以及觸發級别的不同分為DML觸發器，INSTEAD OF 觸發器，系統事件觸發器。
DML觸發器
ORACLE 對DML語句進行觸發，可以在DML操作前或操作後進行觸發，并且可以對每個行或語句操作上進行觸發。
INSTEAD OF 觸發器
在ORACLE裡，對于簡單視圖，可以直接使用DML進行操作，而複雜視圖則不能直接使用DML，是以INSTEAD OF 觸發器應運而生。
INSTEAD OF 觸發器主要是為解決複雜視圖不能執行DML而建立。
系統事件觸發器
在 ORACLE 資料庫系統的事件中進行觸發,如ORACLE系統的啟動與關閉等.使用系統觸發器,便于系統跟蹤,監測資料庫變化情況等。
2.觸發器的組成(一段PL/SQL代碼塊，可以由PL/SQL,Java,C進行開發,特定事件發生将被觸發)
a.觸發事件
Oracle 啟動、關閉
Oracle 錯誤消息
使用者登入與斷開會話
特定的表、視圖上的DML操作
基于schema的DDL操作
b.觸發時間
即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之後(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。
c.觸發器本身
指實際的觸發代碼，當觸發事件發生後，觸發器代碼決定将做何種操作。
觸發器代碼大小不能超過32k，對于超長的代碼可以将其置于單獨的存儲過程，然後在觸發器中使用call 調用該過程。
觸發器代碼隻能包含DQL，DML，而不能包含DDL以及事務控制語言(COMMIT,ROLLBACK,SAVEPOINT) 。
d.觸發頻率
說明觸發器内定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。
語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器隻執行一次。
行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行資料，觸發器都單獨執行一次。
3.觸發器的用途
控制DDL語句的行為，如通過更改、建立或重命名對象
控制DML語句的行為，如插入、更新和删除
實施參照完整性、複雜業務規則和安全性政策
在修改視圖中的資料時控制和重定向DML語句
通過建立透明日志來稽核系統通路和行為的資訊
二、建立DML觸發器文法描述
1.建立觸發器的文法
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF
column [, column …]]} --定義觸發類型，即那一種或多種DML以及特定的列
ON {[schema.] table_name | [schema.] view_name} --特定的觸發對象，表或視圖
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --定義觸發器為行級觸發器
[WHEN condition]
BEGIN
trigger_body;
END;
BEFORE | AFTER | INSTEAD OF
BEFORE指在執行DML之前觸發觸發器，AFTER則是指在DML執行之後觸發觸發器
INSTEAD OF觸發器隻針對視圖和對象視圖建立，而不能對表、模式和資料庫建立INSTEAD OF 觸發器
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照目前的新,舊列值,預設的相關名稱分别為OLD和NEW。
觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句中則不能加冒号。
[FOR EACH ROW ]
定義觸發器為行級觸發器。
行級觸發器和語句級觸發器的差別表現在：一個DML語句可能操縱多行，也可能操縱一行，使用行級觸發器，不論是一行還是多
行資料被操縱，行觸發器為該DML的每一行觸發一次觸發器操作。語句級觸發器将整個語句操作作為觸發事件，不論該語句影響
了多少行，僅僅觸發一次觸發器。
當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則為行觸發器。
觸發條件，當條件為TRUE時，觸發器代碼才會被執行，對于DML觸發器，僅僅允許在行級觸發器上指定觸發條件。
condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。
WHEN 子句可通過引用new或old僞記錄、一個元件選擇符和一個列名來通路僞字段。
WHEN 子句不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。
2.DML觸發器的觸發順序
a.在單行資料上的觸發順序(觸發代碼僅被執行一次)
BEFORE 語句級觸發器
BEFORE 行級觸發器
AFTER 行級觸發器
AFTER 語句級觸發器
b.在多行資料上的觸發順序(語句級觸發器僅被執行一次，行級觸發器在每個作業行上被執行一次)
3.觸發器中的條件謂詞
ORACLE 提供三個參數INSERTING, UPDATING, DELETING 用于判斷觸發了哪些操作。
INSERTING：如果觸發語句是INSERT 語句，則為TRUE,否則為FALSE 。
UPDATING：如果觸發語句是UPDATE語句，則為TRUE,否則為FALSE 。
DELETING：如果觸發語句是DELETE 語句，則為TRUE,否則為FALSE 。
4.NEW、OLD 限定符的使用
使用被插入、更新或删除的記錄中的列值，可以使用NEW和OLD限定符來表示
:old 修飾符通路操作完成前列的值
:new 修飾符通路操作完成後列的值
限定符 INSERT操作 UPDATE操作 DELETE操作
--------- -------------- ----------- ----------
OLD NULL 有效 有效
NEW 有效 有效 NULL
三、建立DML觸發器
1.建立BEFORE 語句級觸發器
sys@ORCL> drop user scott cascade; --删除scott方案
sys@ORCL> start $ORACLE_HOME/rdbms/admin/utlsampl.sql --重建scott方案
sys@ORCL> grant dba to scott; --授予Scott
DBA角色
scott@ORCL> create table emp_check(oper varchar2(30),upd_date
date); --建立表存放emp表的更新記錄操作的跟蹤
CREATE OR REPLACE TRIGGER tr_before_update_emp --建立update觸發器
BEFORE UPDATE ON emp
-- FOR EACH ROW
INSERT INTO emp_check
Values
('Before update, statement level', sysdate);
scott@ORCL> select * from emp_check; --未執行update前，跟蹤表記錄為空
no rows selected
scott@ORCL> update emp set sal = sal + 100 where deptno = 20; --更新了四條記錄
4 rows updated.
scott@ORCL> select * from emp_check; --跟蹤表表插入了一條跟蹤記錄
OPER UPD_DATE
------------------------------ ---------
Before update, statement level 24-DEC-10
scott@ORCL> update emp set sal = sal + 200 where empno = 7369; --更新了一條記錄，跟蹤表再次插入一條新記錄
1 row updated.
scott@ORCL> select * from emp_check;
Before update, statement level 24-DEC-10
2.建立 BEFORE 行級觸發器
使用上面的代碼來建立行級觸發器，與之不同的是将上面的代碼中"-- FOR EACH ROW" 的"--"删除，則建立的觸發器即為行級觸發器
代碼省略
scott@ORCL> update emp set sal = sal + 200 where deptno = 20; --再次更新deptno為的記錄，且記錄總數為四條
4 rows updated.
scott@ORCL> select * from emp_check; --表emp_check中增加四條，即為update的每一行增加一條記錄
OPER UPD_DATE
------------------------------ ---------
Before update, statement level 24-DEC-10
Before update, statement level 24-DEC-10
3.建立 AFTER 語句級觸發器
CREATE TABLE audit_table_emp --建立一張表audit_table_emp存放emp表上DML操作的次數
(
name VARCHAR2(20),
ins INT,
upd INT,
del INT,
starttime DATE,
endtime DATE
);
CREATE OR REPLACE TRIGGER tr_audit_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_temp INT;
SELECT COUNT(*) INTO v_temp FROM audit_table_emp WHERE name = 'EMP';
IF v_temp = 0 THEN
INSERT INTO audit_table_emp VALUES('EMP', 0, 0, 0, SYSDATE, NULL);
END IF;
CASE
WHEN INSERTING THEN --注意此例中條件謂詞的使用INSERTING、UPDATING、DELETING
UPDATE audit_table_emp SET ins = ins + 1, endtime = SYSDATE WHERE name = 'EMP';
WHEN UPDATING THEN
UPDATE audit_table_emp SET upd = upd + 1, endtime = SYSDATE WHERE name = 'EMP';
WHEN DELETING THEN
UPDATE audit_table_emp SET del = del + 1, endtime = SYSDATE WHERE name = 'EMP';
END CASE;
END;
scott@ORCL> update emp set sal=sal+200 where empno=7788;
scott@ORCL> update emp set sal=sal+200 where ename='SMITH';
scott@ORCL> delete from emp where empno=7788;
scott@ORCL> select * from audit_table_emp; --兩次更新及一次被記錄到表中
NAME INS UPD DEL STARTTIME ENDTIME
-------------------- ---------- ---------- ---------- --------- ---------
EMP 0 2 1 24-DEC-10 24-DEC-10
scott@ORCL> update emp set sal=sal+100 where deptno=10;
3 rows updated. --更新了行，當audit_table_emp表中僅僅記錄一次，UPD的值增加到
scott@ORCL> select * from audit_table_emp;
EMP 0 3 1 24-DEC-10 24-DEC-10
4.建立 AFTER 行級觸發器
CREATE TABLE audit_emp_change --建立audit_emp_change存放emp 表sal列被更新前後的值
name VARCHAR2(10),
oldsal NUMBER(6, 2),
newsal NUMBER(6, 2),
time DATE
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp --注意update 觸發器中使用了OF 關鍵字，當sal列發生變化時，tr_sal_change被觸發
FOR EACH ROW --使用行級觸發器
--WHEN （old.job='CLERK')
SELECT COUNT(*) INTO v_temp FROM audit_emp_change WHERE name = :old.ename; --注意OLD 與NEW 的使用
INSERT INTO audit_emp_change VALUES(:old.ename, :old.sal, :new.sal, SYSDATE);
ELSE
UPDATE audit_emp_change SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE WHERE name = :old.ename;
scott@ORCL> update emp set sal=sal-100 where empno=7369; --更新一行
scott@ORCL> select * from audit_emp_change; --audit_emp_change表中記錄了一行
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ---------
SMITH 1400 1300 24-DEC-10
scott@ORCL> delete from audit_emp_change; --删除之前的記錄
scott@ORCL> update emp set sal=sal+200 where deptno=10; --更新了三行
3 rows updated.
scott@ORCL> select * from audit_emp_change; --audit_emp_change表中記錄了三行
CLARK 2550 2750 24-DEC-10
KING 5100 5300 24-DEC-10
MILLER 1400 1600 24-DEC-10
5.限制行級觸發器
限制行級觸發器是通過添加[WHEN condition]判斷條件，來對滿足特定condition的記錄觸發觸發器。
對于DML觸發器而言，僅僅允許在行級觸發器上指定觸發條件。
在上面第4小點建立AFTER 行級觸發器的代碼中，這一行"--WHEN （old.job='CLERK')" 去掉"--"，觸發器變為具有限定條件的行級
觸發器，則對特定的條件，即job='CLERK'的記錄在更新其sal時才會被觸發。示範省略。
6.建立DML觸發器的注意事項
DML觸發器中不能包含對基表的DQL查詢操作
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON emp --注意update 觸發器中使用了OF 關鍵字，當sal列發生變化時，tr_emp_sal被觸發
FOR EACH ROW --使用行級觸發器
DECLARE
maxsal NUMBER(6, 2);
BEGIN
SELECT MAX(sal) INTO maxsal FROM emp;
IF :new.sal > maxsal THEN
RAISE_APPLICATION_ERROR(-20004, 'Beyond the highest salary');
END IF;
END;
scott@ORCL> update emp set sal=5000 where empno=7369;
update emp set sal=5000 where empno=7369
*
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.TR_EMP_SAL", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR_EMP_SAL'
7.使用DML觸發器實作資料完整性，參照完整性
對于複雜的資料完整性，參照完整性，可以通過DML觸發器來完成普通限制所不能完成的任務
CREATE OR REPLACE TRIGGER tr_del_upd_deptno
AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW
IF (UPDATING AND :old.deptno<>:new.deptno) THEN
UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
IF DELETING THEN
DELETE FROM emp WHERE deptno=:old.deptno;
scott@ORCL> update dept set deptno=50 where deptno=10;
1 row updated.
scott@ORCL> select * from emp where deptno=10;
no rows selected
scott@ORCL> select * from emp where deptno=50;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2750 50
7839 KING PRESIDENT 17-NOV-81 5300 50
7934 MILLER CLERK 7782 23-JAN-82 1600 50
四、管理觸發器
1.檢視系統中特定對象上的觸發器
scott@ORCL> select trigger_name,status from user_triggers
2 where table_name='EMP';
TRIGGER_NAME STATUS
------------------------------ --------
TR_BEFORE_UPDATE_EMP ENABLED
TR_AUDIT_EMP ENABLED
TR_SEC_EMP ENABLED
TR_SAL_CHANGE ENABLED
TR_EMP_SAL ENABLED
2.檢視觸發器的源代碼
scott@ORCL> col text format a65
scott@ORCL> select line,text from user_source where name='TR_DEL_UPD_DEPTNO';
LINE TEXT
---------- -----------------------------------------------------------------
1 TRIGGER tr_del_upd_deptno
2 AFTER DELETE OR UPDATE OF deptno ON dept
3 FOR EACH ROW
4 BEGIN
5 IF (UPDATING AND :old.deptno<>:new.deptno) THEN
6 UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
7 END IF;
8
9 IF DELETING THEN
10 DELETE FROM emp WHERE deptno=:old.deptno;
11 END IF;
12 END;
3.禁用觸發器
當觸發器被禁用後，則表上的DML操作将不會觸發該觸發器，直到該觸發器被解除禁用(alter trigger trigger_name disable)
scott@ORCL> alter trigger tr_emp_sal disable;
4.啟用觸發器
被禁用的觸發器可以被解除禁用(alter trigger trigger_name enable)
scott@ORCL> alter trigger tr_emp_sal enable;
5.禁用、啟用表上的所有觸發器
scott@ORCL> alter table emp disable all triggers;
scott@ORCL> alter table emp enable all triggers;
6.重新編譯觸發器
scott@ORCL> alter trigger tr_emp_sal compile;
7.删除觸發器
scott@ORCL> drop trigger tr_emp_sal;
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)