Oracle DB 트리거를 통한 로깅

내가 받은 DB 테이블에 매일 아침 특정 시간마다 데이터가 주입되고 있다.

그런데 관련 히스토리가 남아있는게 없다.

어떤 USER가 정확히 몇시에 어떤 메타정보들을 가지고 주입해주고 있는지 추적하는 트리거를 짜기로 한다.

 

다음은 예시 코드들이다.

순서대로 상황에 맞게 만들어주면 된다.

운영 서버일 경우 반드시 AUTO COMMIT을 꺼두고 시작하자.

 

1. 먼저 로그가 기록될 테이블을 생성한다.

CREATE TABLE audit_log (
    audit_id NUMBER PRIMARY KEY,
    user_name VARCHAR2(30),
    os_user VARCHAR2(30),
    host VARCHAR2(50),
    ip_address VARCHAR2(50),
    operation VARCHAR2(10),
    timestamp TIMESTAMP,
    table_name VARCHAR2(30)
);

 

2. audit_id를 자동으로 채번해 줄 시퀀스를 생성한다.

CREATE SEQUENCE audit_log_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

 

3. 나의 테이블에 INSERT, UPDATE, DELETE가 되면 그걸 감지할 트리거를 작성한다.

CREATE OR REPLACE TRIGGER log_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table_name
FOR EACH ROW
BEGIN
    DECLARE
        v_ip_address VARCHAR2(50);
        v_operation VARCHAR2(10);
    BEGIN
    	-- IP 정보를 가져온다.
        SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ip_address FROM DUAL;
        
        -- 어떤 변화가 이루어졌는지 캐치한다.
        IF INSERTING THEN
            v_operation := 'INSERT';
        ELSIF UPDATING THEN
            v_operation := 'UPDATE';
        ELSIF DELETING THEN
            v_operation := 'DELETE';
        END IF;
        
        -- 아까 만들어 둔 테이블에 삽입한다.
        INSERT INTO audit_log (audit_id, user_name, os_user, host, ip_address, operation, timestamp, table_name)
        VALUES (audit_log_seq.NEXTVAL, SYS_CONTEXT('USERENV', 'SESSION_USER'), SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'HOST'), v_ip_address, 'INSERT', SYSTIMESTAMP, 'your_table_name');
    END;
END;

 

완성이다.

이제 내일 오전에 배치가 돌 때까지 기다리면 된다.

 

+) 여러 테이블에 대해 트리거를 걸고 싶으면 

따로 방법 없다. 새로 트리거를 생성하는 방법밖에.