[SIST] Oracle_days18

1. 트리거(Trigger)

1) 어떤 작업 전(before) 또는 작업 후(after) 트리거에 정의한 로직을 실행하는 PL/SQL의 한 종류

 

2) 대상(테이블)에 미리 트리거를 지정하면 어떤 이벤트(DML)가 발생할 때 자동으로 지정된 트리거가 작동(할당)

 

3) 트리거 예시

4) 트리거 키워드(예약어)
    ㄱ. 작업 전에 자동 처리되는 트리거 : BEFORE TRIGGER
    ㄴ. 작업 후에 자동 처리되는 트리거 : AFTER TRIGGER
    ㄷ. FOR EACH ROW : 행 마다 처리되는 트리거(ROW TRIGGER)
    ㄹ. REFERENCING : 영향받는 행의 값 참조
    ㅁ. :OLD -> 참조되기 전에 열(컬럼)의 값
    ㅂ. :NEW -> 참조한 후에 열(컬럼)의 값

 

5) 트리거 형식
    【형식】 
        CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE ¦ AFTER] -- BEFORE/AFTER 기본값은 AFTER
          trigger_event ON 테이블명
          [FOR EACH ROW [WHEN TRIGGER 조건]]
        DECLARE
          선언문
        BEGIN
          PL/SQL 코드
        EXCEPTION
          예외 처리 부분
        END;

        - 저장프로시저는 COMMIT을 해야되지만, 트리거는 자동으로 COMMIT/ROLLBACK 된다.

---

2. 트리거 생성 및 테스트하기

예시1) tbl_trigger1 테이블에 한 개의 레코드(행)을 INSERT ->대상(tbl_trigger1)에 이벤트(DML)가 발생하면 tbl_trigger2 테이블에 자동으로 로그를 기록하는 트리거

 

<트리거 확인>

        SELECT *
        FROM user_triggers;

 

<테이블 2개 생성>

        CREATE TABLE tbl_trigger1(
            id NUMBER PRIMARY KEY
            , name VARCHAR2(20)
        );
        
        CREATE TABLE tbl_trigger2(
           memo VARCHAR2(100) -- 로그 내용
            , ilja DATE DEFAULT SYSDATE -- 이벤트(DML)가 발생한 시간
        );

 

<트리거 선언> ut = user trigger
      <INSERT 트리거>
        CREATE OR REPLACE TRIGGER ut_exam01 AFTER -- 이벤트 작업이 완료된 후에 작동하는 트리거
        INSERT ON tbl_trigger1 -- 대상 테이블에 INSERT라는 이벤트가 발생할 때
        -- FOR EACH ROW 행트리거
        -- DECLARE -- 변수 선언할 필요없어서 주석처리
        BEGIN
            INSERT INTO tbl_trigger2 (memo) VALUES ('TBL_TRIGGER1 테이블에 새로운 데이터 추가됨');
        -- EXCEPTION
        END;

 

<이벤트 실행 및 확인>
        INSERT INTO tbl_trigger1 VALUES (1, 'admin');
        INSERT INTO tbl_trigger1 VALUES (1, 'hong');
         -- ORA-00001: unique constraint (SCOTT.SYS_C007485) violated
         -- 개체 무결성 위반하면 AFTER 트리거는 작동하지 않는다.
        
        INSERT INTO tbl_trigger1 VALUES (2, 'hong');
    
        ROLLBACK; -- 롤백을 하면 자동으로 트리거 작업도 롤백되어진다.

---

예시2) INSERT, UPDATE, DELETE를 했을 때 로그를 기록하는 트리거

 

