[ORACLE] trigger 세미나

Trigger(트리거)

 

트리거란?

DML(INSERT,UPDATE,DELETE)명령어가 실행 될때 수행되는 묵시적 PROCEDURE(프로시저).

 

즉, 예를들어 우리 넥사크로에서 onload함수에 트랜잭션을 호출하는 명령어를 넣어 폼이 켜질때 이미 트랜잭션으로 필요한 데이터들을 받아와서

폼을 실행 시키는것이나 스프링의 인터셉터와 비슷한 기능과 역할을 함.  

INSERT,UPDATE,DELETE 가 실행되기 전/후 에 원하는 이벤트 등을 넣어서 자동으로 유용하게 쓰는것.

 

트리거의 분류

SQL문 실행시기에 따른 분류	before 트리거	INSERT,UPDATE,DELETE 실행하기 전에 트리거 먼저 실행 됨
	after 트리거	INSERT,UPDATE,DELETE 실행하고 난 후 트리거 실행 됨
실행시 영향을 받는 곳에 따른 분류	row 트리거 (행 트리거)	실행된 트리거가 row(행) 하나하나 마다 실행 됨
	statement 트리거(문장 트리거)	실행된 트리거가 INSERT,UPDATE,DELETE 문장에 1번만 실행 됨

 

 

분류에 따른 트리거의 종류

실행시점	실행범위	내용
before	statement	SQL문을 실행하기 전에 그 문장에 대해 한번 실행
before	row	SQL문을 실행하기 전에 각각 row(행)에 대해 한번씩 실행
after	statement	SQL문을 실행하고 난 후 그 문장에 대해 한번 실행
after	row	SQL문을 실행하고 난 후 각각 row(행)에 대해 한번씩 실행

 

 

 

 

 

 

트리거 문법

빨강 - 문법

핑크 - 생략가능(상황에따라 적을때도있고 안적을때도있는것들)

파랑 - 원하는 값입력

1.트리거 생성

CREATE [OR REPLACE] TRIGGER 트리거명

BEFORE or AFTER

트리거이벤트 ON 테이블명

[FOR EACH ROW [WHEN TRIGGER 조건]]

[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경후 값을 참조하는 변수명}]
DECLARE

선언문

BEGIN

PL/SQL 코드

END;

 

트리거명 : 자신이 원하는 트리거명

트리거이벤트 : insert,update,delete 중에서 자신이 트리거를 적용하기 원하는 곳( OR을 사용해서 여러개 가능 ex. insert or update )

테이블명 : 트리거를 적용시킬 테이블 명

 

[FOR EACH ROW [WHEN TRIGGER 조건]]

FOR EACH ROW 를 쓰면 row(행) 트리거를 생성하고 WHEN 조건을 주면 WHEN 조건에 만족하는 ROW(행)만 트리거 적용 가능.

즉,FOR EACH ROW를 안쓰면 statement(문장) 트리거 생성.

[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경후 값을 참조하는 변수명}]

row(행)트리거 일때, 트리거에 영향 받는 행들의 값을 참조할때 사용(statement(문장) 트리거에는 사용 못함).

{변경전 값을 참조하는 변수명} ,{변경후 값을 참조하는 변수명} 의 기본 변수명

:OLD = 참조 전 열의 값 (INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)

:NEW = 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정할 자료)

2. 트리거 상태 확인 

SELECT TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME FROM USER_TRIGGERS;    트리거의 이름,타입,적용테이블등을 볼 수 있다.

3. 트리거 상태변경 및 재 컴파일

ALTER TRIGGER 트리거명 {ENABLE/DISABLE};                      해당 트리거의 활성/비활성 상태를 변경가능.

ALTER TABLE 테이블명 {ENABLE/DISABLE} FROM TRIGGERS;   해당 테이블에 있는 모든 트리거의 활성/비활성 상태를 변경가능.

ALTER TRIGGER 트리거명 COMPILE;                                   트리거의 내용이 바뀌거나,제대로 작동안할 때 재 컴파일.

4. 트리거 삭제

DROP TRIGGER 트리거명;

 

간단 중간 정리

