[ORACLE] 트리거 221202-02

❗ 트리거(TRIGGER)

 - 어떤 이벤트가 발생되면 자동적으로 다른 테이블의 변경동작이 수행 시키는
   일종의 프로시져
 - 자동적으로 실행되며 수동으로 실행시킬 수 없음
 - 트리거 대상 테이블에는 트리거 내용이 존재할 수 없음
 - 트렌젝션의 일부로 처리됨. 따라서 트리거 내부에 COMMIT,ROLLBACK,SAVEPOINT 등이
   존재할 수 없음

(사용형식)
  CREATE [OR REPLACE] TRIGGER 트리거이름
    BEFORE|AFTER INSERT|DELETE|UPDATE
    ON 테이블명
    [FOR EACH ROW]
    [WHERE 조건]
   [DECLARE]
     [선언영역]
   BEGIN
     트리거 본문;
   END;

•   트리거 타이밍 : 트리거 본문이 실행되는 시점으로 BEFORE 또는 AFTER 사용
•   이벤트 : 트리거 본문을 실행시키는 원인이 되는 DML명령으로 OR연산자로   조합 사용 가능
•  트리거 종류 
       - 문장단위 트리거 : 'FOR EACH ROW'가 생략된 트리거로 DML명령의 결과에 포함된 행의 수와
         관계없이 한번만 실행되는 트리거 -- WHEN 조건 사용X
       - 행단위 트리거 :  'FOR EACH ROW'가 기술한 트리거로 DML명령의 결과에 포함된 행의 수 만큼
         트리거 수행, :OLD, :NEW 의사레코드 사용가능
•  트리거 조건 : 'WHEN 조건'로 기술되며, 트리거 발생에 좀 더 구체적인 조건을 제시할 때 사용   
•  트리거 의사레코드와 트리거 함수

 - 의사레코드
    -------------------------------------------------------------------------
       의사레코드         내용
    -------------------------------------------------------------------------
       :NEW             이벤트가 INSERT, UPDATE일 때 사용되며 새롭게
                        입력(신규 입력이나 갱신을 위한 값)되는 행을 지칭
                        DELETE에 사용되면 모든 열이 NULL로 SETTING
       :OLD             이벤트가 DELETE, UPDATE일 때 사용되며 삭제 또는 갱신의
                        대상이 되는 행을 지칭
                        INSERT에 사용되면 모든 열이 NULL로 SETTING     
    - 트리거 함수 : 이벤트를 OR연산자로 구성한 경우 이벤트의 종류를 구분하기 위한 함수
    --------------------------------------------------------------------------
       함수              내용
    --------------------------------------------------------------------------
     INSERTING          이벤트가 INSERT 이면 true 반환
     UPDATING           이벤트가 UPDATE 이면 true 반환
     DELETING           이벤트가 DELETE 이면 true 반환

✔사용예) LPROD테이블에서 LPROD_ID가 12번인 자료를 삭제하고 삭제 후 '자료삭제 성공'이라는
               메시지를 출력하는 트리거 작성

CREATE OR REPLACE TRIGGER tg_delete_lprod
    AFTER DELETE ON LPROD 
   BEGIN
     DBMS_OUTPUT.PUT_LINE('자료삭제 성공');
   END;
   
   DELETE FROM LPROD WHERE LPROD_ID >=10;
   SELECT * FROM LPROD;

** HR계정의 EMPLOYEES 테이블에서 사원번호,사원명,입사일,직무코드를 조회하여
   EMP테이블을 생성하시오 

CREATE TABLE EMP AS
    SELECT EMPLOYEE_ID AS EMP_ID,
           EMP_NAME,
           HIRE_DATE,
           JOB_ID,
           DEPARTMENT_ID AS DEP_ID
      FROM HR.EMPLOYEES; 

COMMIT;

** HR계정의 EMP테이블을 복사하여 RETIRE 테이블을 생성하시오 모든 자료는 삭제

CREATE TABLE RETIRE AS 
    SELECT * FROM EMP;
   
  DELETE FROM RETIRE;

✔사용예) EMP테이블에서 사원번호 121~125번 사원을 삭제하시오. 단 삭제전 해당사원의 자료를
                RETIRE 테이블에 저장하시오

CREATE OR REPLACE TRIGGER tg_delete_emp
    BEFORE DELETE OR UPDATE ON EMP
    FOR EACH ROW
  BEGIN
    INSERT INTO RETIRE VALUES(:OLD.EMP_ID,:OLD.EMP_NAME,:OLD.HIRE_DATE,
                              :OLD.JOB_ID,:OLD.DEP_ID);
  END;
  
  DELETE FROM EMP WHERE EMP_ID =121 AND EMP_ID<=125;

✔ 사용예)장바구니에 입력,수정,삭제가 발생되었을때 재고 수불테이블에 재고 변경을
               수행하는 트리거를 작성하시오.

CREATE OR REPLACE TRIGGER tg_cart_change
    AFTER INSERT OR UPDATE OR DELETE ON CART
    FOR EACH ROW
   DECLARE
     L_QTY NUMBER:=0;
     L_PID PROD.PROD_ID%TYPE;
     L_DATE DATE;
   BEGIN
     IF INSERTING THEN
        L_QTY:=(:NEW.CART_QTY); 
        L_PID:=(:NEW.CART_PROD);
        L_DATE:=TO_DATE(SUBSTR(:NEW.CART_NO,1,8));
     ELSIF UPDATING THEN
        L_QTY:=(:NEW.CART_QTY-:OLD.CART_QTY); 
        L_PID:=(:NEW.CART_PROD);
        L_DATE:=TO_DATE(SUBSTR(:NEW.CART_NO,1,8));
     ELSIF DELETING THEN
        L_QTY:=-(:OLD.CART_QTY); 
        L_PID:=(:OLD.CART_PROD);
        L_DATE:=TO_DATE(SUBSTR(:OLD.CART_NO,1,8));
     END IF;   
        UPDATE REMAIN A
           SET A.REMAIN_O=A.REMAIN_O+L_QTY,
               A.REMAIN_J_99=A.REMAIN_J_99-L_QTY,
               A.REMAIN_DATE=L_DATE
         WHERE A.REMAIN_YEAR='2020'
           AND A.PROD_ID=L_PID;
           
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('예외발생'||SQLERRM);
   END;
   
   EXECUTE insert_cart_proc('b001',SYSDATE,'P201000001',10);
  
   UPDATE CART
      SET CART_QTY = 15
    WHERE CART_PROD='P201000001'
      AND CART_NO = '2022120200003';
    
   DELETE FROM CART
    WHERE CART_PROD='P201000001'
      AND CART_NO = '2022120200003';