DB Trigger와 Function이란?

온보딩 피드백

• 팀리뷰 : 매번 특정한 요청에 따라 부수효과의 형태로 따라오는 동작의 경우 코드 레벨에서 싱크 맞추기 보다는 trigger를 활용해 보는 것도 좋을 것 같아요. 실제 회사에서 로그를 남기는 방식도 trigger시에 레코드를 json으로 저장해서 로깅합니다.( 다소 자의적 해석이 들어감 ㅎㅎ)

• 아래는 프로젝트 ERD의 일부이며, product_state 테이블에 들어오는 모든 INSERT 에 대해서 history를 남기고자 합니다.(Postgresql 기준으로 작성된 query입니다.)

  

trigger 가 뭐야?

• DML(INSERT, UPDATE, DELETE 등 데이터 조작 쿼리들) 쿼리가 동작하면 자동으로 동작하는 프로그램이다. 하나의 procedure 와 유사하다.
  작성 구조는 아래와 같다.
  

• 다분히 미들웨어 적인 성격이 강하다고 생각된다.

한번 작성해보자!

create trigger product_state_log_action
    after insert
    on product_state
    for each row
execute function insert_history();

• 1째 줄 : product_state_log_action 이라는 트리거를 만들어라~
• 2째 줄 : INSERT 이후에 동작할 것이고,
• 3째 줄 : product_state 테이블에 dml이 작동 될 시 동작 할거다.
• 4째 줄 : 행 단위로 INSERT 가 일어나면 트리거 시켜라( N행 삽입 될경우 N번~)
• 5째 줄 : 트리거는 insert_history 를 가동 시킬거야~!

function은 또 뭐야?

• 트리거 작동 예제를 보다보면 procedure 혹은 function을 execute 하는 예제들이 대다수다.

• procedure : 일련의 쿼리를 하나의 함수로 실행 할 수 있는 쿼리의 집합

  • 하나의 요청으로 여러 SQL문을 실행할 수 있습니다.
  • 서버에서 실행되기 때문에 속도가 빠릅니다.
  • 리턴 값이 있을 수도, 없을 수도 있습니다.
  • 여러개의 리턴 값을 보낼 수 있습니다.
  • 재 사용성이 좋지 않습니다.

• function : 각 프로세스를 수행하기 위해 필요한 기능

  • 리턴 값이 필수 입니다.
  • 클라이언트에서 실행되기 때문에 procedure보다 느립니다.

• 나는 function 을 사용했기 때문에 procedure는 다음에 알아보자(서버/클라이언트가 무엇인지~)
  function 작성의 구조는 아래와 같다.
  

한번 작성해 보자~

create function insert_history() returns trigger
    language plpgsql as $$
BEGIN
    INSERT INTO public.history (id, data, created_at, updated_at)
    VALUES (NEW.id, to_jsonb(NEW), NOW(), NOW());
    RETURN NEW;
END;
$$;

• 1째 줄 : insert_history라는 트리거 함수를 생성한다.
  이 함수는 트리거에서 실행되는 함수이므로 반환 타입이 TRIGGER여야 한다.
• 2째 줄 :  이 함수는 PostgreSQL의 PL/pgSQL(Procedural Language for PostgreSQL) 언어로 작성됨을 알린다.
  함수 본문을 $(블록) 안에 작성하여 **함수 코드의 시작**을 알린다.(작은 따옴표(’)로도 가능하나, 문자열 표현의 작은 따옴표와 혼동 및 이스케이프 문제가 있으므로 $ 사용을 권장 하는 것 같다.)
• 3째 줄 : BEGIN/END 형태로 시작과 끝을 명령한다.
• 4째 줄 : 히스토리 테이블에 레코드를 생성하자~
• 5째 줄 : 새로 생성될 값의 id, 레코드를 통째로 만드는 data 그리고 현재 타임스탬프를 기록한다.
  • to_jsonb : 기존에 재민님이 전부 jsonify해서 로그를 document db(키 -밸류 쌍의 db)와 유사한 형태로 간단하게 가지고 있는다고 말씀하신게 기억나서 비슷한 내장함수를 찾아 보았고 마침 적절한 것이 있었다.
  • NEW : insert, update 구문에서 들어오는 신규 값
  • OLD : update, delete 구문에서 이전의 값
• 6째 줄 : 원본 데이터는 정상으로 반환한다.
• 이후 생략~

(+)[중요치 않음] 나는 id를 auto-increment로 설정했는데..?

• 위의 코드로 실행하니, product_state 에 추가될 레코드의 id 와 동일하게 생성되는 현상이 있었다..
  • 내가 원한건 이게 아닌데,,, 어떻게 해야할까.
• 간단하다. id 구문 삭제!

  create function insert_history() returns trigger
      language plpgsql as $$
  BEGIN
      INSERT INTO public.history (id, data, created_at, updated_at)
      VALUES (NEW.id, to_jsonb(NEW), NOW(), NOW());
      RETURN NEW;
  END;
  $$;

• 성공!!
  

결론

db의 다양한 기능중 trigger 와 function에 대해서 학습해 보는 좋은 시간이었다. 이를 통해서 로그 적재 동작을 쉽게 구현하는데에 사용하고, 이외에도 부수효과를 구현하기 좋을 것 같다.