MySQL #3_ TRIGGER(트리거) 및 DELIMITER(델리미터) 개념과 사용법

안녕하세요.

이번 포스팅에서는 MySQL의 명령어가 아닌 개념적인 내용과 해당 개념의 사용법을 알아보려합니다.

오늘 소개해드릴 MySQL의 개념은 Trigger(트리거)와 Delimiter(델리미터) 입니다.

예제가 함께 포함되어 있으니 따라하면서 하시면 이해하시는데 보다 수월하길 것 입니다.

---

  1. TRIGGER(트리거)

MySQL을 공부하다 보면 트리거(trigger)라는 단어를 볼 수 있습니다.

트리거가 무엇일까요?

위키백과에서의 정의를 보면 이와 같습니다.

 

데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다. 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다. (출처: 위키백과)

 

말 그대로 트리거란, 테이블에 대한 이벤트가 발생했을 때 자동으로 실행되는 작업을 말합니다.

이때 이벤트가 발생하는 경우는 크게 DML 실행, DDL 실행, 데이터베이스 동작 실행(logon, logoff, startup, shutdown, servererror 등) 로 나눌 수 있습니다.

 

트리거는 문장 트리거, 행 트리거, 2종류로 나누어집니다.

행 트리거는 각 행에 대해서 트리거가 발생하는 것이고 문장 트리거는 전체 트랜젝션 작업에 대해 1번 발생되는 트리거 입니다.

다시 말해, 문장 트리거는 트리거가 설정된 테이블에 트리거 이벤트가 발생했을 때 많은 행에 대해 변경작업이 발생하더라도 단 한 번만 트리거가 발생하는 것입니다.

행 트리거는 조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법입니다.

 

또한 트리거는 다음과 같은 속성을 갖습니다.

BEFORE or AFTER : 트리거가 실행되는 시기를 지정합니다.

INSTEAD OF : 트리거를 원래 문장 대신 수행합니다.

WHEN : 트리거를 시작하는 조건식을 지정합니다.

 

MySQL에서는 버전 5.0.2에서 표준 트리거가 지원되었습니다.

INSERT, UPDATE, DELETE 동작에 대한 트리거가 지원되는데 이때 주의하실 점은 MySQL은 각 테이블에 각 형태의 단 하나의 트리거만 허용합니다.

즉, INSERT BEFORE 하나, INSERT AFTER 하나, … 이런 식으로 허용합니다.

 

그렇다면 트리거는 어떻게 생성할까요?

두 개의 테이블을 이용하여 트리거를 확인해보겠습니다.

먼저 각각의 테이블은 아래와 같습니다.

 

그리고 생성한 트리거의 코드는 아래와 같습니다.

DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes') WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;

 

이와 같은 트리거를 작성하고

 

INSERT INTO test (name,age,adult) VALUES (‘트리거’,1,’No’);

 

쿼리를 날린 후 다시 테이블을 확인하면 아래와 같습니다.

 

 

adultNum 테이블의 변화가 보이시나요?

트리거를 작성하고 test 테이블에 INSERT를 했을 뿐인데 adultNum 테이블의 값이 변화하였습니다.

트리거는 이처럼 특정 table에 이벤트가 발생했을 때 작동하는 코드를 말합니다.

그럼 다시 트리거 코드를 통해 자세히 살펴보겠습니다.

DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes') WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;

 

1: DELIMITER는 뒤에서 다시 이야기 하겠습니다.

2: CREATE TRIGGER (트리거이름)

   (트리거이름)이라는 이름을 가진 트리거를 만듭니다.

3: AFTER INSERT ON (테이블이름)

   어떤 테이블에 언제 영향을 미칠지를 정합니다.

저는 test라는 테이블에 INSERT가 이루어진 AFTER(다음)에 트리거가 실행되도록 하였습니다.

4: FOR EACH ROW

   해당 구문을 통해 ‘행 트리거’임을 말합니다.

5: BEGIN

   트리거 구문의 시작을 말합니다.

6~8: 트리거의 내용입니다.

9: Trigger 생성을 끝냅니다.

10: DELIMITER를 다시 수정합니다. (뒤에서 자세히 설명드리겠습니다.)

 

