#14_140702_PL_SQL_TRIGGER

# TRIGGER

l trigger(반응이나 사건을 유발한 계기) : 오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행

l 트리거의 종류

1.     DML 트리거(delete, insert, update)

2.     DDL 트리거(create, alter, drop)

3.     DB 작업에 대한 트리거(log on, log off, start up. shutdown)
l 트리거 확인 : SELECT * FROM user_triggers;

사원170. 사원 테이블에 토요일과 일요일에 데이터 입력작업을 못하게 하도록 트리거를 생성하시오

create or replace  trigger  secure_emp

before  insert  on  emp

begin

 if  (to_char(sysdate,'DY')  in ('토','일')) then 

 raise_application_error(-20500,'토요일 일요일에는 일하지 마세요 ~ ');

 end  if;

end;

/

l day – 툐오일, 일요일, dy -, 토 일

문지171. 위의 트리거를 수정해서 아침 9시 ~ 저녘 6시 외시간에는 emp 테이블에 update 못하게 트리거를 생성하시오

create or replace trigger time_emp

 before update on emp

 begin

 if (to_char(sysdate,'HH24:MI') not between '09:00' and '18:00') then

 raise_application_error(-20003,'근무시간이 아닙니다. ');

 end if;

 end;

/

문제172. 사원 테이블의 월급을 9000 이상으로 갱신하지 못하도록 하는 트리거를 생성하시오

CREATE OR REPLACE TRIGGER restrict_sal

BEFORE INSERT OR UPDATE OF sal ON EMP

FOR EACH ROW

BEGIN

 IF :new.sal >= 9000 THEN

  raise_application_error(-20004,'월급을 9000 이상으로는 갱신못합니다');

 END IF;

END;

/

l 데이터 무결성 트리거

무제173. 사원테이블의 부서번호를 10,20,30 외에는 수정못하게 하도록 트리거를 생성하시오

CREATE OR REPLACE TRIGGER restrict_deptno

BEFORE INSERT OR UPDATE OF deptno ON EMP

FOR EACH ROW

 BEGIN

  IF :new.deptno NOT IN (10,20,30) THEN

   raise_application_error(-20005,'부서번호 10,20,30번 외에는 갱신못합니다');

  END IF;

 END;

 /

l for each row : deptno 같은 컬럼명이 쓰여질 때 추가

문제174. 사원 테이블의 월급을 갱신하면 이름, 갱신되기전 월급과 갱신된 후의 월급 그리고insert 날짜를 아래의 테이블에 입력되게 하시오

CREATE TABLE audit_emp(

 ename VARCHAR2(10),

 old_sal NUMBER(10),

 new_sal number(10),

 happen date);

 CREATE OR REPLACE TRIGGER emp_update

 AFTER UPDATE OF sal on EMP

 FOR EACH ROW

 BEGIN

  INSERT INTO audit_emp

   VALUES(:old.ename, :old.sal, :new.sal, sysdate);

 END;

 /

 UPDATE EMP

 SET sal = 5700

 WHERE ename='KING';

문제175. 아래의 테이블을 생성하고 emp 테이블에 삭제작업을 하면 삭제된 data 중에empno, ename, sal 가 아래의 테이블에 입력되게 하시오

CREATE TABLE emp_delete_audit(

 empno NUMBER(10),

 ename VARCHAR2(10),

 sal NUMBER(10));

CREATE OR REPLACE TRIGGER emp_delete

 AFTER DELETE ON EMP

 FOR EACH ROW

 BEGIN

  INSERT INTO emp_delete_audit

   VALUES(:old.empno, :old.ename, :old.sal);

END;

/

문제176. 사원번호, 이름, 월급, 부서번호와 부서위치를 출력하는 뷰를 생성하시오(view 이름: emp_dept)

CREATE VIEW emp_dept

AS

SELECT e.empno, e.ename, e.sal, e.deptno, d.loc

FROM EMP e, DEPT d

WHERE e.deptno = d.deptno;

l 복합뷰는 DML 여부가 가능한가? – 불가능하다

l 위의 insert 문장을 가능하게 하려면? – instead of 트리거를 사용

문제177. 아래의 데이터를 emp_dept 뷰에 입력할 수 있도록 trigger를 생성하고 수행하시오

사원번호 : 9321

사원이름 : JANE

월급 : 3000

부서번호 : 50

부서위치 : SEOUL

create or replace trigger emp_dept_insert

instead of insert on emp_dept

referencing new as n

for each ROW

declare

    dept_cnt number;

    

begin

 if :n.empno is not null then

  insert into emp(empno, ename, sal, deptno)

   values (:n.empno, :n.ename, :n.sal, :n.deptno);

 end if;

 if :n.deptno is not null then

  select count(*) into dept_cnt

  from dept where deptno = :n.deptno;

 

  if dept_cnt > 0 and (:n.loc is not null) then

   update dept set loc = :n.loc where deptno = :n.deptno;

  else insert into dept(deptno, loc) values(:n.deptno, :n.loc);

  end if;

  

end if;

end;

/

INSERT INTO EMP_DEPT

VALUES(9321,'JANE',3000,50,'SEOUL');

l 복합뷰에 insert는 본래 안되는데 위의 instead of 트리거를 이용해서 가능하게 할 수 있다

l referencing new as n : new를 n으로 정의한다

문제178. emp 테이블을 아예 drop 하지 못하도록 트리거를 생성하시오(emp 테이블 뿐만 아니라 모든 테이블에 대해서)

CREATE OR REPLACE TRIGGER no_ddl

BEFORE DROP ON SCOTT.SCHEMA

BEGIN

 raise_application_error(-20001,'테이블을 삭제하지마세오');

END;

/

문제179. 아래의 작업이 수행안되도록 트리거를 생성하시오

alter table emp

add email varchar2(20);

CREATE OR REPLACE TRIGGER trig_alter

BEFORE ALTER ON SCOTT.SCHEMA

BEGIN

 raise_application_error(-20002,'컬럼을 추가하지마시오');

END;

/

문제180. 오라클에 접속할 떄마다 아래의 테이블의 접속정보가 남겨지게 하시오

CREATE TABLE login_info(

user_id VARCHAR2(10),

login_date DATE,

action VARCHAR2(50));

CREATE OR REPLACE TRIGGER login_info

AFTER logon ON DATABASE

BEGIN

 INSERT INTO login_info

  VALUES(USER,SYSDATE, '로그인 작업을 시도함');

END;

/

문제181. DB를 shutdown 하기전에 login_info 테이블에 아래의 데이터가 입력되게 하시오

CREATE OR REPLACE TRIGGER login_info

BEFORE SHUTDOWN ON DATABASE

BEGIN

 INSERT INTO login_info

  VALUES(USER,SYSDATE, 'DB 내리는 작업시도');

END;

/

l 데이터베이스 셧다운

connect sys/oracle as sysdba

shutdown immediate

l 복구 : startup