湛蓝之海 发表于 2022-1-27 18:23:12

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_gajgwsmcVARCHAR2(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;


https://www.uoften.com/dbs/oracle/20180415/73153.html
页: [1]
查看完整版本: oracle监控某表变动触发器例子(监控增,删,改)