[MySQL] 프로시저와 트리거
1. 프로시저와 트리거를 사용해야하는 이유
지금 어플을 만들며 API를 직접 만들어 DB를 관리하도록 만들고 있다. DB수업시간에 열심히 듣지 않은 나는
두가지 이상의 테이블을 건드려야하는 상황이 왔을 때 트랜잭션을 두개이상 보내어 DB를 건드리게 하게 만들었다.
하지만 이처럼 하나의 사용자 반응에 트랜잭션이 많아지면 문제가 생길 수 있기 때문에 프로시저나 트리거를 사용하여
API를 작성해야 한다. (문제라 함은 다중의 트랜잭션이 작동을 하고 있는 와중에 프로그램이 다운을 하게 되면 하나의 트랜잭션은 완료를 했는데 하나의 트랜잭션은 끝 마치지 못하는 등 트랜잭션의 성질을 범할 수가 있다.)
2. 프로시저와 트리거 차이점
프로시저
: 저장 프로시저는 미리 데이터베이스 서버에 일련의 SQL 명령을 해 놓고, 프로시저를 실행하여 SQL 명령을 간단하게 실행할 수 있도록 할 수 있다. 프로시저 안에는 SQL 문장 뿐 아니라 if문 while문 등의 제어 명령이나 반복 명령을 기술 할 수 있기 때문에 일종의 프로그램도 만들 수 있다.
(프로시저를 만들어 DB에 저장하면 여러 프로그램에서 호출하여 사용하므로 저장 프로시저(Stored Procedure)라고 불림)
트리거
: 트리거는 테이블에 작성한다. 어떤 테이블에 행을 삽입한다든디, 행을 변경, 삭제했을 때에 트리거가 설정되어 있으면, 트리거의 SQL 문장이 자동으로 실행된다.
이 기능을 사용하면 데이터의 추가, 삭제, 변경과 함께 관계하는 테이블을 조작하여 데이터베이스로서 모순이 없는 상태를 자동적으로 생성 할 수 있다.
| 프로지서 | 트리거 |
| CREATE PROCEDURE 문법 사용 | CREATE TRIGGER 문법 사용 |
| 생성하면 소스코드와 실행코드가 생성됨 | 생성하면 소스코드와 실행코드가 생성됨 |
| EXECUTE 명령어로 실행 | 생성 후 자동실행 |
| COMMIT, ROLLBACK 실행가능 | COMMIT, ROLLBACK 실행 불가 |
| 시스템 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용 | 데이터 변경, 무결성 유지, 로그메시지 출력 등에 주로 사용 |
3. 프로시저
- 프로시저 생성
- DECLARE: 프로시저 명칭, 변수, 인수, 데이터 타입 정의
- BEGIN / END: 프로시저 시작 및 종료 의미
- CONTROL: 조건문 및 반복문 삽입되어 순차적 처리
- SQL: DML, DCL 삽입되어 조회, 추가, 수정, 삭제 작업 수행
- EXCEPTION: BEGIN ~ END 내부 구문 실행 시 예외 처리
- TRANSACTION: 수행된 데이터 작업 DB에 적용할지 여부 결정
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
프로시저 BODY;
END;- OR REPLACE: 선택적 예약어 / 동일 이름에 대한 기존 프로시저 대체 가능
- 프로시저명: 생성하려는 프로시저 이름 지정
- 프로시저 BODY
- 프로시저 코드 기록
- BEGIN ~ END으로 묶이며, 이 안에는 적어도 하나의 SQL문 있어야 함
- 파라미터
- IN: 호출 프로그램이 프로시저에게 값 전달
- OUT: 프로시저가 호출 프로그램에게 값 반환
- INOUT: 호출 프로그램이 프로시저에게 값 전달, 프로시저 실행 후 호출 프로그램에게 값 반환
- 매개변수명: 호출 프로그램으로부터 전달받은 값 저장할 변수 이름
- 자료형: 변수의 자료형
CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS
BEGIN
UPDATE 급여 SET 지급방식='S' WHERE 사원번호 = i_사원번호;
EXCEPTION
WHEN PROGRAM_ERROR THEN
ROLLBACK;
COMMIT;
END;-프로시저 실행
//표기 형식 3가지
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;
//'사원번호' 32를 인수로 하여 위에서 생성된 emp_chage_s 프로시저 실행
EXECUTE emp_change_s(32);-프로시저 제거
DROP PROCEDURE 프로시저 명;
// 위에서 생성된 프로시저 emp_change_s 제거
DROP PROCEDURE emp_change_s;4. 트리거
- 선언, 이벤트, 시작, 종료로 구성
- 시작, 종류 구문 사이에는 CONTROL, SQL, EXCEPTION 포함
- DECLARE: 트리거 명칭, 변수, 상수, 데이터 타입 정의
- EVENT: 트리거 실행 조건 명시
- BEGIN / END: 트리거 시작 종료 의미
- CONTROL: 조건문, 반복문 삽입되어 순차적 처리
- SQL: DML문 삽입되어 조회, 추가, 수정, 삭제 작업
- EXCEPTION: BEGIN ~ END 내부 구문 실행 시 예외처리
-트리거의 생성
CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW|OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
트리거 BODY;
END;- OR REPLACE: 동일 트리거 명에 대하여 기존 트리거 대체
- 동작시기 옵션
- AFTER: 테이블 변경 후에 트리거 실행
- BEFORE: 테이블 변경 전에 트리거 실행
- 동작 옵션
- INSERT: 테이블에 새로운 튜플 삽입 시 트리거 실행
- DELETE: 테이블의 튜플 삭제 시 트리거 실행
- UPDATE: 테이블의 튜플 수정 시 트리거 실행
- NEW|OLD: 트리거 적용될 테이블 별칭 지정
- NEW: 추가 및 수정에 참여할 튜플 집합
- OLD: 수정 및 삭제 전 대상 튜플 집합
- FOR EACH ROW: 각 튜플마다 트리거 적용
- WHEN 조건식: 트리거 적용할 튜플 조건 지정
- 트리거 BODY
- 트리거 본문 코드 입력
- BEGIN ~ END로 묶이며 적어도 하나 이상의 SQL문 없을 시 오류 발생
// <학생> 테이블에 새로운 튜플 삽입 시
// 학년 정보 누락했으면 '학년' 속성에 '신입생' 저장하는 트리거
// '학년정보_tri' 이름으로 정의
CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
BEGIN
:new_table)학년:='신입생';
END;-트리거의 제거
DROP TRIGGER 트리거명;//학년정보_tri'라는 트리거 제거
DROP TRIGGER 학년정보_tri;
5. 나만의 결론
1번에서 말한 다중의 트랜잭션을 보낸 케이스는 유저가 SNS회원가입을 했을 때 SNS로그인 테이블에 로그를 남기고 USER테이블에 유저를 추가하고 그 UID를 받아와 USER_INFO테이블에 정보를 입력하는 흐름이었다. 프로시저와 트리거를 이해하고 난 지금 이 경우는 프로시저를 사용하여 고도화 작업을 해주면 되겠다는 결론이 들었다.
지금 내가 해야하는 작업은 유저가 회원가입을 했을 때 카운팅 테이블에 +1을 해주는 작업을 하면 되는데
이 경우에는 트리거를 이용하여 간단하게 작동을 해주면 될 것 같다.
출처
1. https://keumjae.tistory.com/131