빅데이터 Java 개발자 교육 - 23일차 [Oracle 8번째 시간 (트리거)]

Jun_Gyu·2023년 3월 2일
oracle트리거
0

빅데이터 Java 개발자 교육

목록 보기
23/71
post-thumbnail

금일 시간에는 "트리거(TRIGGER)"에 대해서 알아보는 시간을 가졌다.

들어가기에 앞서서 트리거의 개념부터 잡고 들어가보도록 하자.

🔫 트리거(TRIGGER)란?

  • 트리거는 DB 시스템에서 데이터의 INSERT, UPDATE, DELETE 등의 데이터 변화가 일어날 때마다 자동적으로 수행되도록 정의할 수 있는 프로시저이다.
  • 트리거는 VIEW에 대해서가 아닌 TABLE에 관해서만 정의될 수 있다. (데이터 직접적)
  • 트리거는 SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현하고, 관련 테이블의 데이터를 일치시킬 때 주로 사용된다.
  • 조건과 함께 데이터 무결성을 지키는 하나의 방법으로써, 특정 이벤트에 대해서 연속적으로 자동 동작하는 특수한 형태의 저장 프로시저라고 볼 수 있다.


트리거의 종류

트리거의 종류는 흔히 2가지로 나눌 수 있다.

하나는 문장트리거 , 다른하나는 행트리거 이렇게 총 두가지로 나뉘게 된다.

먼저 문장트리거이다.

1. 문장 트리거

UPDATE member SET username = '새로운이름';

우리는 지금까지 SQL문을 공부하면서 알게 모르게 사용해오고 있었다.
INSERT, UPDATE, DELETE 등이 바로 이 문장트리거에 해당하는 내용이다.

위와 같은 내용의 트리거는 많은 행에 작업들이 변경된다 하더라도 단 한번만 실행되는것이 특징이다.



2. 행 트리거

쿼리문의 조건을 만족하는 여러 개의 행에 대해서 트리거를 여러번 반복수행하는 방법으로
[FOR EACH ROW WHEN 조건] 절로 정의가 되며, 컬럼의 데이터행에 변화가 발생하면 실행이 되고, 변경후의 행은 :OLD.~ , :NEW.~를 통해서 값을 불러올 수 있다.

행 트리거의 SQL문은 다음과 같이 이루어진다.

CREATE OR REPLACE TRIGGER 트리거명
    [BEFORE|AFTER] [INSERT, DELETE, UPDATE] ON 테이블명
    [REFERENCING NEW|OLD TABLE AS 테이블명]
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
	[WHEN 조건식]
BEGIN
-- 쿼리문 (문장트리거 + (:old.~), (:new.~))
END;
/

여기서 생소할 몇가지 문법들에 대해서 설명을 부가하겠다.

  • AFTER: 테이블이 변경된 후에 트리거가 실행되는 옵션이다.
  • BEFORE: 테이블이 변경되기 전에 트리거가 실행되는 옵션이다.
  • 변수에 값을 치환할 때는 예약어 SET을 사용한다.

이번에는 위의 내용을 바탕으로 실습을 진행해보도록 하겠다.



1. INSERT 트리거

-- 기본테이블 INSERT (+ 트리거)

CREATE OR REPLACE TRIGGER tri_memberTB_insert
    AFTER INSERT ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    DBMS_OUTPUT.PUT_LINE('memberTB 테이블에 데이터가 추가되었음.'); 
    -- 멤버 데이터가 추가될 때 마다 메세지가 출력됨.
END;
/
COMMIT;

INSERT INTO memberTB(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE)
VALUES ('tri_MEMBER123', 'PWPW', 'MN', '010-1111-1111', '부산', 1, 1, CURRENT_DATE);

memberTB 테이블에 데이터가 추가되었음.


1 행 이(가) 삽입되었습니다.


2. UPDATE 트리거

CREATE OR REPLACE TRIGGER tri_memberTB_update
    AFTER UPDATE ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    DBMS_OUTPUT.PUT_LINE('memberTB 테이블에 데이터가 변경되었음.'); 
    DBMS_OUTPUT.PUT_LINE('변경전 이름 : ' || :old.MEM_NAME); 
    DBMS_OUTPUT.PUT_LINE('변경후 이름 : ' || :new.MEM_NAME); 
END;
/
COMMIT;

UPDATE MEMBERTB SET MEM_NAME = '변경된이름2', MEM_PHONE= '001100' WHERE MEM_ID ='TEST_ID';

memberTB 테이블에 데이터가 변경되었음.
변경전 이름 : 룰루랄라2
변경후 이름 : 변경된이름2


