Quick Reference
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();트리거는 column default·constraint로 표현할 수 없는 DB-level side effect에만 쓰고, BEFORE는 row를 바꾸거나 거부할 때, AFTER는 확정된 변경을 기록할 때, INSTEAD OF는 view의 DML을 처리할 때 선택합니다. row trigger는 affected row마다 실행되므로 대량 변경 비용을 측정하고, nullable column 비교는 IS DISTINCT FROM, 연결된 INSERT의 ID는 RETURNING ... INTO로 받습니다.
문법
트리거는 DML 이벤트에 자동으로 반응하는 DB 레벨 훅이다 — BEFORE/AFTER/INSTEAD OF 차이
트리거는 INSERT, UPDATE, DELETE, TRUNCATE 이벤트에 반응해 미리 정의한 함수를 자동 실행한다. 실행 시점에 따라 세 가지로 나뉜다.
- BEFORE: DML 실행 전에 호출된다. row trigger 함수가
NEW를 수정해 반환하면 실제로 저장되는 값을 바꿀 수 있다. row trigger가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 IS DISTINCT FROM 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 $$
DECLARE
v_user_id bigint;
BEGIN
INSERT INTO users(name, email)
VALUES (NEW.name, NEW.email)
RETURNING id INTO v_user_id;
INSERT INTO profiles(user_id, bio) VALUES (v_user_id, 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();트리거와 함수는 "언제 실행할지"와 "무엇을 실행할지"로 나뉜다
트리거는 실행 시점을 정하고, 함수는 실제 로직을 담습니다. 그래서 "자동 실행이 필요한가"를 먼저 보고, 맞다면 BEFORE/AFTER/INSTEAD OF 중 시점을 고른 뒤 함수 본문을 작성하는 순서가 자연스럽습니다.
sql
-- 함수: 실제 로직
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
-- 트리거: 언제 실행할지 연결
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE
ON articles
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();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;
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);
-- AFTER trigger에서는 반환값이 무시되지만, 함수 자체는 모든 분기를 반환해야 한다.
IF TG_OP = 'DELETE' THEN
RETURN OLD;
END IF;
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;
$$;참고 링크
2 sources