[이것이 MYSQL이다] 스토어드 프로시저, 스토어드 함수, 커서, 트리거에 대하여 정리

본 포스트는 2021년에 업로드된 포스트입니다. 개념이 아직 정리안된 나이에 업로드한 포스트이기에 수정사항이 있으면 연락주세요.

들어가기에 앞서

스토어드 프로시저/함수, 커서, 트리거는 mysql 데이터베이스에서 많이 쓰이는 문법이다.

 

위 개념을 잘 알아두면 MYSQL에서 쿼리문을 작성할때 보다 효율적이고 MYSQL에서 더 많은 처리를 수행할 수 있을것이다.

 

이제부터 위의 중요한 개념을 하나씩 설명하겠다.

스토어드 프로시저

스토어드 프로시저는 쉽게 설명하면 MYSQL에서 쿼리문을 모듈화 해놓을 수 있는 기능이다.

 

실제로 프로그래밍 언어에서 모듈화가 왜 필요한지 생각해보자.

 

모듈화를 하면 중복되는 코드를 하나의 파일이나 함수로 만들어서 호출로 간편하게 사용할 수 있기에 코드의 가독성이 높아지며 또한 중복되는 코드를 모듈화 해놓으면서 코드의 중복도가 낮아지고 같은 처리를 하나의 함수로 만들어놓기때문에 시스템의 효율성이 조금 더 높아질 수 있는 장점이 있다.

 

위의 모듈화의 장점을 보면 알 수 있듯이 프로시저의 장점은 바로 이러한 모듈화의 장점이랑 같다,

 

중복되는 쿼리문을 줄일 수도 있고 그로인에서 시스템의 효율성이 높아질 수 있다는 장점이 있으므로 많은 DBA 개발자가 사용하고 있다.

 

프로시저는 중복되는 쿼리문을 하나의 이름으로 규정해서 묶는 기능이므로 쿼리문이 어떠한 처리에서 한번만 쓰인다면 프로시저를 사용할 필요가 없다.

 

프로시저를 잘못된 곳에 사용하게 된다면 쿼리문 처리 성능을 오리혀 낮출 수 있으므로 잘 생각해서 꼭 필요한 곳이 프로시저를 작성해야 한다.

 

프로시저를 사용하는 방법은 아래와 같다.

DELIMITER $$
CREATE PROCEDURE proc_name (
	IN params,
	OUT params
)
BEGIN
	QUERY
END $$
DELIMITER ;

call proc_name(params ...);

 

위는 mysql documention에 있는 프로시저 사용방법 코드를 요약한것이다.

 

프로시저에서는 IN이라는 형식으로 프로시저에 사용되는 파라미터를 지정할 수 있다.

 

반대로 OUT은 반환되는 값의 파라미터를 지정하는 것이다.

 

BEGIN — END 사이에서 프로시저안에서 동작될 쿼리문을 작성한다.

 

DELIMITER라는 문은 구분기호를 바꿔주는 역할이다.

 

프로시저의 생성의 사용되는 구분기호는 쿼리문의 종료문자와 동일하게 세미콜론이므로 쿼리문 처리에서 같은 구분기호를 사용하여서 오류를 발생 시킬 수 있다.

 

따라서 처음 프로시저를 생성할때 $$(가장 표준으로 많이 사용)기호로 프로시저 생성의 구분기호를 바꿔준다음 다시 세미콜론으로 기호를 변경시켜주어야 정상적으로 쿼리문이 동작하게 된다. (변경하지 않을시 $$ 기호는 알 수 없는 기호라고 인식한다.)

 

생성된 프로시저를 호출할떄는 앞에 CALL이라는 문을 사용하고 뒤에 호출할 프로시저 이름을 작성하면 된다. (프로그래밍 언어에서의 메서드 호출과 호출방식이 비슷하다.)

 

참고로 프로시저를 삭제할때는 DROP PROCEDURE를 사용해서 프로시저를 삭제 할 수있다.

DELIMITER $$
CREATE PROCEDURE testPROC(
	IN id VARCHAR(10),
)
BEGIN
	SELECT * FROM userTBL WHERE userID = id;