<트리거 생성>

        CREATE OR REPLACE TRIGGER ut_exam01 AFTER -- 이벤트 작업이 완료된 후에 작동하는 트리거
        INSERT OR UPDATE OR DELETE ON tbl_trigger1 -- 대상 테이블에 INSERT라는 이벤트가 발생할 때
        -- FOR EACH ROW 행트리거
        -- DECLARE -- 변수선언할필요없어서 주석처리
        BEGIN
            IF INSERTING THEN
                INSERT INTO tbl_trigger2 (memo) VALUES ('TBL_TRIGGER1 테이블에 새로운 데이터 추가됨');
            ELSIF UPDATING THEN
                INSERT INTO tbl_trigger2 (memo) VALUES ('TBL_TRIGGER1 테이블에 데이터 수정됨');
            ELSIF DELETING THEN
                INSERT INTO tbl_trigger2 (memo) VALUES ('TBL_TRIGGER1 테이블에 데이터 삭제됨');
            END IF;
        -- EXCEPTION
        END;   

 

- DML(UPDATE) 자동으로 트리거 발생 -> 로그 기록
        UPDATE tbl_trigger1
        SET name = 'kim'
        WHERE id = 2;

- DML(DELETE) 자동으로 트리거 발생 -> 로그 기록
        DELETE FROM tbl_trigger1
        WHERE id = 2;   

        COMMIT;

---

예시3) tbl_trigger1 테이블에 근무시간X, 주말(토,일)에는 INSERT, UPDATE, DELETE 하면 에러 발생

 

<트리거 선언>
    CREATE OR REPLACE TRIGGER ut_exam02 BEFORE -- 이벤트 작업이 실행되기 전에 작동하는 트리거
    INSERT OR UPDATE OR DELETE ON tbl_trigger1 -- 대상 테이블에 INSERT라는 이벤트가 발생하기 전에
    -- FOR EACH ROW 행트리거
    -- DECLARE -- 변수선언할필요없어서 주석처리
    BEGIN
        -- 가정 : 근무시간은 12~18시
        IF TO_CHAR(SYSDATE, 'DY') IN ('토', '일')
           OR
           NOT(TO_CHAR(SYSDATE, 'HH24') BETWEEN 12 AND 18) THEN
            -- 에러 강제로 발생시키면 DML문도 취소가 되어짐
            RAISE_APPLICATION_ERROR(-20000, '지금은 근무시간 외 또는 주말이기에 작업 안됩니다.'); -- 에러코드번호, 메시지
        END IF;
    -- EXCEPTION
    END;

--> Trigger UT_EXAM02이(가) 컴파일되었습니다.

<실행> 새로운 데이터가 삽입되기 전에 트리거가 발생이 되어서 근무/주말 체크 후에 삽입 결정
    INSERT INTO tbl_trigger1 VALUES (2, 'hong');

INSERT INTO tbl_trigger1 VALUES (2, 'hong')
오류 보고 -
ORA-20000: 지금은 근무시간 외 또는 주말이기에 작업 안됩니다.
ORA-06512: at "SCOTT.UT_EXAM02", line 7
ORA-04088: error during execution of trigger 'SCOTT.UT_EXAM02'

---

예시4) 한 학생의 학번, 이름, 국어, 영어, 수학 -> tbl_trg1 에 INSERT       자동으로 tbl_trg2 테이블에 tot, avg INSERT 되는 트리거 생성 -> 테스트

 

<2개의 테이블 생성>
    create table tbl_trg1
    (
        hak varchar2(10) primary key
      , name varchar2(20)
      , kor number(3)
      , eng number(3)
      , mat number(3)
    );
    -- Table TBL_TRG1이(가) 생성되었습니다.

    create table tbl_trg2
    (
      hak varchar2(10) primary key
      , tot number(3)
      , avg number(5,2)
      , constraint fk_test2_hak foreign key(hak)   references tbl_trg1(hak)
    );
    -- Table TBL_TRG2이(가) 생성되었습니다.


