Oracle day 9 - PL : Trigger 트리거

Trigger 트리거 

 

        Procedure : insert / delete / update
                            IN / OUT 으로 처리(매개변수)
        Function : select
                            매개변수와 RETURN으로 처리
        
        Trigger : 촉발시키다, 스스로 발생하다
                    callback : 이벤트가 발생했을 때 자동호출됨
                    트리거 = 자동 호출되는 함수
                                    old : 이전 값 / new: 새 값
                    insert  :         x                    o
                    delete :        o                   x
                    update :       o                   o
                    select도 같이 묶어서 사용하기도 함 (조회)
                    

-- 서버 키기

SET SERVEROUTPUT ON;

 

 

1. 트리거 생성하기

-- 매개변수는 없음
-- BEFORE: 구문을 실행했을 때 전에 발생시킴 (어떤 경우인지 뒤에 쓴다 : update, delete, insert)
-- AFTER : 구문을 실행했을 때 후에 발생시킴

CREATE OR REPLACE TRIGGER trigger_test
        BEFORE <--   변경 전에 트리거 호출
                UPDATE ON departments  <--  departments 테이블을 업데이트 할때 자동호출된다는 의미 
                FOR EACH ROW   <--   FOR문이 돌아가며 업데이트 여부를 검사함 
BEGIN
        DBMS_OUTPUT.PUT_LINE('변경 전 컬럼의 값 : ' || :OLD.department_name); -- OLD값을 출력
        DBMS_OUTPUT.PUT_LINE('변경 후 컬럼의 값 : ' || :NEW.department_name); -- NEW값을 출력        

         -- OLD와  NEW의 값을 가져올 때는 앞에 반드시 :(콜론)을 붙여주어야 실행이 된다. 
END;
/

 -- 위의 코드는 departments 테이블이 업데이트 되었을 때 전후의 값을 출력하는 자동호출함수(트리거)
 -- 따로 호출하지 않아도 자동적으로 호출이 된다
 

 

 

 

2. 트리거 확인하기

 UPDATE departments
 SET department_name = '정보기술부'
 WHERE department_id = 60;

-- 결과 --
변경 전 컬럼의 값 : IT
변경 후 컬럼의 값 : 정보기술부

 

 

---

Trigger - Insert

EX )  데이터를 추가할 때마다 급여의 평균을 확인해보고 싶은 경우

CREATE OR REPLACE TRIGGER sum_trigger
        BEFORE -- 값이 들어간 다음에 확인해보기
            INSERT OR UPDATE ON employees        -- employees 테이블의 삽입이나 수정이 있는 경우 모든 행을 검사하라
            FOR EACH ROW
DECLARE -- 변수 선언을 할때 사용
        avg_sal NUMBER;
BEGIN
        SELECT ROUND(AVG(salary),3)
                INTO avg_sal
        FROM employees;
        
        DBMS_OUTPUT.PUT_LINE('급여 평균 : ' || avg_sal);
END;
/

 

 

< 트리거 확인하기 >
-- 추가전 평균급여 : 6515.519
SELECT ROUND(AVG(salary),3)
FROM employees;
--데이터 추가
INSERT INTO employees( employee_id, last_name, hire_date, department_id,job_id,salary, email)
VALUES ( 502, 'TigeWWr', SYSDATE, 60, 'IT_PROG', 10000,'i12t@naver.com');

 

 

 

EX 2) 사원번호를 수정하지 못하게 할 경우
-- 사원번호 100번을 수정하지 못하도록 막기

CREATE OR REPLACE TRIGGER emp_trigger
        BEFORE 
        UPDATE OR DELETE OR INSERT ON employees -- 삽입 수정 삭제시 트리거 호출
        FOR EACH ROW
BEGIN
        IF UPDATING THEN -- 업데이트가 되었을 때
                IF :OLD.employee_id= '100' THEN
                    RAISE_APPLICATION_ERROR(-20001, '이 번호를 수정할 수 없습니다.');  

                    ---> RAISE_APPLICATION_ERROR : 에러 발생시키기

                END IF;
        END IF;
END;
/

< 트리거 확인하기 >
UPDATE employees
SET salary = 2500
WHERE employee_id = 100;

 

-- 데이터를 아예 입력하지 못하도록 막는 트리거
CREATE OR REPLACE TRIGGER nodata_tigger
        AFTER INSERT
        ON employees
BEGIN
             RAISE_APPLICATION_ERROR(-20000, '데이터 입력을 시도했습니다.');
             ROLLBACK;    <-- 데이터 입력이 들어오면 롤백
END;
/