Bohemian life

oj_plsql.txt

# 교재 정리

- 테이블이 삭제될 때 테이블의 모든 트리거도 함께 제거됩니다.

  * Defined :

  * Data Dictionary :

  * Invoked : Implicitly vs. Explicitly

  * TCLs allowed? :

   cf.) 트리거 컴파일 도중 에러 발생해도 트리거는 생성됨

  - 업데이트할 수 없는 뷰에 대해 데이터 수정이 가능

  - 뷰에 대해서만 INSTEAD OF 트리거 가능

  * WHEN (condition) 절

    - new, old 앞에 콜론(:)을 사용하지 않습니다. <== 트리거 블록 바깥이므로

  - Row Trigger 에서만 가능

  - 트리거 블록 내에서 사용하며 사용시 콜론(:)을 앞에 붙여야 함

    * INSERT: OLD -> NULL, NEW -> inserted value

    * UPDATE: OLD -> value before update, NEW -> value after update

    * DELETE: OLD -> value before delete, NEW -> NULL

  - REFERENCING

create or replace trigger emp_trg_2

before insert or update of sal on t_emp

referencing new as n_val

--    old as o_val

for each row

-- referencing new as n_val

begin

 if not (:n_val.job in ('PRESIDENT', 'ANALYST'))

 and :n_val.sal >= 3000 then

   raise_application_error(-20002, '급여를 3000 달러 이상 받을 수 없는 직군입니다.');

          end if;

end;

/

DML Row Trigger <-- FOR EACH ROW가 있음

create or replace trigger emp_trg_2

before insert or update of sal on t_emp

for each row

begin

 if not (:new.job in ('PRESIDENT', 'ANALYST'))

 and :new.sal >= 3000 then

   raise_application_error(-20002, '급여를 3000 달러 이상 받을 수 없는 직군입니다.');

          end if;

end;

/

insert into t_emp (empno, ename, job, sal)

 values (1112, 'THOMPSON', 'ANALYST', 3500);

insert into t_emp (empno, ename, job, sal)

 values (1113, 'GIBSON', 'SALESMAN', 2200);

insert into t_emp (empno, ename, job, sal)

 values (1114, 'KLEIN', 'CLERK', 3000);

CREATE OR REPLACE TRIGGER restrict_salary

 BEFORE INSERT OR UPDATE OF salary ON employees

 FOR EACH ROW --> 행 트리거입니다

BEGIN

          dbms_output.put_line('retrict salary trigger is performed!');

 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;

/

drop trigger secure_emp;

set serveroutput on

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

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

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

values (4444, 'JANG', 'jang@...', sysdate-1, 'AD_ASST');

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

values (5555, 'JUNG', 'jung@...', sysdate-1, 'AD_ASST', 16000);

update employees

set last_name = 'CHO'

where employee_id = 4444;

update employees

set salary = 10000

where employee_id = 4444;

update employees

set salary = 16000

where employee_id = 4444; 에러: --> ORA-20202: Employee cannot earn more than $15,000.

  - 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;

DML Statement Trigger <-- FOR EACH ROW가 없음

set severoutput on

drop table t_emp purge;

create table t_emp

as select * from emp where 1=2;

create or replace trigger emp_trg_1

before insert on t_emp

begin

 p('데이터 입력 직전입니다.');

--  p(:new.ename); --> 오류: ORA-04082: 테이블 레벨 트리거에서 NEW 혹은 OLD 참조는 허용되지 않습니

                                                      다 --> 행 트리거가 아니므로

  end;

/

insert into t_emp (empno, ename, sal) values (1111, 'POTTER', 1100);

col name format a25

col text format a80

select name, type, text

from user_source

where type = 'TRIGGER';

  - INSERT

  - UPDATE [OF column, ...]

  - DELETE

ex) INSERT OR UPDATE OF job, sal OR DELETE