<트리거 선언>
    CREATE OR REPLACE TRIGGER ut_trg1DML AFTER
    INSERT ON tbl_trg1
    FOR EACH ROW
    DECLARE
        vtot tbl_trg2.tot%TYPE;
        vavg tbl_trg2.avg%TYPE;
    BEGIN
        vtot := :NEW.kor + :NEW.eng + :NEW.mat;
        vavg := vtot/3;
        INSERT INTO tbl_trg2 (hak, tot, avg) VALUES (:NEW.hak, vtot, vavg);
    -- EXCEPTION
    END;

    -- ORA-04082: NEW or OLD references not allowed in table level triggers
    -- 원인 : 테이블 레벨 트리거에서 :NEW, :OLD 키워드 참조를 허용하지 않는다.
    -- 해결 : FOR EACH ROW를 넣으면 해결 -> 행 레벨 트리거로 선언

    --> Trigger UT_TRG1DML이(가) 컴파일되었습니다.

<실행>
    INSERT INTO tbl_trg1 ( hak, name, kor, eng, mat ) VALUES ( 1, 'hong', 90,78, 99 );

<확인>
    SELECT * FROM tbl_trg1;
    SELECT * FROM tbl_trg2;

    COMMIT;

---

예시5) 학생의 성적을 수정(UPDATE) -> 자동으로 tbl_trg2 테이블의 총점, 평균 수정

 

1 hong 90 78 99  :OLD
1 hong 87 67 100 :NEW

<트리거 선언>
    CREATE OR REPLACE TRIGGER ut_trg1DML AFTER
    INSERT OR UPDATE ON tbl_trg1
    FOR EACH ROW
    DECLARE
        vtot tbl_trg2.tot%TYPE;
        vavg tbl_trg2.avg%TYPE;
    BEGIN
        vtot := :NEW.kor + :NEW.eng + :NEW.mat; -- 1 hong 87 67 100 :NEW
        vavg := vtot/3;

        IF INSERTING THEN
            INSERT INTO tbl_trg2 (hak, tot, avg) VALUES (:NEW.hak, vtot, vavg);
        ELSIF UPDATING THEN
            UPDATE tbl_trg2
            SET tot = vtot , avg = vavg
            WHERE hak = :NEW.hak; -- == :OLD.hak;
        END IF;  
    -- EXCEPTION
    END;

    --> Trigger UT_TRG1DML이(가) 컴파일되었습니다.    

    <실행>
    UPDATE tbl_trg1
    SET kor = 87, eng = 67, mat = 100
    WHERE hak = 1;

    <확인>
    SELECT * FROM tbl_trg1;
    SELECT * FROM tbl_trg2;

    COMMIT;

---

문제) tbl_trg1 테이블에 학번 1인 학생을 삭제하면 자동으로 tbl_trg2 테이블의 학번 1 학생도 삭제하는 트리거를 만들어서 테스트하세요.

 

<트리거 선언>
    CREATE OR REPLACE TRIGGER ut_trg1DML AFTER
    INSERT OR UPDATE OR DELETE ON tbl_trg1
    FOR EACH ROW
    DECLARE
        vtot tbl_trg2.tot%TYPE;
        vavg tbl_trg2.avg%TYPE;
    BEGIN
        vtot := :NEW.kor + :NEW.eng + :NEW.mat; -- 1 hong 87 67 100 :NEW
        vavg := vtot/3;

        IF INSERTING THEN
            INSERT INTO tbl_trg2 (hak, tot, avg) VALUES (:NEW.hak, vtot, vavg);
        ELSIF UPDATING THEN
            UPDATE tbl_trg2
            SET tot = vtot , avg = vavg
            WHERE hak = :NEW.hak; -- == :OLD.hak;
        ELSIF DELETING THEN
            DELETE tbl_trg2
            WHERE hak = :OLD.hak; -- 새로운 데이터가 아니라 기존 데이터 이니까 :OLD
        END IF;  
    -- EXCEPTION
    END;

    <실행>
    DELETE tbl_trg1
    WHERE hak = 1;

    <확인>
    SELECT * FROM tbl_trg1;
    SELECT * FROM tbl_trg2;