END $$
DELIMITER ;

CALL testPROC("TEST");

 

간단하게 IN(파라미터)를 이용한 프로시저를 작성하였다.

 

id라는 파라미터명을 등록하고 뒤에 해당 파라미터의 타입(형식)을 지정해주어야 한다.

 

쿼리문에서는 userTBL에서 파라미터인 id와 일치한 row를 찾아서 출력해주는 프로시저이다.

 

프로시저를 생성하였으면 CALL을 이용해서 생성한 프로시저명을 입력하고 괄호안에는 생성한 프로시저에서 id 파라미터의 들어갈 값을 지정해주어야 한다.

 

위와 같이 실행한다면 이제 TEST와 유저아이디가 일치하는 row를 찾아서 출력해 줄 것이다.

 

이번에는 OUT을 이용한 코드를 확인해본다.

DELIMITER $$
CREATE PROCEDURE testPROC(
	IN id VARCHAR(10),
	OUT testHeight INT,
)
BEGIN
	SELECT heigiht INTO testHeight FROM userTBL WHERE userID = id;
END $$
DELIMITER ;

CALL testPROC("TEST", @testHeight);

SELECT @testHeight;

 

위는 원래 id값에 맞는 행을 출력해주는 프로시저를 응용한것이다.

 

현재 OUT이라는 반환형식의 이름을 testHeight로 지정하였다.

 

또한 쿼리문안에서 id와 일치한 row중 height라는 컬럼의 값을 지정하였던 testHeight에 할당한다.

 

이렇게 되면 현재 testHeight라는 반환 파라미터에는 위의 height 값이 담겨있을 것이다.

 

이제 호출할때 @testHeight(@는 변수명을 의미한다.)라는 파라미터를 추가로 전달하고

 

SELECT @변수명을 이용해서 반환받은 값을 출력해서 확인 할 수 있다.

 

이처럼 스토어드 프로시저는 기본개념은 그리 어렵지 않지만 활용하는 부분이 어렵다.

 

모든 MYSQL문에서는 문법을 배우는 것은 그리 어렵지 않다.

 

하지만 배운 문을 활용해서 실제 개발에 적용하는것이 가장어렵다.

 

만약 실제 개발에 배운 쿼리문을 적용하지 못한다면 아직까지 해당 개념에 대한 이해가 부족한것이므로 계속해서 개발에 적용될 수 있는 부분을 생각해야 보다 효율적이게 습득한 지식을 사용할 수 있을 것이다.

 

스토어드 프로시저가 대표적인 예시인 이유는 프로시저는 문법은 정말 간단하지만 BEGIN안에 들어갈 쿼리문 사용되어야 할 파라미터 등을 생각을 해주어서 사용을 하지않으면 스토어드 프로시저를 사용할 수 없을 것이다.

 

따라서 앞으로는 계속해서 배운 개념들을 어디에 적용될 수 있을 것인지 생각을 해봐야 한다.

스토어드 함수

MYSQL에서는 내장함수가 있다는 것과 집계함수가 있다는 것을 잘 알고 있을것이다.

 

하지만 이러한 내장함수만으로는 구현할 수 있는 바운더리가 좁아질 수 있고 자신의 서비스의 쿼리문에서만 효율적으로 사용될 수 있는 새로운 함수를 지정해서 사용하고 싶을 수도 있을 것이다.

 

스토어드 함수는 사용자 지정 함수를 일컷는다.

 

따라서 사용자가 새롭게 함수를 정의하고 사용할 수 있도록 해준다.

 

스토어드 함수를 쓰게 된다면 어떠한 서비스 쿼리문에서만 엄청나게 효율적인 함수를 구현하게 된다면 DBA 개발자가 계속해서 엄청나게 긴 함수 쿼리문을 일일히 복사하고 실행하지 않아도 될 것이다.

 

이처럼 스토어드 함수는 사용자가 MYSQL에서 보다 효율적인 처리를 할 수 있도록 사용자 지정함수를 만들어 사용하는 것을 허용하였다.

 

