Oracle 개발자 수업 47일차 - 트리거(TRIGGER)

1. 트리거(TRIGGER)

---

1. 트리거(TRIGGER)

[ 정의 ]

- 프로시저의 한 종류이다.
- 개발자의 호출이 아닌, 미리 지정한 특정 사건이 발생하면 자동으로 실행되는 프로시저 ( 예약 + 이벤트 )
- 특정 테이블 지정 -> 감시 -> (INSERT, UPDATE, DELETE) -> 미리 준비해놓은 프로시저가 자동 실행
- 트리거가 많아지면 시스템 속도가 느려진다.

 

[ 구문 ]

CREATE OR REPLACE TRIGGER 트리거명
    - 트리거 옵션
    BEFORE OR AFTER
    INSERT OR UPDATE OR DELETE ON 테이블명
    [FOR EACH ROW]
DECLARE
    선언부;
BEGIN
    실행부;
    [INSERTING, UPDAING, DELETING] ****
EXCEPTION
    예외처리부
END;

 

[ 기본 예제 1]

-- 트리거 예제에서 사용할 테이블, 시퀀스 생성
CREATE TABLE tblLog (
    seq NUMBER PRIMARY KEY,
    num NUMBER NOT NULL REFERENCES tblInsa(num),
    regdate DATE DEFAULT SYSDATE NOT NULL
)

CREATE SEQUENCE seqLog;

CREATE TABLE tblBoard (
    seq NUMBER PRIMARY KEY,
    num NUMBER NOT NULL REFERENCES tblInsa(num),
    subject VARCHAR2(1000) NOT NULL
)

CREATE SEQUENCE seqBoard;


-- 트리거 생성
-- 직원들 > tblBoard 글 작성 > 관리자 확인 + 모니터링 + tblLog
CREATE OR REPLACE TRIGER trgBoard
    AFTER         -- 사전 전/후 (BEFORE/AFTER)
    INSERT        -- 감시 사건 (INSERT, UPDATE, DELETE)
    ON tblBorad   -- 감시 대상 (테이블)
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('직원이 글을 작성 했습니다.');
END;

-- 실행
-- > 트리거 객체 생성 + 트리거 작동시작


-- 값 추가해서 확인하기
INSERT INTO tblBoard (seq, num, subject)
    VALUES (seqBoard.nextVal, (SELECT NUM FROM TBLINSA WHERE NAME = '홍길동'), '테스트 입니다.');

-- 실행
-- > 직원이 글을 작성 했습니다.
-- > 1 행 이(가) 삽입되었습니다.

 

[ 트리거 로그 기록 예제 2 ]

-- 트리거 생성
CREATE OR REPLACE TRIGGER trgBoard
    AFTER
    INSERT
    ON tblBoard
DECLARE
    vnum NUMBER;
BEGIN
    -- 누가 글을 작성 했는지 tblLog 테이블에 기록하기
    SELECT num INTO vnum FROM tblBoard WHERE seq = (SELECT MAX(seq) FROM tblBoard);
    
    -- 로그 기록
    INSERT INTO tblLog (seq, num, regdate) VALUES (seqLog.nextVal, vnum, default);
END;



-- 데이터 추가해서 확인하기
INSERT INTO tblBoard (seq, num, subject)
    VALUES (seqBoard.nextVal, (SELECT NUM FROM TBLINSA WHERE NAME = '홍길동'), '테스트 입니다.');

INSERT INTO tblBoard (seq, num, subject)
    VALUES (seqBoard.nextVal, (SELECT NUM FROM TBLINSA WHERE NAME = '이순신'), '테스트 입니다.');

INSERT INTO tblBoard (seq, num, subject)
    VALUES (seqBoard.nextVal, (SELECT NUM FROM TBLINSA WHERE NAME = '유관순'), '테스트 입니다.');

SELECT * FROM tblLog;
--> tblLog에 로그기록(누가 글을 작성 했는지) 저장확인

 

[ IF를 이용한 트리거 예제 3 ]

-- tblInsa, 직원 퇴사
-- 특정 요일(목요일)에는 퇴사를 할 수 없다.
CREATE OR REPLACE TRIGGER trgInsa
    BEFORE
    DELETE
    ON tblInsa
BEGIN
    IF TO_CHAR(SYSDATE, 'dy') = '목' THEN
        -- 현재 실행 되려는 DELETE 작업을 없던걸로 만들기 -> 강제로 예외 발생
        RAISE_APPLICATION_ERROR(-20001, '목요일에는 퇴사가 불가능합니다.');
    END IF;
END trgInsa;


-- 정상 작동하는지 테스트를 위한 DELETE
DELETE FROM tblInsa WHERE NUM = 1001; --> 현재 오늘 날짜는 2021-06-03 (목요일)이므로 삭제 X) -> '목요일에는 퇴사가 불가능합니다. 로그 출력

 

[ 트리거 로그 기록  예제 4 ]

-- tblMen
-- 테이블에서 발생하는 모든 변화(INSERT, UPDATE, DELETE)를 기록하는 로그 테이블

-- 기록할 테이블 생성
CREATE TABLE tblLogMen (
    seq NUMBER PRIMARY KEY,
    message VARCHAR2(1000) NOT NULL,
    regdate DATE DEFAULT SYSDATE NOT NULL
);
CREATE SEQUENCE seqLogMen;

-- 트리거 생성
CREATE OR REPLACE TRIGGER trgLogMen
    AFTER
    INSERT OR UPDATE OR DELETE  -- 트리거는 모든 변화를 한번에 작성할 수있다.
    ON tblMen
DECLARE
    vmessage VARCHAR2(1000);
BEGIN
    -- 예약 상수(INSERTING, UPDATING, DELETING)
    IF INSERTING THEN
        vmessage := '새 인원이 추가 되었습니다.';
    ELSIF UPDATING THEN
        vmessage := '특정 인원의 정보가 수정 되었습니다.';
    ELSIF DELETING THEN
        vmessage := '특정 인원이 삭제되었습니다.';
    END IF;
    
    INSERT INTO tblLogMen (seq, message, regdate)
        VALUES (seqLogMen.nextVal, vmessage, default);
END trgLogMen;


-- 테스트를 위한 DELETE, UPDATE
SELECT * FROM tblMen;
DELETE FROM tblMen WHERE NAME = '아무개';            --> 삭제시 tblLogMen에 기록이 남음
UPDATE tblMen SET WEIGHT = 80 WHERE NAME = '하하하'; --> 수정시 tblLogMen에 기록이 남음

-- 어떤 사건(INSERT, DELETE, UPDATE)이 있었는지 로그기록 확인
SELECT * FROM tblLogMen;

---

MEMO>

 

# 현재 데이터베이스 프로젝트에 집중하느라 복습이 많이 소홀한거같다. 프로젝트에 집중 할수록 수업에서 배운것을 한가지만 쓰지말고 여러 방법으로 다 적용해서 사용해보자.

 

# 트리거(TRIGGER)는 비유를 하자면 CCTV(감시) 한다고 생각하자.

    --> 언제 어느시각에 어떤 사건(INSERT, UPDATE, DELETE)가 있었는지 확인 할 수 있다고 생각하자.

 

# 이번주까지 DATABASE 수업이고 다음주에 웹(HTML, CSS, JAVASCRIPT) 시작!