1 행 이(가) 업데이트되었습니다.


3. DELETE 트리거

-- 기본테이블 DELETE

CREATE OR REPLACE TRIGGER tri_memberTB_delete
    AFTER DELETE ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    DBMS_OUTPUT.PUT_LINE('memberTB 테이블에 데이터가 삭제되었음.'); 
    DBMS_OUTPUT.PUT_LINE('삭제된 아이디 : ' || :old.MEM_ID);  
END;
/
COMMIT;

  DELETE memberTB WHERE MEM_ID = 'AAA3';
memberTB 테이블에 데이터가 삭제되었음.
삭제된 아이디 : AAA3


1 행 이(가) 삭제되었습니다.


아래 내용은 기존 테이블의 정보를 백업해두는 백업 테이블을 만들어 트리거를 이용하여 데이터를 미러링해보도록 하겠다.

백업 테이블 생성

CREATE TABLE memberTB_backup AS SELECT * FROM memberTB; -- 백업테이블 생성, 기존 테이블의 구조 및 데이터를 복사하는 용도로 사용.

SELECT * FROM memberTB_backup; 
SELECT * FROM memberTB;
-- 두 테이블은 데이터가 동일함.

1. INSERT 트리거

-- 백업테이블 insert

CREATE OR REPLACE TRIGGER tri_memberTB_backup_insert
    AFTER INSERT ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    INSERT INTO memberTB_backup(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE)
    VALUES(:NEW.MEM_ID, :NEW.MEM_PW, :NEW.MEM_NAME, :NEW.MEM_PHONE, :NEW.MEM_ADDRESS, :NEW.MEM_BLOCK_CHK, :NEW.MEM_QUIT_CHK, CURRENT_DATE);   -- :NEW. 은 새로 받아온 값을 그대로 적용시키는 것.
    -- 날짜는 :NEW. 가 아니라 CUREENT로 받기. (백업자료 테이블이 아닌 원본자료의 업로드 시간이 저장되니까!)
    -- COMMIT; 은 사용할 수 없음.
    -- 왜? 자동으로 COMMIT 되니까!
    -- 또한, 여기는 백업테이블이기때문에 굳이 메세지를 출력할필요가 없음!
END;
/

결과값은 기존의 MemberTB와 똑같은 내용이기 때문에 별도로 추가하진 않겠다.



2. UPDATE 트리거

-- 백업테이블 update

CREATE OR REPLACE TRIGGER tri_memberTB_backup_update
    AFTER UPDATE ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
  UPDATE memberTB_backup SET MEM_NAME = :NEW.MEM_NAME, MEM_PHONE= :NEW.MEM_PHONE WHERE MEM_ID = :OLD.MEM_ID;
END;
/


4.DELETE 트리거

-- 백업테이블 UPDATE

CREATE OR REPLACE TRIGGER tri_memberTB_backup_update
    AFTER UPDATE ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
  UPDATE memberTB_backup SET MEM_NAME = :NEW.MEM_NAME, MEM_PHONE= :NEW.MEM_PHONE WHERE MEM_ID = :OLD.MEM_ID;
END;
/



이번에는 좀 더 심화하여 INSERT, UPDATE, DELETE 이 세가지를 한번에 다룰 수 있는 트리거의 쿼리문을 작성해보도록 하겠다.

5. Action 트리거 (INSERT, UPDATE, DELETE)

CREATE OR REPLACE TRIGGER tri_memberTB_backup_action
    AFTER INSERT or UPDATE or DELETE ON memberTB
    FOR EACH ROW -- 행의 데이터가 변환되면 적용됨.
BEGIN
    IF INSERTING THEN
     INSERT INTO memberTB_backup(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE)
        VALUES(:NEW.MEM_ID, :NEW.MEM_PW, :NEW.MEM_NAME, :NEW.MEM_PHONE, :NEW.MEM_ADDRESS, 
            :NEW.MEM_BLOCK_CHK, :NEW.MEM_QUIT_CHK, CURRENT_DATE);
    ELSIF UPDATING THEN
        UPDATE memberTB_backup SET MEM_NAME = :NEW.MEM_NAME, MEM_PHONE= :NEW.MEM_PHONE 
        WHERE MEM_ID = :OLD.MEM_ID;
    
    ELSIF DELETING THEN
    DELETE memberTB_backup WHERE MEM_ID = :OLD.MEM_ID;
  
    END IF;
END;
/

이번에는 위의 예제들을 바탕으로 주어진 여러 문제들을 해결해보았다.

