建立 Oracle 觸發 Trigger 來 Debug

有時候系統會到遇到靈異事件，
經常發生還容易查，用 r.d2+ 就大概可以 debug 出來問題在那，
偶發情況然後又只要重新再做一次又正常了，不知道到底是時間差、還是資料的問題，或是程式的 bug。
系統又沒有記錄的話，使用者刪除或修改資料是不會承認的，都是朝系統的問題來處理。

只好建立 Trigger 來記錄資料的異常歷史記錄了，看到底是誰搞的鬼。
在 TIPTOP 有提供 log_file 和 aooq040 提供 Trigger log 的查詢，但是 Trigger 要自已在 Oracle 建立。

Trigger 的語法：
CREATE OR REPLACE TRIGGER 觸發名稱
BEFORE INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
AFTER INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
FOR EACH ROW
記錄異動的資料
END;

bmd_file 異動資料的範例，把異動的欄位記錄在 log_file：
create or replace trigger bmd_file_log
--只記錄4 個欄位的異動
after insert or update or delete of bmd01,bmd02,bmd03,bmd04,bmd08 on bmd_file
for each row
declare l_col varchar2(3);
        l_user varchar2(6);
        l_type varchar2(20);
        l_cnt  number;
begin
l_col :='';
--先找有沒有資料，當執行失敗時 trigger 就會中止，所以要先找是否有資料，SID 是唯一值，SESSION有可能重複。
select count(*) into l_cnt from v$session,gbq_file where process=gbq01 and sid = userenv('sid') and process not like '%:%';
if l_cnt > 0 then
--把TIPTOP使用者和程式代號找出來
   select gbq03,gbq04 into l_user,l_type from v$session,gbq_file where process=gbq01 and audsid = userenv('sessionid') and process not like '%:%';
else
--把非TIPTOP的使用者和程式找出來
   l_user:=sys_context('userenv','os_user'); l_type:=substr(sys_context('userenv','module'),1,10);
end if ;
--新增
if inserting then
   l_col :='ins';
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
--修改就記錄修改前和修改後
if updating then
   l_col :='upd';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col||'-old',sysdate,l_user,l_type,'','');
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col||'-new',sysdate,l_user,l_type,'','');
end if;
--刪除
if deleting then
   l_col :='del';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
end;

要刪除 TRIGGER ，就用 drop trigger 名稱，就可以刪除了。