오라클 트리거(Trigger), 명령문트리거, 행트리거

 

오라클 트리거(Trigger), 명령문트리거, 행트리거

트리거링 이벤트에 대해 자동으로 실행되는 PL/SQL 블록으로 데이터베이스에 저장되는 스키마 오브젝트 입니다.

트리거링 이벤트에는 다음과 같은것들이  있습니다.

데이터 조작어(DML, Data Manipulation Language) : INSERT, UPDATE, DELETE 데이터 정의어(DDL, Data Definition Language) : CREATE, ALTER, DROP 데이터베이스 오퍼레이션 : SERVER ERROR, LOG ON, STARTUP or SHUTDOWN

트리거 타이밍

BEFORE : 이벤트가 발생하기 전 AFTER : 이벤트가 발생한 후

FOR EACH ROW

DML의 영향을 받는 모든 행, 레코드마다 트리거가 작동되는 트리거를 행 트리거라고 하며 몇건의 데이터가 삽입, 삭제, 갱신되더라도 한번만 동작하는 트리거를 명령문 트리거라고 한다.  CREATE TRIGGER 명령에 FOR EACH ROW절이 있다면 행 트리거이고 행 트리거에 한해 이전/이후 레코드의 값을 제어할 수 있습니다.    :old  : UPDATE 되기전의 값, DELETE 되기전의 값에 접근 가능    :new : INSERT 되는 값, UPDATE된 후의 값에 접근 가능   만약 :old, :new 대신 다른 키워드를 사용하려면 아래처럼 하면 됩니다.   -- EMP 테이블의 모든 행마다 UPDATE가 일어나기 전에 작동하는 행 트리거 입니다. -- 이전행은 o, 이후행은 n으로 접근할 수 있습니다. CREATE OR REPLACE TRIGGER salary_change BEFORE UPDATE ON EMP REFERENCING new AS n old AS o FOR EACH ROW BEGIN    ......    dbms_output.put_line(:n.ename); END;

트리거를 이용하여 UNIQUE, NOT NULL, CHECK 제약조건으로 정의할 수 없는 복잡한 비지니스 로직에 대해 제약을 가할 수 있으며, 파생 컬럼에 대한 값을 자동계산, 데이터값 검사등의 일을 할 수 있습니다. 또한 트리거를 사용하면 SQL문이 간결해 질 수  있으며, 테이블의 모든 변경을 감사할 수 있습니다.

트리거를 사용할 경우 단점은 시스템 유지보수, 관리가 쉽지 않고 대량 데이터 갱신이나 입력시 트리거의 존재를 미처 확인하지 못하는 경우 예기치 않은 동작이 일어날 수 있으며 저장 함수나 저장 프로시저에 비해 디버깅이 어렵습니다. 또한 과도한 트리거의 사용은 DB시스템 및 응용프로그램의 성능에 악 영향을 미칠 수 있으니 사용에 주의해야 합니다. 

트리거에서는 COMMIT, ROLLBACK을 사용할 수 없습니다. 트리거는 트리거를 호출한 이벤트(INSERT, UPDATE, DELETE)의 COMMIT, ROLLBACK을 따라 가는데 트리거링 이벤트가 COMMIT되면 같이 COMMIT 되고 ROLLBACK 되면 같이 ROLLBACK 됩니다.

<기본 형식>

CREATE [ OR REPLACE ] TRIGGER trigger_name [AFTER/BEFORE]  [이벤트1] [OR 이벤트2] ……    ON table_name    [ FOR EACH ROW ]  -- 행트리거 여부결정, DML의 영향을 받는 레코드마다 트리거동작    [WHEN condition] DECLARE    declaration_section BEGIN    execution_section EXCEPTION    exception section END; CREATE [ OR REPLACE ] TRIGGER trigger_name : trigger_name 이름의 트리거를 생성 합니다. 이미 존재한다면 대체 합니다. [AFTER/BEFORE]  : 트리거 동작이 이벤트 전인지 후 인지 기술. ON table_name : 트리거의 타겟 테이블명.  [ FOR EACH ROW ] : 행트리거 여부결정, DML의 영향을 받는 레코드마다 트리거동작  [WHEN condition] : 조건이 맞을 때 트리거가 동작. declaration_section : 변수 선언부 execution_section : 트리거의 실행 코드 영역 exception section : 예외처리 영역