언뜻보면 스토어드 함수와 스토어드 프로시저의 차이점이 명확하게 구분이 안갈 수도 있다.

 

하지만 이 둘은 엄청난 차이점이 있다.

 

함수와 프로시저의 차이점을 정리하면 아래와 같다.

 

IF STORED PROCEDURE STORED FUNCTION

CALL로 호출	O	X
스토어드 내에서 동작하는 쿼리문	O	X
쿼리문안에서 호출하여 사용	X	O
데이터의 어떠한 계산, 처리등	X	O

 

이해가 잘 안될 수도 있다 하지만 점점 설명을 듣게 된다면 이해가 될것이다.

DELIMITER $$
CREATE FUNCTION function_name(params)
RETURNS returnType;
BEGIN
	CODE...;
	RETURN returnValue;
END $$
DELIMITER ;

function_name(params);

 

스토어드 함수의 사용방법을 정리하여 표현한 것이다.

 

보다시피 스토어드 프로시저와 엄청난 구문 차이는 없다.

 

함수는 function으로 생성하고 함수 이름을 지정할 수 있으며 파라미터를 지정한다.

 

RETURNS는 반환할 값의 타입을 지정하는 것이다.

 

BEGIN — END 안에서 코드를 작성할 수 있다.

 

여기서 특별한 차이점이 있다 프로시저는 BEGIN안에서 CRUD와 같은 DDL 쿼리문을 작성할 수 있었다.

 

하지만 스토어드 함수는 BEGIN안에서 DDL 쿼리문과 같이 쿼리문을 작성할 수 없다.

 

이렇게 작성한 코드는 RETURN을 이용해서 반환할 값을 지정하여 값을 반환한다.

 

DELIMITER은 프로시저에서 설명한 개념과 동일하다.

 

특이한 점이 더 있다면 CALL을 사용해서 호출하는 프로시저와 달리 함수는 CALL을 사용하지 않는다.

따라서 내장함수와 비슷하게 호출한다.

SELECT function_name(columns) FROM userTBL;

 

위와 같이 DDL 쿼리문 안에서 함수를 호출한다.

 

하지만 위와 같은 호출 형식은 프로시저에는 사용할 수 없다는 차이점이 있다.

 

따라서 어떠한 테이블에 데이터를 특정한 계산을 이용해서 처리를 해야한다면 위와 같은 스토어드 함수를 사용하는 것이 더 좋고 만약에 중복되는 쿼리를 줄이고 비슷한 쿼리문을 계속해서 작성하지 않아서 쿼리문의 가독성을 높이고 싶을때는 스토어드 프로시저를 사용한다.

 

이처럼 스토어드 프로시저와 함수는 엄연히 구문도 다르고 서로의 차이점도 명확함으로 함수/프로시저중에서 어떤것을 선택해야지 쿼리 처리에 효율성을 높일지 신중하게 결정을 해야한다.

 

이제 한번 테스트 용으로 기본 테이블에서 날짜를 이용해서 나이를 출력하는 함수를 생성해보자.

DELIMITER $$
CREATE FUNCTION testFun(createDate date)
RETURNS INT
BEGIN
	DECLARE age = 0;
	@age = CURDATE() - createDate;
	RETURN @age;
END $$
DELIMITER ;

SELECT testFun(created) AS '나이' FROM userTBL;

 

위와 같이 간단한 현재 나이를 출력하는 함수를 만들어보았다.

 

이처럼 스토어드 함수는 사용자가 원하는 데이터의 처리를 보다 간편하게 수행할 수 있도록 지원해주는 기능이라고 할 수 있다.

 

스토어드 함수는 row 데이터의 어떠한 계산이나 처리에 꼭 필요한 기능이라고 생각하며

스토어드 프로시저와 함수 둘다 상황에 맞게 사용해야 보다 안정적이게 시스템의 효율성을 증가 시킬 수 있을것이라고 생각한다.

 

앞으로 개발 프로젝트를 진행할때 이러한 쿼리문을 잘 사용해야 같은 실수를 반복하지 않을 수 있으므로 지금부터라도 잘 알아두어야 겠다.

커서