문장(statement) 트리거는 트리거가 설정된 테이블에 여러 행이 변경되더라도 오직 한번만 실행 되는 트리거.

행(row) 트리거는 조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러번 수행 하는 트리거.

 

예를들어,emp 테이블에 10명의 사원이있고, UPDATE emp SET 급여 = 급여 * 1.1; 이라는 문장이 실행되면 where조건이 없기때문에

10명의 급여가 모두 오르는데, 문장 트리거는 10명이 다 오르기전,오른 후 에 한번 딱 실행하는 트리거이고,

행(row) 트리거는 1명 오르기 전,후 에 실행하고 2번째 사원 오르기 전,후에 실행하고 총 10번을 실행하는 트리거.

따라서, 자신이 원하는 기능에 따라 원하는 트리거를 잘 선택해서 써야될듯.

(SQL GATE 로 가서 간단한 예제들을 살펴보고 다시 오자)

SELECT parameter, value FROM NLS_SESSION_PARAMETERS;
ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_CURRENCY = '\';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';

 

-- BEFORE 문장(STATEMENT) 트리거 간단한 예제 --
-- 예제 테이블--
SELECT * FROM ATFER_INSA;
 
 
-- 트리거없이 자료 수정 가능--
SELECT basicPay FROM atfer_insa WHERE EMPNO = 1111;
UPDATE atfer_insa SET basicPay = 1500 WHERE EMPNO = 1111;
COMMIT;
SELECT basicPay FROM atfer_insa WHERE EMPNO = 1111;
 
-- 원상복귀 --
UPDATE atfer_insa SET basicPay = 1000 WHERE EMPNO = 1111;
SELECT basicPay FROM atfer_insa WHERE EMPNO = 1111;
COMMIT;
 
 
-- 지정한 요일에 자료를 못건드리는 트리거(보안 기능)--
DROP TRIGGER before_statement_trigger; -- 이미 있을 경우 삭제하고 시작
 
CREATE OR REPLACE TRIGGER before_statement_trigger
BEFORE DELETE OR INSERT OR UPDATE ON atfer_insa
BEGIN
  IF TO_CHAR(SYSDATE, 'DAY') = '목요일'
  THEN
  raise_application_error(-20007, '목요일에는 자료수정이 불가능합니다. 불만사항접수 010-9500-9040'); --PL/SQL의 사용자 정의예외
  END IF;
END;
/
 
-- 트리거만들고 자료 수정 가능--
SELECT basicPay FROM atfer_insa WHERE EMPNO = 1111;
UPDATE atfer_insa SET basicPay = 1500000 WHERE EMPNO = 1111;
COMMIT;
SELECT basicPay FROM atfer_insa WHERE EMPNO = 1111;
 
 
 
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
 
-- AFTER 문장(STATEMENT) 트리거 간단한 예제 --
SELECT * FROM ExamData;
SELECT * FROM ExamMemo;
 
-- 참고))  ilja 칼럼은 DEFAULT 값으로 SYSDATE를 줘서 새로운 레코드가 들어오면 DEFAULT값으로 SYSDATE가 담긴다.
CREATE TABLE ExamMemo(
     memo VARCHAR2(20)
    ,ilja DATE DEFAULT SYSDATE
);
 
 
-- 트리거를 가진 테이블 자료를 입력,수정,삭제 한 후 로그(메모) 테이블에 입력,수정,삭제한 정보를 기록하게하는 트리거--
DROP TRIGGER after_statement_trigger; -- 이미 있을 경우 삭제하고 시작
 
CREATE OR REPLACE TRIGGER after_statement_trigger
   AFTER DELETE OR INSERT OR UPDATE ON ExamData
BEGIN
  -- 삽입할 때
   IF INSERTING THEN -- 이 트리거를 가진 ExamData에 insert 문장이 실행되면 밑에것(ExamMeno에 insert)도 실행
      INSERT INTO ExamMemo(memo) VALUES ('insert');
  -- 수정할 때
   ELSIF UPDATING THEN -- 이 트리거를 가진 ExamData에 update 문장이 실행되면 밑에것(ExamMeno에 update)도 실행
      INSERT INTO ExamMemo(memo) VALUES ('update');
  -- 삭제할 때
   ELSIF DELETING THEN -- 이 트리거를 가진 ExamData에 delete 문장이 실행되면 밑에것(ExamMeno에 delete)도 실행
      INSERT INTO ExamMemo(memo) VALUES ('delete');
   END IF;
