오라클 트리거 동영상,Oracle Trigger란? 행트리거, 명령문트리거, 오라클 트리거 실행순서제어, FOLLOWS, RECEDES, Oracle Trigger Order http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=306instead of trigger, 오라클학원/오라클동영상/SQL학원/SQL교육/자바학원
ojc.asia
http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=308
ojc.asia
http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=306
ojc.asia
https://www.youtube.com/watch?v=Ja6HupDK0Ow&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=12&t=4s
https://www.youtube.com/watch?v=SZ9UqYQR1jw&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=26
https://www.youtube.com/watch?v=A274ySQmJOc&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=27
Setting Trigger Order
- 오라클에서 동일한 트리거링 이벤트, 타이밍에 대해 여러 개의 트리거를 작성하는 것이 가능 합니다.
- Oracle 11g 이후 동일한 트리거링 이벤트 및 트리거 타이밍에 대해 실행되는 트리거가 여러 개인 경우 FOLLOWS, PRECEDES 구를 통해 실행의 우선 순위를 정하는 것이 가능해 졌습니다.
- PRECEDES 구는 REVERSE CROSSEDITION TRIGGER라는 트리거에서만 사용가능 하므로 오늘 실습은 FOLLOWS 구를 이용하여 트리거의 실행 순서를 제어합니다.
[형식]
CREATE OR REPLACE TRIGGER
[TIMING SPECIFICATION]
[EVENT SPECIFICATION]
FOLLOWS [TRIGGER TO BE FOLLOWED]
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER tr_follows_test1
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('tr_follows_test1 실행...');
END;
/
트리거가 생성되었습니다.
CREATE OR REPLACE TRIGGER tr_follows_test2
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('tr_follows_test2 실행...');
END;
/
트리거가 생성되었습니다.
update emp set sal = 9999 where ename = 'KING';
tr_follows_test2 실행...
tr_follows_test1 실행...
CREATE OR REPLACE TRIGGER tr_follows_test2
BEFORE UPDATE ON emp
FOR EACH ROW
FOLLOWS tr_follows_test1
BEGIN
DBMS_OUTPUT.put_line('tr_follows_test2 실행...');
END;
/
트리거가 생성되었습니다.
update emp set sal = 9999 where ename = 'KING';
tr_follows_test1 실행...
tr_follows_test2 실행...
#trigger, #oracletrigger, #오라클트리거, #트리서순서, #오라클교육, #SQL교육, #자바교육, #ORACLE동영상, #SQL동영상, #오라클동영상, #오라클학원,#자바학원, #JAVA학원, trigger, oracletrigger, 오라클트리거, 트리서순서, 오라클교육, SQL교육, 자바교육, ORACLE동영상, SQL동영상, 오라클동영상, 오라클학원,자바학원, JAVA학원,
Instead Of Trigger, 복합 뷰에 INSERT할때 대신 실행되는 Trigger
Instead Of Trigger
- 여러 테이블로 이루어진 뷰에 DML이 가해질 때 대신 trigger를 동작시킬 수 있는데 이때 사용하는 것이 instead of trigger 이다.
drop table employee;
테이블이 삭제되었습니다.
drop table department;
테이블이 삭제되었습니다.
CREATE TABLE department (
deptno NUMBER PRIMARY KEY,
deptname VARCHAR2(20),
manager_num NUMBER
);
CREATE TABLE employee (
empno NUMBER PRIMARY KEY,
empname VARCHAR2(20),
deptno NUMBER REFERENCES department(deptno),
startdate DATE
);
CREATE OR REPLACE VIEW manager_info AS SELECT d.deptno, d.deptname, e.empno, e.empname
FROM employee e, department d
WHERE e.empno = d.manager_num;
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
FOR EACH ROW
DECLARE
employeeCount NUMBER;
BEGIN
SELECT COUNT(*) INTO employeeCount
FROM employee e
WHERE e.deptno = :new.deptno;
IF employeeCount >= 1 THEN
UPDATE department d
SET manager_num = :new.empno
WHERE d.deptno = :new.deptno;
insert into employee (empno, empname, deptno, startdate)
values (:new.empno, :new.empname, :new.deptno, sysdate);
ELSE
insert into department(deptno, deptname, manager_num)
values (:new.deptno, :new.deptname, 0);
insert into employee (empno, empname, deptno, startdate)
values (:new.empno, :new.empname, :new.deptno, sysdate);
END IF;
END;
/
INSERT INTO manager_info VALUES (11,'SALES',1004,'ANGEL');
1 개의 행이 만들어졌습니다.
INSERT INTO manager_info VALUES (11,'SALES',1005,'SUJI');
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> SELECT * FROM department;
DEPTNO DEPTNAME MANAGER_NUM
---------- -------------------- -----------
11 SALES 1005
SQL> SELECT * FROM employee;
EMPNO EMPNAME DEPTNO STARTDAT
---------- -------------------- ---------- --------
1004 ANGEL 11 16/05/01
1005 SUJI 11 16/05/01
SQL> INSERT INTO manager_info VALUES (21,'RND',1006,'SMITH');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM department;
DEPTNO DEPTNAME MANAGER_NUM
---------- -------------------- -----------
11 SALES 1005
21 RND 0
SQL> SELECT * FROM employee;
EMPNO EMPNAME DEPTNO STARTDAT
---------- -------------------- ---------- --------
1004 ANGEL 11 16/05/01
1005 SUJI 11 16/05/01
1006 SMITH 21 16/05/01
SQL> SELECT * FROM manager_info;
DEPTNO DEPTNAME EMPNO EMPNAME
---------- -------------------- ---------- --------
11 SALES 1005 SUJI
#오라클트리거, #Trigger, #ORACLE, #오라클동영상, #트리거동영상, #SQL동영상, #SQL교육, #SQL학원, #오라클학원,#ORACLE학원, #자바학원, #JAVA학원, 오라클트리거, Trigger, ORACLE, 오라클동영상, 트리거동영상, SQL동영상, SQL교육, SQL학원, 오라클학원,ORACLE학원, 자바학원, JAVA학원,
오라클 트리거(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 됩니다.
| TR_EMP_UPDATE 트리거를 삭제 하세요. |
| DROP TRIGGER TR_EMP_UPDATE ; |
<실행결과>
Trigger CHK_EMP_DML이(가) 삭제되었습니다.
#오라클트리거, #오라클Trigger, #PLSQL, #오라클동영상, #행트리거, #명령문트리거, #PLSQL교육, #오라클교육, #오라클학원, #ORACLE교육, #ORACLE학원, 오라클트리거, 오라클Trigger, PLSQL, 오라클동영상, 행트리거, 명령문트리거, PLSQL교육, 오라클교육, 오라클학원, ORACLE교육, ORACLE학원,
https://www.youtube.com/watch?v=EZl9trYM1HM&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=23
