Oracle Instead of 觸發器的使用

Oracle Instead of 觸發器的使用

[@more@]

Oracle Instead of 觸發器的使用
轉自：http://blogger.org.cn/blog/more.asp?name=RandomRen&id=20956
我平時比較少用觸發器，主要是因為程式邏輯不對的時候不容易發現錯誤，有時資料量大了也可能
產生效能上的問題，但這個東西總有用武之地，在很多場合還是會起到巨大的作用。
這兩天就遇到一個問題，有兩張表的一個欄位需要進行同步更新，也就是A表修改時要把對應的B表的記錄
欄位修改，反過來B表修改時也要把A表的修改，保持兩邊資料的一個同步，這個可以在前臺很容易的實現，但開發
人員不想修改程式碼了，就考慮在後臺用trigger實現。
功能很簡單，但在實現時遇到一個問題，就是A上的DML觸發了上面的TRIGGER，然後這個TRIGGER去更新B表，這樣
就會觸發B表上的觸發器，而B表上的TRIGGER又會更新A表，這樣就迭代觸發，沒有結束了，也就是會產生變異表（mutating）
我不知道ORACLE的觸發器是否有屬性來限制這種情況的發生，但以前做SQL SERVER時知道有種Instaed of的觸發器，他表示
當DML啟動他後，他將以TRIGGER裡的程式碼來代替這個DML動作，也就是DML不會真正的執行，只會啟動INSTEAD TRIGGER，最終
執行的是TRIGGER裡面的編碼。
檢視了Docs，看到ORACLE也支援這個型別的觸發器，但這個只能建立到檢視上，不能基於表建立，我要的功能肯定是可以
實現的，在這裡我把原表進行了rename，引如了兩張檢視，名字就是以前的表名，這樣對於他們前臺應用就做了個
透明的切換，然後在兩個檢視上建立INSTEAD觸發器，將任何兩個檢視上的更新都傳播到後面的兩個基表，這樣不管你更新那個
檢視，我都可以捕獲到資料，以程式碼在後面更新，也不存在互相觸發，因為觸發器修改的物件已經轉移到表了，而此時表上是沒有
trigger的，呵呵！！！
過程如下

--建立測試表
SQL> create table mytest1(row_num number,row_name varchar2(50));

表被建立

SQL> create table mytest2(row_num number,row_name varchar2(50));

表被建立

--測試資料
SQL> INSERT INTO MYTEST1 VALUES(1,'Fuck!!!');

1 行 已插入

SQL> INSERT INTO MYTEST2 VALUES(1,'Watch your mouth!!!');

1 行 已插入

SQL> COMMIT;

提交完成

--先在一個表上建立觸發器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 BEFORE UPDATE
3 ON MYTEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /

觸發器被建立

--測試更新
SQL> set serveroutput on
SQL> UPDATE MYTEST1 SET ROW_NAME = 'DO it!!!';
DO it!!! Fuck!!!

1 行 已更新

--更新成功
SQL> SELECT * FROM MYTEST2;

ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 DO it!!!

--另外張表建立觸發器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 BEFORE UPDATE
3 ON MYTEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST1
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /

--產生了變異表，更新失敗
SQL> update mytest1 set row_name = 'mouthkkkkkoo';

update mytest1 set row_name = 'mouthkkkkkoo'

ORA-04091: table MYTEST1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'

--更新失敗
SQL> update mytest2 set row_name = 'mouthkkkkkoo';

update mytest2 set row_name = 'mouthkkkkkoo'

ORA-04091: table MYTEST2 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'

--刪除觸發器
SQL> drop trigger TRI_TEST2;

觸發器被刪掉

SQL> drop trigger TRI_TEST1;

觸發器被刪掉

--建立檢視
SQL> CREATE VIEW V_TEST1 AS SELECT * FROM MYTEST1;

檢視被建立

SQL> CREATE VIEW V_TEST2 AS SELECT * FROM MYTEST2;

檢視被建立

--基於檢視建立Instead觸發器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 INSTEAD OF UPDATE
3 ON V_TEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /

觸發器被建立

SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 INSTEAD OF UPDATE
3 ON V_TEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /

觸發器被建立

--功能已經實現
SQL> update v_test1 set row_name = 'I rock with you!!!';

1 行 已更新

SQL> commit;

提交完成

SQL> select * from v_test2;

ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 I rock with you!!!


SQL> update v_test2 set row_name = 'Don''t kick me!!!';

1 行 已更新

SQL> commit;

提交完成

SQL> select * from v_test1;

ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 Don't kick me!!!

SQL>