1. Trigger (트리거) 의 종류
1) DML 트리거
2) DDL 트리거
3) DB 작업에 대한 트리거 ( log on, log off, startup, shutdown )
트리거란 ?
1) INSERT, UPDATE, DELETE 문이 TABLE에 대해 묵시적으로 수행되는 PROCEDURE이다.
2) 트리거는 TABLE과는 별도로 DATABASE에 저장된다.
3) 트리거는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있다.
4) 행트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길 때마다 실행되며, 그 데이터 행의 실제값을 제어할 수 있다.
5) 문장 트리거 : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다.
==========================================================
트리거 문법 :
CREATE OR REPLACE TRIGGER 트리거명
BEFORE | AFTER
트리거이벤트[ INSERT | UPDATE | DELETE ] (OF 컬럼명) ON 테이블명
FOR EACH ROW
WHEN ( CONDITION )
[ PL/SQL BLOCK ]
==========================================================
- DML 트리거의 사용 예
==========================================================
예제 1.
CREATE OR REPLACE TRIGGER TRIG_TEST_EMP
BEFORE INSERT OR UPDATE OF SAL ON TB_LS_EMP867
BEGIN
-- IF문을 사용해서 트리거를 공통으로 사용할 수있다. 혼합트리거
-- 사용자정의 예외처리
IF (TO_CHAR(SYSDATE,'DY') IN ('토','일'))
THEN RAISE_APPLICATION_ERROR(- 20003,'토요일, 일요일에는 데이터 갱신을 할 수 없습니다.');
END IF;
IF (TO_CHAR(SYSDATE,'HH24MI') NOT BETWEEN '09:00' AND '11:30' )
THEN RAISE_APPLICATION_ERROR(- 20005,' 오전업무시간 외에는 수정할 수 없습니다.');
END IF;
END;
/
SQL > SELECT * FROM TB_LS_EMP867;
SQL > UPDATE TB_LS_EMP867
SET SAL = '3000000'
WHERE EMP_NO = 20;
SQL > SELECT * FROM TB_LS_EMP867;
============================================================
예제 2. 사원테이블의 부서를 갱신하면, 변경되기 전 월급과 변경 후의 월급이 TB_LS_EMP867_HIST
테이블에 입력되게 하시오.
2-1. 테이블 생성을 먼저 해주자.
CREATE TABLE TB_LS_EMP867_HIST
( EMP_NO NUMBER(8),
OLD_DEPT_NO NUMBER(4),
NEW_DEPT_NO NUMBER(4),
WR_DTM VARCHAR2(14));
2-2. 트리거 생성을 해주자.
CREATE OR REPLACE TRIGGER TRIG_EDIT_DEPT
AFTER UPDATE OF DEPT_NO ON TB_LS_EMP867
FOR EACH ROW
- :old - refers to Old Value
- :new - refers to New value
BEGIN
INSERT INTO TB_LS_EMP867_HIST
VALUES ( :OLD.EMP_NO, :OLD.DEPT_NO, :NEW.DEPT_NO, TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') );
-- 트리거에서는 이곳에 COMMIT을 하면 에러가 발생한다.
END;
/
SQL > UPDATE TB_LS_EMP_SET ....으로 테스트
============================================================
예제 3. TB_LS_EMP867_DELETE 테이블을 생성하고 TB_LS_EMP867 의 데이터를 삭제하면,
삭제한 레코드 중 EMP_NO, DEPT_NO, SAL 데이터가 TB_LS_EMP867_DELETE 테이블에 입력하되게
TRIGGER를 생성하시오.
2-1. 테이블 생성을 먼저 해주자.
CREATE TABLE TB_LS_EMP867_DELETE
( EMP_NO NUMBER(8),
DEPT_NO NUMBER(4),
SAL NUMBER(12),
WR_DTM VARCHAR2(14));
2-2. 트리거 생성을 해주자.
CREATE OR REPLACE TRIGGER TRIG_DEL_EMP_INFO
AFTER DELETE ON TB_LS_EMP867
FOR EACH ROW
- :old - refers to Old Value
- :new - refers to New value
BEGIN
INSERT INTO TB_LS_EMP867_DELETE
VALUES (:OLD.EMP_NO, :OLD.DEPT_NO, :OLD.SAL, TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') );
END;
/
SQL > DELETE FROM TB_LS_EMP_SET ....으로 테스트
============================================================
- DDL 트리거 예제
예제 1. TB_LS_EMP867 테이블을 DROP 혹은 ALTER 작업이 수행되지 않도록 TRIGGER(트리거) 를 생성하라.
CREATE OR REPLACE TRIGGER NO_DDL
BEFORE DROP OR ALTER ON chpark.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20010,'테이블을 삭제하거나 변경할 수 없습니다.');
END;
/
SQL > DROP TABLE TB_LS_EMP867; 으로 테스트
