Database_90_Trigger_트리거_Timing_BEFORE, AFTER, INSTEAD OF, EVENT, 수식자_OLD, NEW, ALTER 트리

-- Trigger_트리거 
    -- 데이터베이스에 저장되고 지정된 이벤트에 대한 응답으로 실행되는 PL/SQL 블록입니다.
    -- 오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행합니다.
    -- 테이블, 뷰, 스키마, 데이터베이스에 정의될 수 있습니다.
    -- 유형
        -- 데이터베이스에서 발생할 때마다 실행되는 트리거를 작성할 수 있습니다.
        -- DML 문_DELETE, INSERT, UPDATE
        -- DDL 문_CREATE, ALTER, DROP
        -- SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN
    -- 구현 시나리오
        -- 보안
        -- 감사_Audit
        -- 데이터 무결성
        -- 참조 무결성
        -- 테이블 복제
        -- 파생된 데이터 자동 계산
        -- 이벤트 로깅
    -- 가능한 유형
        -- INSERT
        -- UPDATE [OF column]
        -- DELETE
        -- 트리거 본문은 수행될 작업을 결정하며 PL/SQL 블록 또는 프로시저에 대한 CALL 입니다.
    -- 생성
        /*
        CREATE [OR REPLACE] TRIGGER trigger_name
            timing ?- when to fire the trigger
            event1 [OR event2 OR event3]
            ON object_name
            [REFERENCING OLD AS old | NEW AS new]
            FOR EACH ROW ?- default is statement level trigger
            WHEN (condition)
            [ DECLARE ] 
        BEGIN
            ... trigger_body ?- executable statements
            [EXCEPTION . . .]
        END [trigger_name];
        */
        
        -- TIMING
            /*
            timing = BEFORE | AFTER | INSTEAD OF
            */
            -- 트리거의 작업이 트리거 문 이전 또는 이후에 실행되도록 할 것인지에 대한 여부를 결정합니다.
            -- BREFORE
                -- 테이블에서 DML 이벤트를 트리거하기 전에 트리거 본문을 실행합니다.
            -- AFTER
                -- 테이블에서 DML 이벤트를 트리거한 후에 트리거 본문을 실행합니다.
            -- INSTEAD OF
                -- 트리거 문 대신 트리거 본문을 실행합니다.
                -- 다른 방법으로는 수정이 불가능한 뷰에서 사용합니다.
        -- EVENT
            /*
            event = INSERT | DELETE | UPDATE | UPDATE OF column_list
            */
    -- 트리거 유형
        -- 문장 레벨 트리거
            -- 트리거를 생성할 때의 기본값입니다.
            -- 트리거 이벤트에 대해 한 번 실행됩니다.
            -- 트리거 이벤트의 영향을 받는 행이 전혀 없더라도 한 번 실행됩니다.
        -- 행 레벨 트리거
            -- 트리거를 생성할 때 FOR EACH ROW 절을 사용합니다.
            -- 트리거 이벤트의 영향을 받는 각 행에 대해 한 번 실행됩니다.
            -- 트리거 이벤트의 영향을 받는 행이 없을 경우 실행되지 않습니다.
      
--  특정 업무 시간(월요일-금요일)에만 EMPLOYEES 테이블에 삽입할 수 있는 트리거를 작성합니다.
CREATE OR REPLACE TRIGGER secure_emp
    BEFORE INSERT ON employees 
BEGIN
    IF (TO_CHAR(SYSDATE,'DY') IN ('토＇,＇일')) OR (TO_CHAR(SYSDATE,'HH24:MI') 
        NOT BETWEEN '08:00' AND '18:00') THEN
            RAISE_APPLICATION_ERROR(-20500, 'You may insert'
            ||' into EMPLOYEES table only during ' 
            ||' normal business hours.');
    END IF;
END;
/ 
    /*
    Trigger SECURE_EMP이(가) 컴파일되었습니다.
    */
    
INSERT INTO employees (employee_id, last_name, first_name, email, 
hire_date, job_id, salary, department_id)
VALUES (667, 'Smith', 'Rob', '667', SYSDATE,'IT_PROG', 4500, 60);
    /*
      1 행 이(가) 삽입되었습니다.
    */
        
-- 조건부 술어 사용
CREATE OR REPLACE TRIGGER secure_emp BEFORE
    INSERT OR UPDATE OR DELETE ON employees 
BEGIN
    IF (TO_CHAR(SYSDATE,'DY') IN ('토','일')) OR (TO_CHAR(SYSDATE,'HH24') 
        NOT BETWEEN '08' AND '18') THEN
        IF DELETING THEN RAISE_APPLICATION_ERROR(-20502,'You may delete from 
                                    EMPLOYEES table'||
                                    ' only during normal business hours.');
        ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(-20500,'You may insert into EMPLOYEES table'|| 
                                    ' only during normal business hours.');
        ELSIF UPDATING ('SALARY') THEN
                            RAISE_APPLICATION_ERROR(-20503, 'You may'||
                            ' update SALARY only normal during business hours.');
        ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||
                ' update EMPLOYEES table only during'||' normal business hours.');
        END IF;
    END IF;
