[SQLD] 트리거(TRIGGER) 예제

트리거(TRIGGER)란?

2021.07.16 - [프로그래밍&IT/Oracle] - [Oracle] 트리거(Trigger)

[Oracle] 트리거(Trigger)

 

[예제]

어떤 쇼핑몰에 하루에 수만 건의 주문이 들어온다. 주문 데이터는 주문일자, 주문상품, 수량, 가격이 있으며, 사장을 비롯한 모든 임직원이 일자별, 상품별 총 판매수량과 총 판매가격으로 구성괸 주문 실적을 온라인상으로 실시간 조회한다고 했을 때, 한 사람의 임직원이 조회할 때마다 수만 건의 데이터를 읽어 계산해야 한다. 가끔 한 번씩 조회한다면 문제가 없을 수도 있으나 빈번하게 조회작업이 일어난다면 조회작업에 많은 시간을 허비할 수 있다.

트리거를 사용하여 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량과 판매금액을 집계하여 집계자료를 보관하도록 한다.

* 해당 주문 일자의 주문 상품 레코드가 존재하면 판매 수량(QTY)과 판매 금액(AMOUNT)을 더하고 존재하지 않으면 새로운 레코드를 입력한다.

 

 

테이블 생성

 

// 주문정보 테이블
CREATE TABLE ORDER_LIST(
    ORDER_DATE  CHAR(8) NOT NULL,
    PRODUCT     VARCHAR2(10) NOT NULL,
    QTY         NUMBER NOT NULL,
    AMOUNT      NUMBER NOT NULL
);


// 일자별 판매집계 테이블
CREATE TABLE SALES_PER_DATE(
    SALE_DATE   CHAR(8) NOT NULL,
    PRODUCT     VARCHAR2(10) NOT NULL,
    QTY         NUMBER NOT NULL,
    AMOUNT      NUMBER NOT NULL
);

 

트리거 생성

CREATE OR REPLACE TRIGGER SUMMARY_SALES
AFTER INSERT ON ORDER_LIST
FOR EACH ROW
DECLARE
    o_date ORDER_LIST.order_date%type;
    o_prod ORDER_LIST.product%type;
 BEGIN 
    o_date := :new.order_date;
    o_prod := :new.product;
    UPDATE SALES_PER_DATE
    SET qty = qty + :new.qty,
        amount = amount + :new.amount
    WHERE sale_date = o_date 
      AND product = o_prod;
     IF SQL%notfound THEN
        INSERT INTO SALES_PER_DATE
        VALUES(o_date,o_prod,:new.qty, :new.amount);
     END IF;
 END;
 /

[실행결과 확인]

1. 새로운 레코드 입력

 INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',10,300000);

 

- 주문정보 테이블 조회

SELECT * FROM ORDER_LIST;

 

- 일자별 판매 집계 테이블 조회
 SELECT * FROM SALES_PER_DATE;

 

주문정보 테이블과 일자별 판매 집계 테이블에 데이터가 삽입된다.

 

2. 해당 주문 일자의 주문 상품 레코드가 존재하는 경우

INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000);

 

- 주문정보 테이블 조회

SELECT * FROM ORDER_LIST;

 

- 일자별 판매 집계 테이블 조회
 SELECT * FROM SALES_PER_DATE;

주문일자와, 주문 상품이 같이 때문에  합산되어 업데이트 된다.

 

 

3. ROLLBACK

이전까지 입력한 데이터를 COMMIT한 뒤 데이터를 입력해준다.

INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000);

 

- 주문정보 테이블 조회

SELECT * FROM ORDER_LIST;

 

- 일자별 판매 집계 테이블 조회
 SELECT * FROM SALES_PER_DATE;

 

ROLLBACK 실행

 

- 주문정보 테이블 조회

SELECT * FROM ORDER_LIST;

 

- 일자별 판매 집계 테이블 조회
 SELECT * FROM SALES_PER_DATE;

 

 

※ 트리거는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 구문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.