15.5.1. 주말에는 DML 방지 트리거 실습 : 명령문 트리거

EMP 테이블에 대해 토요일, 일요일에는 DML 사용을 막는 명령문 트리거를 작성하세요.

CREATE OR REPLACE TRIGGER CHK_EMP_DML -- EMP 테이블에 INSERT/UPDATE/DELETE 전에 동작 BEFORE INSERT OR UPDATE OR DELETE ON EMP BEGIN    -- 토/일요일의 경우    IF (TO_CHAR(SYSDATE,'DY') IN ('토','일')) THEN        -- PL/SQL에서 예외가 발생하는 경우는 서버에 의해 자동으로 발생되는 경우와        -- 사용자가 RAISE, RAISE_APPLICATION_ERROR등을 기술하여 예외를 발생시키는 두가지 경우가 있다.       RAISE_APPLICATION_ERROR(-20001,'주말 변경 불가합니다.');    END IF; END;

<실행결과>

Trigger CHK_EMP_DML이(가) 컴파일되었습니다.

오늘의 요일을 확인해 보겠습니다.

SELECT TO_CHAR(SYSDATE,'DY')  FROM DUAL;

<실행결과>

	TO_CHAR(SYSDATE,'DY')
1	일

EMP 테이블에서 모든 사원의 급여를 0으로 변경해 보겠습니다.

UPDATE EMP SET SAL=0 ;

<실행결과>

UPDATE EMP SET SAL=0

오류 보고 -

ORA-20001: 주말 변경 불가합니다.

ORA-06512: "SCOTT.CHK_EMP_DML",  6행

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

오늘이 일요일 이므로 UPDATE문은 수행되지 않습니다.

CHK_EMP_DML 트리거를 삭제 하세요.

DROP TRIGGER CHK_EMP_DML;

<실행결과>

Trigger CHK_EMP_DML이(가) 삭제되었습니다.

15.5.2. EMP 테이블의 트리거링 이벤트 확인 실습 : 명령문 트리거

EMP 테이블에 INSERT/UPDATE/DELETE 실행시 명령문 트리거에서 이벤트를 잡아 메시지를 출력하세요.

CREATE OR REPLACE TRIGGER CHK_EMP -- EMP 테이블에서 INSERT/UPDATE(ENAME, SAL 컬럼)/DELETE 전에 트리거 동작 BEFORE INSERT OR UPDATE OF ENAME, SAL OR DELETE ON EMP BEGIN    CASE       -- INSERT이면       WHEN INSERTING THEN          DBMS_OUTPUT.PUT_LINE('Inserting...');       -- SAL 컬럼의 수정이면       WHEN UPDATING('SAL') THEN          DBMS_OUTPUT.PUT_LINE('Updating sal...');       -- ENAME 컬럼의 수정이면       WHEN UPDATING('ENAME') THEN         DBMS_OUTPUT.PUT_LINE('Updating ename...');       -- 삭제이면       WHEN DELETING THEN         DBMS_OUTPUT.PUT_LINE('Deleting...');    END CASE; END;

<실행결과>

Trigger CHK_EMP이(가) 컴파일되었습니다.

7369 SMITH 직원의 데이터를 삭제 하세요.

DELETE FROM EMP WHERE EMPNO = 7369;

<실행결과>

Deleting...

1 행 이(가) 삭제되었습니다.

트리거가 동작되어 트리거의 WHEN DELETING THEN절이 실행 되었음을 확인 할 수 있습니다.

트랜잭션을 롤백 하세요..

ROLLBACK;

