7. 스토어드 프로시저-3

0. 학습 목표

• 스토어드 프로시저의 작성 방법을 이해
• 스토어드 함수와 커서에 대해서 이해하고 활용방법을 학습
• 트리거의 적용 분야와 실제 사용방법을 학습

0. 요약

• 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능
• 스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현
• 스토어드 프로시저 작성 후 CALL 문을 통해 스토어드 프로시저 호출
• 입력 매개변수는 스토어드 프로시저에 값을 전달(IN)
• 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려 받음(OUT)
• 동적 SQL은 다이나믹하게 SQL을 생성한 후 실행, PREPARE, EXECUTE 문을 사용
• MySQL은 내장 함수로 제공되지 않는 기능을 스토어드 함수로 만들어 사용 가능
• 스토어드 함수는 RETURNS 예약어를 통해 반환된 데이터 형식을 미리 지정해야 함
• 스토어드 함수의 매개변수는 입력 매개변수, 출력 매개변수 없음
• 커서는 한 행씩 처리되도록 하는 기능, DECLARE로 선언, 내용은 SELECT
• 커서는 행이 끝날 때까지 반복, 행의 끝을 판단하기 위해 변수 endOfRow를 준비하고 TRUE인지를 체크
• 트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동
• AFTER DELETE는 DELETE 문이 발생한 후 트리거를 작동하라는 의미
• 트리거는 행 데이터가 삭제/수정되면 기존 데이터를 백업 테이블에 저장하도록 가능
• 트리거에서 기존 데이터는 OLD 테이블에 새로운 데이터는 NEW 테이블에 잠시 저장

---

3. 자동 실행되는 트리거

※핵심 키워드:  트리거, DML문, ALTER DELETE, 백업 테이블, OLD 테이블, NEW 테이블

트리거는 INSERT, UPDATE, DELETE 문이 작동할 때 자동으로 실행되는 프로그래밍 기능이다. 예를 들어 트리거를 활용하면 데이터가 삭제될 때 해당 데이터를 다른 곳에 자동으로 백업할 수 있다.

---

3-1. 트리거 기본

트리거의 개요

트리거란 테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드이다. 한 가지 예로 member 테이블에 블랙핑크가 탈퇴한 경우 DELETE로 삭제하면 된다. 하지만 추 후 해당 정보를 확인할 일이 있는 경우 따로 백업을 해놔야지만 확인이 가능하다. 이것을 자동으로 처리할 수 있도록 사용하는 것이 트리거이다.

---

트리거의 기본 작동

