13. 오라클 - TRIGGER

. 특정 Event 가 발생 할때 자동으로 임의의 처리를 하고자 할때 사용한다.
. Trigger를 감사에 이용할 수 있다. => 디버그나 log로써 사용할 수 있다.
. Application 개발 시 관련 테이블의 Transaction 처리를 간소화 할수 있다.
. 데이터 무결성을 유지 하기 위해 사용 될 수 있다.
. 분산처리 옵션이 없더라도 원격 데이터의 로컬 사본을 만들 수 있다.


사용 예)
.어떤 자료가 ROLLBACK 될 때 어디의 자료로 ROLLBACK 해야 된다는 TRIGGER 작성가능
.인사 정보가 사라지면 인사과로 삭제되어야 한다.
.테이블을 원격지에 똑같이 만들때


TRIGGER 종류
. DML TRIGGER : DML(INSERT, UPDATE , DELETE) Event 가 발생 할때
. DDL TRIGGER : DDL(CREATE, ALTER, DROP) Event 가 발생 할때 작동
. DATABASE EVENT TRIGGER : DATABASE 를 STARTUP, SHUTDOWN 할때, DATABASE에 LOGON, LOGOFF 할 때 , DATABASE에서 오류가 발생 했을 때 작동
. BEFORE TRIGGER : Event 가 발생한 직전에 실행
. AFTER TRIGGER : Event 가 발생한 직후에 실행
. INSTEAD OF TRIGGER : DML 문장에 의해 직접 변경할 수 없는 VIEW를 변경하기 위해 사용 (매우 유용하면서 중요하다.)


실습을 위한 준비
. 실습개요
 - DML TRIGGER를 사용하여 신규 사원추가에 따라 성적테이블 초기화
 - DML TRIGGER를 사용하여 성적변화에 따른 급여의 가감을 처리
 - DDL TRIGGER를 사용하여 Object에 대한 감사 기능을 수행
 - DATABASE EVENT TRIGGER 를 사용하여 사용자 LOGON/LOGOGG에 대한 감사 기능 수행

예1)
CREATE OR REPLACE TRIGGER emp AFTER INSERT ON emp FOR EACH ROW
BEGIN
         INSERT INTO emp_rating(empno) VALUES(:new.empno);
END;
 - > :new.empno : 트리거에서 제공하는 것 바꾼것의 새로운 것
 - > FOR EACH ROW : 여러 ROW가 영향을 받았을 경우 각각을 실행한다는 의미


예2)
CREATE OR REPLACE TRIGGER emp
AFTER INSERT ON emp FOR EACH ROW
BEGIN
        IF :new.job <> 'PRESIDENT' THEN
            INSERT INTO emp_rating(empno) VALUES(:new.empno)
END;
 - >  IF :new.job <> 'PRESIDENT' THEN  - 새로운 job 이 PRESIDENT 가 아니면 이라는 의미


예3)
CREATE OR REPLACE TRIGGER emp_rating.aft
AFTER UPDATE OF computing ON emp_rating FOR EACH ROW
BEGIN
         IF :old.computing < :new.computing THEN
             UPDATE emp SET sal=sal+sal*0.01 WHERE empno = :new.empno
END;
 - >  AFTER UPDATE OF computing ON emp_rating FOR EACH ROW  : 각각의 emp_rating 테이블의 computing 컬럼이 바뀌었을때  란 뜻이다.


삭제 예)
DROP TRIGGER emp_aft_ins_row ;


TRIGGER - INSTEAD OF TRIGGER
예) UNION 혹은 함수를 사용한 VIEW에서는 INSERT가 불가능하다. 이를 가능하게 하는 TRIGGER를 작성한다.
CREATE OR REPLACE VIEW animal(animal_kind,animal_name,animal_weight) AS 
SELECT 'BIRD', bird_name, weight FROM bird UNION ALL
SELECT 'FISH', fish_name, weight FROM fish UNION ALL
SELECT 'MAMAL',mamal_name,weight FROM mammal ;
- > UNION 은 합집합을 만들어 주는 명령어로 컬럼만 일치하면 합집합을 만들어 준다. 이 경우 VIEW 자체에 INSERT가 불가능하다

위 VIEW 에 INSERT 가 가능케 하려면 INSTEAD OF TRIGGER 가 필요
CREATE OR REPLACE TRIGGER animal_ifno_insert
INSTEAD OF INSERT ON animal
REFERENCING NEW AS n FOR EACH ROW
BEGIN
          IF :n.animal_name IS NOT NULL THEN
             IF :n.animal_kind = 'FISH' THEN
                  INSERT INTO FISH(fish_name,weight) VALUES(:n.animal_name, :n.animal_weight);
            ELSIF :n.animal_kind = 'BIRD' THEN
                  INSERT INTO bird(brid_name, weight) VALUES(:n.animal_name, :n.animal_weight);
           ELSIF  :n.animal_kind = 'MAMMAL' THEN
                      INSERT INTO mammal(mammal_name, weight) VALUES(:n.animal_name, :n.animal_weight);
           END IF;
        END IF;
END;



TRIGGER - DDL TRIGGER (LOG 라고 생각해라)
CREATE OP REPLACE TRIGGER andit_object AFTER CREATE ON SCHEMA[DATABASE]  
BEGIN
        INSERT INTO trigger_log VALUES('CREATE' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || 'by' || sys.login_user, sysdate);
END;
 - > SCHEMA 는 현재 사용자 영역이라고 생각해라. 데이터베이스 전체에 활용하고 싶다면 DATABASE 라고 적으면 된다.
 - > 문자열과 값을 연결할때는 || 을 사용한다.

 

< - 사용자가 CREATE 를 실행시키면 위 andit_object 트리거가 실행되어 trigger_log 에 값을 저장하게 된다.
CREATE 뿐만 아니라 ALTER. DROP등 DDL 에 대해 동일하게 트리거가 작동되며 즉 로그처럼 트리거를 사용할수 있다.











TRIGGER - BATABASE EVENT TRIGGER
CREATE OR REPLACE TRIGGER log_logon AFTER LOGON ON DATABASE
WHEN ( USER = 'scott' OR USER LIKE 'system%' )
BEGIN
        INSERT INTO trigger_log VALUES('LOGON' || USER, sysdate);
        COMMIT;
END;

CREATE OR REPLACE TRIGGER log_logoff BEFORE LOGOFF ON DATABASE
WHEN (USER = 'SCOTT' OR USER LIKE 'SYSTEM%')
BEGIN
        INSERT INTO trigger_log VALUES('LOGON' || USER , sysdate);
        COMMIT;
END;