ORACLE触发器记录对表的INSERT和UPDATE及DELETE操作

创建表

对表的 INSERT、UPDATE 、 DELETE 操作

建立测试源表 EMP1

CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP; 这样可以建立一个带有一定测试数据的 EMP1 表

建立修改日志表：

-- Create table create table MODI_EMP1_LOG

(

modi_time TIMESTAMP(6), table_id VARCHAR2(20), empno VARCHAR2(20), modi_type VARCHAR2(20), empname_old VARCHAR2(20), empname_new VARCHAR2(20), data_date DATE );

-- Add comments to the table comment on table MODI_EMP1_LOG is '修改 EMP1 表日志表 '; -- Add comments to the columns

comment on column MODI_EMP1_LOG.modi_time is '修改时间 '; comment on column MODI_EMP1_LOG.table_id is ' 修改的表名 '; comment on column MODI_EMP1_LOG.empno is '修改的员工号 '; comment on column MODI_EMP1_LOG.modi_type is ' 修改类型 ';

comment on column MODI_EMP1_LOG.empname_old is '修改前的员工名 '; comment on column MODI_EMP1_LOG.empname_new is '修改后的员工名 '; comment on column MODI_EMP1_LOG.data_date is '修改日期 ';

创建触发器

CREATE OR REPLACE TRIGGER MODI_EMP1_1 BEFORE UPDATE OR INSERT OR DELETE ON EMP1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

--DECLARE MODITIME TIMESTAMP; --TABLEID VARCHAR2(20); --MODI_TYPE VARCHAR2(20); --DATA_DATE DATE; BEGIN

--SELECT SYSDATE INTO MODITIME FROM DUAL; --SELECT 'EMP1' INTO TABLEID FROM DUAL; --SELECT 'UPDATE' INTO MODI_TYPE FROM DUAL; --SELECT TRUNC(SYSDATE) INTO DATA_DATE FROM DUAL; IF UPDATING THEN

IF :OLD.ENAME<>:NEW.ENAME THEN

INSERT INTO MODI_EMP1_LOG

VALUES(SYSDATE,'EMP1',:OLD.EMPNO,'UPDATE',:OLD.ENAME,:NEW.ENAME,TRUNC(SYSDATE));

--INSERT INTO MODI_EMP1_LOG

VALUES(MODITIME,TABLEID,:NEW.EMPNO,MODI_TYPE,:OLD.ENAME,:NEW.ENAME,DATA_DATE);

END IF; END IF;

IF INSERTING THEN

INSERT INTO MODI_EMP1_LOG

VALUES(SYSDATE,'EMP1',:NEW.EMPNO,'INSERT',NULL,:NEW.ENAME,TRUNC(SYSDATE));

END IF;

IF DELETING THEN

INSERT INTO MODI_EMP1_LOG

VALUES(SYSDATE,'EMP1',:OLD.EMPNO,'DELETE',:OLD.ENAME,NULL,TRUNC(SYSDATE));

END IF; END;

测试

select * from MODI_EMP1_LOG;

insert into emp1 values(8888,'test','tjob',9999,date'2018-07-10',9999,8888,66); commit; select * from MODI_EMP1_LOG; delete from emp1 where empno=8888;

commit;

select * from MODI_EMP1_LOG;

酚 亞 | A 葩 S £ u- EMPND 73« 7湖 73€9 73C-9 6BCE MO3I^/PE -UPCATE UPDATE UPDATE UPDATE IMSERT -DELETE INSLHI MODI^TIME _J| TABL EJD ll-JUL 'S n OOMMPM - EMP1 1 2 T1JUL-131200 00 OOODOD AM EMP1 J 13 JUL 10 11 S4 3J OODOOO PM EMP1 4 T3 JUL IB 115341 (XBKJOF^ EMP1 5 14^ JUL-16 IB.3S05 000000 删 EMP1 & 14-JUL-1 吕 =MP1 14-JUL-1B(B.4<1.S4.00CHXM AM iMPI 卜 7 a T*JUL78(».曲.49.000000 阳- ±MP1 $ T4-JUL-1$O&44 4$DDOOOO AM EMP1 - * JUL \"3 (B 旺 56(X)0000 胸 - -MP1 [EMPf4AME O C SMITH ■ SMITH! SMITH 12 SMITH 102 Last EbbiJ ?3t3 伽 8s&a -^PDA'E -UPCATE -3FIETF SMIlr'ifi SMITH 1^2 \"* [ togf .EkFMAVE_^£W _|QATA-CIATE 1 SLIT Hl SMTH12 SMITHia2 TSMITH192 20107^1 - 曲列 201B^