백엔드/SQL

Oracle SQL : 2023-03-02

SeungEunii 2023. 3. 2. 11:20

1. 트리거(Trigger)

 

CREATE OR REPLACE TRIGGER 트리거명
    (BEFORE | AFTER) INSERT, DELETE, UPDATE ON 테이블명
    FOR EACH ROW  --행의 데이터가 변환되면 적용됨
BEGIN


END;
/

 

 

 

테이블 백업 (테이블 구조 및 데이터 복사)

 

CREATE TABLE member_backup AS SELECT * FROM member;

 

 

 

<예시>

 

INSERT

 

member 테이블 데이터 추가

 

CREATE OR REPLACE TRIGGER tri_member_insert
    AFTER INSERT ON member
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('member 테이블에 데이터가 추가되었음');
END;
/

 

 

CREATE OR REPLACE TRIGGER tri_member_backup_insert
    AFTER INSERT ON member
    FOR EACH ROW
BEGIN
    INSERT INTO member_backup(userid, userpw,username,userage,userphone, usergender,userdate) 
           VALUES(:new.userid, :new.userpw, :new.username, :new.userage, :new.userphone, :new.usergender, CURRENT_DATE);
                --COMMIT은 사용할 수 없음, 자동으로 COMMIT됨.
END;
/

 

 

INSERT INTO member(userid, userpw, username, userage, userphone, usergender, userdate) 
                VALUES('la','la','회원236',55,'010-0000-8201','F',CURRENT_DATE);

 

실행하면 백업한 테이블에 자동으로 추가해줌

 

실행했을 때 나오는 글

 

member 테이블

member 테이블

 

member_backup 테이블

member_backup 테이블

 

 

 

 

UPDATE

 

member 테이블 수정

CREATE OR REPLACE TRIGGER tri_member_update
    AFTER UPDATE ON member
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('member 테이블에 데이터가 변경됨');
    DBMS_OUTPUT.PUT_LINE('변경전 이름 =>' || :old.username);
    DBMS_OUTPUT.PUT_LINE('변경후 이름 =>' || :new.username);
END;
/

 

 

CREATE OR REPLACE TRIGGER tri_member_backup_update
    AFTER UPDATE ON member
    FOR EACH ROW
BEGIN
    UPDATE member_backup SET username=:new.username, userage=:new.userage  WHERE userid=:old.userid;
END;
/

 

 

UPDATE member set username='회원110', userage=58 WHERE userid='lal';

 

 

 

실행했을 때

 

member 테이블

member 테이블

 

member_backup 테이블

member_backup 테이블

 

둘 다 변경된걸 볼 수 있다

 

 

 

 

DELETE

 

CREATE OR REPLACE TRIGGER tri_member_backup_delete
    AFTER DELETE ON member
    FOR EACH ROW
BEGIN
    DELETE FROM member_backup WHERE userid=:old.userid;
END;
/

 

 

DELETE FROM member WHERE userid='lal';

member 테이블,  member_backup 테이블의 id가 lal인 데이터가 삭제 됨

 

 

 

 

INSERT OR UPDATE OR DELETE 

 

CREATE OR REPLACE TRIGGER tri_member_backup_action
    AFTER INSERT OR UPDATE OR DELETE ON member
    FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO member_backup(userid, userpw,username,userage,userphone, usergender,userdate) 
                VALUES(:new.userid, :new.userpw, :new.username, :new.userage, :new.userphone, :new.usergender, CURRENT_DATE);
    ELSIF UPDATING THEN
        UPDATE member_backup SET username=:new.username, userage=:new.userage  WHERE userid=:old.userid;
    ELSIF DELETING THEN
        DELETE FROM member_backup WHERE userid=:old.userid;
    END IF;
END;
/

 

3개 동시에도 가능

 

 

 

 

 

문제 1)  purchase테이블에 주문이 추가되면 item 테이블의 재고 수량을 주문한 만큼 차감 시키는 트리거 생성

 

CREATE OR REPLACE TRIGGER tri_purchase_update_item
    AFTER INSERT ON purchase
    FOR EACH ROW
BEGIN
    UPDATE item SET quantity = quantity - :new.cnt WHERE code = :new.code;
END;
/

 

INSERT INTO purchase(no, cnt, regdate, code, userid) 
		VALUES(seq_purchase_no.NEXTVAL, 5, CURRENT_DATE, 1, 'a1');

 

변경 전 item

 

변경 후 item

 

 

 

문제 2) purchase 테이블의 주문수량을 변경하면 재고수량을 변경 시키는 트리거
         - 기본 주문수량 10일경우 9로 변경되면 재고수량 1증가 됨 
         - 기본 주문수량 10일경우 11로 변경되면 재고수량 1감소 됨

 

뭔소리지?

원래 item 재고수량이 20개였다고 가정했을 때 

원래 주문 수량이 10이였는데 변심해서 9로 변경하면 item 재고수량이 10개가 될게 11이 되고 
원래 주문 수량이 10이였는데 변심해서 11로 변경하면 item 재고수량이 10개가 될게 9개가 된다고?

 

CREATE OR REPLACE TRIGGER tri_purchase_cnt_item
    AFTER UPDATE ON purchase
    FOR EACH ROW
BEGIN
    IF :old.cnt > :new.cnt THEN --재고수량 증가
          UPDATE item SET quantity = quantity + (:old.cnt - :new.cnt) WHERE code = :new.code;
    ELSE                        --재고수량 감소
        UPDATE item SET quantity = quantity - (:new.cnt - :old.cnt ) WHERE code = :new.code;
    END IF;
END;
/

 

UPDATE purchase SET cnt=16 WHERE no=10012;

 

 

 

문제 3) 주문 내역의 추가, 수정, 삭제시 재고수량을 실시간으로 변경하는 트리거 작성

 

3개 동시에 하기

CREATE OR REPLACE TRIGGER tri_purchase_cnt_action
    AFTER INSERT OR UPDATE OR DELETE ON purchase
    FOR EACH ROW
BEGIN
    IF INSERTING THEN
        UPDATE item SET quantity = quantity - :new.cnt WHERE code = :new.code;
    ELSIF UPDATING THEN
        IF :old.cnt > :new.cnt THEN --재고수량 증가
            UPDATE item SET quantity = quantity + (:old.cnt - :new.cnt) WHERE code = :new.code;
        ELSE                        --재고수량 감소
            UPDATE item SET quantity = quantity - (:new.cnt - :old.cnt ) WHERE code = :new.code;
        END IF;
    ELSIF DELETING THEN
        UPDATE item SET quantity = quantity + :old.cnt WHERE code = :old.code;
    END IF;
END;
/

 

 

 

 

 

 

트리거 활성화, 비활성화

 

ALTER TRIGGER tri_트리거명 DISABLE|ENABLE;

DISABLE 비활성화, ENABLE 활성화

 

색을 잃은게 비활성화 된 것