오라클 트리거(TRIGGER)

//*****************************************
// 트리거(TRIGGER)
   TRIGGER란 DML 작업 즉, INSERT, DELETE, UPDATE 작업이 일어날 때 자동으로 실행되는 객체로 특히 이런 TRIGGER를 DML TRIGGER라 한다. TRIGGER는 데이터의 무결성 뿐만 아니라 다음과 같은 작업에도 사용된다.
   트리거 내에서는 COMMIT, ROLLBACK 문을 사용할 수 없다.

 -- 문장 트리거(문장당 한번만 실행)
    트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법

 -- 행 트리거
    조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건]절 정의된다.

 -- 형식
CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER]
  이벤트-1 [OR 이벤트-2 OR 이벤트-3] ON 테이블명
  [FOR EACH ROW [WHEN TRIGGER 조건]]
  DECLARE
    선언문
  BEGIN
    PL/SQL 코드
  END;

 -- 이벤트
     INSERT, UPDATE, DELETE

    -- BEFORE : 구문을 실행하기 전에 트리거를 시작
    -- AFTER : 구문을 실행한 후에 트리거를 시작
    -- FOR EACH ROW : 행 트리거임을 알림
    -- WHEN 조건 : 사용자의 트리거 이벤트 중에 조건에 만족하는 데이터만 트리거 한다.
    -- REFERENCING : 영향 받는 행의 값을 참조
    -- :OLD : 참조 전 열의 값(INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)
    -- :NEW : 참조 후 열의 값(INSERT : 입력할 자료, UPDATE : 수정할 자료)

      ROW 트리거에서 컬럼의 실제 데이터 값을 제어하는데 사용하는 연산자는 :OLD와 :NEW 의사 레코드이다. 이 연산자와 함께 컬럼 명을 함께 기술한다. 예를 들어, 컬럼명이 sal이라고 하면, 변경전의 값은 :OLD.sal이고 변경 후의 값은 :NEW.sal 처럼 표기한다.

      문장 트리거에서는 :NEW, :OLD 를 참조 할 수 없다.

 -- 트리거 상태 확인
SQL>SELECT trigger_name, trigger_type, table_name FROM user_triggers;

 -- 트리거 삭제
DROP TRIGGER 트리거명;

//*****************************************
-- SYS 계정
-- 사용자에게 트리거를 만들 수 있는 권한 부여
GRANT CREATE TRIGGER TO 사용자명;

-- 사용자 계정
-- 부여된 권한 확인
SELECT * FROM USER_SYS_PRIVS;

//*****************************************
-- 문장 트리거
  트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법

SELECT * FROM tab;

-- EXAM1 테이블이 존재하면 삭제
DROP TABLE EXAM1 PURGE;

-- 문장 트리거
CREATE TABLE EXAM1 (
    id NUMBER PRIMARY KEY
    ,name VARCHAR2(20)
);

CREATE TABLE EXAM2 (
    memo VARCHAR2(100)
    ,ilja    DATE DEFAULT SYSDATE
);

----------------------------------------------------
-- exam1 테이블에 추가, 수정, 삭제가되면 시간과 상태 저장 트리거
  -- 문장, AFTER 트리거
CREATE OR REPLACE TRIGGER trgExam
   AFTER DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
    IF INSERTING THEN
        INSERT INTO EXAM2(memo) VALUES ('추가');
    ELSIF UPDATING THEN
        INSERT INTO EXAM2(memo) VALUES ('수정');
    ELSIF DELETING THEN
        INSERT INTO EXAM2(memo) VALUES ('삭제');
    END IF;
END;

----------------------------------------------------
-- 트리거 확인
SELECT trigger_name, trigger_type, table_name
            FROM user_triggers;
SELECT TEXT FROM user_source;

INSERT INTO EXAM1(id, name) VALUES(1, 'aaa');
COMMIT;
SELECT * FROM EXAM1;
SELECT * FROM exam2;

-- 테이블의 프로시저, 함수, 패키지 등 상호 참조되는 관계 확인
 SELECT * FROM user_dependencies;

----------------------------------------------------
-- 날자 표현 형식 변경
ALTER SESSION SET NLS_DATE_LANGUAGE='KOREAN';

-- BEFORE 트리거(AFTER 트리거로 작성해도 동일한 결과 출력)
-- 일반적으로 BEFORE는 데이터를 검증할 때 사용됨)
CREATE OR REPLACE TRIGGER trgExam1
        BEFORE DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
    IF TO_CHAR(SYSDATE, 'DAY') IN ('토요일', '일요일')
         OR (TO_CHAR(SYSDATE, 'hh24') >= 12 AND
                TO_CHAR(SYSDATE, 'hh24') <= 13) THEN
                raise_application_error(-20007, '지금은 일못해');
    END IF;
