트리거로 작업 자동화하기

상상해봐, 네가 대형 데이터베이스의 관리자야. 갑자기 누가 테이블에서 중요한 정보를 지워버렸고, 모두가 "누가 한 거야?!"라고 소리쳐. 이런 상황을 피하려면, 데이터베이스에서 변경 사항을 기록하고 무슨 일이 있었는지 추적할 수 있어야 해. 이게 바로 로그와 감사(audit)의 역할이야.

• 변경 로그는 무슨 일이 일어났는지, 어떤 레코드가 어떻게, 언제 바뀌었는지 히스토리를 저장해줘.
• 데이터 감사는 더 깊은 검증을 위해, 변경뿐만 아니라 누가 그걸 했는지까지 기록해.

이제 "왜"가 이해됐으니, "어떻게" 하는지 배워보자.

로그 테이블 만들기

트리거를 설정하기 전에, 변경 로그를 저장할 테이블이 필요해. 예시를 볼게:

-- 변경 로그를 위한 테이블 생성
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- 레코드의 유니크 아이디
    table_name TEXT NOT NULL,        -- 변경이 일어난 테이블 이름
    operation TEXT NOT NULL,         -- 작업 타입: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- 변경 시간
    old_data JSONB,                  -- 변경 전 데이터 (UPDATE/DELETE용)
    new_data JSONB                   -- 변경 후 데이터 (INSERT/UPDATE용)
);

여기서 무슨 일이 일어나냐면:

1. log_id — 각 로그 레코드의 유니크 아이디야.
2. table_name — 어떤 테이블이 바뀌었는지 기록해.
3. operation — 작업 타입: INSERT, UPDATE, DELETE 중 하나야.
4. change_time — 변경된 정확한 시간을 저장해.
5. old_data 와 new_data — 변경 전/후 데이터를 JSON 형식으로 저장해.

트리거로 변경 로그 남기기

이제 로그 테이블이 있으니까, students 같은 테이블에 트리거를 만들어보자. 이 트리거는 학생 추가, 수정, 삭제 등 모든 변경을 기록할 거야. 어떻게 할 거냐면:

1. 로그 테이블에 레코드를 추가하는 PL/pgSQL 함수를 작성할 거야.
2. students 테이블에 트리거를 만들 거야.

함수는 작업 정보(INSERT, UPDATE, DELETE)와 변경된 데이터(OLD와 NEW)를 받아.

-- 변경 로그를 기록하는 함수
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- INSERT 작업 로그
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- DELETE 작업 로그
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- UPDATE 작업 로그
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- AFTER 트리거라서 NULL 반환
END;
$$ LANGUAGE plpgsql;

여기서:

• TG_OP — 현재 작업(INSERT, UPDATE, DELETE)이 들어있는 특별한 변수야.
• row_to_json(OLD) 와 row_to_json(NEW) — 행 데이터를 JSON으로 바꿔서 저장해.
• RETURN NULL — AFTER 트리거라서 변경된 데이터를 반환하지 않아.

이제 이 함수를 students 테이블에 연결하자.

-- 변경 로그를 위한 트리거 생성
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

여기서 무슨 일이냐면:

• AFTER INSERT OR UPDATE OR DELETE — students 테이블에서 해당 작업이 끝난 후 트리거가 실행돼.
• FOR EACH ROW — 변경된 각 행마다 트리거가 실행돼.
• EXECUTE FUNCTION log_student_changes() — 우리가 만든 로그 함수가 호출돼.

트리거 테스트하기

이제 트리거가 잘 동작하는지 테스트해보자.

1. 새 레코드 삽입

INSERT INTO students (name, age, grade)
VALUES ('오토 린', 20, 'A');

로그 테이블에 뭐가 기록됐는지 보자:

SELECT * FROM change_logs;

예시 결과:

2. 레코드 업데이트

UPDATE students
SET grade = 'B'
WHERE name = '오토 린';

다시 로그 테이블을 확인해보자:

SELECT * FROM change_logs ORDER BY change_time DESC;

결과:

3. 레코드 삭제

DELETE FROM students
WHERE name = '오토 린';

다시 로그를 확인해보자:

실제 사용 예시

1. 중요 테이블 작업 로그: 예를 들어, 은행 계좌 테이블은 모든 변경을 기록해서 사기를 막아야 해.
2. 시스템 감사: 규정 준수나 사용자 활동 분석을 위해 기록을 저장할 수 있어.
3. 데이터 복구 보장: 누가 실수로 데이터를 지워도, 로그 테이블에서 복구할 수 있어.

특징과 주의할 점

트리거로 로그를 남길 때 성능을 꼭 고려해야 해. 트리거가 너무 자주 실행되면 DB에 부담이 커질 수 있어. 그래서:

• 정말 중요한 테이블에만 로그를 남겨.
• 로그가 너무 많아지면, 아카이빙 전략을 세워야 해.

트리거는 기타 줄처럼, 잘 조율하면 멋진 자동화와 데이터 통제를 해주지만, 세심한 관리가 필요해!