커서는 파일포인터와 비슷한 개념이라고 생각할 수 있다.

 

MySQL에서 커서는 결과 집합의 행 단위 처리를 허용한다.

 

커서는 결과 집합에 사용되며 쿼리에서 반환된다.

 

한마디로 커서는 MYSQL에 SELECT문을 이용하여 출력되는 테이블 형식의 데이터를 하나 하나씩 파일포인터를 가리켜가며 행 하나하나당 처리를 수행하는 프로그래밍 언어로 생각하면 forEach문과 비슷한 역할을 수행한다고 볼 수 있다.

 

forEach에서는 array형식의 데이터를 하나하나씩 가져오는 반복문이였다.

 

커서도 비슷하다 출력된 테이블형식의 데이터를 파일포인터로 하나씩 가리키며 row를 하나하나씩 가져와서 처리하는 역할을 수행하고 있다.

 

커서를 이용하면 많은 장점이 있지만 그중 큰 장점은 "행단위로 데이터처리가 가능하다"라는 것이 가장 큰 장점이다.

 

예를 들어서 고객의 물건 구메율에 따라서 각각의 고객의 등급을 선정하는 시스템을 개발하게 된다면 커서를 사용할 수 있어진다.

 

커서를 사용하면 고객테이블에서 한개의 고객 row를 가져와서 구메율을 파악하고 등급을 설정 할 수 있다.

 

이렇게 되면 데이터를 하나씩 비교하면서 등급을 선정할 수 있어진다.

 

이러하게 데이터를 하나씩 가져올 수 있는 커서는 다음과 같이 동작이 진행된다.

 

위의 사진은 커서의 생명주기이다.

 

먼저 커서에 들어갈 쿼리문을 입력해서 저장한다.

 

그 다음 open을 통해서 저장되어있는 쿼리문의 결과파일을 연다.

 

fetch를 이용해서 결과데이터를 하나씩 가져오는 작업을 처리한다.

 

fetch 작업은 커서안에 가져올 데이터가 더 이상 없을때까지 계속해서 진행되며 만약 커서안에서 더이상 가져올 데이터가 없다면 커서를 닫는다.

 

커서의 생명주기는 의외로 간단한 구조를 가진다.

 

이제부터 위의 예시를 들었던 고객 등급을 선정하는 쿼리문을 커서를 이용해 작성하자.

DELIMITER $$
CREATE PROCEDURE gradeProc() -- 프로시저 생성
BEGIN
	DECLARE id VARCHAR(10); -- 유저 아이디를 담을 변수
    DECLARE hap BIGINT; -- 총 구매수를 담을 변수
    DECLARE userGrade CHAR(5); -- 등급을 저장할 변수
    
    DECLARE endOfRow BOOLEAN DEFAULT false; -- 커서가 끝났을때 (기본값 false)
    
    DECLARE userCursor CURSOR FOR -- 커서생성
		SELECT U.userid, sum(price*amount) -- 커서안에는 해당 쿼리 결과가 들어있음
			FROM buytbl B
				RIGHT JOIN usertbl U
				ON B.userId = U.userId
			GROUP BY U.userId, U.name;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = true; -- 커서의 길이가 다 끝나면 true로 변경
    
    OPEN userCursor; -- 커서 오픈
    grade_loop: LOOP -- 무한반복지정
		FETCH userCursor INTO id, hap; -- 쿼리문의 결과를 하나씩 변수에 결과를 저장함
        IF endOfRow THEN -- endOfRow가 true라면 반복문 종료
			LEAVE grade_loop;
		END IF;
        
        CASE -- 등급 변수에 저장
			WHEN (hap >= 1500) THEN SET userGrade = "최우수고객";
            WHEN (hap >= 1000) THEN SET userGrade = "우수고객";
            WHEN (hap >= 1) THEN SET userGrade = "일반고객";
            ELSE SET userGrade = "유령고객";
		END CASE;
        
        UPDATE usertbl SET grade = userGrade WHERE userId = id; -- 하나씩 업데이트
	END LOOP grade_loop;
	CLOSE userCursor; -- 커서 종료