END;

-- raise_application_error
    -- 표준화되지 않은 에러 코드와 에러 메시지를 리턴하기 위해 raise_application_error 프로시저 사용
     -- 코드값은 -20000~-20999사이의 수 사용   

INSERT INTO EXAM1(id, name) VALUES(5, 'bbb');
SELECT * FROM EXAM1;
SELECT * FROM exam2;

-- 위 트리거 수정
CREATE OR REPLACE TRIGGER trgExam1
        BEFORE DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
    IF TO_CHAR(SYSDATE, 'DAY') IN ('토요일', '일요일')
         OR TO_CHAR(SYSDATE, 'hh24') < 9 OR
                TO_CHAR(SYSDATE, 'hh24') > 18 THEN
                raise_application_error(-20007, '지금은 일못해');
    END IF;
END;

----------------------------------------------------
-- 테이블을 삭제하면 트리거도 자동적으로 지워진다.

//*****************************************
-- 행트리거
    조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건]절 정의된다.
-- :NEW -> 참조후 열의 값
     -- INSERT에 의해 새로 추가된(할) 레코드
     -- UPDATE에 의해 수정된(할) 레코드
-- :OLD -> 참조전 열의 값
     -- UPDATE에 의해 수정되기 전 레코드
     -- DELETE에 의해 삭제된(할) 레코드

--  다음과 같은 방법을 이용하여 관련된 트리거는 하나의 트리거로 작성 할 수 있다.
CREATE OR REPLACE TRIGGER 트리거이름
AFTER[또는 BEFORE] DELETE OR INSERT OR UPDATE ON 테이블명
FOR EACH ROW
[
DECLARE
   변수 타입;
]
BEGIN
  IF INSERTING THEN
    -- 추가할 때
  ELSIF UPDATING THEN
    -- 수정할 때
  ELSIF DELETING THEN
    -- 삭제할 때
  END IF;
END;
/

----------------------------------------------------
CREATE TABLE demo1(
    id NUMBER,
    data VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER demo_trigger
    BEFORE INSERT ON demo1 FOR EACH ROW
BEGIN
        SELECT TO_CHAR(SYSDATE, 'YYYY') INTO :new.id FROM dual;
END;
/
-- after 트리거는 :NEW를 수정 할 수 없다.

INSERT INTO demo1(id, data) VALUES(5, '우리');
COMMIT;

----------------------------------------------------
CREATE TABLE exama(
    hak VARCHAR2(20) PRIMARY KEY
    ,name VARCHAR2(20)
    ,kor NUMBER
    ,eng NUMBER
    ,mat NUMBER
);

CREATE TABLE examb(
    hak VARCHAR2(20) PRIMARY KEY
    ,tot NUMBER
    ,ave NUMBER(5, 1)
    ,CONSTRAINT fk_examb_hak FOREIGN KEY(hak)
                   REFERENCES exama(hak)
);

-- 행 트리거(AFTER) : 추가
CREATE OR REPLACE TRIGGER triInsExama
AFTER INSERT ON exama
FOR EACH ROW
DECLARE
    vtot NUMBER;
    vave NUMBER(5, 1);
BEGIN
    vtot := :NEW.kor + :NEW.eng + :NEW.mat;
    vave := ROUND(vTOt/3, 1);

    INSERT INTO examb(hak, tot, ave)
              VALUES(:NEW.hak, vtot, vave);
END;
/

INSERT INTO exama(hak, name, kor, eng, mat) VALUES
       ('1111', '홍길동', 80, 80, 80);
COMMIT;
SELECT * FROM exama;
SELECT * FROM examb;

-- 행 트리거(BEFORE) : 삭제
CREATE OR REPLACE TRIGGER triDelExama
BEFORE DELETE ON exama
FOR EACH ROW
BEGIN
    DELETE FROM examb WHERE hak = :OLD.hak;
END;
/

DELETE FROM exama WHERE hak = '1111';
COMMIT;
SELECT * FROM exama;
SELECT * FROM examb;

-- 행 트리거(AFTER) : 수정
CREATE OR REPLACE TRIGGER triUpdateExama
AFTER UPDATE ON exama
FOR EACH ROW
DECLARE
    vtot NUMBER;
    vave NUMBER(5, 1);
BEGIN
    vtot := :NEW.kor + :NEW.eng + :NEW.mat;
    vave := ROUND(vtot/3, 1);

    UPDATE examb SET tot=vtot, ave=vave
                 WHERE hak = :NEW.hak;  -- :OLD.hak
END;
/