[SQL] Trigger: 이벤트를 감지하여 DB에 자동으로

Trigger

Trigger는 특정 테이블에 INSERT, DELETE, UPDATE 등의 이벤트가 발생하였을 때 데이터베이스상에서 자동 동작하도록 하는 기능이다. 서버/백엔드 개발자가 호출할 필요 없이, DB의 변화를 감지하여 자동으로 호출된다는 큰 장점을 가지고 있다.

 

필자는 trigger를 로깅에 사용하였다. 주문 정보를 담은 order 테이블에 변화가 감지되는 경우 logging 테이블에 이를 기록했는데, 특히 status에 관심을 가지고 기록하였다.

 

(코드 전체)

CREATE OR REPLACE FUNCTION log_order_status_trans()
RETURNS TRIGGER AS $$
BEGIN

    INSERT INTO public.order_status_trans_log(order_id, modified_status)
    VALUES (NEW.id, NEW.status);

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS log_order_status_trans ON public.order;


CREATE TRIGGER log_order_status_trans
AFTER INSERT OR UPDATE ON public.order
FOR EACH ROW
EXECUTE FUNCTION log_order_status_trans();

 

 

 

우선 트리거를 생성하는 아래쪽부터 설명하겠다.

 

CREATE TRIGGER log_order_status_trans

(3)AFTER (2)INSERT OR UPDATE ON (1)public.order

(4)FOR EACH ROW

(5)EXECUTE FUNCTION log_order_status_trans();

 

(1) public.order라는 테이블에 (2)INSERT 또는 UPDATE라는 이벤트가 발생한 (3)후에, (4)각 행에 대하여 (5) function을 호출한다는 뜻이다.

 

(1) 트리거를 발생시키는 테이블명

(2) 이벤트 (INSERT/UPDATE/DELETE)

(3) 트리거 발동 시점 (BEFORE/AFTER)

(4) 트리거 발동 기준 (FOR EACH ROW/FOR EACH STATEMENT)

: 변화가 생기는 각 행에 대해 트리거를 발동할 것인지, 변화를 야기한 명령문에 대해 트리거를 발동할 것인지 (예를 들어 하나의 명령문으로 1000개의 row를 생성하는 경우 row 기준일 때는 1000번의 트리거가, statement 기준일 때는 1번의 트리거가 발동된다.)

(5) 위에 작성한 함수명

(6) 같은 이름으로 작성해놓은 트리거가 있다면 제거

 

 

 

함수 코드에 대해서도 설명하겠다.

(7)CREATE OR REPLACE FUNCTION log_order_status_trans()

RETURNS TRIGGER AS $$

(9)BEGIN

    (11)INSERT INTO public.order_status_trans_log(order_id, modified_status)

    VALUES (NEW.id, NEW.status);

        

    RETURN (12)NEW;

(10)END;

$$ LANGUAGE (8)plpgsql;



(6)DROP TRIGGER IF EXISTS log_order_status_trans ON public.order;

 

(7) 해당 이름을 가진 함수가 이미 있다면 대체

(8) plpgsql로 작성한 함수이다. (Plpgsql/sql)

(9) begin과 (10) end 사이에 (11) 원하는 명령문을 작성한다.

(12) trigger의 경우 어떤 변화이냐에 따라 변화 전/후 값을 OLD/NEW 키워드를 통해 받아올 수 있다.

예를 들여 이 코드에서는 order에 새로 들어온/update된 이후의 id와 status를 NEW.id, NEW.status를 통해 받아온 것이다.

이벤트	OLD 사용 가능 여부	NEW 사용 가능 여부
INSERT	X	O
UPDATE	O	O
DELETE	O	X

 

트랜잭션과의 연결점

트리거를 발동하는 명령문을 입력한 후, 해당 트랜잭션을 rollback시킨다면 트리거로 인해 입력된 데이터는 어떻게 될까?

 

트리거로 인해 발생하는 함수는 트리거를 발동하는 INSERT/UPDATE/DELETE와 같은 트랜잭션 안에 있기 때문에,

Order 테이블에서 rollback으로 인해 row가 사라지게 되면 order_logging 테이블에서도 사라지게 된다.

 

(트리거 안에서는 rollback, commit를 사용할 수 없다고 한다.)