postgresql - $$, function, trigger를 이용하여 데이터 갱신 정보 갱신하기

Function, Trigger 관리는 번거롭다, 하지만...

DB를 관리하는 입장에서는
사용자가 생성한 Function과 Trigger가 많다면

이것또한 비즈니스 로직 및 시스템 구조 변경시 관리할 대상이 된다.

git으로 모듈, 코드의 버전 관리를 하는만큼 

DBA는 Function, Trigger를 관리하는 것이다. 

때문에 처음에는 최대한 내부 Function이나 Trigger를 두지 않고
차라리 history테이블을 하나 더 만드려고 했다.

하지만 테이블 field에 row가 생성된 날짜, 생성한 user 이외에

row의 데이터에 변경이 일어났을 때도 기록하기 위해

updated_at, updated_by field를 추가하려고 했더니 

postgre에는 데이터 갱신 날짜를 추적해주는 함수는 없었다.

그래서 데이터 갱신용 function과 trigger를 만들었다.

A 테이블에서 ROW 데이터를 갱신시,
언제, 누군가에 의해 갱신됐는지도 기록하려한다면

A 테이블에 ROW 데이터를 갱신시 TRIGGER 될 TRIGGER를 만들어야한다.

그리고 그 A 테이블에 걸린 TRIGGER가 실행할 FUNCTION을 만들어야한다.

1. TRIGGER가 실행할 FUNCTION에 맞는 FIELD 설정에 맞는 테이블 생성

2. TRIGGER가 실행할 FUNCTION 생성

3. TRIGGER 생성

FUNCTION의 재사용

A 테이블 이외의 B,C,D,...등의 테이블에도 똑같은 로직이 필요하다고 한다면

FUNCTION을 그대로 두고 TRIGGER를 해당 테이블에 걸어주면 된다.

$$ 표현

create or replace function update_history()

returns trigger as

$$

begin

new.updated_at = NOW();

new.updated_user = CURRENT_USER;

return new;

end;

$$ language plpgsql;

여기서 $$표현은 FUNCTION을 정의할 때 시작과 끝을 알리는 기호이다.

TRIGGER에서의 CREATE OR REPLACE

아 그리고 postgresql은

TRIGGER에서 CREATE OR REPLACE 문을 지원하지 않는다.

FUNCTION은 된다.

TRIGGER에 CREATE OR REPLACE 로직을 적용하기 위해서는

BEGIN;

    DROP TRIGGER IF EXISTS MY_TRIGGER;

    CREATE TRIGGER MY_TRIGGER

        BEFORE UPDATE

        ON MY_TABLE

        FOR EACH ROW

        EXECUTE PROCEDURE MY_FUNCTION();

COMMIT;

의 꼴로 Transaction으로 감싸주는 것이 가장 적당한 로직이라고 생각한다.