oracle触发器审计某个表的关键列更新或行删除

1、背景

用户要求对业务的表进行审计，原因是最近有人频繁的在改相关的数据，导致业务出现问题。

2、触发器创建和使用

conn zlhis/zlhis



-- Create table

drop table 病案主页_TRIGGER_LOG;



create table 病案主页_TRIGGER_LOG

(

病人id NUMBER(18),

主页id NUMBER(5),

filedidname VARCHAR2(50),

ovalue VARCHAR2(200),

nvalue VARCHAR2(200),



optype VARCHAR2(30),

opttime date,

sessionid       VARCHAR2(10),

clientgroupinfo VARCHAR2(30),

clientuser      VARCHAR2(30),

clienipaddress  VARCHAR2(30)

);



select * from 病案主页_TRIGGER_LOG;





CREATE OR REPLACE TRIGGER TRIGGER_病案主页

  after DELETE OR UPDATE ON 病案主页

  FOR EACH ROW



DECLARE

  病人id  病案主页_TRIGGER_LOG.病人ID%
type;

  主页id  病案主页_TRIGGER_LOG.主页id%
type;

  opttime 病案主页_TRIGGER_LOG.opttime%
type := sysdate;

  optype  病案主页_TRIGGER_LOG.optype%
type;

  filedidname 病案主页_TRIGGER_LOG.filedidname%
type;

  ovalue      病案主页_TRIGGER_LOG.ovalue%
type;

  nvalue      病案主页_TRIGGER_LOG.nvalue%
type;

  SESSIONID       病案主页_TRIGGER_LOG.sessionid%
type;

  CLIENTGROUPINFO 病案主页_TRIGGER_LOG.Clientgroupinfo%
type;

  CLIENTUSER      病案主页_TRIGGER_LOG.Clientuser%
type;

  CLIENIPADDRESS  病案主页_TRIGGER_LOG.Clienipaddress%
type;



BEGIN



  select SYS_CONTEXT(
'USERENV', 
'SESSIONID') SESSIONID,

         sys_context(
'USERENV', 
'HOST') clientgroupinfo,

         sys_context(
'USERENV', 
'OS_USER') clientuser,

         sys_context(
'USERENV', 
'IP_ADDRESS') clienipaddress

    into SESSIONID, clientgroupinfo, clientuser, CLIENIPADDRESS

    from dual;



/*住院号,

姓名,

门诊医师*/



  IF  UPDATING THEN

    optype := 
'UPDATE';

    病人id    := :new.病人id;

    主页id    := :new.主页id;



    --住院号

    
if :old.住院号 <> :new.住院号 
then

       INSERT INTO 病案主页_TRIGGER_LOG VALUES (病人id,主页id,
'住院号',:old.住院号,:new.住院号,optype,opttime,SESSIONID,CLIENTGROUPINFO,CLIENTUSER,CLIENIPADDRESS);

       commit;

    end 
if;

    --姓名

    
if :old.姓名 <> :new.姓名 
then

      INSERT INTO 病案主页_TRIGGER_LOG VALUES (病人id,主页id,
'姓名',:old.姓名,:new.姓名,optype,opttime,SESSIONID,CLIENTGROUPINFO,CLIENTUSER,CLIENIPADDRESS);

      commit;

    end 
if;

    --门诊医师

    
if :old.门诊医师 <> :new.门诊医师 
then

      INSERT INTO 病案主页_TRIGGER_LOG VALUES (病人id,主页id,
'门诊医师',:old.门诊医师,:new.门诊医师,optype,opttime,SESSIONID,CLIENTGROUPINFO,CLIENTUSER,CLIENIPADDRESS);

      commit;

    end 
if;



  ELSIF DELETING THEN

    optype := 
'DELETE';

    病人id    := :old.病人id;

    主页id    := :old.主页id;

      INSERT INTO 病案主页_TRIGGER_LOG VALUES (病人id,主页id,null,null,null,optype,opttime,SESSIONID,CLIENTGROUPINFO,CLIENTUSER,CLIENIPADDRESS);

      commit;

  END IF;





EXCEPTION

  WHEN OTHERS THEN

    NULL;

END TRIGGER_病案主页;




##测试在表中的字段更新或删除均被记录到了病案主页_TRIGGER_LOG中。

3、总结

##表结构说明

create table 病案主页_TRIGGER_LOG

(

病人id NUMBER(18),  --业务表的主键列

主页id NUMBER(5),   --业务表的主键列

filedidname VARCHAR2(50),  --记录修改或者删除的字段

ovalue VARCHAR2(200),  --记录修改的原值

nvalue VARCHAR2(200),  --记录修改后的新值



optype VARCHAR2(30),  --记录是什么操作，update还是delete

opttime date,  --记录时间

sessionid       VARCHAR2(10),  --会话id

clientgroupinfo VARCHAR2(30),  --主机信息

clientuser      VARCHAR2(30),  --主机的用户

clienipaddress  VARCHAR2(30)   --ip

);




##触发器中可以根据自身需求进行调整，比如加入insert操作或者记录其他更新操作的字段，都可以使用if endif进行判断。