기본 패턴
sql
-- 트리거 함수 정의
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
-- 트리거 연결: INSERT/UPDATE 직전에 실행
CREATE TRIGGER trg_set_updated_at
BEFORE INSERT OR UPDATE
ON articles
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();설명
트리거는 DML 이벤트에 자동으로 반응하는 DB 레벨 훅이다 — BEFORE/AFTER/INSTEAD OF 차이
트리거는 INSERT, UPDATE, DELETE, TRUNCATE 이벤트에 반응해 미리 정의한 함수를 자동 실행한다. 실행 시점에 따라 세 가지로 나뉜다.
- BEFORE: DML 실행 전에 호출된다. 함수가
NEW를 수정해 반환하면 실제로 저장되는 값을 바꿀 수 있다.NULL을 반환하면 해당 행의 DML이 취소된다. - AFTER: DML 실행 후 호출된다. 다른 테이블을 업데이트하거나 이벤트를 발행하는 용도에 적합하다. 행 값 수정은 이미 반영된 후라 의미가 없다.
- INSTEAD OF: 뷰(View)에만 사용할 수 있다. DML을 뷰가 아닌 기반 테이블에 직접 위임할 때 사용한다.
sql
-- BEFORE 트리거: 저장 전 값 변환
CREATE OR REPLACE FUNCTION normalize_email()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.email := lower(trim(NEW.email));
RETURN NEW; -- 수정된 NEW를 반환해야 변경이 적용된다
END;
$$;
CREATE TRIGGER trg_normalize_email
BEFORE INSERT OR UPDATE OF email
ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_email();
-- AFTER 트리거: 변경 후 감사 로그 기록
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF OLD.price <> NEW.price THEN
INSERT INTO price_history(product_id, old_price, new_price, changed_at)
VALUES (OLD.id, OLD.price, NEW.price, now());
END IF;
RETURN NULL; -- AFTER 트리거는 반환값이 무시된다
END;
$$;
CREATE TRIGGER trg_log_price
AFTER UPDATE OF price
ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();
-- INSTEAD OF 트리거: 뷰에 대한 INSERT 처리
CREATE OR REPLACE FUNCTION insert_into_view()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO users(name, email) VALUES (NEW.name, NEW.email);
INSERT INTO profiles(user_id, bio) VALUES (lastval(), NEW.bio);
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_user_profile_view
INSTEAD OF INSERT
ON user_profile_view
FOR EACH ROW
EXECUTE FUNCTION insert_into_view();NEW와 OLD는 변경 전후 행을 참조하는 특수 레코드다 — INSERT/UPDATE/DELETE별 가용성
NEW와 OLD는 PL/pgSQL 트리거 함수 내에서만 사용할 수 있는 특수 레코드 변수다. DML 종류에 따라 가용성이 다르다.
| DML | OLD | NEW |
|---|---|---|
| INSERT | NULL | 삽입될 행 |
| UPDATE | 변경 전 행 | 변경 후 행 |
| DELETE | 삭제될 행 | NULL |
sql
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_old_data jsonb;
v_new_data jsonb;
v_action text;
BEGIN
v_action := TG_OP; -- 'INSERT', 'UPDATE', 'DELETE'
IF TG_OP = 'DELETE' THEN
v_old_data := to_jsonb(OLD);
v_new_data := NULL;
-- DELETE는 NEW가 없으므로 OLD를 반환
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
v_old_data := to_jsonb(OLD);
v_new_data := to_jsonb(NEW);
ELSE -- INSERT
v_old_data := NULL;
v_new_data := to_jsonb(NEW);
END IF;
INSERT INTO audit_log(table_name, action, old_data, new_data, changed_at, changed_by)
VALUES (TG_TABLE_NAME, v_action, v_old_data, v_new_data, now(), current_user);
RETURN NEW;
END;
$$;
-- 특정 컬럼 변경만 감지
CREATE TRIGGER trg_audit
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_changes();
-- 특정 컬럼 변경 시에만 트리거 발동 (UPDATE OF)
CREATE TRIGGER trg_status_change
AFTER UPDATE OF status
ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION audit_changes();TG_OP, TG_TABLE_NAME, TG_WHEN, TG_LEVEL 등 특수 변수로 트리거 컨텍스트를 조회할 수 있다.
PL/pgSQL 함수가 트리거의 실행 단위인 이유 — RETURNS TRIGGER, 언어 선택
트리거는 반드시 RETURNS TRIGGER를 반환 타입으로 선언한 함수와 연결된다. PostgreSQL은 PL/pgSQL 외에도 PL/Python, PL/Perl 등을 지원하지만, 트리거에서는 NEW/OLD 레코드와 트랜잭션 컨텍스트에 자연스럽게 접근할 수 있는 PL/pgSQL이 사실상 표준이다.
sql
-- 기본 PL/pgSQL 함수 구조
CREATE OR REPLACE FUNCTION enforce_positive_stock()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
-- 유효성 검사: 비즈니스 규칙 강제
IF NEW.stock_qty < 0 THEN
RAISE EXCEPTION 'stock_qty cannot be negative: %', NEW.stock_qty
USING ERRCODE = 'check_violation';
END IF;
-- 파생 값 자동 계산
NEW.is_available := (NEW.stock_qty > 0);
NEW.updated_at := clock_timestamp();
RETURN NEW;
END;
$$;
-- 함수 재사용: 여러 테이블에 동일 함수 연결
CREATE TRIGGER trg_products_stock
BEFORE INSERT OR UPDATE OF stock_qty
ON products
FOR EACH ROW
EXECUTE FUNCTION enforce_positive_stock();
-- PL/pgSQL 함수에서 다른 함수 호출
CREATE OR REPLACE FUNCTION sync_denormalized_count()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
UPDATE users
SET post_count = (
SELECT count(*) FROM posts WHERE author_id = NEW.author_id
)
WHERE id = NEW.author_id;
RETURN NEW;
END;
$$;행 단위(FOR EACH ROW) vs 문 단위(FOR EACH STATEMENT) 트리거의 성능 차이
FOR EACH ROW는 영향받는 행마다 한 번씩 함수를 호출하고, FOR EACH STATEMENT는 DML 문 전체에 대해 한 번만 호출한다. 행 수가 많을 때 FOR EACH ROW는 오버헤드가 크다.
sql
-- FOR EACH ROW: 1000행 UPDATE → 함수 1000번 호출
CREATE TRIGGER trg_per_row
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_change(); -- 1000번 실행
-- FOR EACH STATEMENT: 1000행 UPDATE → 함수 1번 호출
CREATE TRIGGER trg_per_stmt
AFTER UPDATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_summary(); -- 1번 실행
-- FOR EACH STATEMENT에서는 NEW/OLD가 없다 (TRUNCATE 포함)
CREATE OR REPLACE FUNCTION notify_bulk_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
-- TG_OP, TG_TABLE_NAME 사용 가능, NEW/OLD는 사용 불가
PERFORM pg_notify(
'table_changed',
json_build_object('table', TG_TABLE_NAME, 'op', TG_OP)::text
);
RETURN NULL; -- AFTER/STATEMENT 트리거는 NULL 반환
END;
$$;
-- 전환 테이블(Transition Tables)로 문 단위에서 변경 집합에 접근
CREATE TRIGGER trg_bulk_audit
AFTER UPDATE ON orders
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION bulk_audit();빠른 정리
| 상황 | 적합한 선택 |
|---|---|
| 저장 전 값을 변환해야 한다 | BEFORE + FOR EACH ROW, NEW 수정 후 반환 |
| 변경 후 다른 테이블을 갱신해야 한다 | AFTER + FOR EACH ROW |
| 뷰에 DML을 위임해야 한다 | INSTEAD OF |
| 대량 DML 후 집계만 갱신하면 된다 | FOR EACH STATEMENT |
| 특정 컬럼 변경 시에만 트리거를 발동한다 | UPDATE OF column + WHEN 조건 |
| 트리거 내에서 예외를 발생시켜야 한다 | RAISE EXCEPTION |
| 변경 전후 집합 전체를 참조해야 한다 | 전환 테이블 (REFERENCING OLD/NEW TABLE AS) |
주의할 점
BEFORE 트리거에서 NULL을 반환하면 해당 행의 DML이 조용히 취소된다. 오류 없이 무시되므로 디버깅이 어렵다. 취소를 명시적으로 표현하려면 RAISE EXCEPTION을 사용한다.
sql
-- ❌ NULL 반환으로 DML 취소 — 호출자에게 아무 알림 없음
CREATE OR REPLACE FUNCTION maybe_cancel()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF NEW.status = 'blocked' THEN
RETURN NULL; -- 이 행의 INSERT/UPDATE가 취소됨, 오류 없음
END IF;
RETURN NEW;
END;
$$;
-- 삽입 시도해도 0 rows affected, 오류 없음
INSERT INTO orders (status) VALUES ('blocked');
-- ✅ 명시적 예외로 취소 이유를 알린다
CREATE OR REPLACE FUNCTION reject_blocked()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF NEW.status = 'blocked' THEN
RAISE EXCEPTION 'Orders with status "blocked" cannot be created'
USING ERRCODE = 'check_violation';
END IF;
RETURN NEW;
END;
$$;