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;