mysql:10898

MySQLのトリガー
Peter Gulutzan著

2005年２月3日
http://www.onlamp.com/pub/a/onlamp/2005/02/03/triggers.html

新機能をテストするためのアルファ版であるMySQL 5.0にはトリガー
のサポートがあります。MySQLの開発予定に入っていたので
なんら驚くことはありません。しかし、「MySQLはこれは
出来ないだろう。」と言われてきた機能を使えることと、MySQL
がそれを提供したということは新鮮な驚きです。

テストのための開発ソースツリーからマニュアルにある
ようにMySQL5.0をダウンロードしました。 通常のMySQLの
ダウンロードページからのものよりもかなり新しくて
あまりテストされていません。

トリガーをテストドライブ

Linux のシェルからクライアントを立ち上げます。最初に
本当に第五版であるか確かめます。

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を1行毎に
説明します。

トリガーの説明

CREATE TRIGGER trigger_name            /* line 1 */

最初はCREATE TRIGGERで新しいトリガーの名前を指定します。
私の場合はいつも同じ方法で指定します。テーブルの名前、
下線そしてこの６つのうちの１つ bi, ai, bu, au, bd, or ad.
このコードの説明は:

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クローズがあるMy triggerは
テーブルtにINSERTをすると活性化されます。

FOR EACH ROW                           /* line 3 */

特に挿入される行毎に活性化が起こります。ゼロ行をINSERTすると
（これは INSERT ... SELECTステートメントでは可能ですが）、
ゼロ回の活性化が起こります。1000行のINSERT を行うと1000回の
活性化が起こります。スタンダードのSQLであればFOR EACH STATEMENT
と置き換えることができます。これは何行あっても活性化は1度だけ
しか起こりません。

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に行を挿入するたびに、@xの値は増加すべきです。なんとなれば
tにはFOR EACH ROWトリガーが設定されているからです。それで
SELECTとすると

SELECT @x;                              /* line 7 */

結果は2となります。2行あるので。

もっと凝ったかっこの良いトリガー

もっとかっこの良い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というトリガーは アップデートされる行ごとに活性化されます。
このトリガーの中身は複合ステートメントです。 stored procedureの
複合ステートメント用のシンタクスは全てMySQLのレファレンス・マニュアル
にあります。ですからここで、簡単に分かりやすく説明します。
column1の「新しい」値に２を掛けます。しかし、もしレンジオーバの
エラーが生ずると、column1の「新しい」値を-1にセットしてください。

トリガーの中身はMySQL関数の中身に含むことができるものであれば
殆ど全て含むことが出来ます。その他に行の「新しい」か又は「古い」
値に対するレファレンスも含むことが出来ます。

どんなときにエラーが起り得るでしょうか。column1のデータタイプは
TINYINTですから、最大の値は127です。MySQL 5.0はある状況では
タイプのチェックを行うことが出来ます。UPDATEを行って
「レンジ・オーバー」のエラーを引き起こして見ましょう。

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です。これはexception handlerを起動
して、値を-1に戻してしまいます。2番目の行ではcolumn1の値はNULL
です。だから何も起こりません。テーブルtの中身をみて検証してみましょう。

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

結論の現在の問題点

明らかにトリガーはINSERTと UPDATEに関して正しく動作します。
トリガーの中身は複雑なステートメントを含むことができますし、
BEFORE トリガーは挿入やアップデートされる値を読んだりも
変更したりも出来ます。これらは全て非常に良いことです。ところで、
あまり興奮して喜び過ぎないように２つの注でこの記事を
終わります。

注: MySQL アルファ版はまだ不安定です。MySQLのバグデータベース
で「trigger*」を探してください。ともかく注意深く進んでください。

注: MySQL関数は今厳しい制限があります。例えば、テーブルからSELECT
出来ません。トリガーの活性化は関数コールのようなもので、
同様の制限を受けます。

Peter Gulutzanはシニア・ソフト・アーキテクトでカナダの
アルバータ州エドモントンに在住です。
---------------------
Zen Kishimoto                        zen@xxxxxxxxxx
IP Devices, Inc.                       (408) 567-9391
2175 De La Cruz Blvd., Suite 10  (801) 720-8847 (FAX)
Santa Clara, CA 95050