PostgreSQL-트리거(trigger)

🎈개요

나는 회사 업무를 진행하다가 다음과 같이 [저장] 버튼을 누르면 4개의 쿼리가 동시 실행되는 로직을 구현하였다. 물론 우리 웹 시스템에서만 진행되는 로직이라면 문제는 없었다.
하지만 우리 시스템을 비롯한 각 기관은 웹 시스템이 존재하고, DB를 공통으로 사용하여 연계 서비스를 제공한다.

따라서 우리 시스템의 [저장] 버튼을 눌렀을 때 4개의 쿼리만 실행되는 것이 아닌, 각 기관별 시스템에서 DB 내 이벤트가 일어났을 떄 자동으로 처리해주는 기능을 바로 트리거라고 들었다.

물론 어디까지나 이건 내가 구두로 이해한 내용이고, 해당 부분에 대해서 정리해보려고 한다.

---

🎈트리거(trigger)란?

트리거는 대부분의 데이터베이스에서 이벤트에 의해 자동으로 응답하는 방법을 말한다.

DB 내 특정 테이블에 연결되어 이벤트를 수신하면 특정 코드세트를 실행시킨다. 쉽게 말해서 테이블에 INSERT , UPDATE , DELETE 등의 이벤트가 실행되는 순간 내가 원하는 동작을 자동으로 수행하는 방법이다.

---

🎈트리거 사용 예시

• 민감한 테이블에 대한 변경 감사 및 로깅 사용자가 내용을 변경하는 그 순간에, 누가, 언제, 어떤값을 변경 했는지에 대한 기록을 history 테이블에 저장하고 싶다.

• 보안적인 이유 보안상 사용자가 접근할 수 없는 테이블에 값을 추가해야 할 때 (접속한 사용자의 ip address를 수집하고 싶다.)

• 간단한 검증 & 수정 ex) 센서로부터 들어오는 데이터의 입력 시각을 초 단위까지 저장할 것이지만 초의 1의 자릿수 하위는 절삭하고 싶다. (12:01:32.123123 -> 12:01:30.000000)

나같은 경우는 이력 및 기록을 남길 때 트리거를 사용하였다. 그러나 위의 내용을 정리하면서 보안적인 부분 및 간단한 데이터의 검증과 수정을 진행할 때도 활용할 수 있다는 점이 놀랍다.

---

🎈주의사항

Q_1. 트리거가 너무 많이 존재하는 경우
A_1. 유지보수가 어려워지는 문제가 발생합니다.

Q_2. 트리거 함수의 코드가 너무 복잡한 경우
A_2. 해당 트리거가 어떠한 이벤트의 어떤 작업을 처리하는지 이해하기 어려워집니다.

Q_3. 트리거 함수의 코드 내부에 또 다른 저장 프로시저가 존재하는 경우.
A_3. 실제론 복잡한데 단순해보이게 함으로써 혼동을 주고, A_2와 비슷한 상황이 일어남.

Q_4. 트리거 함수가 재귀 순환을 하는 경우
A_4. 성능 저하의 큰 원인이 됨

Q_5. 트리거에서 반복문이 도는 경우
A_5. 단일행 트리거인 경우 복수행 입력 시 성능 저하를 일으킨다.

그렇다면 우리는 트리거를 어떻게 사용해야 하는가?

1. 해당 트리거가 왜 존재하는지, 뭘 하는지, 어떻게 동작 하는지 알 수 있도록

2. 하나의 작업당 하나의 트리거만 사용

3. 트리거는 필요에 의해서만 사용하고, 무분별한 사용은 지양

4. 가능하면 간단히 작성한다. 너무 복잡한 비즈니스 로직은 트리거에서 처리하지 않는다.

---

일단 개념적인 부분은 어느정도 이해를 했다. 그렇다면 남은건 트리거를 실제로 작성하는 방법을 알아보고 그 예시를 정리해보려고 한다.

🎈트리거 예제

1. 트리거 함수 생성

트리거 함수는 트리거가 실행될 때 호출되는 함수를 의미한다. 이 함수 안에서 원하는 동작을 정의할 수 있다.

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 여기에 원하는 동작을 작성합니다.
    RETURN NEW; -- 또는 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. 트리거 생성

• CREATE TRIGGER 문을 사용하여 트리거를 생성. 이때, 원하는 이벤트(INSERT, UPDATE, DELETE)와 실행 시점(BEFORE, AFTER)을 지정할 수 있다.
• BEFORE INSERT: 삽입하기 전에 실행
• AFTER INSERT: 삽입한 후에 실행
• BEFORE UPDATE: 업데이트하기 전에 실행
• AFTER UPDATE: 업데이트한 후에 실행
• BEFORE DELETE: 삭제하기 전에 실행
• AFTER DELETE: 삭제한 후에 실행

CREATE TRIGGER trigger_name 
BEFORE INSERT OR UPDATE OR DELETE ON table_name 
FOR EACH ROW 
EXECUTE FUNCTION trigger_function();

위의 코드에서 trigger_name은 트리거의 이름으로 고유해야 한다. table_name은 해당 테이블의 이름이며, trigger_function()은 앞서 생성한 트리거 함수의 이름을 의미.

3. 예제
다음은 예제로서 customers라는 테이블에 새로운 레코드가 삽입될 때마다 로그를 출력하는 간단한 예시이다.

-- 1. 트리거 함수 생성
CREATE OR REPLACE FUNCTION log_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'New record inserted with id %', NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 2. 트리거 생성 (AFTER INSERT)
CREATE TRIGGER insert_trigger 
AFTER INSERT ON customers 
FOR EACH ROW 
EXECUTE FUNCTION log_insert_trigger();

위 코드에서 customers라는 이름의 테이블에 새로운 레코드가 삽입될 때마다 'New record inserted with id %' 메시지와 함께 해당 레코드의 ID가 출력된다.

SQL에서 제공되는 PL/pgSQL 언어를 사용하여 복잡한 로직도 구현할 수 있으며, 필요에 따라 조건문(IF, CASE)과 반복문(LOOP, WHILE) 등을 사용할 수 있다.