트리거 TRIGGER

SET SERVEROUT ON;

• 트리거 생성. MEMBER2에 INSERT가 끝나면 BEGIN 실행

CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT 
    AFTER INSERT ON MEMBER2
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가됨');
    INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
    VALUES (:NEW.USERID, :NEW.USERPW, :NEW.USERNAME, :NEW.USERAGE, CURRENT_DATE);
END;
/

• 트리거 확인

INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES ('A1', 'A1', '이름', 12, CURRENT_DATE);
COMMIT;

• 테이블 구조, 내용 복사

CREATE TABLE MEMBER2_BK AS SELECT * FROM MEMBER2;

SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;

---

• UPDATE 트리거 생성.

CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE
    AFTER UPDATE ON MEMBER2
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 변경됨');
    DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :OLD.USERNAME);
    DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :NEW.USERNAME);
    UPDATE MEMBER2_BK SET USERNAME=:NEW.USERNAME
    WHERE USERID=:OLD.USERID;
END;
/

• 수정 트리거 확인

UPDATE MEMBER2 SET USERNAME='변경이름2' WHERE USERID='A';
COMMIT;

---

• 삭제 트리거 생성.

CREATE OR REPLACE TRIGGER TRI_MEMBER2_DELETE
    AFTER DELETE ON MEMBER2
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 삭제됨');
    DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :NEW.USERNAME);
    DELETE MEMBER2_BK WHERE USERID=:OLD.USERID;
END;
/

• 트리거 확인

DELETE MEMBER2 WHERE USERID='W10';
COMMIT;

---

• 일체형 트리거 생성.

CREATE OR REPLACE TRIGGER TRI_MEMBER2_IUD
    AFTER INSERT OR UPDATE OR DELETE ON MEMBER2
    FOR EACH ROW
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가됨');
        INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
        VALUES (:NEW.USERID, :NEW.USERPW, :NEW.USERNAME, :NEW.USERAGE, CURRENT_DATE);
    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 변경됨');
        DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :OLD.USERNAME);
        DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :NEW.USERNAME);
        UPDATE MEMBER2_BK SET USERNAME=:NEW.USERNAME
        WHERE USERID=:OLD.USERID;
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 삭제됨');
        DBMS_OUTPUT.PUT_LINE('변경 전 데이터: ' || :NEW.USERNAME);
        DELETE MEMBER2_BK WHERE USERID=:OLD.USERID;
    END IF;    
END;
/

---

• 주문 했을 때 주문수량만큼 물품테이블의 재고수량 변경

CREATE OR REPLACE TRIGGER TRI_ORDER2_INSERT 
    AFTER INSERT ON ORDER2
    FOR EACH ROW
DECLARE 
    V_QTY NUMBER:= 0;
BEGIN
    -- 물품테이블에서 재고수량을 가져옴.
    SELECT I.ITEMQTY INTO V_QTY
    FROM ITEM2 I WHERE I.ITEMNO =:NEW.ITEMNO;
    
    -- 주문수량이 재고수량보다 작거나 같아야 함.
    IF :NEW.ORDCNT <= V_QTY THEN
        -- 물품의 재고수량 주문수량만큼 뺌
        UPDATE ITEM2 SET ITEMQTY=ITEMQTY-:NEW.ORDCNT 
        WHERE ITEMNO=:NEW.ITEMNO;
    ELSE
        -- 오류 출력
        RAISE_APPLICATION_ERROR(-20022, '재고부족');
    END IF;
END;
/

---

• 실습
• 주문이 추가되면 주문백업 테이블(ORDER2_BK)에 자료 백업
• 추가, 수정, 삭제
• 추가시는 재고수량 감소
• 수정때는 재고수량을 감소, 증가
• 삭제시는 재고수량 증가

CREATE OR REPLACE TRIGGER TRI_ORDER2_IUD
    AFTER INSERT OR UPDATE OR DELETE ON ORDER2
    FOR EACH ROW
BEGIN    
    IF INSERTING THEN
        UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :new.ORDCNT 
        WHERE ITEMNO = :new.ITEMNO; 
    ELSIF UPDATING THEN
        UPDATE ITEM2 SET ITEMQTY = ITEMQTY + (:old.ORDCNT - :new.ORDCNT) 
        WHERE ITEMNO = :new.ITEMNO;
    ELSIF DELETING THEN
        UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :new.ORDCNT 
        WHERE ITEMNO = :new.ITEMNO;
    END IF;
END;
/