트리거(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;

---------------------------------------------------

-- 행트리거

-- :NEW -> 참조후 열의 값

     -- INSERT에 의해 새로 추가된(할) 레코드

     -- UPDATE에 의해 수정된(할) 레코드

-- :OLD -> 참조전 열의 값

     -- UPDATE에 의해 수정되기 전 레코드

     -- DELETE에 의해 삭제된(할) 레코드

----------------------------------------------------

--테이블 작성

CREATE TABLE exama

(

  hak VARCHAR2(20) PRIMARY KEY

  ,name VARCHAR2(20)

  ,kor NUMBER(3)

  ,eng NUMBER(3)

  ,mat NUMBER(3)

);

CREATE TABLE examb

(

  hak VARCHAR2(20) PRIMARY KEY

  ,tot NUMBER

  ,ave NUMBER

  ,CONSTRAINT fk_examb_hak FOREIGN KEY (hak) REFERENCES exama (hak)

);

CREATE TABLE examc

(

  memo VARCHAR2(20)

  ,created DATE DEFAULT SYSDATE

);

-- 문장 트리거 작성

CREATE OR REPLACE TRIGGER trgExam

  AFTER   INSERT   OR   UPDATE   OR   DELETE   ON   exama 

--AFTER : 실행 시간 -> 이벤트(insert update delete) 실행하고  BEGIN문 실행 // BEFOR는 그 반대, 오라클에서는 자기가 알아서 한다.

  BEGIN

  IF INSERTING THEN

    INSERT INTO examc(memo) VALUES ('추가');

  ELSIF UPDATING THEN

    INSERT INTO examc(memo) VALUES ('수정');

  ELSIF DELETING THEN

    INSERT INTO examc(memo) VALUES ('삭제');

  END IF;

END;

/

INSERT INTO exama(hak, name, kor, eng, mat) VALUES('1','a',100,100,100);

INSERT INTO exama(hak, name, kor, eng, mat) VALUES('2','a',100,100,100);

INSERT INTO exama(hak, name, kor, eng, mat) VALUES('3','a',100,100,100);

COMMIT;

UPDATE exama SET name='b';

COMMIT;

DELETE FROM exama;

COMMIT;

SELECT * FROM examc;

SELECT * FROM tab;

DELETE FROM exama;

-------------------------------------------------------------------------------

--행트리거 

  --입력

CREATE OR REPLACE TRIGGER  insExama

AFTER INSERT ON exama

FOR EACH ROW 

DECLARE -- 프로시져와 펑션의 IS와 다르게 DCLARE를 써준고 변수선언

  vtot NUMBER(3);

  vave NUMBER(5,1);

BEGIN -- INSERT 해줄때 사용하는 실행문

  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('1','a',100,100,100);

INSERT INTO exama (hak, name,kor,eng,mat) VALUES('2','b',90,90,90);

COMMIT;

SELECT * FROM exama;

SELECT * FROM examb;

  -- 수정

CREATE OR REPLACE TRIGGER upExama

AFTER UPDATE ON exama 

FOR EACH ROW

DECLARE

  vtot NUMBER(3);

  vave NUMBER(5,1);

BEGIN --UPDATE해줄 때 사용할 실행문

  vtot := :NEW.kor + :NEW.eng + :NEW.mat;

  vave := ROUND(vtot/3,1);

  UPDATE examb SET tot=vtot, ave=vave WHERE hak =: OLD.hak; -- AFTER UPDATE ON exama의 UPDATE를 써주엇기 떼ㅐ문에 여기다가

END;

/

UPDATE exama SET kor=10;

COMMIT;

SELECT * FROM exama;

SELECT * FROM examb;

  --삭제

CREATE OR REPLACE TRIGGER  insExama

AFTER DELETE ON exama 

FOR EACH ROW

BEGIN-- DELETE 할 떄 사용하는 실행문

  DELETE FROM examb WHERE hak =:OLD.hak;

END;

/

DELETE FROM exama;

COMMIT;

SELECT * FROM exama;

SELECT * FROM examb;

---

NEW -> INSERT  한 데이터 , UPDATE 한 데이터

OLD -> UPDATE 전 데이터 , DELETE 전 데이터

---