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;
记录表修改删除日志
最新推荐文章于 2024-05-29 10:48:38 发布