15일차 # 10-13: Conditional Predicates

  - if, while 과 같은 조건식이 사용되는 곳에 사용

  * INSERTING

  * UPDATING, UPDATING('컬럼명')

  * DELETING

CREATE OR REPLACE TRIGGER secure_emp 

  BEFORE INSERT OR UPDATE OR DELETE ON employees 

BEGIN

    IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR

       (TO_CHAR(SYSDATE,'HH24') BETWEEN '08' AND '18') THEN

      IF DELETING THEN 

          RAISE_APPLICATION_ERROR(-20502,'You may delete from EMPLOYEES table '||

                                  'only during business hours.');

      ELSIF INSERTING THEN

          RAISE_APPLICATION_ERROR(-20500,'You may insert into EMPLOYEES table '|| 

                                  'only during business hours.');

      ELSIF UPDATING('SALARY') THEN

          RAISE_APPLICATION_ERROR(-20503, 'You may '||

                                  'update SALARY only during business hours.');

      ELSE 

          RAISE_APPLICATION_ERROR(-20504,'You may'||

                                  ' update EMPLOYEES table only during'||

                                  ' normal hours.');

      END IF;

    END IF;

END;

/

insert into employees (employee_id, last_name, email, hire_date, job_id)

values (3333, 'Lee', 'lee@...', sysdate-1, 'AD_ASST');

update employees

set salary = 0

where employee_id = 1111;

update employees

set last_name = 'Jang'

where employee_id = 1111;