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表指定字段并且字段有修改时，插入日志表）
标签：指定字段 varchar values after use replace value uid err
本文系统来源：https://www.cnblogs.com/ysaw/p/13362110.html
【说明】：
本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！