--==============================
-- PL/SQL --> INSTEAD OF 觸發器
--==============================
?
??? INSTEAD OF 觸發器常用于管理編寫不可更新的視圖， INSTEAD-OF 觸發器必須是行級的。
??? 可以用 INSTEAD OF 觸發器來解釋 INSERT 、 UPDATE 和 DELETE 語句，并用備用的程序代碼替換那些指令。
?
一、不可更新視圖
??? 基于下列情形創建的視圖，不可直接對其進行 DML 操作
??????? 使用了集合操作運算符 (UNION,UNION ALL ,INTERSECT,MINUS)
??????? 使用了分組函數 (MIN,MAX,SUM,AVG)
??????? 使用了 GROUP BY ,CONNECT BY ,START WITH 子句
??????? 使用了 DISTINCT 關鍵字
??????? 使用了連接查詢
??? ? 對于基于上述情況創建的視圖，不能對其直接執行 DML ，但可以在該視圖上創建 INSTEAD OF 觸發器來間接執行 DML 。
???
二、創建 INSTEAD OF 觸發器的語法
??? CREATE [OR REPLACE] TRIGGER trigger_name
??? INSTEAD OF { dml_statement }
??? ON { object_name | database | schema}
??? FOR EACH ROW
??? [WHEN (logical_expression)]
??? [DECLARE]
??????? declaration_statements ;
??? BEGIN
??????? execution_statements ;
??? END [trigger_name] ;
??? /
?
三、創建視圖
??? -- 在下面創建的視圖中，由于使用了連接查詢，因此視圖將不可更新
??????? CREATE OR REPLACE VIEW vw_dept_emp
??????? AS
??????? ? SELECT deptno , d . dname , e . empno , e . ename
??????? ? FROM dept d
??????? ? JOIN emp e
??????? ? USING ( deptno );
???
??? -- 從數據字典 (user_updatable_columns) 中查詢某一視圖哪些列是可更新或不可更新的
??????? scott@ORCL > col owner format a15
??????? scott@ORCL > select * from user_updatable_columns where table_name = 'VW_DEPT_EMP' ; ?
?
??????? OWNER ?????????? TABLE_NAME ???????????????????? COLUMN_NAME ???? UPD INS DEL
??????? --------------- ------------------------------ --------------- --- --- ---
??????? SCOTT ?????????? VW_DEPT_EMP ???????????????? ??? DEPTNO ????????? YES YES YES
??????? SCOTT ?????????? VW_DEPT_EMP ??????????????????? DNAME ?????????? NO ? NO ? NO ?? -- 可以看到列 DNAME 不能執行 DML
??????? SCOTT ?????????? VW_DEPT_EMP ??????????????????? EMPNO ?????????? YES YES YES
??????? SCOTT ?????????? VW_DEPT_EMP ??????????????????? ENAME ?????????? YES YES YES
??????? ?
??? -- 嘗試更新視圖時，更新失敗
??????? scott@ORCL > update vw_dept_emp set dname = 'Developement' where deptno = 10 ;
??????? update vw_dept_emp set dname = 'Developement' where deptno = 10
??????????????????????????? ?? *
??????? ERROR at line 1 :
??????? ORA - 01779 : cannot modify a column which maps to a non key - preserved table ??????
??? ?
??????? scott@ORCL > update vw_dept_emp set ename = 'Henry' where empno = 7369 ;
?
??????? 1 row updated .
?
??????? scott@ORCL > select empno , ename , job from emp where empno = 7369 ;
?
??????????? ? EMPNO ENAME ????? JOB
??????? ---------- ---------- ---------
??????????? ? 7369 Henry ????? CLERK
??? ?
??? -- 創建一個基于 UPDATE 的 INSTEAD OF 觸發器
??????? CREATE OR REPLACE TRIGGER tr_vw_dept_emp
??????? INSTEAD OF UPDATE
??????? ON vw_dept_emp
??????? FOR EACH ROW
??????? BEGIN
??????? ? UPDATE dept
??????? ? SET dname =: new . dname
??????? ? WHERE deptno =: old . deptno ;
??????? END ;
?
??? -- 更新視圖
??????? scott@ORCL > update vw_dept_emp set dname = 'Developement' where deptno = 20 ;
?
??????? 4 rows updated .
???
??? -- 驗證更新后的結果
??????? scott@ORCL > select * from vw_dept_emp where rownum < 2 and deptno = 20 ;
?
??????????? DEPTNO DNAME ?????????????? EMPNO ENAME
??????? ---------- -------------- ---------- ----------
??????????????? 20 Developement ?? ?????? 7369 Henry
?
??????? scott@ORCL > select * from dept where deptno = 20 ;
?
??????????? DEPTNO DNAME ????????? LOC
??????? ---------- -------------- -------------
??????????????? 20 Developement ?? DALLAS ??? ?
?
四、 INSTEAD OF 觸發器的應用
??? 在工作中，有時候需要將兩個或多個表中的字段進行同步的問題。即假定有表 A 和 B ，表 A 中的字段 COLa 和表 B 中的字段 COLb 需要時時保持同
??? 步，當表 A 中 COLa 被更新時，需要將更新的內容同步到表 B 的 COLb 中，反之，當表 B 的 COLb 被更新時，需要將 COLb 的內容更新到 A 表的 COLa 中。
??? 對于這樣的問題，按照一般的想法是在表 A 和表 B 分別創建觸發器來使之保持同步，但實際上表 A 和表 B 上的觸發器將會被迭代觸發，即 A 表的
??? 更新將觸發 B 表上的觸發器，而 B 表上的觸發器反過來又觸發 A 上的觸發器，最終的結果是導致變異表的產生。基于此，我們可以使用 INSTEAD
??? OF 觸發器完成此項任務，下面給出全部過程。
???
??? -- 分別創建表 tb_a,tb_b 并插入記錄
??????? scott@ORCL > create table tb_a ( ID int , COLa varchar2 ( 40 ));
?
??????? scott@ORCL > create table tb_b ( ID int , COLb varchar2 ( 40 ));
?
??????? scott@ORCL > insert into tb_a select 1 , 'Robinson' from dual ;
?
??????? scott@ORCL > insert into tb_b select 1 , 'Jackson' from dual ;
?
??????? scott@ORCL > commit ;
?
??? -- 在表 tb_a 上創建觸發器
??????? CREATE OR REPLACE TRIGGER tr_tb_a
??????? ? BEFORE UPDATE ON tb_a
??????? ? FOR EACH ROW
??????? DECLARE
??????? ? lv_newcol ??? VARCHAR2 ( 40 );
??????? ? lv_oldcol ??? VARCHAR2 ( 40 );
??????? BEGIN
??????? ? lv_newcol := : new . COLa ;
??????? ? lv_oldcol := : old . COLa ;
??????? ? IF lv_newcol <> lv_oldcol THEN
??????????? UPDATE tb_b
??????????? ?? SET COLb = : new . COLa
??????????? ? WHERE ID = : new . ID ;
??????? ? END IF ;
??????? ? DBMS_OUTPUT . PUT_LINE ( lv_oldcol || '=>' || lv_newcol );
??????? END ;
???
??? -- 更新表 tb_a 時，表 tb_b 的字段也被更新
??????? scott@ORCL > update tb_a set COLa = 'Willson' where ID = 1 ;
??????? Robinson => Willson
?
??????? scott@ORCL > select * from tb_b ;
?
??????????????? ID COLB
??????? ---------- ----------------------------------------
??????????????? ? 1 Willson
?
??? -- 在表 B 上創建觸發器
??????? CREATE OR REPLACE TRIGGER tr_tb_b
??????? ? BEFORE UPDATE ON tb_b
??????? ? FOR EACH ROW
??????? DECLARE
??????? ? lv_newcol ??? VARCHAR2 ( 40 );
??????? ? lv_oldcol ??? VARCHAR2 ( 40 );
??????? BEGIN
??????? ? lv_newcol := : new . COLb ;
??????? ? lv_oldcol := : old . COLb ;
??????? ? IF lv_newcol <> lv_oldcol THEN
??????????? UPDATE tb_a
??????????? ?? SET COLa = : new . COLb
??????????? ? WHERE ID = : new . ID ;
??????? ? END IF ;
??????? ? DBMS_OUTPUT . PUT_LINE ( lv_oldcol || '=>' || lv_newcol );
??????? END ;
???
??? -- 更新表 tb_b 時，出現了表變異的提示，同樣更新表 tb_a 時也會出現類似的提示
??????? scott@ORCL > update tb_b set COLb = 'Other' where ID = 1 ;
??????? update tb_b set COLb = 'Other' where ID = 1
??????????? ?? *
??????? ERROR at line 1 :
??????? ORA - 04091 : table SCOTT . TB_B is mutating , trigger / function may not see it
??????? ORA - 06512 : at "SCOTT.TR_TB_A" , line 8
??????? ORA - 04088 : error during execution of trigger 'SCOTT.TR_TB_A'
??????? ORA - 06512 : at "SCOTT.TR_TB_B" , line 8
??????? ORA - 04088 : error during execution of trigger 'SCOTT.TR_TB_B'
?
??? -- 禁用觸發器
??????? scott@ORCL > alter trigger tr_tb_a disable ;
?
??????? scott@ORCL > alter trigger tr_tb_b disable ;
?
??? -- 分別在表 tb_a ， tb_b 上創建視圖
??????? scott@ORCL > create view vw_tb_a as select * from tb_a ;
?
??????? scott@ORCL > create view vw_tb_b as select * from tb_b ;
???
??? -- 基于視圖 vw_tb_a 創建 instead of 觸發器
??????? CREATE OR REPLACE TRIGGER tr_vw_tb_a
??????? ? INSTEAD OF UPDATE ON vw_tb_a
??????? ? FOR EACH ROW
??????? DECLARE
??????? ? lv_newcol ??? VARCHAR2 ( 40 );
??????? ? lv_oldcol ??? VARCHAR2 ( 40 );
??????? BEGIN
??????? ? lv_newcol := : new . COLa ;
??????? ? lv_oldcol := : old . COLa ;
??????? ? IF lv_newcol <> lv_oldcol THEN
??????????? UPDATE tb_a
??????????? ? SET COLa = : new . COLa
??????????? WHERE ID = : new . ID ;
???????????
??????????? UPDATE tb_b
??????????? ? SET COLb = : new . cola
??????????? WHERE ID =: new . ID ;
??????? ? END IF ;
??????? ? DBMS_OUTPUT . PUT_LINE ( lv_oldcol || '=>' || lv_newcol );
??????? END ;
???
??? -- 基于視圖 vw_tb_b 創建 instead of 觸發器
??????? CREATE OR REPLACE TRIGGER tr_vw_tb_b
??????? ? INSTEAD OF UPDATE ON vw_tb_b
??????? ? FOR EACH ROW
??????? DECLARE
??????? ? lv_newcol ??? VARCHAR2 ( 40 );
??????? ? lv_oldcol ??? VARCHAR2 ( 40 );
??????? BEGIN
??????? ? lv_newcol := : new . COLb ;
??????? ? lv_oldcol := : old . COLb ;
??????? ? IF lv_newcol <> lv_oldcol THEN
??????????? UPDATE tb_a
??????????? ? SET COLa = : new . COLb
??????????? WHERE ID = : new . ID ;
???????????
??????????? UPDATE tb_b
??????????? ? SET COLb = : new . colb
??????????? WHERE ID =: new . ID ;
??????? ? END IF ;
??????? ? DBMS_OUTPUT . PUT_LINE ( lv_oldcol || '=>' || lv_newcol );
??????? END ; ???
???
??? -- 對視圖進行更新，驗證成功
??????? scott@ORCL > update vw_tb_a set COLa = 'Many' where ID = 1 ;
??????? Willson => Many
?
??????? scott@ORCL > select * from tb_b ;
?
??????????????? ID COLB
??????? ---------- ----------------------------------------
??????????????? ? 1 Many
?
??????? scott@ORCL > update vw_tb_b set COLb = 'Much' where ID = 1 ;
??????? Many => Much
?
??????? scott@ORCL > select * from tb_a ;
?
??????????????? ID COLA
??????? ---------- ----------------------------------------
??????????????? ? 1 Much
???
五、總結
??? 視圖創建時未指定 WITH CHECK OPTION 選項
??? INSTEAD OF 觸發器只適用于視圖
??? 基于視圖的 INSTEAD OF 觸發器不能指定 BEFORE 和 AFTER 選項
??? INSTEAD OF 觸發器，必須指定 FOR EACH ROW
??? 當創建的視圖被重新定義之后，基于視圖上創建的觸發器將需要重新定義
?
六、更多參考
有關 SQL 請參考
??????? SQL 基礎--> 子查詢
??????? SQL 基礎--> 多表查詢
SQL 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
?
?
???
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
