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; 

到此这篇关于oracle监控某表变动触发器例子(监控增,删,改)就介绍到这了。积极向上的心态，是成功者的最基本要素。更多相关oracle监控某表变动触发器例子(监控增,删,改)内容请查看相关栏目，小编编辑不易，再次感谢大家的支持！