문제1

-- 문제1) PURCHASE 테이블에 주문이 추가되면 물품 테이블의 재고수량을 주문한 만큼 차감시키는 트리거 생성 (상품갯수 이상으로는 주문을 할 수 없음.)
-- 트리거명 : tri_purchase_update_item
CREATE OR REPLACE TRIGGER tri_purchase_update_item
    AFTER INSERT OR UPDATE ON PURCHASE 
    FOR EACH ROW
BEGIN
   
    UPDATE ITEM SET QUANTITY = (QUANTITY - :NEW.CNT) WHERE CODE = (:NEW.CODE); -- :NEW. , :OLD. 두 명령어는 두번째 트리거 명령어 두번째줄 테이블의 구성요소를 가르킨다.

END;
/

 INSERT INTO PURCHASE (NO, CNT, REGDATE, CODE, USERID)
    VALUES(SEQ_PURCHASE_NO.NEXTVAL, 2015, CURRENT_DATE, 1, 'a');
COMMIT;

-- 업데이트 확인용
SELECT * FROM PURCHASE;
SELECT * FROM ITEM;

문제2

-- 문제2) PURCHASE 테이블에 주문수량을 변경하면 재고수량을 변경시키는 트리거
-- 기본 주문수량 10일경우 9로 변경되면 재고 수량 1 증가됨.
CREATE OR REPLACE TRIGGER tri_purchase_CNTupdate_item
    AFTER UPDATE OR UPDATE ON PURCHASE 
        FOR EACH ROW
BEGIN 
-- EX1)10 시켰는데 4개로 변경 = 재고 6개 증가
    IF :OLD.CNT > :NEW.CNT THEN
        UPDATE ITEM SET QUANTITY = (QUANTITY + (:OLD.CNT - :NEW.CNT))  WHERE CODE = :NEW.CODE;
    
-- EX2)10 시켰는데 14개로 변경 = 재고 4개 추가감소
    ELSE
        UPDATE ITEM SET QUANTITY = (QUANTITY - (:NEW.CNT - :OLD.CNT))  WHERE CODE = :NEW.CODE;
    END IF;
END;
/

SELECT * FROM PURCHASE;
SELECT * FROM ITEM;

문제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 -- INSERTING, UPDATING, DELETING은 변수이름을 정해준게 아니라, 그 자체의 기능임! (내용 안에다가 따로 추가되는 SQL문을 넣을필요가 없음.)
            UPDATE ITEM SET QUANTITY = (QUANTITY - :NEW.CNT)  WHERE CODE = :NEW.CODE;
            DBMS_OUTPUT.PUT_LINE('해당 주문이 추가되었습니다.'); 
            DBMS_OUTPUT.PUT_LINE('추가된 주문번호 : ' || :NEW.NO); 
            DBMS_OUTPUT.PUT_LINE('추가된 주문코드 : ' || :NEW.CODE);
            DBMS_OUTPUT.PUT_LINE('추가된 주문수량 : ' || :NEW.CNT);
            
        
     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;
         DBMS_OUTPUT.PUT_LINE('해당 주문이 변경되었습니다.');
         DBMS_OUTPUT.PUT_LINE('변경된 주문번호 : ' || :OLD.NO);
         DBMS_OUTPUT.PUT_LINE('변경된 주문수량 : ' || :OLD.CNT);

     ELSIF DELETING THEN
            DBMS_OUTPUT.PUT_LINE('해당 주문이 삭제되었습니다.'); 
            DBMS_OUTPUT.PUT_LINE('삭제된 주문번호 : ' || :OLD.NO); 
        UPDATE ITEM SET QUANTITY = (QUANTITY + :OLD.CNT)  WHERE CODE = :NEW.CODE;
     END IF;
END;
/
COMMIT;

SELECT * FROM PURCHASE;
SELECT * FROM ITEM;

INSERT INTO PURCHASE (NO, CNT, REGDATE, CODE, USERID)
    VALUES(SEQ_PURCHASE_NO.NEXTVAL, 2015, CURRENT_DATE, , 'a');
    
UPDATE PURCHASE SET CNT = 20 WHERE NO = 10051;

DELETE PURCHASE WHERE NO = 10050;

추가로, 여러 트리거가 활성화되어있으면 오류가 발생하거나 속도가 느려질 수 있으니 아래의 쿼리문을 숙지해두도록 하자!

트리거 비활성화, 활성화

-- 트리거 비활성화, 활성화
ALTER TRIGGER tri_purchase_CNT_action DISABLE;
ALTER TRIGGER tri_purchase_CNT_action ENABLE;

