한빛출판네트워크

저자: Peter Gulutzan / 한동훈 역
원문: http://www.onlamp.com/pub/a/onlamp/2005/02/03/triggers.html

시험중인 새로운 기능을 이용할 수 있는 MySQL의 알파버전, MySQL 5.0은 트리거(Trigger)를 지원한다. MySQL 개발 로드맵에 약속된 기능이므로 놀라운 것은 아니지만, "MySQL은 할 수 없는 것들의 목록" 중에서 자주 언급되던 것을 사용하고, 동작하는 것을 지켜보는 것은 놀라운 경험이다.

이번 테스트를 위해 MySQL 레퍼런스 매뉴얼에서 개발 소스 트리에서 설치하기 절의 설명대로 가장 최신의 MySQL 5.0 소스를 다운로드하였다. 소스 트리에서 다운 받는 것은 MySQL 5.0 다운로드 페이지에서 받을 수 있는 것보다 최신판이며, 당연히 덜 테스트된 버전을 의미한다.

트리거 시험해보기

리눅스 쉘에서 mysql 클라이언트 프로그램을 실행했으며, 버전 5를 설치한 것이 맞는지 확인하기 위해 다음과 같은 코드를 작성하였다.

mysql> SELECT version();
+-------------------+
| version()         |
+-------------------+
| 5.0.2-alpha-debug |
+-------------------+
1 row in set (0.00 sec)

버전을 확인한 후에 테스트 데이터베이스에 테이블과 트리거를 차례대로 생성하고, 트리거를 테스트하기 위해 INSERT 문을 실행해보았다.

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.27 sec)
mysql> USE test_db;
Database changed
mysql> CREATE TABLE t (column1 TINYINT);
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TRIGGER t_bi              /* line 1 */
    -> BEFORE INSERT ON t               /* line 2 */
    -> FOR EACH ROW                     /* line 3 */
    -> SET @x = @x + 1;                 /* line 4 */
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x = 0;                      /* line 5 */
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (1),(NULL); /* line 6 */
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT @x;                       /* line 7 */
+------+
| @x   |
+------+
| 2    |
+------+
1 row in set (0.01 sec)

위 예제에서 알 수 있는 것처럼 MySQL에서 트리거는 제대로 동작한다. 동작을 설명하기 위해 CREATE TRIGGER 문을 한번에 한줄씩 살펴보자.


트리거 이해하기

CREATE TRIGGER trigger_name            /* line 1 */

당연히 첫번째는 CREATE TRIGGER 문과 트리거의 이름이다. 나는 테이블 이름 다음에 언더스코어(_), 그 다음에 bi, ai, bu, au, bd, ad와 같은 코드를 사용하는 방식으로 트리거 이름을 지정한다. 6개의 코드는 다음과 같은 의미를 지닌다.

BEFORE INSERT ON table_name            /* line 2 */
or AFTER INSERT ON table_name
or BEFORE UPDATE ON table_name
or AFTER UPDATE ON table_name
or BEFORE DELETE ON table_name
or AFTER DELETE ON table_name

트리거가 동작할 수 있는 부분은 6곳이 있다. 트리거는 항상 테이블 하나에서 데이터 변경문이 발생할 때 동작하게 된다. 내가 작성한 BEFORE INSERT ON t 트리거는 테이블 t에 INSERT가 실행되기 전에 수행된다.

FOR EACH ROW                           /* line 3 */

위 문장은 내가 INSERT 문을 사용하는 각 열에 대해 동작하는 것을 지정한다. INSERT … SELECT 문을 사용하는 경우에 0개의 행이 삽입되는 경우가 발생한다면 트리거는 0번 동작하게 된다. 마찬가지로, 1,000개의 열을 삽입(INSERT)하게 되면 트리거도 1,000번 발생하게 된다. 표준 SQL 에서는 삽입되는 열의 수에 상관없이 트리거를 한번만 동작하게 하는 FOR EACH STATEMENT 문을 정의한다.

SET @x = @x + 1;                       /* line 4 */

마지막으로 살펴볼 것은 트리거의 "몸체"부분이다. 트리거가 동작하게 될 때 트리거 몸체 부분에 있는 문장이 실행된다. 예제의 트리거에서는 SET @x = @x + 1 문장이 실행된다. 이 코드는 트리거가 실행될 때마다 @x 변수를 증가시킨다.

다시 말해서, @x는 카운터다. 각 열에 INSERT가 발생할 때 마다 @x는 증가한다. 물론, @x의 기본값은 NULL이기 때문에 코드가 동작하기 않기 때문에 카운터를 초기화했다.

