oracle创建触发器(例:当有操作x_yonghu表指定字段并且字段有修改时,插入日志表)

微信扫一扫,分享到朋友圈

oracle创建触发器(例:当有操作x_yonghu表指定字段并且字段有修改时,插入日志表)

最近总发现用户表一些数据被删了,找不到原因,后面通过创建触发器来排查,当有操作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;

微信扫一扫,分享到朋友圈

oracle创建触发器(例:当有操作x_yonghu表指定字段并且字段有修改时,插入日志表)

1、MySQL常见的操作命令

上一篇

私家车价值凸显,疫情让17%的职场人想买车

下一篇

你也可能喜欢

oracle创建触发器(例:当有操作x_yonghu表指定字段并且字段有修改时,插入日志表)

长按储存图像,分享给朋友