PL//Trigger

    Trigger : 촉발시키다, 스스로 발생하다.
            자동 호출되는 함수.
            (procedual, func과 다르게 직접 호출이아닌 자동 호출이 된다)
            매개변수가 없음, select, insert, update 모두사용가능
            
            before, after
            원하는 지점에 Qurey문이 추가되고 그 후에 할지 그 전에 할지 정하는것.
            
            회원한명 추가 
            INSERT INTO EMPLOYYES
            같은사람 있는지 확인 -> 촉발 함수 제작 후 확인(b/f 선택)
            
                    * before, after
                                  OLD         NEW
                    INSERT                      O
                    DELETE      O
                    UPDATE     O             O

 

 

 

SET SERVEROUTPUT ON;

 

 

 

BEFORE 

 

예) 부서명 재설정

CREATE TRIGGER trigger_test
    BEFORE                              --아랫문장이 업되이트 되기 전에 실행
        UPDATE ON departments         
        FOR EACH ROW
BEGIN 
    dbms_output.put_line('변경 전 컬럼의 값 : ' || :OLD.department_name);      --OLD
    dbms_output.put_line('변경 후 컬럼의 값 : ' || :NEW.department_name);      --NEW

END;
/

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

 

 

 

 

 

--row를 추가하는데 항상 평균 급여를 확인

CREATE TRIGGER avg_trigger
    BEFORE
        INSERT OR UPDATE ON employees       --insert나 update 쿼리문이 사용되면 자동호출됨
        FOR EACH ROW
DECLARE
    avg_sal NUMBER;
BEGIN
    SELECT ROUND(AVG(salary),3) INTO avg_sal        -- INTO로 내보냄
    FROM employees;
    
    dbms_output.put_line('급여 평균' || avg_sal);
END;
/

--실행하면 급여평균이 자동으로 출력 

INSERT INTO employees(employee_id, last_name, hire_date, department_id, job_id, salary, email)
VALUES (500, 'Tiger', SYSDATE, 60, 'IT_PROG', 10000, 'Tiger@naver.com');


INSERT INTO employees(employee_id, last_name, hire_date, department_id, job_id, salary, email)
VALUES (501, 'Tiger', SYSDATE, 60, 'IT_PROG', 10000, 'Tiger1@naver.com');

 

 

 

 

--수정되지 않도록 설정(보호)

CREATE TRIGGER emp_triger
    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, '이 번호는 수정할 수 없습니다');
        END IF;
    END IF;
END;
/

 

UPDATE employees
SET salary = 2500
WHERE employee_id = 100;

 

 

 

-- 입력불가 되도록 

CREATE TRIGGER nodata_trigger
    AFTER INSERT
    ON employees
BEGIN
    RAISE_APPLICATION_ERROR(-20000, '데이터의 입력을 시도하였습니다');
END;
/

 

INSERT INTO employees(employee_id, last_name, hire_date, department_id, job_id, salary, email)
VALUES(600, 'Tiger', SYSDATE, 60, 'IT_PROG', 10000, 'Tiger600@naver.com');

 

 

 

삭제 필

DROP TRIGGER trigger_test;
DROP TRIGGER avg_triger;
DROP TRIGGER emp_triger;
DROP TRIGGER nodata_trigger;