END;
    /*
    Trigger SECURE_EMP이(가) 컴파일되었습니다.
    */

-- 지정 날짜에 데이터를 삽입합니다.
INSERT INTO employees (employee_id, last_name, first_name, email, 
hire_date, job_id, salary, department_id)
VALUES (767, 'Smith', 'Rob', '767', SYSDATE,'IT_PROG', 4500, 60);
    /*
        <오류>-----------------------------------------------------------------------------
        오류 보고 -
        ORA-20500: You may insert into EMPLOYEES table only during normal business hours.
        ORA-06512: at "HR.SECURE_EMP", line 7
        ORA-04088: error during execution of trigger 'HR.SECURE_EMP'
        ------------------------------------------------------------------------------------
    */
       
--  특정 조건 위반일 경우 BEFORE 행(row) 트리거를 작성하여 트리거 작업을 방지할 수 있습니다.      
CREATE OR REPLACE TRIGGER restrict_salary
    BEFORE INSERT OR UPDATE OF salary ON employees
    FOR EACH ROW
BEGIN
    IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))
        AND :NEW.salary > 15000 THEN
        RAISE_APPLICATION_ERROR(-20202,
            'Employee cannot earn more than $15,000.');
    END IF;
END;
    /*
    Trigger RESTRICT_SALARY이(가) 컴파일되었습니다.
    */

    -- 오류 발생?
UPDATE employees
SET salary = 15500
WHERE last_name = 'Russell';
    /*
        <오류>-----------------------------------------------------------------------------
        오류 보고 -
        ORA-20503: You may update SALARY only normal during business hours.
        ORA-06512: at "HR.SECURE_EMP", line 10
        ORA-04088: error during execution of trigger 'HR.SECURE_EMP'
        ------------------------------------------------------------------------------------
    */



    -- 수식자
        -- 행 레벨 트리거가 실행될 때 PL/SQL 런타임 엔진이 생성하는 데이터 구조
            -- OLD
                -- 트리거가 처리한 레코드의 원래 값을 저장합니다.
                -- INSERT= NULL
            -- NEW
                -- 새 값을 포함합니다.
                -- DELETE= NULL
        -- NEW, OLD는 트리거가 연결된 테이블에서 %ROWTYPE을 사용하여 선언한 레코드와 구조가 동일합니다.
        -- 행 크리거에서만 사용할 수 있습니다.
        
    -- 수식자 사용
-- AUDIT_EMP 테이블 생성
CREATE TABLE audit_emp (
    user_name VARCHAR2(30),
    time_stamp date,
    id NUMBER(6),
    old_last_name VARCHAR2(25),
    new_last_name VARCHAR2(25),
    old_title VARCHAR2(10),
    new_title VARCHAR2(10),
    old_salary NUMBER(8,2),
    new_salary NUMBER(8,2) 
    )
/
    /*
    Table AUDIT_EMP이(가) 생성되었습니다.
    */

-- AUDIT_EMP_VALUES 트리거 생성
CREATE OR REPLACE TRIGGER audit_emp_values
    AFTER DELETE OR INSERT OR UPDATE ON employees
    FOR EACH ROW
BEGIN
    INSERT INTO audit_emp(user_name, time_stamp, id,
    old_last_name, new_last_name, old_title,
    new_title, old_salary, new_salary)
    VALUES (USER, SYSDATE, :OLD.employee_id,
    :OLD.last_name, :NEW.last_name, :OLD.job_id,
    :NEW.job_id, :OLD.salary, :NEW.salary);
END;
    /*
    Trigger AUDIT_EMP_VALUES이(가) 컴파일되었습니다.
    */

-- EMPLOYEES 테이블에 대한 DML 수행 후 AUDIT_EMP 테이블에서 확인합니다.
INSERT INTO employees (employee_id, last_name, job_id, salary, email, hire_date)
VALUES (999, 'Temp emp', 'SA_REP', 6000, 'TEMPEMP', TRUNC(SYSDATE))
/
UPDATE employees
SET salary = 7000, last_name = 'Smith'
WHERE employee_id = 999
/
SELECT *
FROM audit_emp;
    /*
    USER_NAME   TIME_STA    ID     OLD_LAST_NAME  NEW_LAST_NAME   OLD_TITLE  NEW_TITLE  OLD_SALARY NEW_SALARY
    -----------------------------------------------------------------------------------------------------------
    HR          23/07/21  ( null )  ( null )      Temp emp        SA_REP     ( null )      6000     ( null )
    HR          23/07/21    999     Temp emp      Smith           SA_REP     SA_REP        6000      7000    
    */


    -- 행 트리거 제한
