PL/SQL 없이 한 테이블의 평균값을 구하여 다른 테이블에 UPDATE 하기

무려 2년전 파이널 프로젝트 때 실제 코드 작성을 시작한 이후로 내가 제일 많은 시간을 투자했던 부분은 바로 DB였다.

이 글도 당시 파이널 프로젝트 종료 직후에 삽질한게 너무 아까운 나머지 정리할 겸 비공개로 작성해놓고 

검토하기 전에 갑자기 대형 프로젝트에 들어가는 바람에 묻힐 뻔 하다가 이제서야 발굴했다.

사용자들이 어떤 상품에 대해 별점을 입력하면 해당 상품에 대한 별점 평균을 계산하고,

그 값을 다시 상품 테이블의 '평점' 컬럼 값으로 입력해주는 구조를 만들어내고 싶었다.

항상 아무 생각도 없이 썼던 기능이었고, 필요한 로직도 다 이해했으니 금방 만들겠거니 했는데 금방 만들기는 개뿔....대략 3일동안 끙끙댔던 것 같다. 

별점이 입력되는 순간 avg()를 써서 다른 테이블에 업데이트하는 트리거를 하나 만들었다. 

트리거 컴파일은 아무 문제없이 되지만 실제 사용 시 바로 mutating error......ㅠㅠㅠㅠ

인터넷을 뒤져본 결과로는 PL/SQL로 한방에 해결할 수 있는 듯 하나, 불행하게도 PL/SQL의 사용법을 숙지하지 못했던 탓에 아래와 같은 노가다를 했다.

1. 별점 테이블에 대한 복사 테이블 생성 

2. 별점 입력/수정 시 복사 테이블에 동일하게 데이터를 입력해주는 트리거 생성 

3. 별점 테이블에 입력/수정 발생 건마다 평점을 계산하여 상품 테이블에 업데이트해주는 트리거 생성 

이에 대한 쿼리는 아래와 같다

--상품 테이블

CREATE TABLE GUIDE_BOARD

(

    GUI_NO            NUMBER           NOT NULL, 

    GUI_TITLE         VARCHAR2(100)    NOT NULL, 

    GUI_CONTENT       CLOB             NOT NULL, 

    GUI_WRITER        VARCHAR2(30)     NOT NULL, 

    GUI_ENROLLDATE    DATE             NOT NULL, 

    GUI_LOC_L         VARCHAR2(10)     NOT NULL, 

    GUI_LOC_M         VARCHAR2(10)     NOT NULL, 

    GUI_LOC_S         VARCHAR2(10)     NOT NULL, 

    GUI_COUNT         NUMBER           NOT NULL, 

    GUI_POINT         NUMBER           NOT NULL,   <- 얘가 바로 문제가 된 평점!

    GUI_NOTIFY        NUMBER           NOT NULL, 

    GUI_PRICE         NUMBER           NOT NULL, 

    GUI_MAP           VARCHAR2(200)    NOT NULL,

    GUI_IMAGE VARCHAR2(100 byte),

    GUI_DATE CLOB,  

    

    CONSTRAINT GUIDE_BOARD_PK PRIMARY KEY (GUI_NO)

)

/

--별점 테이블 원본

CREATE TABLE GUIDE_POINT

(

    BOARD_NO    NUMBER    NOT NULL, 

    POINT       NUMBER    NOT NULL, 

    MB_ID VARCHAR2(30 byte)    NOT NULL

)

/

--별점 테이블의 복사 테이블 생성

CREATE TABLE GUIDE_POINT_COPY

AS SELECT * FROM GUIDE_POINT;

--별점 원본 테이블에 새로운 데이터 입력 시 복사 테이블에 동일 입력

CREATE OR REPLACE TRIGGER INSERT_POINT

AFTER INSERT ON GUIDE_POINT

FOR EACH ROW

BEGIN

 insert into guide_point_copy

 values (:NEW.board_no, :NEW.point, :NEW.mb_id);

END;

/

--별점 원본 테이블에 데이터 수정 시 복사 테이블에 동일 입력

CREATE OR REPLACE TRIGGER UPDATE_POINT

AFTER UPDATE ON GUIDE_POINT

FOR EACH ROW

BEGIN

  update guide_point_copy

  set point = :NEW.point

  where board_no = :NEW.board_no and mb_id = :NEW.mb_id;

END;

/

--별점 테이블에 insert 발생 시 평점 계산하여 상품 테이블에 update하는 트리거

CREATE OR REPLACE TRIGGER UPDATE_BOARD_POINT_1

AFTER INSERT ON GUIDE_POINT

FOR EACH ROW

FOLLOWS INSERT_POINT

BEGIN update guide_board

         set gui_point = (select avg(point) 

                             from guide_point_copy

                             group by board_no

                             having board_no = :NEW.board_no)

         where gui_no = :NEW.board_no;

END;

/

--별점 테이블에서 update 발생 시 평점 계산하여 상품 테이블에 update하는 트리거

CREATE OR REPLACE TRIGGER UPDATE_BOARD_POINT_2

AFTER UPDATE ON GUIDE_POINT

FOR EACH ROW

FOLLOWS UPDATE_POINT

BEGIN update guide_board

         set gui_point = (select avg(point) 

                             from guide_point_copy

                             group by board_no

                             having board_no = :NEW.board_no)

         where gui_no = :NEW.board_no;

END;

/

이렇게 PL/SQL 없이 트리거만으로 짤 경우, 트리거들이 모두 after statement trigger기 때문에 우선 순위를 정해주지 않으면 안되는 것 같다.

즉, 

1) 별점 원본 테이블에 데이터 입력

2) 별점 복사본 테이블에 동일 데이터 입력 트리거 작동 

3) 복사본 테이블에서 계산한 평점을 상품 테이블에 업데이트하는 트리거 작동

이라는 순서대로 움직여줘야 하는데, 자기들끼리 누가 먼저 나갈지 모르니까 제일 마지막에 작동되어야 할 트리거가 제일 먼저 튀어나가는 사태가 발생하는 것이다. 

그래서 색칠해둔 것처럼 "FOLLOWS 트리거명"절을 추가해서 어떤 트리거가 먼저 작동할지 명시해주고 나서야 원하는 결과를 얻었다. 당시에는 시간이 너무 모자라서 FOLLOWS 뒤에 복수의 트리거를 써도 되는지 테스트할 생각도 못하고 전부 별도로 생성했다....

아무튼 Oracle 11g부터는 "FOLLOWS"를 선언함으로써 여러개의 트리거 중 어떤걸 먼저 작동시킬지 지정할 수 있다는 것이 포인트