PL/SQL (24) 오라클(Oracle) 트리거(Trigger)

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;  으로 테스트