DML触发器--记录某张表的dml操作

来源：互联网发布：淘宝香港丰胸编辑：程序博客网时间：2024/05/01 15:00

CREATE TABLE logs(
         LOG_ID NUMBER(10) PRIMARY KEY,
         LOG_TABLE VARCHAR2(10) NOT NULL,
         LOG_DML VARCHAR2(10),
         LOG_KEY_ID NUMBER(10),
         LOG_DATE DATE,
         LOG_USER VARCHAR2(15),
         LOG_TERMINAL varchar2(100),
         LOG_IP_ADDRESS varchar2(20)
         );
CREATE SEQUENCE logs_id_squ INCREMENT BY 1
         START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER DML_LOG
         BEFORE --触发时间为操作前
        DELETE OR INSERT OR UPDATE -- 由三种事件触发
        ON emp
        FOR EACH ROW -- 行级触发器
        BEGIN
IF INSERTING THEN
        INSERT INTO logs        VALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER,SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','IP_ADDRESS'));
          ELSIF DELETING THEN
         INSERT INTO logs       VALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER,SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','IP_ADDRESS'));
         ELSE
             INSERT INTO logs       VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER,SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','IP_ADDRESS'));
        END IF;
        END;
/

/** 测试部分

insert into emp values(1234,'shawn','clerk',7902,sysdate,3000);
commit;
select * from emp;
select * from logs;
update emp set sal=5000 where empno=1234;
commit;
select * from emp;
select * from logs;
delete * from emp where empno=1234;
commit;
select * from emp;
select * from logs;

**/