END;
/
--INSERTING : 이 트리거를 가진 테이블의 문장이 INSERT일때 TRUE 그렇지 않으면 FALSE
--UPDATING  : 이 트리거를 가진 테이블의 문장이 UPDATE일때 TRUE 그렇지 않으면 FALSE
--DELETING  : 이 트리거를 가진 테이블의 문장이 DELETE일때 TRUE 그렇지 않으면 FALSE
 
-- 트리거 점검 --
INSERT INTO ExamData(id, name) VALUES (1, 'aaaa');
INSERT INTO ExamData(id, name) VALUES (2, 'bbbb');
COMMIT;
DELETE FROM ExamData  WHERE id = 1;
COMMIT;
SELECT * FROM ExamMemo;
 
 
 
 
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
 
 
-- 행(row) 트리거 예제는 흐름에 따라 before,after를 같이 한번 보자 --
 
SELECT * FROM 상품;
SELECT * FROM 입고;
SELECT * FROM 판매;
 
 
-- 입고테이블에 상품이 추가 되면 상품테이블에 재고수량이 자동으로 업데이트되는 트리거 (AFTER ROW(행) 트리거 )--
DROP TRIGGER insTrg_Ipgo;
 
CREATE OR REPLACE TRIGGER insTrg_Ipgo
AFTER INSERT ON 입고
FOR EACH ROW
BEGIN
     UPDATE 상품 SET 재고수량 = 재고수량 + :NEW.입고수량 WHERE 상품코드 = :NEW.상품코드;
END;
/
 
--  :OLD = 참조 전 열의 값 (INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)
--  :NEW = 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정할 자료)
 
 
 
-- 트리거 테스트 --
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) VALUES (1, 'AAAAAA', TO_DATE('2004-10-10','yyyy-mm-dd'), 5,   50000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) VALUES (2, 'BBBBBB', TO_DATE('2004-10-10','yyyy-mm-dd'), 15, 700000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) VALUES (3, 'AAAAAA', TO_DATE('2004-10-11','yyyy-mm-dd'), 15, 52000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) VALUES (4, 'CCCCCC', TO_DATE('2004-10-14','yyyy-mm-dd'), 15,  250000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) VALUES (5, 'BBBBBB', TO_DATE('2004-10-16','yyyy-mm-dd'), 25, 700000);
COMMIT;
 
SELECT * FROM 상품;
SELECT * FROM 입고;
 
 
-- 판매 테이블에 판매가 추가 되면 상품 테이블의 재고가 변경되는 트리거 (BEFORE ROW(행) 트리거)--
-- BEFORE 인 이유 : 판매가 입력(인설트) 되기 전에 상품테이블의 재고수량을 먼저 체크해야 되기 때문 --
DROP TRIGGER insTrg_Pan;
 
CREATE OR REPLACE TRIGGER insTrg_Pan
BEFORE INSERT ON 판매
FOR EACH ROW
DECLARE
    j_qty NUMBER;
BEGIN
    SELECT 재고수량 INTO j_qty FROM 상품 WHERE 상품코드 = :NEW.상품코드;
        IF :NEW.판매수량 > j_qty
        THEN
                raise_application_error(-20007, '판매 오류 : 재고가 부족합니다');
        ELSE
            UPDATE 상품 SET 재고수량 = 재고수량 - :NEW.판매수량  WHERE 상품코드 = :NEW.상품코드;
        END IF;
END;
/
 
-- 트리거 확인 --
INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES (1, 'AAAAAA',TO_DATE('2004-11-10','yyyy-mm-dd'), 5, 1000000);
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;
 
INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES (2, 'AAAAAA',TO_DATE('2004-11-10','yyyy-mm-dd'), 50, 1000000);
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;
 
 
 
--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
 
