[mySQL] 다른 테이블의 값을 가져와 테이블에 저장하는 평균 트리거 만들기 / mysql INSERT Trigger

내가 하려는 상황 (쇼핑몰 프로젝트)

1. 고객이 후기를 남길 때 상품에 평점을 메긴다. (게시글 테이블)

2. 이 평점들의 평균을 상품 테이블의 평점 컬럼에 저장한다.

 

이 과정에서 INSERT TRIGGER를 이용하기로 했다.

DROP TRIGGER IF EXISTS `사용하는 DB이름`.`새로 만들 트리거 명`;

DELIMITER $$
CREATE TRIGGER `새로 만들 트리거 명` AFTER INSERT ON `인서트가 진행될 테이블 명` FOR EACH ROW BEGIN
	DECLARE _oldSum INT DEFAULT 0;
	DECLARE _oldCount INT DEFAULT 0;
    	DECLARE _sum FLOAT DEFAULT 0;
	DECLARE _avg FLOAT DEFAULT 0;

	IF new.rating IS NOT NULL THEN -- 평점의 값이 들어 있을 때만 트리거 실행
		SET _oldSum = ( -- 기존의 평점 합계 구하기
        		SELECT SUM(rating) FROM board 
            		WHERE category LIKE '후기' AND pcode LIKE new.pcode
            	);	
                
		SET _oldCount = (	-- 기존의 게시글 개수 구하기
        		SELECT COUNT(rating) FROM board 
            		WHERE category LIKE '후기' AND pcode LIKE new.pcode 
        	);
		
		SET _sum = _oldSum + new.rating;	-- 기존 평점 + 새로 추가하는 평점
		SET _avg = _sum / ( _oldCount + 1 );	-- 총 평점 / 기존 게시글 개수 + 1
        
        UPDATE '해당 행위로인해 변경 될 테이블' 
        SET rating = ROUND(_avg,2)	-- 평균은 소수점 2자리까지 (소수점 3번째 자리에서 반올림 후 저장)
        WHERE pcode = new.pcode ;
    END IF;
END$$
DELIMITER ;

테이블 구조 (필요한 컬럼만 발췌)

product 테이블
pcode __ PK	varchar(20)
rating	float

상품의 정보를 저장하는 product(상품 테이블)

기본키는 pcode이고, 해당 상품의 평점은 float(실수 형태)로 저장한다. _최고 5점이라 double안씀

board 테이블
pcode __ FK	varchar(20)
category	varchar(10)
rating	int

게시물의 정보를 저장하는 board(게시글 테이블)

category(카테고리구분)으로 후기글인지 문의글인지 구분하며

외래키로 product테이블의 pcode를 이용하고, 해당 상품의 평점은 int로 저장한다.

 

아래는 delemeter안쓰고도 편하게 트리거를 생성해주는 mySQL 툴을 이용했다.

 

사진 누르면 좀 크게 나와요...

빨간 네모부분이 내가 실질적으로 입력한 부분이다