融會貫通學習trigger

很多的東西在工作中用到的時候才能理解深刻，有些東西停留在理論層面而不去實踐，就不會真正理解。
昨天寫了一個很簡單的trigger，但是中間也費了一些周折。
系統中碰到一個很嚴重的問題，一個資料處理引擎是基於表驅動設計的，裡面的一個表中已經pending了很多的事務資訊，對系統造成了嚴重的影響，為了第一時間排查這個問題，同事為了避免對目前的事務處理的進一步影響。討論最後決定，我們需要修改一個欄位的值，把它改為一個不被系統設別的值，這樣新的事務資訊就不會對目前的問題排查工作所影響。
這個時候需要dba要做的就是每一個新增的事務都需要修改那個欄位的值，比如我們設定欄位為source_comp_id，這個欄位的值為2003的時候就被系統設別，能夠進一步處理，如果設定為203，系統就不捨別，就不會處理，我們現在要做的就是把每一個新增的事務的source_comp_id值都去掉一位。
這個用trigger實現還是很自然的，但是因為這個表是生產的一個很大的分割槽表，自己分析和考慮的時候就走了一些彎路。
這個過程中也對trigger有了進一步的學習。發現自己很多以前的東西都忘記了。

首先從效能的角度考慮，能不能從欄位的級別進行觸發，類似下面的樣子。
CREATE TRIGGER pub_source_code_chg_trg
before insert of source_comp_id
ON test_number for each row
begin
.....
end
/

馬上得到了錯誤，所以基於列的修改，insert還是不支援的。還有delete也是同樣的問題，update就可以。
before INSERT of SOURCE_COMP_ID
              *
ERROR at line 2:
ORA-04073: column list not valid for this trigger type
好了繼續學習。
接著我寫了如下的trigger:
create or replace trigger pub_source_code_chg_trg 
after insert
on trb1_pub_log
for each row
begin
if (:new.source_comp_id =2003) then
 update pub_log  set source_comp_id = 203,buffer_id=-1  where source_comp_id=:new.SOURCE_COMP_ID and PUB_TRX_ID=:new.PUB_TRX_ID and BUFFER_ID=:new.BUFFER_ID;
 commit;
end if;
end;
/
看似很簡單，已經完成了。
做了一個簡單的Insert操作。就報瞭如下的錯誤。看來建立好了只是開始，還不能用。
insert into app_tmp.pub_log select *from test_pub_log where source_comp_id=2003 and rownum<2
                  *
ERROR at line 1:
ORA-04091: table APP_TMP.PUB_LOG is mutating, trigger/function may not see
it
ORA-06512: at "APP_TMP.PUB_SOURCE_CODE_CHG_TRG", line 3
ORA-04088: error during execution of trigger
'APP_TMP.PUB_SOURCE_CODE_CHG_TRG'
最後發現是同一個表insert的同時做update會有問題，果斷加了自治事務的部分。
create or replace trigger pub_source_code_chg_trg 
after insert
on trb1_pub_log
for each row
declare
 pragma autonomous_transaction; 
begin
if (:new.source_comp_id =2003) then
 update pub_log  set source_comp_id = 203,buffer_id=-1  where source_comp_id=:new.SOURCE_COMP_ID and PUB_TRX_ID=:new.PUB_TRX_ID and BUFFER_ID=:new.BUFFER_ID;
 commit;
end if;
end;
/
又做了簡單的Insert，看似就沒有問題了，但是資料有問題。
--&gt做insert操作前。有9條記錄都是2003.
SQL> select source_comp_id from trb1_pub_log;
SOURCE_COMP_ID
--------------
          2003
          2003
          2003
          2003
          2003
          2003
          2003
          2003
          2003
9 rows selected.

insert into app_tmp.pub_log select *from test_pub_log where source_comp_id=2003 and rownum<3
2 rows created.
沒有提交，結果source_comp_id就自動改變了。
SQL>  select source_comp_id from trb1_pub_log;
SOURCE_COMP_ID
--------------
           203
           203
           203
          2003
          2003
          2003
          2003
          2003
          2003
          2003
          2003
最後發現還是這個自治事務的問題，在insert的過程中，所做的Update修改的資料不是insert的資料。
這個也是自治事務不是很常用的原因。

還是先來實現目標才考慮其他的因素吧。
下面的這個triggr就實現了新增事務的欄位值修改。

CREATE OR REPLACE TRIGGER trb_pub_source_code_chg_trg
before  INSERT 
ON trb1_pub_log for each row
begin
    if (:new.source_comp_id =2003)
    then
      :new.source_comp_id := 203;
      :new.buffer_id:=-1  ;
    end if;
end;
/
但是還有一個問題。報了ora-14402的問題，這個時候可以啟用row movement就可以了。
insert into app_tmp.pub_log select *from  test where source_comp_id=2003  and  rownum<3
                    *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
ORA-06512: at "APP_TMP.PUB_SOURCE_CODE_CHG_TRG", line 5
ORA-04088: error during execution of trigger
'APP_TMP.PUB_SOURCE_CODE_CHG_TRG'

做了好幾輪測試，考慮了很多的因素，都沒有發現問題。
所以大道至簡，這也給自己好好上了一課。