记录表修改删除日志

connect medrec/medrec@local

-- Create table 日志记录表
create table T_LOG
(
  ID         NUMBER(8),
  HOS_NAME   varchar2(30),
  USER_NAME  varchar2(20),
  OPER_DATE  date,
  EVENT_TYPE varchar2(30),
  CONTENT    varchar2(200)
)
tablespace TSP_MEDREC
  storage
  (
    initial 40K
    next 40K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Add comments to the table 
comment on table T_LOG
  is '修改日志记录表';
-- Add comments to the columns 
comment on column T_LOG.ID
  is '序列号';
comment on column T_LOG.HOS_NAME
  is '工作站名';
comment on column T_LOG.USER_NAME
  is '操作员';
comment on column T_LOG.OPER_DATE
  is '操作时间';
comment on column T_LOG.EVENT_TYPE
  is '事件类型';
comment on column T_LOG.CONTENT
  is '内容';


-- Grant/Revoke object privileges 
grant select, insert, update, delete on T_LOG to public;


--日志记录序号
-- Create sequence 
create sequence T_LOG_ID
minvalue 10000001
maxvalue 99999999
start with 10000001
increment by 1
nocache
cycle
order;



grant select on t_log_id to public;


connect inpbill/inpbill@local
--触发器例子，记录费用明细表的删除与修改数量

CREATE OR REPLACE TRIGGER Audit_trig
 AFTER UPDATE OR DELETE ON inpbill.inp_bill_detail
FOR EACH ROW
DECLARE
    Time_now DATE;
    Terminal varchar2(20);
    Content  varchar2(200);
BEGIN
    --get current time, and the terminal(终端) of the user:
    Time_now := SYSDATE;    
    
    --Terminal := USERENV('TERMINAL');  --机器名称
    Terminal := sys_context('USERENV','IP_ADDRESS');    --IP
          
    --record new employee primary key
    IF INSERTING THEN --记录对inp_bill_detail的插入操作
       null;
      --record primary key of the deleted row:
    ELSIF DELETING THEN --记录对emp_tab的删除操作 
        
        Content := :old.patient_id ||'-' || to_char(:old.visit_id) || '-' || to_char(:old.item_no) ||'-'||:old.item_name||'-'||to_char(:old.amount);
        
        INSERT INTO MEDREC.T_LOG VALUES( MEDREC.T_LOG_ID.Nextval,Terminal,user,time_now,'DELETE',content );
        
    ELSE --记录对emp_tab的修改操作    
    
        content := :old.patient_id ||'-' || to_char(:old.visit_id) || '-' || to_char(:old.item_no) ||
                                         '-原:'||:old.item_name||'-'||to_char(:old.amount)||'-新:'||:new.item_name||'-'||to_char(:new.amount);
        
        INSERT INTO MEDREC.T_LOG VALUES( MEDREC.T_LOG_ID.Nextval,Terminal,user,time_now,'DELETE',content );

    END IF;
  
END;