개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › SQL

뷰, 프로시저, 트리거

INSERT 한 줄을 실행했는데 왜 재고가 줄었나

DB 안에는 테이블 말고도 저장해 둘 수 있는 것이 있습니다. 뷰는 저장해 둔 SELECT 문이고, 프로시저는 이름으로 부르는 여러 문장이며, 트리거는 INSERT나 UPDATE가 일어날 때 DB가 자동으로 실행하는 문장입니다.

실행으로 대조 · MySQL 8.4에 같은 뷰, 프로시저, 트리거를 만들고 열두 가지 조합의 주문을 실행해 대조

이 실습에 나오는 개념

뷰(View)
SELECT 문에 이름을 붙여 저장해 두고 테이블처럼 조회할 수 있게 한 것이다. 데이터를 따로 저장하지 않으며 조회할 때마다 정의된 쿼리가 실행된다. 복잡한 쿼리를 단순하게 쓰거나 일부 컬럼만 보여 주어 접근을 제한하는 데 쓴다.
저장 프로시저
여러 SQL 문장을 하나로 묶어 이름을 붙이고 DB 안에 저장해 둔 것이다. 애플리케이션은 CALL 이름(값)으로 부르기만 하면 되고, 안에 든 문장들은 DB 서버에서 차례로 실행된다. 여러 문장을 한 번의 호출로 처리할 수 있지만, 업무 규칙이 애플리케이션 코드가 아니라 DB 안에 놓이게 되어 코드만 읽어서는 동작을 알 수 없고 버전 관리와 테스트가 어려워진다. 프로시저라고 해서 안의 문장들이 자동으로 한 트랜잭션이 되지는 않으므로, 함께 성공하거나 함께 취소되어야 하면 트랜잭션으로 묶어야 한다.
트리거
테이블에 INSERT, UPDATE, DELETE가 일어날 때 DB가 자동으로 실행하도록 걸어 둔 문장이다. 어느 테이블의 어떤 변경에, 변경 전(BEFORE)과 후(AFTER) 가운데 언제 실행할지를 정해 만들며, 안에서는 NEW와 OLD로 바뀌는 행의 값을 읽을 수 있다. 변경 기록을 남기는 일처럼 빠뜨리면 안 되는 처리를 맡길 수 있지만, 문장을 보낸 쪽의 코드에는 전혀 드러나지 않는다. 그래서 왜 다른 테이블이 바뀌었는지, 왜 오류가 났는지 찾기 어렵게 만들 수 있고, 트리거가 실패하면 그것을 일으킨 원래 문장도 실패한다.
CHECK 제약
컬럼에 들어갈 수 있는 값의 조건을 테이블에 적어 두는 제약이다. 예를 들어 CHECK (stock >= 0)을 걸어 두면 재고를 음수로 만드는 INSERT나 UPDATE는 오류와 함께 거절된다. 어느 프로그램이 값을 바꾸든 DB가 마지막에 한 번 더 막아 준다는 점이 장점이다. MySQL은 8.0.16부터 이 제약을 실제로 검사하며, 그 전 버전은 문법만 받아들이고 검사하지 않았다.
트랜잭션
여러 데이터 변경 작업을 하나의 단위로 묶어, 전부 반영되거나(커밋) 전부 취소되게(롤백) 하는 것이다. 계좌 이체에서 출금과 입금 중 하나만 반영되는 중간 상태를 막는다. 데이터베이스가 정합성을 지키는 가장 기본적인 수단이다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. 준비: product 테이블에 키보드 한 행이 있고 재고는 10개입니다. v_stock 뷰와 stock_copy 복사본 테이블을 만들어 두었습니다. 뷰에는 SELECT 문만 저장되고, 복사본에는 지금의 결과(재고 10개)가 행으로 저장됩니다.
  2. INSERT INTO orders ... VALUES (1, 3): 애플리케이션이 실행한 문장은 이것 하나입니다. orders에 주문 한 행(수량 3개)이 들어갑니다.
  3. 트리거 trg_order_stock: orders에 INSERT가 일어났으므로 DB가 트리거를 자동으로 실행합니다. 트리거의 UPDATE가 재고를 10개에서 7개로 줄입니다. 애플리케이션 코드에는 이 UPDATE가 어디에도 없습니다.
  4. 트리거의 둘째 문장: stock_log에 "트리거"라는 메모와 함께 변경 기록 한 행을 넣습니다. 여기까지가 INSERT 한 문장 안에서 일어난 일입니다.
  5. SELECT stock FROM v_stock: 뷰에는 데이터가 없고 SELECT 문만 저장돼 있습니다. 조회하는 지금 product를 읽어 7개를 돌려줍니다. 뷰를 고친 적은 없지만 원본 테이블이 바뀌었으므로 결과도 바뀌었습니다.
  6. SELECT stock FROM stock_copy: 복사본 테이블은 만들 때의 결과를 자기 행으로 저장해 두었습니다. product가 바뀌어도 따라 바뀌지 않으므로 10개 그대로입니다. 실제 재고와 어긋난 값입니다.

실행 결과

코드

-- product 테이블: (1, '키보드', stock 10)
-- stock 컬럼에는 CHECK (stock >= 0) 제약이 있다

CREATE VIEW v_stock AS            -- 뷰: SELECT 문만 저장
  SELECT name, stock FROM product;
CREATE TABLE stock_copy AS        -- 복사본: 지금의 결과를 행으로 저장
  SELECT name, stock FROM product;

CREATE TRIGGER trg_order_stock AFTER INSERT ON orders FOR EACH ROW
BEGIN
  UPDATE product SET stock = stock - NEW.qty WHERE id = NEW.product_id;
  INSERT INTO stock_log (product_id, change_qty, memo)
    VALUES (NEW.product_id, -NEW.qty, '트리거');
END;

-- 애플리케이션이 실행하는 문장은 이것 하나
INSERT INTO orders (product_id, qty) VALUES (1, 3);

SELECT stock FROM v_stock;        -- 뷰로 조회
SELECT stock FROM stock_copy;     -- 복사본으로 조회

이 모형의 전제

열두 가지 조합 모두, 화면의 SQL과 같은 테이블, 뷰, 프로시저, 트리거를 이 PC의 MySQL 8.4(InnoDB, 자동 커밋 켜짐)에 만들고 주문 문장을 실행해 오류 여부, 각 테이블의 행 수, 재고, 뷰와 복사본의 값을 대조했습니다. 뷰가 데이터를 저장하지 않는다는 것은 information_schema에서 v_stock이 저장 엔진이 없는 VIEW로, stock_copy가 InnoDB 테이블로 나오는 것으로 확인했습니다. "트랜잭션으로 묶고 ROLLBACK하면 재고가 돌아온다"는 것도 같은 DB에서 한 번 실행해 확인했습니다. 트리거가 실패했을 때 원래 문장이 함께 취소되는 것은 InnoDB처럼 트랜잭션을 지원하는 엔진에서의 동작입니다. 프로시저 안에서 오류가 났을 때 어디까지 남는지는 자동 커밋 설정, 프로시저 안의 START TRANSACTION과 오류 처리기(HANDLER)에 따라 달라지며, 여기서는 그런 장치가 없는 가장 단순한 프로시저를 썼습니다. 재고가 모자랄 때의 오류는 CHECK 제약(MySQL 8.0.16부터 실제로 검사)에서 나오게 했습니다.

앞 실습: OFFSET과 키셋 페이징 · 다음 실습: 이진 탐색과 B-트리

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요