PL/pgSQL 트리거 함수
PL/pgSQL 트리거 함수 (Trigger Functions)
데이터가 바뀔 때나 데이터베이스 이벤트가 발생할 때 자동으로 뭔가 실행하고 싶을 때, PL/pgSQL로 트리거 함수를 정의할 수 있어요. 트리거 함수는 CREATE FUNCTION으로 만들되, 인자가 없는 함수로 선언하고 반환 타입을 trigger(데이터 변경 트리거) 또는 event_trigger(DB 이벤트 트리거)로 둬요. 함수가 호출된 원인을 설명하는 TG_로 시작하는 특별한 로컬 변수들이 자동으로 정의돼요. 어떤 트리거 함수를 어떻게 만드는지 살펴볼게요.
출처: 공식문서
데이터 변경 트리거 (Triggers on Data Changes)
데이터 변경 트리거는 인자 없이 반환 타입이 trigger인 함수로 선언돼요. 주의할 점: CREATE TRIGGER에서 인자를 받을 걸 기대해도 함수는 인자 없이 선언해야 해요. 그런 인자들은 TG_ARGV라는 특별 변수로 전달돼요.
INSERT와 UPDATE 연산에서는 반환값이 NEW여야 해요. 트리거 함수가 NEW를 수정하면 INSERT RETURNING·UPDATE RETURNING을 지원하고, 이후 트리거에 전달되는 행 값이나 INSERT 안의 특별한 EXCLUDED 별칭 참조에도 영향을 줘요.
예제 41.3 — 데이터 변경 트리거 함수
이 예제 트리거는 테이블에 행이 삽입되거나 갱신될 때마다 현재 사용자 이름과 시간을 그 행에 찍어 줘요. 또 직원 이름이 있고 급여가 양수인지 확인해요.
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
예제 41.4 — 감사(Audit) 트리거 함수
테이블 변경을 기록하는 다른 방법은, 발생한 삽입·갱신·삭제 각각에 대해 행을 담는 새 테이블을 만드는 거예요. 테이블 변경을 감사(audit)한다고 볼 수 있어요. 이 예제는 emp 테이블의 행이 삽입·갱신·삭제될 때마다 그 사실을 emp_audit 테이블에 기록해요. 수행된 연산의 종류와 함께 현재 시각·사용자 이름을 행에 찍어요.
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
AFTER 트리거는 결과가 무시되므로 RETURN NULL을 써요. 여기서 특별 변수 TG_OP로 어떤 연산인지 판단한 뒤 OLD.*나 NEW.*를 통째로 기록하는 걸 볼 수 있어요.
예제 41.5 — 뷰 감사 트리거 함수
이 방법은 테이블 변경의 전체 감사 추적을 여전히 기록하면서도, 각 항목에 대해 감사 추적에서 유도된 마지막 수정 시각만 보여주는 단순화된 보기를 제공해요. 뷰에 트리거를 걸어 뷰를 갱신 가능하게 만들고, 뷰의 행이 삽입·갱신·삭제될 때마다 emp_audit에 기록하는 예제예요.
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
뷰에 걸린 INSTEAD OF 트리거였기 때문에, 각 연산에 맞는 ret_ 반환값(OLD/NEW)을 돌려주는 걸 볼 수 있어요.
예제 41.6 — 요약 테이블 유지 트리거 함수
이 기법은 측정·관측 데이터의 테이블(사실 테이블, fact table)이 매우 클 수 있는 데이터 웨어하우징에서 흔히 쓰여요. 이 예제는 데이터 웨어하우스의 사실 테이블을 위한 요약 테이블을 유지하는 PL/pgSQL 트리거 함수예요. 스키마는 Ralph Kimball의 The Data Warehouse Toolkit의 Grocery Store 예제를 일부 참고했어요.
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_sum numeric;
delta_qty integer;
delta_amt numeric;
...
-- figure out how much the price changed
delta_sum := NEW.amount - OLD.amount;
...
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
AFTER 트리거로 OLD와 NEW의 차이(delta)를 계산해 요약 컬럼을 증분 갱신하는 전형적인 패턴이에요.
문장 레벨(statement-level) 감사 트리거
FOR EACH STATEMENT + REFERENCING으로, 특정 연산이 일어난 행의 집합 전체(OLD TABLE / NEW TABLE)에 접근할 수 있어요. 예를 들어 삽입·갱신·삭제 각각에 대해 문장 레벨 감사 트리거를 만들면, 행 전체를 한 번에 감사 테이블에 넣을 수 있어요.
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
이벤트 트리거 (Triggers on Events)
PL/pgSQL로 이벤트 트리거도 정의할 수 있어요. 이벤트 트리거로 호출될 함수는 인자 없이 반환 타입이 event_trigger인 함수로 선언해야 해요. 이벤트 트리거로 호출되면 최상위 블록에 몇몇 특별 변수가 자동 생성되는데, 그게 바로 TG_EVENT(트리거가 발화된 이벤트, text)과 TG_TAG(트리거가 발화된 명령 태그, text)예요.
예제 41.8 — 이벤트 트리거 함수
이 예제 트리거는 지원되는 명령이 실행될 때마다 NOTICE 메시지를 하나 올려요.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
ddl_command_start 이벤트에 트리거를 걸면 DDL 명령이 시작될 때마다 snitch()가 불리고, tg_event와 tg_tag로 어떤 이벤트·어떤 명령인지 알 수 있어요.
더 알아보기 (Learn more)
- 트리거에 쓰이는 모든 특별 변수(
TG_OP,TG_ARGV,NEW,OLD등): Section 41.10.2 및 Trigger section - 트리거 생성 명령:
CREATE TRIGGER,CREATE EVENT TRIGGER - 데이터 변경 트리거와 이벤트 트리거의 차이: Chapter 38. Triggers