[Oracle] 트리거를 이용하여 로그 테이블 기록하기. Trigger Log Table

오라클 데이터베이스에서 유지보수를 하다보면, 데이터의 수정과 삭제가 어떻게 이루어지고 있는지 즉시 확인하고 싶은 경우가 있습니다.

오늘은 트리거를 이용하여 로그 테이블에 데이터를 기록하는 방법에 대해 알아보도록 하겠습니다~ 로그 테이블 스크립트와 트리거 스크립트를 작성해 보도록 하겠습니다.

트리거 로그 테이블

오라클 로그 테이블을 만들어 봅시다. 사용자가 로그를 볼 수 있도록 추가해 보는 것이 목표랍니다~

기록을 남길 테이블은 EMP 테이블이며, 로그 테이블 구성은 본래의 테이블 컬럼과 함께, 터미널, 아이피, 로그데이트, 타입을 남기도록 하겠습니다.

--기존 테이블 컬럼
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)

--로그 관련 컬럼
  LOG_TERMINAL  VARCHAR2 (100 BYTE),
  LOG_IPADDRESS VARCHAR2 (100 BYTE),
  LOG_DATE      DATE DEFAULT SYSDATE,
  LOG_TYPE      VARCHAR2 (1 BYTE)

그리하여 로그 테이블은 트리거 대상 테이블보다 컬럼 개수가 많게 설정합니다.

--로그 테이블 전체 컬럼
  EMPNO          NUMBER(4),
  ENAME          VARCHAR2(10 BYTE),
  JOB            VARCHAR2(9 BYTE),
  MGR            NUMBER(4),
  HIREDATE       DATE,
  SAL            NUMBER(7,2),
  COMM           NUMBER(7,2),
  DEPTNO         NUMBER(2),
  LOG_TERMINAL   VARCHAR2(100 BYTE),
  LOG_IPADDRESS  VARCHAR2(100 BYTE),
  LOG_DATE       DATE DEFAULT SYSDATE,
  LOG_TYPE       VARCHAR2(1 BYTE)

로그 테이블

CREATE TABLE user_name.log_table_name
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2),
  LOG_TERMINAL  VARCHAR2 (100 BYTE),
  LOG_IPADDRESS VARCHAR2 (100 BYTE),
  LOG_DATE      DATE DEFAULT SYSDATE,
  LOG_TYPE      VARCHAR2 (1 BYTE)
)
TABLESPACE tablespace_name
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 64 M
         NEXT 1 M
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE user_name.log_table_name IS '로그 테이블';
COMMENT ON COLUMN user_name.log_table_name.LOG_TERMINAL IS '로그 단말';
COMMENT ON COLUMN user_name.log_table_name.LOG_IPADDRESS IS '로그 주소';
COMMENT ON COLUMN user_name.log_table_name.LOG_DATE IS '로그 일시';
COMMENT ON COLUMN user_name.log_table_name.LOG_TYPE IS '로그 유형';

로그 트리거

CREATE OR REPLACE TRIGGER tr_log_table_name
   AFTER DELETE OR UPDATE
   ON user_name.emp
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   L_V_IPADDRESS   VARCHAR2 (100);
   L_V_TERMINAL    VARCHAR2 (100);
   L_V_LOGTYPE     VARCHAR2 (1);
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
          SYS_CONTEXT ('USERENV', 'TERMINAL')
     INTO L_V_IPADDRESS, L_V_TERMINAL
     FROM DUAL;

   IF UPDATING
   THEN
      L_V_LOGTYPE := 'U';
   ELSE
      L_V_LOGTYPE := 'D';
   END IF;

    Insert into user_name.log_table_name
       (EMPNO, 
        ENAME, JOB, MGR, HIREDATE, SAL, 
        COMM, DEPTNO,
        LOG_TERMINAL, LOG_IPADDRESS, LOG_DATE, LOG_TYPE)
     Values
       (:OLD.EMPNO, 
        :OLD.ENAME, :OLD.JOB, :OLD.MGR, :OLD.HIREDATE, :OLD.SAL, 
        :OLD.COMM, :OLD.DEPTNO,
        L_V_TERMINAL, L_V_IPADDRESS, SYSDATE, L_V_LOGTYPE);

EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

트리거 예제

수정, 삭제 이후 OLD 값을 기록하도록 합니다. 변경 이후 값은 본래 테이블에 갱신되기 때문에 NEW 값을 별도로 기록할 필요는 없을 것입니다.

다음과 같이 로그 트리거 스크립트를 확인하기 위한 테스트를 진행하겠습니다. 트리거 확인 차원에서 현재 데이터의 일부 값을 변경해 봅시다. 그리고 로그 테이블을 조회해 봅시다.

UPDATE EMP
   SET EMPNO = EMPNO
 WHERE EMPNO = 7839;

1 row updated.

로그 테이블에 수정 내역을 확인할 수 있답니다. 수정 이전 내역을 로그 테이블에서 다음과 같이 확인할 수 있습니다.

SELECT * FROM LOG_TABLE_NAME;

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO LOG_TERMINAL    LOG_IPADDRESS
 LOG_DATE LO
----------
      7839 KING                 PRESIDENT                     96/11/17       5000                    10 TERMINAL
 99/12/31 U