<실행결과>

롤백 완료.

CHK_EMP 트리거를 삭제 하세요.

DROP TRIGGER CHK_EMP;

<실행결과>

Trigger CHK_EMP이(가) 삭제되었습니다.

15.5.3. 급여 변경시 백업 테이블에 이전/이후 값 저장하기 : 행 트리거

EMP 테이블의 급여(SAL) 값의 변경에 대해 로깅을 남기기 위해 테이블을 하나 만들고 급여값이 수정되면 이전/이후 값을 트리거를 이용하여 저장하는 예제를 작성해 봅시다. 

급여의 이전/이후 값을 저장하기 위한 EMP_BAK 테이블을 생성 하세요.

CREATE TABLE EMP_BAK (     EMPNO NUMBER    ,ENAME VARCHAR2(50)    ,OLD_SAL NUMBER    ,NEW_SAL NUMBER    ,U_DATE DATE    ,ACTION VARCHAR2(20) );

<실행결과>

Table EMP_BAK이(가) 생성되었습니다.

급여값이 수정되면 동작하는 트리거를 만들고 이전, 이후 값을 EMP_BAK 테이블에 저장 하세요.

CREATE OR REPLACE TRIGGER TR_EMP_UPDATE AFTER UPDATE OF SAL ON EMP -- EMP 테이블에서 SAL 칼럼값의 변경이 일어난 후에 동작 FOR EACH ROW               -- 변경되는 행마다 동작하는 행 트리거 BEGIN   -- 로깅용 테이블에 급여값의 이전/이후 값을 입력 합니다.   -- :NEW.EMPNO와 :OLD.EMPNO, :NEW.ENAME과 :OLD.ENAME은 값은 값입니다.   INSERT INTO EMP_BAK VALUES (:NEW.EMPNO, :NEW.ENAME, :OLD.SAL, :NEW.SAL, SYSDATE,'UPDATE'); END;

<실행결과>

Trigger TR_EMP_UPDATE이(가) 컴파일되었습니다.

EMP 테이블에서 모든 직원의 급여를 10% 인상하세요.

UPDATE EMP SET SAL = SAL + ( SAL * 10 / 100);

<실행결과>

14개 행 이(가) 업데이트되었습니다.

EMP_BAK 테이블에서 데이터를 확인 하세요.

SELECT *  FROM EMP_BAK;

<실행결과>

	EMPNO	ENAME	OLD_SAL	NEW_SAL	U_DATE	ACTION
1	7369	SNITH	800	880	21/02/14	UPDATE
2	7499	AALLEN	1600	1760	21/02/14	UPDATE
…...	…...	…...	…...	…...	…...	…...
14	7934	MILLER	1300	1300	21/02/14	UPDATE

TR_EMP_UPDATE 트리거가 정상 작동하여 EMP_BAK 테이블에 급여(SAL) 값에 대한 변경전, 변경후 값이 잘 저장되어 있습니다. 

다음 실습을 위해 전체 트랜잭션을 롤백 하세요.

ROLLBACK;

<실행결과>

롤백 완료.

롤백을 하면 EMP_BAK 테이블의 데이터도 같이 롤백 됩니다. 확인해 보세요, 빈 데이터가 보일 겁니다. 트리거는 자체적으로 COMMIT, ROLLBACK을 할 수가 없습니다. 트리거를 호출한 트리거링 이벤트(여기서는 UPDATE)를 따라 갑니다. 즉 여기에서 UPDATE가 COMMIT 되면 트리거의 의 DML도 COMMIT 되고 UPDATE가 ROLLBACK 되면 트리거의 DML도 ROLLBACK 됩니다.

TR_EMP_UPDATE 트리거를 삭제 하세요.

DROP TRIGGER TR_EMP_UPDATE ;

<실행결과>

Trigger CHK_EMP_DML이(가) 삭제되었습니다.​

 

 #오라클트리거 #Trigger #명령문트리거 #행트리거