[오라클 트리거] ORACLE 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 사용자명;

GRANT CREATE TRIGGER TO SKY;

-- 사용자 계정

-- 부여된 권한 확인

SELECT * FROM USER_SYS_PRIVS;

//*****************************************

-- 문장 트리거

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

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

-- 트리거 확인

SELECT trigger_name, trigger_type, table_name

            FROM user_triggers;

SELECT TEXT FROM user_source;

//*****************************************

-- 행트리거

    조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [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;

/

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

-- 문장 트리거

DROP TABLE exam2 PURGE;

DROP TABLE exam1 PURGE;

CREATE TABLE  exam1 (

    id   VARCHAR2(50) PRIMARY KEY

,name VARCHAR2(50) NOT NULL

);

CREATE  TABLE  exam2(

    memo  VARCHAR2(100)

,created  DATE  DEFAULT  SYSDATE

);

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

CREATE OR REPLACE TRIGGER triExam  AFTER

INSERT OR UPDATE OR DELETE  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 * FROM user_triggers;

SELECT * FROM user_source;

INSERT INTO exam1(id, name) VALUES('1', 'aaa');

INSERT INTO exam1(id, name) VALUES('2', 'bbb');

COMMIT;

SELECT * FROM exam1;

SELECT * FROM exam2;

DELETE FROM exam1;

COMMIT;

SELECT * FROM exam1;

SELECT * FROM exam2;

-- exam1 테이블에 9~18시간에만 작업이 가능한 트리거 만들기

CREATE OR REPLACE TRIGGER triExam2  BEFORE

INSERT OR UPDATE OR DELETE  ON  exam1

BEGIN

   IF TO_CHAR(SYSDATE, 'D') IN (7, 1) OR TO_CHAR(SYSDATE, 'HH24') < 9

      OR TO_CHAR(SYSDATE, 'HH24') > 18 THEN

  RAISE_APPLICATION_ERROR(-20007, '놀땐 좀 놀아라...');

   END IF;   

END;

/

CREATE OR REPLACE TRIGGER triExam2  BEFORE

INSERT OR UPDATE OR DELETE  ON  exam1

BEGIN

   IF TO_CHAR(SYSDATE, 'D') IN (7, 1) OR (TO_CHAR(SYSDATE, 'HH24') >=10 

      AND TO_CHAR(SYSDATE, 'HH24') <=12) THEN

  RAISE_APPLICATION_ERROR(-20007, '놀땐 좀 놀아라...');

   END IF;   

END;

/

INSERT INTO exam1(id, name) VALUES('1', 'aaa');

SELECT * FROM exam1;

DROP TRIGGER triExam2;

DROP TRIGGER triExam;

DROP TABLE exam1 PURGE;

DROP TABLE exam2 PURGE;

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

DROP TABLE score3 PURGE;

DROP TABLE score2 PURGE;

DROP TABLE score1 PURGE;

CREATE TABLE score1 (

   hak VARCHAR2(30) PRIMARY KEY,

   name VARCHAR2(30) NOT NULL,

   kor  NUMBER(3) NOT NULL,

   eng  NUMBER(3) NOT NULL,

   mat  NUMBER(3) NOT NULL

);

CREATE TABLE score2 (

   hak VARCHAR2(30) PRIMARY KEY,

   tot  NUMBER(3) NOT NULL,

   ave  NUMBER(5,1) NOT NULL,

   CONSTRAINT  fk_score2_hak  FOREIGN  KEY(hak)  REFERENCES score1(hak)

);

CREATE TABLE score3 (

   hak VARCHAR2(30) PRIMARY KEY,

   kor  NUMBER(3,1) NOT NULL,

   eng  NUMBER(3,1) NOT NULL,

   mat  NUMBER(3,1) NOT NULL,

   CONSTRAINT  fk_score3_hak  FOREIGN  KEY(hak)  REFERENCES score1(hak)

);

SELECT * FROM user_procedures;

SELECT fnGrade(95) FROM dual;

CREATE OR REPLACE FUNCTION fnGrade

(

    pScore NUMBER

)

RETURN NUMBER

IS

   n NUMBER(2,1);

BEGIN

   IF pScore>=95 THEN n:=4.5;

   ELSIF pScore>=90 THEN n:=4.0;

   ELSIF pScore>=85 THEN n:=3.5;

   ELSIF pScore>=80 THEN n:=3.0;

   ELSIF pScore>=75 THEN n:=2.5;

   ELSIF pScore>=70 THEN n:=2.0;

   ELSIF pScore>=65 THEN n:=1.5;

   ELSIF pScore>=60 THEN n:=1.0;

   ELSE n:=0.0;

   END IF;

   RETURN n;

END;

/  

-- 행트리거

CREATE OR  REPLACE  TRIGGER  triInsertScore

AFTER  INSERT  ON  score1

FOR  EACH  ROW

DECLARE

   vtot  NUMBER(3);

   vave  NUMBER(5,1);

BEGIN

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

vave := vtot / 3;

INSERT INTO score2(hak, tot, ave) VALUES (:NEW.hak, vtot, vave);

INSERT INTO score3(hak, kor, eng, mat) VALUES(:NEW.hak, 

      fnGrade(:NEW.kor), fnGrade(:NEW.eng), fnGrade(:NEW.mat));

END;

/

INSERT INTO score1(hak, name, kor, eng, mat) VALUES('1', 'aaa', 80, 85, 75);

SELECT * FROM score1;

SELECT * FROM score2;

SELECT * FROM score3;

--

CREATE OR  REPLACE  TRIGGER  triUpdateScore

AFTER  UPDATE  ON  score1

FOR  EACH  ROW

DECLARE

   vtot  NUMBER(3);

   vave  NUMBER(5,1);

BEGIN

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

vave := vtot / 3;

UPDATE score2 SET tot = vtot, ave = vave WHERE hak = :OLD.hak;

UPDATE score3 SET kor=fnGrade(:NEW.kor), eng=fnGrade(:NEW.eng), 

       mat=fnGrade(:NEW.mat)  WHERE hak = :OLD.hak;

END;

/

UPDATE score1 SET kor=90, eng=80, mat=85 WHERE hak='1';

SELECT * FROM score1;

SELECT * FROM score2;

SELECT * FROM score3;

CREATE OR  REPLACE  TRIGGER  triDeleteScore

AFTER  DELETE  ON  score1

FOR  EACH  ROW

BEGIN

    DELETE FROM score2 WHERE hak = :OLD.hak;

    DELETE FROM score3 WHERE hak = :OLD.hak;

END;

/

DELETE FROM score1;

COMMIT;

SELECT * FROM score1;

SELECT * FROM score2;

SELECT * FROM score3;