トリガTRAIGGER

SET SERVEROUT ON;

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;

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;
/

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;
/