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进行判断。

复制