-- 실제 프로젝트에 적용--
-- 발령상세데이터 입력시 발령테이블에 있는 발령여부를 Y로 자동으로 변경해주는 트리거--
CREATE OR REPLACE TRIGGER TRG_HA_APPMT_UPDATE
AFTER INSERT ON HA_APPMT_DETAIL
FOR EACH ROW
BEGIN
     UPDATE HA_APPMT SET APPMTYN = 'Y' WHERE EMP_NO = :NEW.EMP_NO;
END;
/
 
--발령 입력,수정,삭제 할때 입력,수정,삭제기록을 남겨주는 트리거 --
CREATE OR REPLACE TRIGGER TRG_HA_APPMT_LOG
   AFTER DELETE OR INSERT OR UPDATE ON HA_APPMT
BEGIN
  -- 삽입할 때
   IF INSERTING THEN -- 이 트리거를 가진 ExamData에 insert 문장이 실행되면 밑에것(ExamMeno에 insert)도 실행
      INSERT INTO HA_APPMT_LOG(LOGDATA) VALUES ('insert');
  -- 수정할 때
   ELSIF UPDATING THEN -- 이 트리거를 가진 ExamData에 update 문장이 실행되면 밑에것(ExamMeno에 update)도 실행
      INSERT INTO HA_APPMT_LOG(LOGDATA) VALUES ('update');
  -- 삭제할 때
   ELSIF DELETING THEN -- 이 트리거를 가진 ExamData에 delete 문장이 실행되면 밑에것(ExamMeno에 delete)도 실행
      INSERT INTO HA_APPMT_LOG(LOGDATA) VALUES ('delete');
   END IF;
END;
/

 

트리거 장단점

장점

1. 데이터 무결성 강화 (참조 무결성)

: 물류를 예로 들면 견적등록하고,수주까지 등록하고 난뒤 견적테이블의 수주여부(Y/N) 칼럼을 변경해야 될 경우, 견적테이블의 수주여부를 변경해주는 쿼리를 다시 요청하는것보다, 수주를 INSERT할 떄 트리거를 이용하여 바로 견적을 수정하는게 더 빠르고 안전하다고 한다.

2. 업무 규칙의 설정

: 트리거에서 사용자 정의예외를 직접 설정할 수 있으므로 복잡한 업무 규칙 제약조건을 좀 더 편하고 효율적으로 표현 할 수 있다고 한다. 

3. 감사기능의 확장

: 상세한 변경 내역을 트리거를 이용해서 쉽고 편하게 관리 할 수 있다(다만,이경우는 큰 프로젝트의 경우 오라클 성능에 부담을 많이 준다)

단점

유지보수의 어려움

:트리거는 고질적인 유지 보수 문제를 야기한다. 트리거는 직접 실행되지 않는 작은 코드다. 그저 다른 작업의 부산물로“발생”할뿐이다. 작업이 부산물로 이루어지기 때문에 사람들은 트리거가있다는 사실을 종종 잊곤 한다(그리고 모든 부산물에 대한 코드 검토가 불가능한 것은 아니지만 어렵다).

또 담당자가 바뀌거나 업무를 인수인계할때 제대로 전달이 안되면 트리거 오류를 찾아내기도 어렵다.(트리거 유무자체도 모르고 있었다면 다른곳에서 계속 오류를 찾겠지) 그리고 덕지덕지 붙어있는 트리거들은 업무를 분석하는대도 더 복잡하고 어렵게 만든다.

 

만약 완벽한 인수인계와 적절한 문서화작업으로 프로젝트의 모든 트리거를 다알아서 유지보수문제를 어느정도 해결한다고 해도 트리거 자체가 문제를 발생시킬 수 있다.개발자가 트리거를 생성할때는 생각하지못한 오류가 추후에 생기면 그 트리거 오류들을 또 해결해야하므로 2차적인 오류가 생긴다. 

결론

따라서, 트리거를 사용해서 얻을 수 있는 장점(좀더 빠르고 안전)보다 단점(유지보수의어려움,예상치못한오류들이생길가능성)이 더 타격이 크므로 현업에서는 트리거를 많이 쓰지는 않는다고 한다고 한다고 한다.