--=======================
-- PL/SQL --> DML 觸發器
--=======================
???
??? 何謂觸發器？簡言之，是一段命名的 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 觸發器則為行觸發器。 ???
??????? ???
??????? [WHEN condition]
??????????? 觸發條件，當條件為 TRUE 時，觸發器代碼才會被執行，對于 DML 觸發器，僅僅允許在行級觸發器上指定觸發條件。
??????????? condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用 PL / SQL 函數。
WHEN 子句可通過引用 new 或 old 偽記錄、一個組件選擇符和一個列名來訪問偽字段。
??????????? WHEN 子句不能用在 INSTEAD OF 行觸發器和其它類型的觸發器中。
???????????
??? 2.DML 觸發器的觸發順序
??????? a . 在單行數據上的觸發順序 ( 觸發代碼僅被執行一次 )
??????????? BEFORE 語句級觸發器
??????????????? BEFORE 行級觸發器
??????????????? AFTER 行級觸發器
??????????? AFTER 語句級觸發器
??????? b . 在多行數據上的觸發順序 ( 語句級觸發器僅被執行一次，行級觸發器在每個作業行上被執行一次 )
??????????? BEFORE 語句級觸發器
??????????????? BEFORE 行級觸發器
??????????????? AFTER 行級觸發器
??????????????? BEFORE 行級觸發器
??????????????? AFTER 行級觸發器
??????????? AFTER 語句級觸發器
???????????
???
??? 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
??????? BEGIN
??????? ? INSERT INTO emp_check
??????? ? Values
??????????? ( 'Before update, statement level' , sysdate );
??????? END ;
?
??????? 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 ;
?
??????? OPER ?????????????????????????? UPD_DATE
??????? ------------------------------ ---------
??????? Before update , statement level 24 - DEC - 10
??????? 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
??????????? Before update , statement level 24 - DEC - 10
??????????? Before update , statement level 24 - DEC - 10
??????????? 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 ;
??????? BEGIN
??????????? 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 ;
?
??????? NAME ??????????????????????? INS ??????? UPD ??????? DEL STARTTIME ENDTIME
??????? -------------------- ---------- ---------- ---------- --------- ---------
??????? 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')
??????? DECLARE
??????????? v_temp INT ;
??????? BEGIN
??????????? SELECT COUNT (*) INTO v_temp FROM audit_emp_change WHERE name = : old . ename ; ? -- 注意 OLD 與 NEW 的使用
??????????? IF v_temp = 0 THEN
??????????????? 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 ;
??????????? END IF ;
??????? END ;
?
??????? 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 表中記錄了三行
?
??????? NAME ?????????? OLDSAL ???? NEWSAL TIME
??????? ---------- ---------- ---------- ---------
??????? 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
??????????? BEGIN
??????????? ? IF ( UPDATING AND : old . deptno <>: new . deptno ) THEN
??????????????? UPDATE emp SET deptno =: new . deptno WHERE deptno =: old . deptno ;
??????????? ? END IF ;
??????????? ?
??????????? ? IF DELETING THEN
??????????????? DELETE FROM emp WHERE deptno =: old . deptno ;
??????????? ? END IF ;
??????????? END ;
?
??????????? 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 ;
?
五、更多參考
有關 SQL 請參考
??????? SQL 基礎--> 子查詢
??????? SQL 基礎--> 多表查詢
SQL 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
???????
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
