DB Trigger (트리거) 사용법

 

DB의 trigger 사용법에 대해 알아봅시다. 

1. Trigger란?

trigger는 ‘방아쇠’라는 뜻으로, ‘방아쇠’를 당기면 자동으로 총알이 나오는 것 처럼 trigger를 하면 추가적인 작업이 자동으로 수행되어지는 작업을 의미합니다.

이를 테이블에 적용해보면,

trigger는 update/insert/delete 발생했을 때(→방아쇠를 당김)

추가 쿼리 작업을 자동으로 수행할 수 있게 해주는 역할을 합니다(→ 총알이 나옴).

 

 

[ Trigger 특징 ]

• 트리거는 자신 스스로 실행이 불가하고, 테이블에 설정한 이벤트(Update, insert, delete)가 발생해야 작동합니다.
• 테이블에 부착되는 프로그램 코드입니다.
• MySql은 view에 트리거 부착 불가합니다.
• 트리거는 수정이 불가능하며, 수정을 하고 싶을 때는 삭제 후 다시 생성해야 합니다.
• 테이블에 설정한 이벤트와 트리거는 하나의 트랜잭션으로 인식하기 때문에, 트리거에서 ROLLBACK 시, 이벤트도 ROLLBACK이 됩니다.

 

2. Trigger 생성하기

[trigger 생성 템플릿]

DELIMITER $$

CREATE TRIGGER trigger_name // trigger 이름 지정
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name // trigger 실행 시점과 이벤트 유형 
FOR EACH ROW // 행 트리거 사용 시 
BEGIN
    -- 트리거의 동작을 여기에 작성
END $$

DELIMITER ;

트리거를 사용하기 위한 기본 템플릿 입니다.

아래에는 트리거 템플릿에 대한 설명이 있습니다.

 

1) 트리거 종류

트리거 종류는 행 트리거와 문장 트리거가 있습니다.

 

a. 행 트리거 : 테이블 안의 각각 모든 행에 대해 실행합니다.

• 행 트리거를 사용하려면 위의 템플릿에서 “FOR EACH ROW” 추가하면 됩니다.
• 행 트리거 사용 시 가상의 데이터(OLD, NEW)에 접근할 수 있는데, 이벤트 유형에 따라, OLD와 NEW에 대해 접근 가능/불가능할 수 있다.
• OLD : 이벤트에 의해 트리거 발생 시, 이벤트 전 변경 전 DATA 상태
• NEW : 이벤트에 의해 트리거 발생 시, 이벤트 후 변경된 DATA 상태
• OLD, NEW는 이벤트 발생 시점에 TRIGGER 사용 시, 임시 테이블에 저장해 놓기 때문에 사용이 가능하다.

	INSERT	UPDATE	DELETE
OLD	X	O	O
NEW	O	O	X

INSERT는 생성하는 것이므로 INSERT 전의 DATA는 없으므로 OLD는 사용 못합니다.
UPDATE는 수정하는 것이므로 UPDATE 전, 후의 DATA 둘 다 존재하므로 NEW, OLD 사용 가능합니다.
DELETE는 삭제하는 것이므로 DELETE 후의 DATA는 없으므로 NEW는 사용 못합니다.

 

 

b. 문장 트리거 : 트리거에 대해 1번만 실행

• 문장 트리거로 실행하려면 위의 템플릿에서 “FOR EACH ROW”를 지우면 됩니다.

 

2 ) 실행시점 {BEFORE | AFTER}

트리거의 실행 시점을 지정하는 기능입니다.

• BEFORE를 지정하면, 이벤트(insert, update, delete) 전에 실행
• AFTER를 지정하면, 이벤트(insert, update, delete) 후에 실행

 

3) 이벤트 유형 { INSERT | UPDATE | DELETE }

테이블에서 발생하는 이벤트 유형을 지정합니다.

ex) BEFORE INSERT : INSERT 이벤트 전에 trigger 실행

ex) AFTER UPDATE : UPDATE 이벤트 후 trigger 실행

 

4) 트리거 작업

BEGIN~END 사이에 트리거가 발생 시, 작업하고 싶은 내용을 추가하면 됩니다.

트리거 작업 시, 변수 선언, 대입, NEW, OLD 사용, 조건문 사용 등이 가능합니다.

 

a. 변수 선언

BEGIN
	...
	DECLARE {변수명} {TYPE};
	
	// example
	DECLARE int_name INT;
	DECLARE varchar_name VARCHAR(100);
	...
END;

DECLARE {변수명} {타입}; 형태로 작성하면 됩니다.

 

b. 변수 대입

BEGIN
	...
	SET {변수명} = 변수_value
	
	// example
	SET int_name = 1
	SET varchar_name = 'hello'
	...
END;

SET  명령어를 통해 변수에 VALUE 대입이 가능합니다.

 

만약 테이블에 있는 값에 대해 변수 대입을 하고 싶다면,  SELECT 명령어를 통해 아래 코드를 참조해서 대입하면 됩니다.

BEGIN
	...
	SELECT {필드} INTO {변수명} FROM {테이블} WHERE {조건}
	...
END;

 

 

c. NEW, OLD 사용 + 조건문 사용

DELIMITER $$

CREATE TRIGGER trigger_func_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
	-- PUBLIC_YN 값이 'Y'에서 'N'으로 변경될 때 작업 실행
    IF (OLD.PUBLIC_YN = 'Y' AND NEW.PUBLIC_YN = 'N') THEN
        -- 작업
    END IF;
END $$;

DELIMITER ;

OLD, NEW 는 행트리거일 때(FOR EACH ROW 코드 사용)만 사용 가능합니다.

 

 

3. Trigger 조회(확인)

trigger 조회 코드는 간단합니다. 

 show triggers;

 

 

4. Trigger 수정, 삭제

trigger는 한번 만들어지면 수정이 불가능합니다!

그래서, 수정하고 싶으면 기존에 있는 트리거를 삭제 후 새로 등록해야 합니다. 

DROP TRIGGER IF EXISTS {trigger_name};

 

 

 

 

trigger는 실제 코드에서 작동하는 것이 아니라, db에서 이벤트가 발생 후 작동하는 것이기 때문에

문서화 및 공유가 제대로 안되어있을 경우 예상치 못한 결과를 초래하기도 합니다. 

또한 트리거가 복잡할 경우, 추후 유지보수에도 악영향을 끼치기 때문에 

트리거는 주의를 해서 사용하셔야 합니다.