트리거는 테이블에서 DML(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다. 트리거는 스토어드 프로시저와 문법이 비슷하지만 다른 점이 있다.

• CALL문으로 실행시킬 수 없고 오직 테이블에 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행
• IN, OUT 매개변수를 사용할 수 없음

간단한 트리거를 보기 위해 테이블을 만들어 보자.

CREATE TABLE IF NOT EXISTS trigger_table(id INT, txt VARCHAR(10));
INSERT INTO  trigger_table VALUES(1, '레드벨벳');
INSERT INTO  trigger_table VALUES(2, '잇지');
INSERT INTO  trigger_table VALUES(3, '블랙핑크');

이제 트리거를 부착해 보자.

DELIMITER $$
CREATE TRIGGER myTrigger	-- 트리거 생성
	AFTER DELETE			-- DELETE 문이 발생된 이후 작동
    ON trigger_table		-- trigger_table에 부착
    FOR EACH ROW			-- 각 행마다 적용(트리거에 거의 항상 쓰임)
BEGIN
	SET @msg = '가수 그룹이 삭제됨';	-- 트리거 실행 시 작동되는 코드로 여기서는 간략하게 메시지
END $$
DELIMITER ;

우선 트리거를 부착한 테이블에 값을 INSERT, UPDATE 해보자. 그리고 @msg값을 확인하면 당연하게도 DELETE를 사용하지 않았으므로 작동하지 않는다.

SET @msg = ' ';
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;

UPDATE trigger_table SET txt = '블랙핑크' WHERE id =3;
SELECT @msg;

# @msg
' '

DELETE문을 적용해보자. 결과를 확인할 수 있다.

DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;

# @msg
'가수 그룹이 삭제됨'

---

3-2. 트리거 활용

이번에는 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해 보자.

우선 mem_id, mem_name, mem_number, addr 4개의 열로 구성된 singer 테이블을 복사로 만들어 보자.

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

SELECT * FROM singer;

# mem_id, mem_name, mem_number, addr
'APN', '에이핑크', '6', '경기'
'BLK', '블랙핑크', '4', '경남'
'GRL', '소녀시대', '8', '서울'
'ITZ', '잇지', '5', '경남'
'MMU', '마마무', '4', '전남'
'OMY', '오마이걸', '7', '서울'
'RED', '레드벨벳', '4', '경북'
'SPC', '우주소녀', '13', '서울'
'TWC', '트와이스', '9', '서울'
'WMN', '여자친구', '6', '경기'

이번에는 INSERT, UPDATE 작업이 일어나는 경우 변경되기 전에 데이터를 저장할 백업 테이블을 미리 만들자.

• modType: 변경된 타입
• modDate: 변경된 날짜
• modUser: 변경한 사용자

CREATE TABLE backup_singer 
(	mem_id		CHAR(8) NOT NULL,
	mem_name	VARCHAR(10) NOT NULL,
    mem_number	INT NOT NULL,
    addr		CHAR(2) NOT NULL,
    modType		CHAR(2),
    modDate		DATE,
    modUser		VARCHAR(30)
);

이제 UPDATE와 DELETE가 발생할 때 작동하는 트리거를 singer 테이블에 부착해 보자. 특이한 것은 OLD 테이블인데 UPDATE나 DELETE가 수행될 때 변경되기 전의 데이터가 잠시 저장되는 임시 테이블이다.

DELIMITER $$
CREATE TRIGGER singer_updateTrg
	AFTER UPDATE
    ON singer
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES 
		(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

CREATE TRIGGER singer_deleteTrg
	AFTER DELETE
    ON singer
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES 
		(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

한 건의 업데이트와 여러 건의 삭제를 해보자.

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

SELECT * FROM backup_singer;

# mem_id, mem_name, mem_number, addr, modType, modDate, modUser
'BLK', '블랙핑크', '4', '경남', '수정', '2022-02-23', 'root@localhost'
'GRL', '소녀시대', '8', '서울', '삭제', '2022-02-23', 'root@localhost'
'OMY', '오마이걸', '7', '서울', '삭제', '2022-02-23', 'root@localhost'
'SPC', '우주소녀', '13', '서울', '삭제', '2022-02-23', 'root@localhost'
'TWC', '트와이스', '9', '서울', '삭제', '2022-02-23', 'root@localhost'

이번에는 DELETE 대신 TRUNCATE TABLE문으로 모든 행을 삭제해 보자. DELETE문으로 수행하지 않았기 때문에 백업 테이블에 입력되지 않았다.

TRUNCATE TABLE singer;

SELECT * FROM backup_singer;

# mem_id, mem_name, mem_number, addr, modType, modDate, modUser
'BLK', '블랙핑크', '4', '경남', '수정', '2022-02-23', 'root@localhost'
'GRL', '소녀시대', '8', '서울', '삭제', '2022-02-23', 'root@localhost'
'OMY', '오마이걸', '7', '서울', '삭제', '2022-02-23', 'root@localhost'
'SPC', '우주소녀', '13', '서울', '삭제', '2022-02-23', 'root@localhost'
'TWC', '트와이스', '9', '서울', '삭제', '2022-02-23', 'root@localhost'

---

3-3. 트리거가 사용하는 임시 테이블

INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 2개 있다. NEW, OLD로 MySQL이 알아서 생성하고 관리하는 테이블이다.

• INSERT 문 - NEW 테이블

• DELETE - OLD 테이블

• UPDATE - NEW/OLD 테이블