트리거 문법 기초: CREATE TRIGGER, WHEN, EXECUTE FUNCTION

PostgreSQL에서 트리거를 만들려면 다음 컴포넌트들을 정의해야 해:

• 트리거 이름.
• 이벤트 타입 (INSERT, UPDATE, DELETE).
• 실행 시점 (BEFORE 또는 AFTER).
• 트리거가 적용될 테이블.
• 실행할 함수 (PL/pgSQL이나 다른 언어로 작성).

명령어의 기본 구조는 이래:

CREATE TRIGGER 트리거_이름
[BEFORE | AFTER] {INSERT | UPDATE | DELETE}
ON 테이블_이름
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (조건)
EXECUTE FUNCTION 함수_이름();

간단한 트리거 예제

기본 students 테이블을 만들고, 새 레코드가 추가될 때 동작하는 트리거를 추가해보자.

먼저 students 테이블을 만들어보자

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

여기서 뭐가 일어나는 거냐면? 우리는 id, name, last_modified 필드가 있는 테이블을 만들었어. last_modified 필드는 레코드가 마지막으로 수정된 날짜와 시간을 저장해.

트리거는 항상 함수에 연결돼 있어. 먼저, 레코드가 추가될 때마다 last_modified 필드를 업데이트하는 간단한 함수를 만들어보자:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- last_modified 필드에 현재 날짜와 시간 설정
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

이게 무슨 마법이냐고?

1. NEW — INSERT나 UPDATE 이벤트에서 새로 들어온 행의 값을 담고 있는 특별한 변수야.
2. CURRENT_TIMESTAMP — 현재 날짜와 시간을 반환하는 함수야.
3. RETURN NEW — 수정된 행을 다음 단계로 넘겨주는 거야.

이제 트리거 자체를 만들어보자:

CREATE TRIGGER set_last_modified
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

해석해보면:

• AFTER INSERT: 새 행이 추가된 후에 트리거가 실행돼.
• ON students: students 테이블에 적용되는 트리거야.
• FOR EACH ROW: 새 행마다 트리거가 실행돼.
• EXECUTE FUNCTION: 어떤 함수를 호출할지 지정해.

트리거 동작 확인하기

트리거가 어떻게 동작하는지 확인해보자:

INSERT INTO students (name) VALUES ('Alice');
SELECT * FROM students;

아마 이런 결과가 나올 거야:

트리거가 last_modified 필드를 자동으로 업데이트했어. 마법 같지? 아니, 그냥 PostgreSQL이야.

WHEN 조건 사용하기

가끔 트리거를 항상 실행하는 게 아니라, 특정 조건에서만 실행하고 싶을 때가 있어. 이럴 때 WHEN 키워드를 써.

특정 값에만 트리거가 동작하는 예제를 보자.

예를 들어, 이름이 "Alice"인 학생에게만 트리거가 동작하게 하고 싶어. 트리거를 이렇게 바꿔보자:

CREATE OR REPLACE FUNCTION update_last_modified_condition()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified_condition
AFTER INSERT
ON students
FOR EACH ROW
WHEN (NEW.name = 'Alice')
EXECUTE FUNCTION update_last_modified_condition();

이제 트리거는 이름이 "Alice"인 학생에게만 last_modified 필드를 업데이트해.

확인해보자:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
SELECT * FROM students;

결과:

주의: "Bob" 학생의 last_modified 필드는 비어 있어. 트리거가 실행되지 않았기 때문이야.

트리거와 함수 연결: EXECUTE FUNCTION

함수는 트리거의 핵심이야. 트리거는 반드시 그 로직을 정의하는 함수가 있어야 해. PostgreSQL에서는 PL/pgSQL이나 Python, C 같은 다른 언어로 함수 작성이 가능해.

PL/pgSQL 함수 사용 예제를 들어볼게.

변경 사항을 별도의 audit_log 테이블에 기록하는 함수를 만들어보자.

먼저 audit_log 테이블을 만들자

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    student_id INTEGER NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

그리고 함수:

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation, student_id)
    VALUES ('INSERT', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

이제 트리거를 작성하자:

CREATE TRIGGER log_student_insert
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION log_insert();

그리고 동작을 확인해보자:

INSERT INTO students (name) VALUES ('Charlie');
SELECT * FROM audit_log;

아마 이런 결과가 나올 거야:

트리거가 새 레코드에 대한 로그를 자동으로 남겼어.

트리거 사용 시 실수와 주의사항

오류: 함수 없음. 함수 없이 트리거를 만들려고 하면 PostgreSQL이 에러를 내. 항상 트리거 만들기 전에 함수를 먼저 만들어야 해.

성능 문제. 트리거가 너무 많거나 함수가 복잡하면 DB가 느려질 수 있어. 신중하게 써야 해.

재귀. 트리거가 자기 자신이 적용된 테이블을 수정하면 무한 루프가 생길 수 있어. WHEN 조건을 잘 써서 이런 상황을 피하자.