最近总发现用户表一些数据被删了,找不到原因,后面通过创建触发器来排查,当有操作x_yonghu表时,记录下来插入日志表中。 create or replace trigger trg_x_yonghu_log_aft_modif
最近总发现用户表一些数据被删了,找不到原因,后面通过创建触发器来排查,当有操作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;
--结束END--
本文标题: oracle创建触发器(例:当有操作x_yonghu表指定字段并且字段有修改时,插入日志表)
本文链接: https://www.lsjlt.com/news/7400.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0