27.4.1 Trigger Syntax and Examples

27.4.1 Trigger Syntax and Examples

trigger를 생성하거나 삭제하려면, Section 15.1.26, “CREATE TRIGGER Statement”와 Section 15.1.39, “DROP TRIGGER Statement”에 설명된 CREATE TRIGGER 또는 DROP TRIGGER statement를 사용합니다.

다음은 table에 trigger를 연결하여 INSERT 연산에 대해 활성화되도록 하는 간단한 예제입니다. trigger는 누산기(accumulator)로 동작하여, table의 한 column에 insert된 값들을 합산합니다.

1mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
2Query OK, 0 rows affected (0.03 sec)
3
4mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
5       FOR EACH ROW SET @sum = @sum + NEW.amount;
6Query OK, 0 rows affected (0.01 sec)

CREATE TRIGGER statement는 ins_sum이라는 이름의 trigger를 생성하고, 이를 account table에 연결합니다. 또한 trigger action time, triggering event, trigger가 활성화될 때 수행할 작업을 지정하는 절들을 포함합니다:

• BEFORE keyword는 trigger action time을 나타냅니다. 이 경우, trigger는 table에 각 row가 insert되기 이전에 활성화됩니다. 여기에서 허용되는 다른 keyword는 AFTER입니다.

• INSERT keyword는 trigger event를 나타냅니다. 즉, trigger를 활성화하는 연산의 유형입니다. 이 예제에서는 INSERT 연산이 trigger를 활성화합니다. 또한 DELETE 및 UPDATE 연산에 대해서도 trigger를 생성할 수 있습니다.

• FOR EACH ROW 다음의 statement는 trigger body를 정의합니다. 즉, trigger가 활성화될 때마다 실행할 statement이며, triggering event에 의해 영향을 받는 각 row마다 한 번씩 실행됩니다. 이 예제에서 trigger body는 단순한 SET으로, 사용자 변수에 amount column에 insert되는 값들을 누산합니다. statement는 column을 NEW.amount로 참조하는데, 이는 “새 row에 insert될 amount column의 값”을 의미합니다.

trigger를 사용하려면 누산기 변수를 0으로 설정하고 INSERT statement를 실행한 다음, 그 이후에 변수가 어떤 값을 가지는지 확인합니다:

1mysql> SET @sum = 0;
2mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
3mysql> SELECT @sum AS 'Total amount inserted';
4+-----------------------+
5| Total amount inserted |
6+-----------------------+
7|               1852.48 |
8+-----------------------+

이 경우, INSERT statement가 실행된 후 @sum의 값은 14.98 + 1937.50 - 100, 즉 1852.48이 됩니다.

trigger를 삭제하려면 DROP TRIGGER statement를 사용합니다. trigger가 기본 스키마에 있지 않다면 스키마 이름을 지정해야 합니다:

1mysql> DROP TRIGGER test.ins_sum;

table을 drop하면, 해당 table에 대한 모든 trigger도 함께 drop됩니다.

trigger 이름은 스키마 네임스페이스에 존재하므로, 하나의 스키마 내에서는 모든 trigger가 고유한 이름을 가져야 합니다. 서로 다른 스키마에 있는 trigger는 같은 이름을 가질 수 있습니다.

하나의 table에 대해 동일한 trigger event와 action time을 갖는 여러 개의 trigger를 정의하는 것도 가능합니다. 예를 들어, table에 대해 두 개의 BEFORE UPDATE trigger를 가질 수 있습니다. 기본적으로, 동일한 trigger event와 action time을 갖는 trigger는 생성된 순서대로 활성화됩니다.

trigger 순서에 영향을 주려면 FOR EACH ROW 뒤에 FOLLOWS 또는 PRECEDES와, 동일한 trigger event와 action time을 갖는 기존 trigger의 이름을 지정하는 절을 사용합니다. FOLLOWS를 사용하면 새 trigger는 기존 trigger 이후에 활성화됩니다. PRECEDES를 사용하면 새 trigger는 기존 trigger 이전에 활성화됩니다.

예를 들어, 다음 trigger 정의는 account table에 대해 또 다른 BEFORE INSERT trigger를 정의합니다:

1mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
2       FOR EACH ROW PRECEDES ins_sum
3       SET
4       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
5       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
6Query OK, 0 rows affected (0.01 sec)

이 trigger ins_transaction은 ins_sum과 유사하지만, 입금액과 인출액을 별도로 누산합니다. 이 trigger에는 PRECEDES 절이 있어 ins_sum보다 먼저 활성화되도록 합니다. 이 절이 없다면, ins_sum 이후에 생성되었기 때문에 ins_sum 이후에 활성화되었을 것입니다.

trigger body 내에서 OLD와 NEW keyword를 사용하면, trigger에 의해 영향을 받는 row의 column에 접근할 수 있습니다. OLD와 NEW는 trigger에 대한 MySQL 확장이며, 대소문자를 구분하지 않습니다.

INSERT trigger에서는 NEW.col_name만 사용할 수 있습니다. 기존 row가 없기 때문입니다. DELETE trigger에서는 OLD.col_name만 사용할 수 있습니다. 새 row가 없기 때문입니다. UPDATE trigger에서는 OLD.col_name을 사용하여 update되기 전 row의 column을 참조할 수 있고, NEW.col_name을 사용하여 update된 후 row의 column을 참조할 수 있습니다.

OLD로 명명된 column은 읽기 전용입니다. 해당 column에 대한 SELECT 권한이 있다면 이를 참조할 수는 있지만, 수정할 수는 없습니다. NEW로 명명된 column은 SELECT 권한이 있다면 참조할 수 있습니다. BEFORE trigger에서는 UPDATE 권한이 있다면 SET NEW.col_name = value를 사용하여 그 값을 변경할 수도 있습니다.

이는 trigger를 사용하여 새 row에 insert될 값이나 row를 update하는 데 사용될 값을 수정할 수 있음을 의미합니다. (이러한 SET statement는 row 변경이 이미 발생한 후인 AFTER trigger에서는 아무 효과가 없습니다.)

BEFORE trigger에서 AUTO_INCREMENT column에 대한 NEW 값은 실제로 새 row가 insert될 때 자동으로 생성되는 시퀀스 번호가 아니라 0입니다.

BEGIN ... END 구문을 사용하면, 여러 개의 statement를 실행하는 trigger를 정의할 수 있습니다. BEGIN block 내부에서는 조건문과 반복문 같은, 저장 루틴 내에서 허용되는 다른 구문도 사용할 수 있습니다.

그러나 저장 루틴과 마찬가지로, 여러 statement를 실행하는 trigger를 정의하기 위해 mysql 프로그램을 사용할 경우, trigger 정의 내에서 ; statement 구분자를 사용할 수 있도록 mysql statement 구분자를 재정의해야 합니다.

다음 예제는 이러한 점을 보여 줍니다. 이 예제는 각 row를 update하는 데 사용될 새 값을 검사하고, 그 값을 0에서 100 사이의 범위 내로 조정하는 UPDATE trigger를 정의합니다. 이 값은 row를 update하는 데 사용되기 전에 검사되어야 하므로, 반드시 BEFORE trigger여야 합니다:

1mysql> delimiter //
2mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
3       FOR EACH ROW
4       BEGIN
5           IF NEW.amount < 0 THEN
6               SET NEW.amount = 0;
7           ELSEIF NEW.amount > 100 THEN
8               SET NEW.amount = 100;
9           END IF;
10       END;//
11mysql> delimiter ;

저장 프로시저를 별도로 정의한 다음 간단한 CALL statement를 사용하여 trigger에서 이를 호출하는 편이 더 쉬울 수 있습니다. 이는 여러 trigger 내에서 동일한 코드를 실행하고자 할 때도 유리합니다.

trigger가 활성화될 때 실행되는 statement에 포함될 수 있는 내용에는 제한이 있습니다:

• trigger는 client에 데이터를 반환하거나 동적 SQL을 사용하는 저장 프로시저를 호출하기 위해 CALL statement를 사용할 수 없습니다. (저장 프로시저는 OUT 또는 INOUT 매개변수를 통해 trigger에 데이터를 반환하는 것은 허용됩니다.)

