Oracle SQL(7)

김성국·2023년 3월 2일

■ TRIGGER

01. 트리거 생성

트리거는 어떤 테이블에 insert, update, delete이 수행될때 자동으로 호출되는 프로시저이다.

이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다.

트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다.

트리거는 자동으로 COMMIT이 되기때문에 COMIIT, ROLLBACK을 사용할 수 없다.

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

02. INSERT

● INSERT 트리거 생성

CREATE OR REPLACE  TRIGGER tri_member_insert
    AFTER INSERT ON member
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    DBMS_OUTPUT.PUT_LINE('member 테이블에 데이터가 추가되었음');
END;
/

● INSERT_BACKUP 트리거 생성

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;

member테이블에 INSERT를 이용하셔 데이터를 추가할시 member_backup테이블에도 자동으로 데이터가 추가된다.

03. UPDATE

● UPDATE 트리거 생성

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;

● UPDATE BACKUP 트리거 생성

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;

04. DELETE

● 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 BACKUP 트리거 생성

CREATE OR REPLACE TRIGGER tri_member_delete
    AFTER DELETE ON member
    FOR EACH ROW --행의 데이터가 변환되면 적용됨
BEGIN
    DBMS_OUTPUT.PUT_LINE('member 테이블에 데이터가 삭제되었음');
END;

05. 3가지 복합(INSERT, UPDATE, DELET)

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;

05. 데이터 3가지를 동시에 변경하고 싶을때

UPDATE member SET username = 'a' , userage = 11, userphone = '010', usergender ='M' WHERE userid = 'a';
UPDATE member SET username = 'a' , userage = 11, userphone = '010' WHERE userid = 'a';
UPDATE member SET username = 'a' , userage = 11 WHERE userid = 'a';
UPDATE member SET username = 'a'  WHERE userid = 'a';

● JAVA

userid가 "a"이면 이름을 "axa"로 변경하고
userager가 0이 아니면 55로 변경
userphone가 NULL이 아니면 "010-5555"로 변경
usergender이 NULL이면 변경하지 않음

06. 컬럼명을 변수로 받아 찾기

컬럼명을 변수로 받아(map.put("column","userpw");) txt에 a가 포함되어 있는 userpw를 조회한다

● 문제1)

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

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;

● 문제2)

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

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
        UPDATE item SET quantity = quantity + (:old.cnt - :new.cnt) WHERE code = :new.code;
    ELSIF DELETING THEN
        UPDATE item SET quantity = quantity + :old.cnt WHERE code = :new.code;
    END IF;
END;

●문제3)

-- 아이디가 a,a1인 항목에 대해서 각각의 이름과 나이 일괄변경

UPDATE member SET
    username = (CASE
        WHEN userid = 'a' THEN '이름1000'
        WHEN userid = 'a1' THEN '이름1001'
    END),
    userage = (CASE
        WHEN userid = 'a' THEN 111
        WHEN userid = 'a1' THEN 222
    END)
    WHERE userid IN('a','a1');
COMMIT;

● 문제4)

upser
userid에 a1000이 있으면 UPDATE문을 사용 없으면 INSERT문을 사용

MERGE INTO
    member
USING DUAL
    ON (userid='xxxxxx')
    WHEN MATCHED THEN
        UPDATE SET username = 'bbbb', userage = 111
    WHEN NOT MATCHED THEN
        INSERT(userid, userpw, username, userage, userphone, usergender, userdate)
            VALUES('xx1','암호2','이름2', 22, '010-','M',CURRENT_DATE);