트리거의 코드에 대해서 어느정도 이해가 가시나요?

 

트리거는 많은 테이블을 관리할 때 상당히 유용하게 쓰일 수 있습니다.

그만큼 활용방안도 많지만, 주의할 점도 많습니다.

몇 가지 주의사항으로는

A에 대한(A테이블의 변화에 대한)특정 트리거에서 A라는 테이블의 값에 대한 INSERT / UPDATE / DELETE 쿼리가 존재한다면 A라는 테이블에 INSERT / UPDATE / DELETE 의 쿼리입력은 불가합니다.

이때 만약 A라는 테이블에 대해 INSERT / UPDATE / DELETE 쿼리 수행이 필요한데 트리거를 걸고 싶다면, B라는 테이블을 해당 트리거에서 A라는 테이블에 대한 변화에 따라 A테이블의 모든 값을 복사하여 B라는 테이블에 입력하도록 하는 쿼리를 추가하면 될 것 입니다.

 

또한 트리거 동작은 함수 호출과 같이 동작하기 때문에 MySQL 함수와 같은 제약을 받으며 트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT 등)을 사용할 수 없습니다.

 

추가적으로, 생성된 트리거를 확인하기 위한 쿼리는,

SHOW TRIGGERS;

이며 특정 트리거를 제거할 때는

DROP TRIGGERS (트리거 이름);

이라는 쿼리를 입력하면 됩니다.

 

이처럼 트리거에 대한 내용은 더 많으니 직접 쿼리를 통해 실행해보고 오류가 발생시에 해당 오류에 대한 답을 스스로 찾아보는 것이 중요합니다.

 

 

  2. DELIMITER(델리미터)

위의 트리거 내용에서 DELIMITER 라는 쿼리가 등장하였습니다.

해당 쿼리는 아래와 같이 쓰입니다.

DELIMITER $$ 또는 DELIMITER ; 등 DELIMITER (아무거나!)

DELIMITER가 무엇일까?

DELIMITER에 대해 간단히 말하면, 구문의 끝을 설정해주는 녀석이라고 생각하면 됩니다.

보통 procedure, trigger를 신규로 생성하거나 수행될 때 delimiter를 사용합니다.

이때 trigger(트리거)라는 것은 위에서 말한바와 같이 특정 동작이 이루어질 때 자동으로 실행되는 함수 같은 친구를 말합니다.

DELIMITER를 확인하기 위해 위에서 쓰인 코드를 다시 확인해보겠습니다.

 

DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes') WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;

 

 

1번 라인을 보시면 DELIMITER 가 사용되었습니다.

1번 라인에서 DELIMITER $$ 으로 쿼리를 입력하였으므로 이제 procedure 및 trigger의 종료시점은 $$로 설정되었습니다. 따라서 트리거의 내용인 BEGIN~END 까지 쿼리내에 ; 를 입력하여도 쿼리가 끝나지 않습니다. 그리고 마지막에서 $$ 를 입력함으로써 쿼리가 끝남을 알려줍니다.

그럼 다시 해야 될 일은 무엇일까요?

네, 바로 10번 라인의 DELIMITER ; 입니다.

우리는 트리거를 작성하기 편하도록 컴퓨터에게 잠시동안 ‘쿼리의 끝은 $$ 야!’ 라고 알려준 것입니다.

이제 트리거 작성이 끝났으니, 트리거가 실행될 때 입력한 쿼리가 잘 작동할 수 있도록 다시 컴퓨터에게 ‘쿼리의 끝은 ; 야!’ 라고 말해줘야겠죠?

DELIMITER ; 를 하지 않는다면 우리가 해당 트리거를 실행시켜도 트리거 내부에 있는 쿼리는 입력만 될 뿐, 끝나지 않아서 제대로 실행되지 않을 것 입니다.

DELIMITER 뒤의 $$ 는 자신이 원하는 문자로 수정이 가능하오니 자신이 편한 문자로 사용하시면 됩니다.

---

이렇게 해서 이번에는 트리거(trigger)와 델리미터(delimiter)에 대해서 알아보았습니다.

내용에 대한 피드백이나 문의사항은 언제든지 댓글 및 이메일(doorbw@outlook.com)으로 말씀해주세요 :)