Tag Archives: oracle

[ORACLE] 트리거 사용 예제

오늘 오라클에 대해 잠깐 볼 기회가 생겨 보던중에 트리거라는 녀석과 마주치게 되었습니다.

그래도 공부한 내용인데 까먹지 않기 위해 기록해 둬야 겠습니다.

트리거를 이용해 특정 테이블의 데이터에 생기는 작업을 감시 하여 추가적인 작업을 수행할 수 있습니다.

마치 스프링 AOP의 Advice같은 느낌입니다. 하지만 DB에서 이런것을 마구 쓰다보면 퍼포먼스 저하는 자명한 결과일것 같네요.

[code]SQL> DESC RENTAL_LIST;
 이름                                      널?      유형
 —————————————– ——– —————————-
 NO                                        NOT NULL NUMBER(4)
 RENTAL_DATE                               NOT NULL DATE
 TO_RETURN_DATE                                     DATE
 RETURN_DATE                                        DATE
 CUSTOMER_NO                               NOT NULL NUMBER(4)
 DVD_NO                                    NOT NULL NUMBER(4)



SQL> DESC DVD_TITLE;
 이름                                      널?      유형
 —————————————– ——– —————————-
 NO                                        NOT NULL NUMBER(4)
 TITLE                                     NOT NULL VARCHAR2(20)
 GENRE                                              VARCHAR2(20)
 DIRECTOR                                           VARCHAR2(20)
 MAIN_ACTOR                                         VARCHAR2(20)
 PURCHASE_DATE                                      DATE
 QUANTITY                                  NOT NULL NUMBER(2)[/code]

위와 같은 스키마 구조일 때 DVD의 렌탈이 일어나면 DVD_TITLE의 QUANTITY를 1개 줄이고, 반납 일자인 RETURN_DATE를 RENTAL_DATE의 3일 후로 자동 입력하는 코드를 작성하여 봅시다.

BEFORE와 AFTER를 사용하면 됩니다.

[code]CREATE OR REPLACE TRIGGER CHANGE_QUANTITY
AFTER INSERT ON RENTAL_LIST FOR EACH ROW
BEGIN
  UPDATE DVD_TITLE SET QUANTITY = QUANTITY – 1 WHERE NO = :NEW.DVD_NO;
END;

INSERT INTO RENTAL_LIST(NO, RENTAL_DATE, TO_RETURN_DATE, RETURN_DATE, CUSTOMET_NO, DVD_NO)
VALUES(RENTAL_SEQ.NEXTVAL, ‘2007-11-02’, ‘2007-11-05’, ”, 50, 1) [/code]

위와 같은 쿼리문 실행시 :NEW.DVD_NO에는 방금 입력된 DVD_NO의 값이 대입됩니다. DVD_NO로 DVD_TITLE 테이블에서 값을 찾아 QUANTITY를 1 감소시키게 됩니다.

AFTER INSERT ON RENTAL_LIST 에서 알 수 있듯이 RENTAL_LIST에 INSERT가 일어난 후에 이 트리거가 실행됩니다.

FOR EACH ROW 는 여러 ROW가 영향을 받았을 경우 각각을 실행한다는 의미입니다.

이번에는 INSERT 직전에 RETURN_DATE에 RENTAL_DATE의 3일 후 날짜를 자동으로 입력하는 트리거를 작성해 보죠.

[code]CREATE OR REPLACE TRIGGER AUTO_ADDED_RETURN_DATE
BEFORE INSERT ON RENTAL_LIST FOR EACH ROW
BEGIN
  :NEW.RETURN_DATE := :NEW.RENTAL_DATE + 3
END;[/code]

BEFORE INSERT ON RENTAL_LIST 에서 볼 수 있듯이 INSERT가 일어나기 전에 실행됩니다.

값이 입력되지 않은 :NEW.RETURN_DATE 에 :NEW.RENTAL_DATE를 3증가시켜 입력합니다. +3을 하면 3일이 증가됩니다.

이로써 RETURN_DATE에도 값이 자동으로 입력됩니다.