• trigger는 명시적 또는 암시적으로 트랜잭션을 시작하거나 종료하는 statement, 예를 들어 START TRANSACTION, COMMIT, ROLLBACK 등을 사용할 수 없습니다. (ROLLBACK to SAVEPOINT는 트랜잭션을 종료하지 않으므로 허용됩니다.)

또한 Section 27.10, “Restrictions on Stored Programs”를 참조하십시오.

MySQL은 trigger 실행 중 오류를 다음과 같이 처리합니다:

• BEFORE trigger가 실패하면, 해당 row에 대한 연산은 수행되지 않습니다.

• BEFORE trigger는 row를 insert하거나 수정하려는 시도에 의해 활성화되며, 그 시도가 이후에 성공하는지 여부와는 관계가 없습니다.

• AFTER trigger는 모든 BEFORE trigger와 row 연산이 성공적으로 실행된 경우에만 실행됩니다.

• BEFORE 또는 AFTER trigger에서의 오류는 trigger 호출을 유발한 전체 statement의 실패로 이어집니다.

• 트랜잭션 가능한 table의 경우, statement의 실패는 해당 statement에 의해 수행된 모든 변경 사항의 롤백을 초래해야 합니다. trigger의 실패는 statement의 실패를 초래하므로, trigger 실패 역시 롤백을 초래합니다. 비트랜잭션 table의 경우에는 이러한 롤백을 수행할 수 없으므로, statement는 실패하지만, 오류 발생 이전에 수행된 변경 사항은 그대로 유지됩니다.

trigger에는 다음 예제에 나와 있는 testref라는 trigger처럼, table 이름에 대한 직접 참조를 포함할 수 있습니다:

1CREATE TABLE test1(a1 INT);
2CREATE TABLE test2(a2 INT);
3CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
4CREATE TABLE test4(
5  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
6  b4 INT DEFAULT 0
7);
8
9delimiter |
10
11CREATE TRIGGER testref BEFORE INSERT ON test1
12  FOR EACH ROW
13  BEGIN
14    INSERT INTO test2 SET a2 = NEW.a1;
15    DELETE FROM test3 WHERE a3 = NEW.a1;
16    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
17  END;
18|
19
20delimiter ;
21
22INSERT INTO test3 (a3) VALUES
23  (NULL), (NULL), (NULL), (NULL), (NULL),
24  (NULL), (NULL), (NULL), (NULL), (NULL);
25
26INSERT INTO test4 (a4) VALUES
27  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

이제 다음과 같이 test1 table에 다음 값들을 insert한다고 가정해 봅니다:

1mysql> INSERT INTO test1 VALUES
2       (1), (3), (1), (7), (1), (8), (4), (4);
3Query OK, 8 rows affected (0.01 sec)
4Records: 8  Duplicates: 0  Warnings: 0

그 결과, 네 개의 table에는 다음과 같은 데이터가 저장됩니다:

1mysql> SELECT * FROM test1;
2+------+ 
3| a1   |
4+------+
5|    1 |
6|    3 |
7|    1 |
8|    7 |
9|    1 |
10|    8 |
11|    4 |
12|    4 |
13+------+
148 rows in set (0.00 sec)
15
16mysql> SELECT * FROM test2;
17+------+
18| a2   |
19+------+
20|    1 |
21|    3 |
22|    1 |
23|    7 |
24|    1 |
25|    8 |
26|    4 |
27|    4 |
28+------+
298 rows in set (0.00 sec)
30
31mysql> SELECT * FROM test3;
32+----+
33| a3 |
34+----+
35|  2 |
36|  5 |
37|  6 |
38|  9 |
39| 10 |
40+----+
415 rows in set (0.00 sec)
42
43mysql> SELECT * FROM test4;
44+----+------+
45| a4 | b4   |
46+----+------+
47|  1 |    3 |
48|  2 |    0 |
49|  3 |    1 |
50|  4 |    2 |
51|  5 |    0 |
52|  6 |    0 |
53|  7 |    1 |
54|  8 |    1 |
55|  9 |    0 |
56| 10 |    0 |
57+----+------+
5810 rows in set (0.00 sec)