oracle创建触发器（例：当有操作x_yonghu表指定字段并且字段有修改时，插入日志表）

• Z时代
• 2024-01-10
• 分类：综合

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

createorreplacetrigger trg_x_yonghu_log_aft_modify
  after updateOF 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";
casewhen 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
insertinto 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);
endif;
endcase;
exception
when others then
begin
      pri_sqlcode := sqlcode;
      pri_sqlerrm := sqlerrm;
insertinto 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;

以上是 oracle创建触发器（例：当有操作x_yonghu表指定字段并且字段有修改时，插入日志表） 的全部内容， 来源链接： utcz.com/z/534767.html