END $$;
DELIMITER ;

CALL gradeProc();
SELECT * FROM usertbl;

 

위의 코드는 스토어드 프로시저로 커서를 구현한 예제이다.

 

참고로 스토어드 함수 내에서는 쿼리문을 실행할 수 없기때문에 스토어드 함수에서는 커서의 사용이 불가능 하다는 것을 알아두어야 한다.

생명주기를 기준으로 위의 쿼리문을 살펴보자.

 

처음에 변수인 EndOfRow는 위에서 생명주기인 비었을때 TRUE로 값이 바뀔 것이며 기본값을 FALSE로 지정한 변수이다 TRUE로 바뀌는 시점은 더이상 확인할 데이터가 없을때 이다.

 

userCursor변수 안에 CURSOR FOR로 커서를 생성하고 해당 커서안에 확인할 쿼리문은 buyTBL과 userTBL을 RIGHT JOIN한 쿼리문의 결과가 들어있다.

 

이부분은 생명주기에서 커서의 생성단계이다.

 

이제 OPEN cursor_name으로 커서를 연다. 커서를 열게되면 위의 JOIN한 쿼리문의 결과가 들어있을 것이다.

 

이제 해당 커서를 : LOOP로 무한반복한다.

 

FETCH로 한 행씩 가져온 데이터를 변수에 하나씩 할당한다. (생명주기에서 FETCH과정)

 

DECLARE CONTINUE HANDLER FOR NOT FOUND 이것은 커서의 에러 핸들링 과정이다.

 

NOT FOUND에러가 나오면 endOrRow의 BOOLEAN 값을 TRUE로 바꾼다.

 

이제 커서의 생명주기인 마지막 단계이다. LOOP안에서 endOfRow가 TRUE일시 반복문을 나가고 커서를 CLOSE한다.

위와 같이 처리가 됨으로 고객의 등급이 설정될 것이다.

 

커서는 앞으로 한 행씩 데이터를 처리해야 할 때는 사용하면 엄청 유용할 기능일 것이다.

 

MYSQL에서는 조건을 이용하여 하나의 데이터를 가져오는 경우는 있지만 조건없이 1개씩 데이터를 가져오는 것은 커서에서만 가능한 일이다.

 

따라서 커서를 알아두면 나중에 위와 같은 상황에도 어렵지 않게 위기를 극복해 나갈 수 있을 것이므로 잘 알아두어야 할 개념중 하나인 것 같다.

트리거

트리거는 정의하자면 백업 테이블, 복구 테이블로 정의 할 수 있을 것같다.

 

트리거를 가장 잘 이해할 수 있는 예시를 들어보겠다.

 

어떤 쇼핑몰 사이트를 운영하는데 회원이 회원탈퇴를 하고 탈퇴한 정보를 열람하고 싶어한다.

 

하지만 우리는 회원을 탈퇴함과 동시에 유저 테이블에 해당 회원을 지워놓고 그 어디에도 기록하지 않아서 해당 회원이 사이트를 탈퇴했다는 사실을 그 어디에도 찾을 수 없다.

 

이러한 상황을 해결하려면 데이터의 삭제가 일어날때 어딘가에는 삭제되는 정보를 기록하는 테이블이 있어야 할 것이다.

 

만약에 이것을 수동으로 구현한다면 회원 탈퇴를 할때 삭제하기전 미리 해당 회원의 정보를 기록하고 데이터를 삭제해야 할 것이다.

회원이 얼마되지 않는다면 상관이 없겠지만 트래픽이 몰리거나 갑작스럽게 탈퇴를 많이 한다면?

 

대규모의 회원이 탈퇴를 진행할떄 위와 같은 방식으로 데이터를 기록하게 된다면 유저가 많아질때는 데이터베이스 서버가 과부하가 올 수 있다.

 

트리거는 이러한 위험을 방지해주는 backup 테이블과 같다.

 

회원이 탈퇴할때 발생하는 이벤트를 바운딩 시켜서 회원 데이터를 삭제하는 작업이 일어날때 다른 백업 테이블에 데이터가 기록되도록 할 수 있는 것이 바로 트리거이다.

