오라클 트리거(Trigger), 행트리거, 명령문트리거, for each row, create trigger(ORACLE동영상교육)
트리거(Trigger)
트리거링 이벤트에 대해 자동으로 실행되는 PL/SQL 블록으로, 테이블, 인덱스처럼 데이터베이스에 저장되는 스키마 오브젝트 입니다.
트리거를 이용하여 UNIQUE, NOT NULL, CHECK 제약조건으로 정의할 수 없는 복잡한 비지니스 로직에 대해 제약을 가할 수 있으며, 파생 컬럼에 대한 값을 자동계산, 데이터값 검사등의 일을 할 수 있습니다. 또한 트리거를 사용하면 SQL문이 간결해 질 수 있으며, 테이블의 모든 변경을 감사할 수 있습니다.
트리거를 사용할 경우 단점은 시스템 유지보수, 관리가 쉽지 않고 대량 데이터 갱신이나 입력시 트리거의 존재를 미처 확인하지 못하는 경우 예기치 않은 동작이 일어날 수 있으며 저장 함수나 저장 프로시저에 비해 디버깅이 어렵습니다. 또한 과도한 트리거의 사용은 DB시스템 및 응용프로그램의 성능에 악 영향을 미칠 수 있으니 사용에 주의해야 합니다.
트리거에서는 COMMIT, ROLLBACK을 사용할 수 없습니다. 트리거는 트리거를 호출한 이벤트(INSERT, UPDATE, DELETE)의 COMMIT, ROLLBACK을 따라 가는데 트리거링 이벤트가 COMMIT되면 같이 COMMIT 되고 ROLLBACK 되면 같이 ROLLBACK 됩니다.
트리거링 이벤트에는 다음과 같은것들이 있습니다.
데이터 조작어(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;
WHEN : WHEN절에 조건을 줘서 조건에 맞는 경우에만 트리거가 동작되도록 할 수 있습니다.
CREATE [ OR REPLACE ] TRIGGER trigger_name [타이밍 AFTER/BEFORE] [이벤트1] [OR 이벤트2] …… ON table_name [ FOR EACH ROW ] -- 행트리거 여부를 결정, 모든 레코드마다 트리거동작 [WHEN 조건] 선언부 BEGIN 트리거 실행 코드 EXCEPTION 예외처리 END;
주말에는 DML 방지 트리거 실습 : 명령문 트리거
EMP 테이블에 대해 토요일, 일요일에는 DML 사용을 막는 명령문 트리거를 작성하세요. 마침 오늘이 2021년 12월25일 토요일 이네요~ 즐거운 성탄절 되세요~~
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이(가) 삭제되었습니다.
EMP 테이블의 트리거링 이벤트 확인 실습 : 명령문 트리거
EMP 테이블에 INSERT/UPDATE/DELETE 실행시 명령문 트리거에서 이벤트를 잡아 메시지를 출력하세요.
SET SERVEROUTPUT ON;
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이(가) 삭제되었습니다.
급여 변경시 백업 테이블에 이전/이후 값 저장하기 : 행 트리거
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 -- 로깅용 테이블에 급여값의 이전/이후 값을 입력 합니다. 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 됩니다.
