[Database] 프로시저와 트리거

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

2. https://velog.io/@mrnglory/%EC%A0%95%EC%B2%98%EA%B8%B0-%EC%9A%94%EC%95%BD-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-%ED%8A%B8%EB%A6%AC%EA%B1%B0