MySQL AFTER TRIGGER — UPDATE·DELETE·INSERT 트리거 완전 정리

 

 

MySQL 실습 정리

AFTER TRIGGER

UPDATE/DELETE 후 OLD 값을 백업 테이블에 기록하고 INSERT 시 SIGNAL을 발생시킨다.

1 핵심 개념

UPDATE/DELETE 후 OLD 값을 백업 테이블에 기록하고 INSERT 시 SIGNAL을 발생시킨다. 이 폴더는 프로시저보다 한 단계 더 나아가 함수, 커서, 트리거처럼 실행 시점과 반환 방식이 다른 객체를 정리한다.

함수는 값을 반환하고, 커서는 결과 집합을 한 행씩 읽으며, 트리거는 테이블 변경 이벤트에 자동 반응한다. 세 문법 모두 강력하지만 실행 시점을 혼동하면 디버깅이 어렵다.

FUNCTION은 직접 호출, PROCEDURE는 CALL, TRIGGER는 이벤트 발생 시 자동 실행이라는 차이를 먼저 잡는다.

2 원본 예제 코드

USE sqlDB; CREATE TABLE backup_userTbl ( userID CHAR(8) NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, birthYear INT NOT NULL, addr CHAR(2) NOT NULL, mobile1 CHAR(3), mobile2 CHAR(8), height SMALLINT, mDate DATE, modType CHAR(2), modDate DATE, modUser VARCHAR(256) ); DROP TRIGGER IF EXISTS backUserTbl_UpdateTrg; DELIMITER // CREATE TRIGGER backUserTbl_UpdateTrg AFTER UPDATE ON userTBL FOR EACH ROW BEGIN INSERT INTO backup_userTbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate, '수정', CURDATE(), CURRENT_USER()); END // DELIMITER ; DROP TRIGGER IF EXISTS backUserTbl_DeleteTrg; DELIMITER // CREATE TRIGGER backUserTbl_DeleteTrg AFTER DELETE ON userTBL FOR EACH ROW BEGIN INSERT INTO backup_userTbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate, '삭제', CURDATE(), CURRENT_USER()); END // DELIMITER ; UPDATE userTbl SET addr = '몽고' WHERE userID = 'JKW'; DELETE FROM userTbl WHERE height >= 177; SELECT * FROM backup_userTbl;

3 용어 카드

FUNCTION값을 반환하는 사용자 정의 함수

RETURN함수의 결과 값을 돌려주는 문법

CURSORSELECT 결과를 한 행씩 읽는 포인터

HANDLER커서 종료 같은 예외 상황을 처리하는 선언

TRIGGERINSERT, UPDATE, DELETE 시 자동 실행되는 객체

OLDUPDATE/DELETE 전의 기존 행 값

NEWINSERT/UPDATE로 들어오는 새 행 값

SIGNAL사용자 정의 오류를 발생시키는 문법

 

반응형

 

4 실행 흐름

1

객체 종류 확인함수, 프로시저, 트리거 중 어떤 실행 방식을 쓰는지 구분한다.

2

구분자 변경본문에 세미콜론이 있으므로 DELIMITER를 임시로 변경한다.

3

본문 작성RETURN, FETCH, OLD/NEW처럼 객체별 핵심 문법을 넣는다.

4

실행 조건 만들기함수는 SELECT, 프로시저는 CALL, 트리거는 INSERT/UPDATE/DELETE로 검증한다.

5

결과 검산반환값, 평균 계산, 백업 테이블, 재고 테이블 결과를 확인한다.

5 객체별 차이

객체	실행 방법	주요 용도
FUNCTION	SELECT 함수명(...)	계산값 반환
PROCEDURE	CALL 프로시저명(...)	여러 SQL 묶음 실행
CURSOR	프로시저 내부 OPEN/FETCH/CLOSE	행 단위 반복 처리
AFTER TRIGGER	변경 후 자동 실행	로그, 백업, 후속 처리
BEFORE TRIGGER	변경 전 자동 실행	입력값 보정 또는 검증

6 함수 기준

사용자 정의 함수는 반드시 RETURNS 타입과 RETURN 값을 가져야 한다. 예제의 getAgeFunc는 birthYear를 받아 현재 연도와의 차이를 계산한다.

문법	역할	확인
CREATE FUNCTION	함수 생성	SHOW CREATE FUNCTION
RETURNS INT	반환 타입 지정	SELECT 결과 타입
RETURN age	계산 결과 반환	SELECT getAgeFunc(...)
DROP FUNCTION	함수 제거	재생성 전 정리

7 커서 기준

커서는 SELECT 결과를 한 번에 집계하기 어려운 로직에서 행 단위 처리를 할 때 사용한다. 다만 SQL은 집합 처리에 강하므로 커서는 꼭 필요한 경우에만 쓰는 편이 좋다.

단계	문법	역할
선언	DECLARE cursor CURSOR FOR SELECT	읽을 결과 집합 지정
종료 처리	DECLARE HANDLER FOR NOT FOUND	끝까지 읽었을 때 플래그 변경
열기	OPEN cursor	읽기 시작
읽기	FETCH cursor INTO 변수	한 행 값을 변수에 저장
닫기	CLOSE cursor	커서 자원 정리

8 트리거 기준