DELIMITER $$
CREATE TRIGGER trigger_name
	{BEFORE | AFTER} {INSERT | UPDATE | DELETE | SELECT}
	ON table_name
	FOR EACH ROW
BEGIN
	query ...
END $$
DELIMITER ;

 

트리거의 기본적인 사용방법은 위와 같다.

 

스토어드 프로시저와 함수랑 구문이 살짝 유사한 것을 눈치챌 수 있었을 것이다.

 

트리거도 마찬가지로 구분기호를 지정하고 CREATE TRIGGER을 이용해서 트리거를 생성한다. before이나 after을 이용해서 (아래의 설명할 것 이다) 전후를 설정한 다음 CRUD중 어떠한 이벤트에 트리거가 동작될지 지정해야한다.

 

ON을 이용해서 트리거를 적용시킬 테이블을 입력해서 적용시키고 FOR EACH ROW는 모든 행에 트리거를 전부 적용하겠다는 뜻이다.

 

BEGIN — END 사이에서 트리거가 발동 되었을때 동작할 쿼리문을 입력하고 다시 DELIMITER로 기호를 바꿔준다.

 

한번 위의 예시를 구현해보자.

DELIMITER $$
CREATE TRIGGER backUserTbl_DeleteTrg
	AFTER DELETE
	ON userTBL
	FOR EACH ROW
BEGIN
	INSERT INTO backupTBL VALUES (old.userID, old.name, CURDATE())
END $$
DELIMITER ;

DELETE FROM userTBL WHERE userID = "SKB";

SELECT * FROM backupTBL;

 

아직 old와 AFTER은 설명하지 않았으므로 나중에 설명하겠다.

 

위의 쿼리문을 보면 DELETE 이벤트가 발생할 때 트리거가 작동하도록 설정하였고 userTBL에 트리거를 적용하였다.

 

만약 데이터의 삭제가 발생한다면 backupTBL에 삭제된 정보를 기록하고 트리거를 종료한다.

 

위의 쿼리문을 작성하고 실행한다음 userTBL에서 DELETE를 진행한다면 backupTBL에 데이터가 기록될 것이다.

 

이보다 CRUD 모든 작업에 트리거를 설정 할 수 있다.

 

이제부터 BEFORE, AFTER, OLD, NEW에 대해서 설명해보겠다.

BEFORE과 AFTER

TRIGGER는 처리가 발생하는 시점을 2가지로 정할 수 있다.

 

AFTER는 ~후라는 뜻으로 DELETE, UPDATE등의 처리가 일어난 후에 발생하는 트리거이다.

 

따라서 AFTER 시점과 DELETE에 발생하는 트리거를 사용하게 된다면 데이터를 삭제 한 후 트리거가 동작 하게 된다.

 

정리하자면 AFTER는 어떠한 DDL 처리가 일어난 후에 동작되는 트리거라고 생각할 수 있다.

 

BEFORE 트리거는 반대로 ~전이라는 뜻을 가지고 있다.

 

따라서 어떠한 DDL 처리가 일어나기 전에 동작되는 트리거라고 생각할 수 있다.

 

간단한 예시를 들자면 INSERT를 하여 데이터를 삽입하는 과정에서 입력된 데이터가 어떠한 제약조건에 걸리지 않는지 확인하여 제약조건에 걸리게 된다면 INSERT를 중단 시킬 수 있는 역할을 한다.

 

정리하자면 BEFORE는 DDL 처리가 일어나기 전에 발생하는 트리거라고 할 수 있다.

 

BEFORE/AFTER 트리거를 사용하는 방법은 아래와 같다.

DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE | AFTER event_name

 

위의 어떠한 DDL 처리가 일어날때 발생할 지 정하는 시점에 BEFORE이나 AFTER을 사용하여 발생하는 시점을 설정할 수 있다.

OLD와 NEW

위의 쿼리문을 다시 확인해보자.

DELIMITER $$
CREATE TRIGGER backUserTbl_DeleteTrg
	AFTER DELETE
	ON userTBL
	FOR EACH ROW
