[PostgreSQL] 트리거 변경을 자동으로 잡아 감사 로그를 남긴다

설치·접속: PostgreSQL 설치와 접속

부제: 가격이 바뀔 때마다 애플리케이션 코드가 잊지 않고 이력을 기록하길 바라지 말고, 테이블 자체가 UPDATE를 감지해 자동으로 감사 로그를 쌓게 하고 싶을 때

CREATE TABLE m10_prod  (id int PRIMARY KEY, name text, price numeric);
CREATE TABLE m10_audit (at timestamptz DEFAULT now(),
                        action text, prod_id int, old_price numeric, new_price numeric);

-- 변경 이력을 남기는 트리거 함수. TG_OP로 어떤 연산인지 안다
CREATE FUNCTION m10_log_price() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'UPDATE' AND NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO m10_audit(action, prod_id, old_price, new_price)
    VALUES ('UPDATE', NEW.id, OLD.price, NEW.price);
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO m10_audit(action, prod_id, new_price) VALUES ('INSERT', NEW.id, NEW.price);
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO m10_audit(action, prod_id, old_price) VALUES ('DELETE', OLD.id, OLD.price);
    RETURN OLD;
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_m10_price
  AFTER INSERT OR UPDATE OR DELETE ON m10_prod
  FOR EACH ROW EXECUTE FUNCTION m10_log_price();

INSERT INTO m10_prod VALUES (1,'keyboard',30), (2,'mouse',15);
UPDATE m10_prod SET price = 35 WHERE id = 1;
UPDATE m10_prod SET name  = 'mouse pro' WHERE id = 2;  -- 가격 그대로 → 로그 안 남음
DELETE FROM m10_prod WHERE id = 2;

SELECT action, prod_id, old_price, new_price FROM m10_audit ORDER BY at;

 action | prod_id | old_price | new_price 
--------+---------+-----------+-----------
 INSERT |       1 |           |        30
 INSERT |       2 |           |        15
 UPDATE |       1 |        30 |        35
 DELETE |       2 |        15 |          
(4 rows)

트리거는 특정 테이블에 INSERT/UPDATE/DELETE가 일어날 때 DB가 지정한 함수를 자동 실행하는 장치다. 함수 안에서 OLD(변경 전 행)와 NEW(변경 후 행)를 모두 볼 수 있어 "무엇이 무엇으로 바뀌었는지"를 그대로 기록할 수 있다. 핵심은 애플리케이션이 어디서 접근하든 이 로직이 빠짐없이 걸린다는 점이다. 위에서 이름만 바꾼 UPDATE가 로그를 남기지 않은 건 IS DISTINCT FROM으로 가격이 실제 달라졌을 때만 기록하게 했기 때문이다. 감사 추적, 파생 컬럼 자동 계산, 무결성 강제에 두루 쓴다.

이렇게도 쓴다

행마다가 아니라 문장 단위로 한 번만 실행한다. (조합: FOR EACH STATEMENT)

CREATE TRIGGER trg_stmt AFTER UPDATE ON m10_prod
  FOR EACH STATEMENT EXECUTE FUNCTION m10_log_price();

 

값을 저장 전에 가공한다(변경 전 개입). (조합: BEFORE + NEW 수정)

CREATE FUNCTION m10_touch() RETURNS trigger AS $$
BEGIN NEW.name := lower(NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_before BEFORE INSERT ON m10_prod
  FOR EACH ROW EXECUTE FUNCTION m10_touch();

 

특정 컬럼이 바뀔 때만 발동한다. (조합: WHEN 조건)

CREATE TRIGGER trg_price_only AFTER UPDATE OF price ON m10_prod
  FOR EACH ROW WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION m10_log_price();

 

트리거를 잠시 껐다 켠다(대량 적재 시 성능 확보).

ALTER TABLE m10_prod DISABLE TRIGGER trg_m10_price;
ALTER TABLE m10_prod ENABLE  TRIGGER trg_m10_price;

 

테이블에 걸린 트리거를 확인한다. (조합: \d)

\d m10_prod   -- 하단 Triggers: 목록에 표시