oracle监控某表变动触发器例子(监控增,删,改)

湛蓝之海

使用oracle触发器 实现对某个表的增改删的监控操作,并记录到另一个表中。
代码：

create or replace trigger test_trigger  
  before insert or update or delete on test_table  
  for each row  
declare  
  v_id    varchar2(30);  
  v_bdlb    varchar2(1);  
  v_jgdm    VARCHAR2(12);  
  v_jgmc    VARCHAR2(60);  
  v_gajgmc  VARCHAR2(60);  
  v_gajgwsmc  VARCHAR2(30);  
  v_jz    VARCHAR2(30);  
  v_ksdwsdwmc VARCHAR2(30);  
begin  
  /*  
  插入时往历史表中存放的是新插入的数据.  
  修改时往历史表中存放的是修改后的数据.  
  删除时往历史表中存放的是删除之前的数据.  
  */  
  select org_id_s.nextval into v_id from dual; -- 利用seq生成主键  
  v_jgdm   := :new.row_id;  
  v_jgmc   := :new.dept_name;  
  v_gajgmc   := :new.dept_name;  
  v_gajgwsmc := :new.bmjc;  
  v_jz     := substr(v_jgdm, 7, 2);  
  if '2' = :new.depttype then  
  v_ksdwsdwmc := 'shiju';  
  else  
  if '03' = v_jz then  
    v_ksdwsdwmc := 'zhi';  
  elsif '05' = v_jz then  
    v_ksdwsdwmc := 'xing';  
  elsif '51' = v_jz then  
    v_ksdwsdwmc := 'she';  
  else  
    v_ksdwsdwmc := 'qita';  
  end if;  
  end if;  
  if inserting then  
  v_bdlb := '1';  
  insert into test_table_h  
    (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)  
  values  
    (v_id,  
     v_bdlb,  
     v_jgdm,  
     v_jgmc,  
     v_gajgmc,  
     v_gajgwsmc,  
     v_jz,  
     v_ksdwsdwmc);  
  elsif updating then  
  v_bdlb := '2';  
  insert into test_table_h  
    (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)  
  values  
    (v_id,  
     v_bdlb,  
     v_jgdm,  
     v_jgmc,  
     v_gajgmc,  
     v_gajgwsmc,  
     v_jz,  
     v_ksdwsdwmc);  
  else  
  v_bdlb   := '3';  
  v_jgdm   := :old.row_id;  
  v_jgmc   := :old.dept_name;  
  v_gajgmc   := :old.dept_name;  
  v_gajgwsmc := :old.bmjc;  
  v_jz     := substr(v_jgdm, 7, 2);  
  if '2' = :old.depttype then  
    v_ksdwsdwmc := 'shiju';  
  else  
    if '03' = v_jz then  
    v_ksdwsdwmc := 'zhi';  
    elsif '05' = v_jz then  
    v_ksdwsdwmc := 'xing';  
    elsif '51' = v_jz then  
    v_ksdwsdwmc := 'she';  
    else  
    v_ksdwsdwmc := 'qita';  
    end if;  
  end if;  
  insert into test_table_h  
    (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)  
  values  
    (v_id,  
     v_bdlb,  
     v_jgdm,  
     v_jgmc,  
     v_gajgmc,  
     v_gajgwsmc,  
     v_jz,  
     v_ksdwsdwmc);  
  end if;  
end;