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;