各位用户为了找寻关于oracle监控某表变动触发器例子(监控增,删,改)的资料费劲了很多周折。这里教程网为您整理了关于oracle监控某表变动触发器例子(监控增,删,改)的相关资料，仅供查阅，以下为您介绍关于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;