最近总发现用户表一些数据被删了,找不到原因,后面通过创建触发器来排查,当有操作x_yonghu表时,记录下来插入日志表中。

create or replace trigger trg_x_yonghu_log_aft_modify

  after update OF shouji, zigezhenghao,zidianshurufa,shurufa on x_yonghu

  for each row

declare

  pri_sqlcode  varchar2(100);

  pri_sqlerrm  varchar2(4000);

  pri_username varchar2(50);

  pri_ip       varchar2(100);

  pri_host     varchar2(200);

  pri_type     varchar2(100);

begin

    pri_username := sys_context('userenv', 'session_user');

    pri_ip       := sys_context('userenv', 'ip_address');

    pri_host     := sys_context('userenv', 'host');

    pri_type     := 'update';

 case when updating('shouji') or updating('zidianshurufa') or updating('shurufa') then

    if :new.shouji != :old.shouji  or  :new.zidianshurufa != :old.zidianshurufa  or :new.shurufa != :old.shurufa then

      insert into x_yonghu_log

        (yonghuid_old,

         yiyuanid_old,

         zidianshurufa_old,

         shurufa_old,

         shouji_old,

         zigezhenghao_old,

         yonghuid_new,

         yiyuanid_new,

         zidianshurufa_new,

         shurufa_new,

         shouji_new,

         zigezhenghao_new,

         modify_host,

         modify_ip,

         modify_username,

         modify_type)

      values

        (:old.yonghuid,

         :old.yiyuanid,

         :old.zidianshurufa,

         :old.shurufa,

         :old.shouji,

         :old.zigezhenghao,

         :new.yonghuid,

         :new.yiyuanid,

         :new.zidianshurufa,

         :new.shurufa,

         :new.shouji,

         :new.zigezhenghao,

         pri_host,

         pri_ip,

         pri_username,

         pri_type);

    end if;

  end case;

exception

  when others then

    begin

      pri_sqlcode := sqlcode;

      pri_sqlerrm := sqlerrm;

      insert into x_yonghu_log

        (modify_username,

         modify_ip,

         modify_host,

         modify_type,

         errormsg_sqlcode,

         errormsg_sqlerrm)

      values

        (pri_username,

         pri_ip,

         pri_host,

         pri_type,

         pri_sqlcode,

         pri_sqlerrm);

end;

end;

 

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。
扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