-- WHEN 절을 사용하여 트리거 작업을 특정 조건에 맞는 행(row)으로 제한합니다.
CREATE OR REPLACE TRIGGER derive_commission_pct
    BEFORE INSERT OR UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (NEW.job_id = 'SA_REP')
BEGIN
    IF INSERTING THEN
        :NEW.commission_pct := 0;
    ELSIF :OLD.commission_pct IS NULL THEN
        :NEW.commission_pct := 0;
    ELSE
        :NEW.commission_pct := :OLD.commission_pct+0.05;
    END IF;
END;
/
    /*
    Trigger DERIVE_COMMISSION_PCT이(가) 컴파일되었습니다.
    */


    -- AFTER 트리거
        -- 무결성 제약 조건을 구현합니다.
        
-- Integrity constraint violation error ?2991 raised.
UPDATE employees SET department_id = 999 
WHERE employee_id = 178;
    /*
    <오류>-----------------------------------------------------------------------------
    오류 보고 -
    ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
    ------------------------------------------------------------------------------------
    */
    
-- AFTER 트리거 생성
CREATE OR REPLACE TRIGGER employee_dept_fk_trg
    AFTER UPDATE OF department_id ON employees 
    FOR EACH ROW
BEGIN
    INSERT INTO departments VALUES(:new.department_id,
                        'Dept '||:new.department_id, NULL, NULL);
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    NULL; -- mask exception if department exists
END;
/
    /*
    Trigger EMPLOYEE_DEPT_FK_TRG이(가) 컴파일되었습니다.
    */

UPDATE employees SET department_id = 999 
WHERE employee_id = 178;
    /*
    1 행 이(가) 업데이트되었습니다.
        -- 오류가 발생하지 않습니다.
    */

-- 부서테이블을 조회합니다.
SELECT DISTINCT department_id 
FROM employees;

-- 사원테이블을 조회합니다.
SELECT department_id, department_name 
FROM departments
ORDER BY department_id;

    -- INSTEAD OF 트리거
        -- 트리거의 대상이 뷰입니다.
        -- 뷰에 대해 동작하지 않는 경우 발생합니다.
        
    -- 트리거 관리
        -- 트리거 활성화/비활성화
            /*
            ALTER TRIGGER trigger_name DISABLE | ENABLE;
            */
        -- 트리거 재컴파일
            /*
            ALTER TRIGGER trigger_name COMPILE;
            */
        -- 트리거 삭제
            /*
            DROP TRIGGER trigger_name;
            */
    -- 트리거 정보 보기
        /*
        SELECT trigger_type, trigger_body
        FROM user_triggers
        WHERE trigger_name = 'AUDIT_EMP_VALUES';
        */



---<실습>--------------------------------------------------------
CREATE TABLE audit_employees(
     os_user VARCHAR2(10),
     dml_date TIMESTAMP(0),
     addr VARCHAR2(15),
     description VARCHAR2(1000) ) 
/
    /*
    Table AUDIT_EMPLOYEES이(가) 생성되었습니다.
    */
    
CREATE OR REPLACE TRIGGER hrsalary_audit
    AFTER UPDATE OF salary
    ON hr.employees 
    REFERENCING NEW AS NEW OLD AS OLD 
    FOR EACH ROW 
BEGIN
    IF :old.salary != :new.salary THEN
        INSERT INTO audit_employees 
        VALUES (sys_context('userenv','os_user'), sysdate, sys_context('userenv','ip_address'),
        :new.employee_id || ' salary changed from '||:old.salary||' to '||:new.salary);
    END IF; 
END;
/
    /*
    Trigger HRSALARY_AUDIT이(가) 컴파일되었습니다.
    */   

SELECT * FROM audit_employees;
    /*
    -- 데이터가 없습니다.
    */


--DML 실행
update employees 
set salary = salary*1.1
where employee_id = 178
/
rollback
/
select employee_id, salary
from employees
where department_id = 50
/
update employees
set salary = salary*1.1
where department_id = 50
/
commit
/
    /*
    1 행 이(가) 업데이트되었습니다.
    
    롤백 완료.
    
    EMPLOYEE_ID     SALARY
    ----------- ----------
        107       4620
        124       6380
        141       3850
        142       3410
        143       2860
    
    
    5개 행 이(가) 업데이트되었습니다.
    
    커밋 완료.
    */

-- 감사 데이터를 조회합니다.
SELECT * FROM audit_employees;
        -- 기록이 남습니다.

--실습정리
drop table audit_employees purge;
    /*
    Table AUDIT_EMPLOYEES이(가) 삭제되었습니다.
    */

drop trigger hrsalary_audit;
    /*
    Trigger HRSALARY_AUDIT이(가) 삭제되었습니다.
    */