구분	사용 값	예시
BEFORE INSERT	NEW	입력 전 birthYear 보정
AFTER UPDATE	OLD, NEW	변경 전 값을 백업
AFTER DELETE	OLD	삭제된 값을 백업
AFTER INSERT	NEW	주문 입력 후 재고 차감
SIGNAL	SQLSTATE	사용자 메시지 오류 발생

9 검산 쿼리

SELECT getAgeFunc(1979); SHOW CREATE FUNCTION getAgeFunc; CALL cursorProc(); SHOW TRIGGERS FROM sqlDB; SELECT * FROM backup_userTbl; SELECT * FROM orderTbl; SELECT * FROM prodTbl; SELECT * FROM deliverTbl;

트리거는 직접 CALL하지 않는다. 이벤트를 발생시키는 INSERT, UPDATE, DELETE를 실행한 뒤 관련 테이블 결과를 확인해야 한다.

10 자주 하는 실수

실수	증상	해결
함수에 RETURN 누락	생성 오류	RETURNS와 RETURN을 함께 작성
커서 CLOSE 누락	자원 정리 안 됨	루프 이후 CLOSE 실행
HANDLER 선언 위치 오류	프로시저 생성 실패	커서 선언 뒤 핸들러 선언
트리거에서 OLD/NEW 혼동	컬럼 참조 오류	이벤트별 사용 가능 값 확인
트리거 연쇄 영향 미확인	예상 밖 테이블 변경	관련 테이블 모두 SELECT

11 결과 판정표

결과	정상 해석	다음 확인
나이 값 반환	FUNCTION 정상	다른 birthYear 테스트
평균 키 문자열 출력	CURSOR 루프 정상	cnt가 0이 아닌지 확인
backup_userTbl에 행 생성	AFTER 트리거 정상	modType과 modUser 확인
birthYear가 보정됨	BEFORE 트리거 정상	입력 전후 값 비교
배송 테이블에 행 생성	트리거 체인 정상	재고 차감 수량 확인

12 운영 관점 주의

트리거는 애플리케이션 코드에서 직접 보이지 않아도 자동 실행된다. 그래서 데이터 변경 원인을 추적하기 어려울 수 있으므로 트리거 이름과 역할을 명확하게 관리해야 한다.

커서는 편리하지만 많은 행을 반복 처리하면 느릴 수 있다. 가능하면 집계 함수로 해결하고, 행 단위 로직이 꼭 필요한 경우에만 커서를 사용한다.

13 한 줄 복습

상황	문법	핵심
계산값 반환	CREATE FUNCTION	RETURNS와 RETURN 필수
행 단위 반복	CURSOR	OPEN, FETCH, CLOSE
조회 프로시저	CREATE PROCEDURE	CALL로 실행
변경 후 기록	AFTER TRIGGER	OLD 값 백업
입력 전 보정	BEFORE TRIGGER	NEW 값 수정
트리거 확인	SHOW TRIGGERS	등록 여부 확인

14 디버깅 체크

증상	확인할 부분	해결 방향
함수 생성 실패	RETURNS 타입, RETURN 문, DELIMITER	본문을 최소 형태로 줄여 재생성
커서 루프가 끝나지 않음	NOT FOUND HANDLER와 LEAVE 조건	endOfRow 플래그 위치 확인
평균 계산이 NULL	height NULL 또는 cnt 0	WHERE height IS NOT NULL 추가 검토
트리거가 실행되지 않음	SHOW TRIGGERS와 이벤트 종류	INSERT/UPDATE/DELETE 이벤트 재확인
OLD/NEW 오류	트리거 시점과 이벤트	DELETE는 OLD, INSERT는 NEW 중심으로 수정

15 실습 전후 비교

작업 전	작업 후	판정 기준
함수 없음	SELECT getAgeFunc 실행 가능	계산값 반환
평균 키 수동 계산	cursorProc 호출	평균 문자열 출력
회원 조회 SQL 반복	delivProc 호출	id별 결과 조회
수정/삭제 기록 없음	backup_userTbl에 로그 생성	modType 확인
비정상 출생연도 입력	BEFORE 트리거가 값 보정	입력 후 SELECT로 확인

16 마무리 기준

함수, 커서, 트리거는 모두 데이터베이스 안에서 자동화 수준을 높이는 기능이다. 하지만 보이지 않는 실행 흐름이 늘어나므로 생성 명령과 함께 검산 쿼리를 반드시 남겨야 한다.

함수는 반환값, 커서는 반복 종료, 트리거는 실행 이벤트와 변경된 테이블을 확인하면 대부분의 오류를 좁힐 수 있다.

핵심 요약

주제AFTER TRIGGER

핵심UPDATE/DELETE 후 OLD 값을 백업 테이블에 기록하고 INSERT 시 SIGNAL을 발생시킨다.

FUNCTION값을 반환하므로 SELECT 안에서 사용할 수 있다.

CURSOR결과 집합을 한 행씩 읽고 반드시 닫는다.

TRIGGER테이블 변경 이벤트에 자동 실행되며 OLD/NEW 사용 범위가 다르다.

Tags

#MySQL #FUNCTION #PROCEDURE #CURSOR #TRIGGER #SQL

티스토리 태그 입력용

MySQL, FUNCTION, PROCEDURE, CURSOR, TRIGGER, OLD, NEW, SQL, 데이터베이스, DB