오라클 트리거(Trigger), 행트리거, 명령문트리거, for each row, create trigger(ORACLE동영상교육/JAVA학원/자바학원/오라클학원/오라클동영상)
https://www.youtube.com/watch?v=dofAcgYofvA&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=7&t=3s
https://www.youtube.com/watch?v=-2CzUYAmRvk&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=4
https://www.youtube.com/watch?v=9cS-bMTbkLo&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=5
https://www.youtube.com/watch?v=7iTlIcwOX4A&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=6
https://www.youtube.com/watch?v=gaEMXCtRg6U&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=8
트리거(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 됩니다.
| TR_EMP_UPDATE 트리거를 삭제 하세요. |
| DROP TRIGGER TR_EMP_UPDATE ; |
<실행결과>
Trigger CHK_EMP_DML이(가) 삭제되었습니다.
#오라클트리거, #오라클Trigger, #PLSQL, #오라클동영상, #행트리거, #명령문트리거, #PLSQL교육, #오라클교육, #오라클학원, #ORACLE교육, #ORACLE학원, 오라클트리거, 오라클Trigger, PLSQL, 오라클동영상, 행트리거, 명령문트리거, PLSQL교육, 오라클교육, 오라클학원, ORACLE교육, ORACLE학원,