SET @x = 0;                            /* line 5 */

INSERT를 실행해서 트리거를 테스트할 차례다.

INSERT INTO t VALUES (1),(NULL);       /* line 6 */

테이블 t에 ROW EACH ROW 트리거를 두었기 때문에 열을 삽입할 때마다 @x의 값이 증가해야한다. 따라서 다음과 같은 SELECT 문을 실행하였을 때,

SELECT @x;                              /* line 7 */

열을 두 개 추가했으므로 결과도 2가 되어야 한다.


트리거 길들이기

앞으로 작성할 UPDATE 트리거는 ;를 포함한 여러 개의 문장으로 되어 있기 때문에 UPDATE 트리거를 작성하기 전에 mysql 클라이언트에서 문장의 끝을 나타내는 구분자를 //로 변경할 것이다.

mysql> DELIMITER //

이제 UPDATE 트리거를 작성할 차례다.

mysql> CREATE TRIGGER t_bu
    -> BEFORE UPDATE ON t
    -> FOR EACH ROW
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR 1264 SET new.column1 = -1;
    ->   SET new.column1 = new.column1 * 2;
    ->   END;//
Query OK, 0 rows affected (0.00 sec)

트리거 t_bu는 여러 개의 SQL 문장(compound statement)으로 되어 있으며, 각 열을 업데이트할 때마다 수행된다. 저장 프로시저에서 사용하는 복합문(compound statement)에 대한 구문은 MySQL 레퍼런스 매뉴얼을 참고하기 바란다. 위 트리거의 의미를 문장으로 옮기면 다음과 같다:

column1의 값에 2를 곱하고, 범위를 벗어남(out-of-range) 오류가 발생하면 column1의 값을 -1로 설정하라.

트리거의 몸체에는 MySQL 문장 및 함수들까지 모두 사용할 수 있으며, 뿐만 아니라, 해당 열의 이전 값을 "old"로, 새로운 값은 "new"로 참조할 수 있다.

에러가 발생할 때는 무엇을 할 수 있는가?

어떤 경우 에러가 발생할 수 있는가? 예를 들어, column1의 데이터 타입은 TINYINT이며, 이것의 최대값은 127이다. MySQL 5.0에서는 정상적인 동작환경에서는 타입에 대해 적절한 값이 들어가는지를 검사한다. 다음은 UPDATE 문은 "값의 범위를 벗어남(out of range value)" 오류를 고의로 만든다.

mysql> UPDATE t SET column1 = column1 + 100;//
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 1

테이블 t의 첫번째 열에 대해서 column1의 값이 1이고, 여기에 100을 더해서 101로 만들면 트리거가 이 값을 2배, 즉 202로 만든다. TINYINT 타입의 최대값은 127 이므로 예외 처리기가 동작하게 되며, column1의 값을 -1로 설정한다. 두번째 열에 있는 column1의 값은 NULL이므로 아무일도 발생하지 않는다. 이와 같은 동작이 테이블 t에 일어난 것을 증명하기 위해 테이블 t를 살펴보자.

mysql> SELECT * FROM t//
+---------+
| column1 |
+---------+
|      -1 |
|    NULL |
+---------+
2 rows in set (0.00 sec)


결론 및 주의할 점

INSERT와 UPDATE 문 양쪽에 대해 트리거가 동작하며, 트리거 몸체에는 복잡한 SQL 문장을 작성할 수 있다. BEFORE 트리거는 값을 삽입하거나 업데이트할 때 값을 읽고 변경할 수 있다. 이런 지원은 멋진 소식이다. 따라서, 여기서는 MySQL의 새로운 기능에 대해 주의할 점으로 끝을 맺겠다.

주의: MySQL 알파 버전은 불안정하다. MySQL 버그 데이터베이스(http://bugs.mysql.com/)에서 trigger* 키워드로 검색하고, 알려진 버그에 대해 주의해야 한다.

주의: MySQL 함수는 매우 엄격한 제약을 갖고 있다. 예를 들어, MySQL 함수는 테이블에서 SELECT를 수행할 수 없다. 트리거 동작은 함수 호출과 같이 동작하기 때문에 MySQL 함수와 같은 제약을 갖는다.

---

Peter Gulutzan은 수석 소프트웨어 아키텍트로 MySQL AB를 위해 일하고 있으며, 알버타(Alberta)주 에드몬튼(Edmonton)에 살고 있다.