BEGIN
	INSERT INTO backupTBL VALUES (old /*이부분 집중*/.userID, old.name, CURDATE())
END $$
DELIMITER ;

DELETE FROM userTBL WHERE userID = "SKB";

SELECT * FROM backupTBL;

 

일반적인 컬럼명을 쓰는 것이 아닌 앞에 OLD.을 붙여서 사용하고 있다.

 

이렇게 사용하면 다른 데이터가 들어올 것 같겠지만 정상적으로 데이터가 들어오게 된다.

 

이러한 방식을 이해하려면 임시 테이블을 이해 할 수 있어야 한다.

 

위의 사진을 보자 모든 삽입, 수정, 삭제는 임시테이블을 가지고 있다.

 

NEW와 OLD가 있는것을 알 수 있다.

 

INSERT와 UPDATE는 NEW라는 임시테이블을 가지게 된다.

 

사진을 보게 된다면 insert를 하기 전에 new 테이블에 삽입을 하기 전에 데이터를 저장해놓은 다음 삽입을 진행하게 된다.

 

따라서 삽입이 되기 전에 임시로 데이터를 저장해놓은 것을 임시테이블이라고 한다.

 

DELECT와 UPDATE는 OLD라는 값을 가지고 있는데 해당 값은 중간에 테이블을 예전값을 지우고 나서 지워진 데이터를 OLD 테이블에 저장한다.

 

이처럼 DDL 쿼리문이 실행되기 전, 후에 임시로 데이터를 저장해놓는 공간을 임시테이블이라고 한다.

 

이제 위의 코드를 다시 보면

DELIMITER $$
CREATE TRIGGER backUserTbl_DeleteTrg
	AFTER DELETE
	ON userTBL
	FOR EACH ROW
BEGIN
	INSERT INTO backupTBL VALUES (old /*이부분 집중*/.userID, old.name, CURDATE())
END $$
DELIMITER ;

DELETE FROM userTBL WHERE userID = "SKB";

SELECT * FROM backupTBL;

 

delete를 진행하였고 after를 사용하였으므로 데이터가 삭제된 후에 저장되는 공간인 임시테이블에 값이 저장되고 저장된 값을 바탕으로 다른 테이블에 데이터를 insert를 하는 것이라고 할 수 있다.

 

new를 사용하고 싶으면 앞에서 말했던 쿼리문이 동작되기 전인 before을 사용하게 된다면 new를 사용할 수 있다. (사진과 같이 new는 insert와 update에서만 동작하고 old는 after에서 사용할 수 있기 때문에 insert에서는 old를 사용할 수 없다.)

 

이렇게 트리거의 기본 사용방법에 설명하였다.

다중 트리거

다중트리거는 아래의 사진을 보자.

 

위는 다중트리거가 사용되는 예시다.

 

고객이 물건을 구매하면 구매테이블에 데이터가 삽입되고 물품테이블에서는 남은 개수를 감소시키고 최종적으로 배송테이블에 정보를 입력해야한다.

 

이동작을 트리거로 구현하게 된다면 어떨까?

 

구매 테이블에서 삽입이 될때 구매테이블에서는 insert 트리거를 작동시킨다.

 

트리거에서는 물품 테이블에서 해당 물품을 감소시키는 update 작업을 하고 물품테이블은 update 트리거가 작동됨으로 최종적으로 배송테이블에 입력되게 된다.

 

어떤가? 트리거를 이용해서 3번에 걸쳐 진행되어야 할 작업이 트리거를 이용해서 1번의 작업으로 단축되었다.

 

이처럼 다중 트리거는 하나의 트리거의 여러개의 트리거가 계속해서 동작되는 트리거를 MYSQL에서는 다중 트리거, 중첩 트리거라고 한다.

정리

트리거와 스토어드 프로시저/함수, 커서는 MYSQL에서 핵심적으로 동작되는 기능이므로 잘 기억해두고 이해해서 실제 개발을 하게 될때 응용을 할 수 있어야 된다.

 

따라서 위의 개념을 잘 인지하고 실 개발환경에서 잘 활용할 수 있을때까지 계속해서 개발을 하자