마지막으로는 Mybatis를 활용하여 일괄추가함과 동시에 UPSERT(값이 존재하면 UPDATE, 없으면 INSERT) 되도록 SQL문과 Mapper코드를 구성해보았다.

일괄추가(SQL문 + Java)

-- 일괄추가 (시퀀스, 함수)
--(지난시간에 했었음!)

-- 일괄수정 (한번에 두 데이터를 하나의 동일한 내용으로 수정)
SELECT M.* FROM MEMBERTB M ORDER BY MEM_ID ASC;
UPDATE MEMBERTB SET MEM_NAME = '일괄수정된 이름' , MEM_PHONE = 1111 WHERE MEM_ID = 'AAA1' OR MEM_ID = 'AAA2'
/

-- 일괄 수정(두가지 데이터를 각각 수정)
UPDATE MEMBERTB SET
    MEM_NAME = (CASE
        WHEN MEM_ID = 'AAA1' THEN '각각변경된 이름1번' -- MyBatis (1) 
        WHEN MEM_ID = 'AAA2' THEN '각각변경된 이름2번'
    END),
    MEM_ADDRESS=(CASE
        WHEN MEM_ID = 'AAA1' THEN '부산광역시 사하구'  -- MyBatis (2) 
        WHEN MEM_ID = 'AAA2' THEN '부산광역시 진구'
    END)
        WHERE MEM_ID IN ('AAA1','AAA2');             -- MyBatis (3) 
COMMIT;

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import dto.MemberTB;

@Mapper
public interface MemberMapper {


@Update({
		" <script> ",
		" UPDATE MEMBERTB SET ",
			" MEM_NAME = (CASE ",
				" <foreach collection = 'list' item = 'obj' separator=' ' > ", // separator -> 하나의 쿼리문 끝에 붙어있는 구분자  
					" WHEN MEM_ID = #{obj.MEM_ID} THEN #{obj.MEM_NAME} ",
				" </foreach> ",
			" END), ",
			"  MEM_ADDRESS=(CASE ",
		        " <foreach collection = 'list' item = 'obj' separator=' ' > ",
			    	" WHEN MEM_ID = #{obj.MEM_ID} THEN #{obj.MEM_ADDRESS} ",
			    " </foreach> ",
			" END) ",
			 " WHERE MEM_ID IN ( ",
			 	" <foreach collection = 'list' item = 'obj' separator=',' > ",      
			        " #{obj.MEM_ID} ",
			    " </foreach> ",
			" ) ",
			" </script> "
	})
	public int memberUpdateBatch(@Param("list") List<MemberTB> list);
}
ERGE INTO MEMBERTB USING DUAL ON (MEM_ID = '업데이트')
    WHEN MATCHED THEN
        UPDATE SET MEM_NAME='UPSET_새이름', MEM_PHONE = 0110101
    WHEN NOT MATCHED THEN
        INSERT (MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE)
            VALUES ('업데이트', 'PWPW', 'MN', '010-1111-1111', '부산시', 1, 1, CURRENT_DATE);
COMMIT;

SELECT * FROM MEMBERTB;

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import dto.MemberTB;

@Mapper
public interface MemberMapper {

@Update({
		" <script> ",
		" MERGE INTO MEMBERTB USING DUAL ON (MEM_ID = #{obj.MEM_ID}) ",
			" WHEN MATCHED THEN ",
				" UPDATE SET MEM_NAME = #{obj.MEM_NAME}, MEM_PHONE = #{obj.MEM_PHONE} ",
			" WHEN NOT MATCHED THEN ",
				" INSERT (MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE) ",
					" VALUES (#{obj.MEM_ID}, #{obj.MEM_PW}, #{obj.MEM_NAME}, #{obj.MEM_PHONE}, ",
					" #{obj.MEM_ADDRESS}, #{obj.MEM_BLOCK_CHK}, #{obj.MEM_QUIT_CHK}, CURRENT_DATE) ",
		" </script> "
	})
	public int memberUpsertBatch(@Param("obj") MemberTB obj);
}

[참고글 출처]
https://mine-it-record.tistory.com/107

profile
Jun_Gyu
시작은 미약하지만, 그 끝은 창대하리라
이전 포스트

빅데이터 Java 개발자 교육 - 22일차 [Oracle 7번째 시간 (프로시저)]

다음 포스트

빅데이터 Java 개발자 교육 - 24일차 [Oracle 9번째 시간 (평가 전 최종복습, 데이터 부분 수정 및 조회)]

0개의 댓글