04월10일 이론 및 실습 Trigger

/* TRIGEER */

 

1. Statement Trigger(문장 트리거)

1.1 Before Statement Trigger

1.2 After Statement Trigger

 

2. Row Trigger(행 트리거)

(특정 행의 VALUE 값에 따라)

2.1 Before Row Trigger

2.2 After Row Trigger

 

 

/* 공통

 

Before : DML 문(Insert, Update, Delete)이 발생하기 전 미리 실행하는 것으로 주로 검사를 목적으로 한다.

After : DML 문(Insert, Update, Delete)이 발생한 후 그 결과에 따라 또 다른 명령을 실행하는 것.

주로, 연쇄적인 액션을 취하는 것으로 사용한다. */

 

 

/*BEFORE STATMENT TRIGGER 실습 1*/

 

 

CREATE TABLE TBLMEMO

(NO NUMBER

,NAME VARCHAR2(10)

,CONTENTS VARCHAR2(100)

,NALJA DATE DEFAULT SYSDATE

);

 

--tblmemo 테이블에 insert, update, delete를 할 수 있는 요일명과 시간을 제한하도록 한다.

-- 이때 사용가능한 시간은 월~ 금 , 10:00~18:00 까지로 한다.

--이렇게 되도록 하는 Before Statement Trigger(문장 트리거)

 

 

 

--요일보기

SELECT TO_CHAR(SYSDATE,'DAY'),

TO_CHAR(SYSDATE,'DY'),

TO_CHAR(SYSDATE,'DY'),

TO_CHAR(SYSDATE,'D') --1(일)2(월)3(화)4(수)5(목)6(금)7(토)

FROM DUAL;

----

 

 

BTRI_TBLMEMO_DML trigger 작성

CREATE OR REPLACE TRIGGER BTRI_TBLMEMO_DML BEFORE INSERT OR UPDATE OR DELETE ON TBLMEMO -- TABLE_NAME   DECLARE IMPOSSIBLEDML EXCEPTION; --'IMPOSSIBLEDML'= >변수명 , 'EXCEPTION'은 사용자정의 예외절   BEGIN   IF TO_CHAR(SYSDATE,'D') IN ('7','1') OR NO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 11 OR NO_NUMBER(TO_CHAR(SYSDATE,'HH24')) > 17   /*NO_NUMBER(TO_CHAR(SYSDATE,'HH24')) <= 10 OR NO_NUMBER(TO_CHAR(SYSDATE,'HH24')) >= 18*/--위와 같은 결과값   THEN --DML이 불가능합니다 라는 에러메세지를 보여주고 싶다. (사용자정의 오류 메세지, = 사용자정의 예외절(EXCEPTION))   RAISE IMPOSSIBLEDML;   END IF;     EXCEPTION WHEN IMPOSSIBLEDML THEN RAISE_APPLICATION_ERROR(-20001,'월~금요일에 11:00 ~ 18:00까지만 DML 작업이 가능합니다.'); ---사용자정의가능 오류코드 범위 : 20001 ~ -20999   END; /

 

 

INSERT INTO TBLMEMO VALUES(1,'홍길동','TRIGGER_TEST',DEFAULT);

위 insert DML문을 위 지정한 시간 외에 실행하게 되면, 사용자 정의 오류메세지와 함께 오류가 발생하게 된다.

이와 관련된 정보들을 검색하기 위해 아래 쿼리문들을 실행한다.

 

SELECT *

FROM USER_TRIGGERS

WHERE TABLE_NAME='TBLMEMO';

 

 

 

SELECT *

FROM USER_SOURCE --SOURCE 정보조회

WHERE TYPE='TRIGGER' AND NAME='btri_tblmemo_dml';

 

 

SELECT STATUS

FROM USER_TRIGGERS

WHERE TRIGGER_NAME='btri_tblmemo_dml';

 

 

ALTER TRIGGER btri_tblmemo_dml DISABLE;

btri_tblmemo_dml에서 정의한 trigger를 비활성화 시키기 위해서 위와 같이 'Disable' 옵션을 사용한다.

 

SELECT STATUS

FROM USER_TRIGGERS

WHERE TRIGGER_NAME='btri_tblmemo_dml';

 

--TRIGGER를 비활성화 시켰기 떄문에 dml문의 실행이 이루어진다.

 

ALTER TRIGGER btri_tblmemo_dml ENABLE;

 

 

/*                BEFORE ROW TRIGGER                */

 

 

SELECT *

FROM TBLHUMAN;

 

--TRIGGER 내에서 발생한 INSERT 된 행은 가상 테이블인 :NEW 에 들어옴

--TRIGGER 내에서 발생한 DELETE 된 행은 가상 테이블인 :OLD 에 들어옴

--UPDATE란 = 먼저 DELETE 하고 나서 INSERT 되어~~

 

BTRI_TBLHUMAN_INSUPD

CREATE OR REPLACE TRIGGER BTRI_TBLHUMAN_INSUPD BEFORE INSERT OR UPDATE OF JUBUN ON TBLHUMAN   FOR EACH ROW     DECLARE JUBUNFAIL EXCEPTION;   BEGIN   IF F_JUBUNCHECK(:NEW.JUBUN)=0 THEN   RAISE JUBUNFAIL; END IF;   EXCEPTION WHEN JUBUNFAIL THEN raise_application_error(-20002,'주민번호에 문제가 있습니다.');   END; /

 

 

--위와 같이 BTRI_TBLHUMAN_INSUPD 트리거를 생성후, 올바르지 않은 민증번호를 아래와 같이 입력해본다

 

INSERT INTO TBLHUMAN VALUES('강감찬','8111111234567',300);

--ORA-20002: 주민번호에 문제가 있습니다.

ORA-06512: "SCOTT.BTRI_TBLHUMAN_INSUPD", 줄 13에서

ORA-04088: 트리거 'SCOTT.BTRI_TBLHUMAN_INSUPD'의 수행시 오류