株式会社 RYUS

スタッフブログ

カテゴリ技術全般の最新配信
RSS

MySQLのトリガの使い方

: 技術全般 2009/6/4 9:28

Blogger's Avatar

satoです。

以前に、MySQLのストアドプロシージャの使い方を紹介しましたが、MySQL5.0では他にもトリガが実装されています。

■トリガとは
トリガ（trigger）とは、名前の通り、何らかの条件によって実行されるイベントのことです。
例えば、「ユーザーをINSERTしたときに特定のグループメンバーにもINSERTする」のようなことをRDBMSレベルで行うことができるようになります。

■簡単な例
トリガとは別物ですが、「このテーブルのレコードを削除したときに、外部キーとしてこのテーブルを参照している他のテーブルのレコードも同時に削除する」というのであれば、MySQL4.1から対応しています。
具体的には、

CREATE TABLE users (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name TEXT
) ENGINE=InnoDB;

CREATE TABLE entries (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

とすると、usersテーブルのレコードを削除すると、そのidが入っているentriesテーブルのレコードも同時に削除されます。

INSERT INTO users(name) VALUES('sato');
INSERT INTO users(name) VALUES('ryuji');
SELECT * FROM users;

+----+-------+
| id | name  |
+----+-------+
|  1 | sato  |
|  2 | ryuji |
+----+-------+
3 rows in set (0.00 sec)

INSERT INTO entries(user_id, title, body) VALUES(1, 'タイトル1', '本文1');
INSERT INTO entries(user_id, title, body) VALUES(1, 'タイトル2', '本文2');
INSERT INTO entries(user_id, title, body) VALUES(2, 'タイトル3', '本文3');
INSERT INTO entries(user_id, title, body) VALUES(2, 'タイトル4', '本文4');
SELECT * FROM entries;

+----+---------+---------------+---------+
| id | user_id | title         | body    |
+----+---------+---------------+---------+
|  1 |       1 | タイトル1 | 本文1 |
|  2 |       1 | タイトル2 | 本文2 |
|  3 |       2 | タイトル3 | 本文3 |
|  4 |       2 | タイトル4 | 本文4 |
+----+---------+---------------+---------+
4 rows in set (0.01 sec)

DELETE FROM users WHERE id = 1;
SELECT * FROM users;

+----+-------+
| id | name  |
+----+-------+
|  2 | ryuji |
+----+-------+
1 row in set (0.00 sec)

SELECT * FROM entries;

+----+---------+---------------+---------+
| id | user_id | title         | body    |
+----+---------+---------------+---------+
|  3 |       2 | タイトル3 | 本文3 |
|  4 |       2 | タイトル4 | 本文4 |
+----+---------+---------------+---------+
2 rows in set (0.00 sec)

ON UPDATE/DELETEが、”親のテーブルが”更新または削除が行われた時にどうするかというのを指定します。
・RESTRICT：子のテーブルにデータが残っている場合はエラーにする（デフォルト）
・CASCADE：子のテーブルに結果を反映する（削除されたら削除）
・SET NULL：子の該当カラムをNULLにする

なお、外部キー制約自体がMySQLではInnoDBのみの機能なので、InnoDB以外では使えません。

■トリガ
この要領で、INSERT、UPDATE、DELETE時に更に複雑な処理をできるようにしたのがトリガです。
上記のことと同じ内容をトリガで行うと、

CREATE TABLE users (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name TEXT
);

CREATE TABLE entries (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

DELIMITER //
CREATE TRIGGER user_delete_trigger BEFORE DELETE ON users
  FOR EACH ROW BEGIN
    DELETE FROM entries WHERE user_id = OLD.id;  
  END;
//
DELIMITER ;

INSERT INTO users(name) VALUES('sato');
INSERT INTO users(name) VALUES('ryuji');
SELECT * FROM users;

+----+-------+
| id | name  |
+----+-------+
|  1 | sato  |
|  2 | ryuji |
+----+-------+
3 rows in set (0.00 sec)

INSERT INTO entries(user_id, title, body) VALUES(1, 'タイトル1', '本文1');
INSERT INTO entries(user_id, title, body) VALUES(1, 'タイトル2', '本文2');
INSERT INTO entries(user_id, title, body) VALUES(2, 'タイトル3', '本文3');
INSERT INTO entries(user_id, title, body) VALUES(2, 'タイトル4', '本文4');
SELECT * FROM entries;

+----+---------+---------------+---------+
| id | user_id | title         | body    |
+----+---------+---------------+---------+
|  1 |       1 | タイトル1 | 本文1 |
|  2 |       1 | タイトル2 | 本文2 |
|  3 |       2 | タイトル3 | 本文3 |
|  4 |       2 | タイトル4 | 本文4 |
+----+---------+---------------+---------+
4 rows in set (0.01 sec)

DELETE FROM users WHERE id = 1;
SELECT * FROM users;

+----+-------+
| id | name  |
+----+-------+
|  2 | ryuji |
+----+-------+
1 row in set (0.00 sec)

SELECT * FROM entries;

+----+---------+---------------+---------+
| id | user_id | title         | body    |
+----+---------+---------------+---------+
|  3 |       2 | タイトル3 | 本文3 |
|  4 |       2 | タイトル4 | 本文4 |
+----+---------+---------------+---------+
2 rows in set (0.00 sec)

このようになります。
DELIMITERについては前回の記事を参照してください。

トリガの箇所だけ抜き出すと以下になります。

CREATE TRIGGER user_delete_trigger BEFORE DELETE ON users
  FOR EACH ROW BEGIN
    DELETE FROM entries WHERE user_id = OLD.id;  
  END;

トリガはCREATE TRIGGER文で作成します。

CREATE TRIGGER [トリガ名] [実行タイミング] [対象命令] ON [対象テーブル]

トリガ名は自由に命名して構いません。
実行タイミングは、BEFOREとAFTERがあり、対象命令が実行される前か後かを指定します。通常は挿入系は後、削除系は前、になると思います。
対象命令は、INSERT、UPDATE、DELETEを指定します。どれかの操作が行われた場合にこのトリガが実行されます。複数指定はできないようです。
対象テーブルは、対象命令が行われるテーブルを指定します。

後は、FOR EACH ROW BEGIN〜END;の間に、ストアドと同様にSQL文を書いていきます。
この際、OLDとNEWという疑似テーブルが既に用意されており、このトリガ操作が処理される前の対象テーブルのレコードの状態がOLD、後の状態がNEWになります。通常は、DELETEの場合はOLD、INSERTの場合はNEWを参照することになります。

トリガは制約とは直接関係しないので、InnoDB以外でも使用することが可能です。
ちなみにトリガを削除する場合は、DROP TRIGGER [トリガ名];で削除が可能です。また、SHOW TRIGGERS;でトリガの一覧を確認することができます。

最近のO/Rマッパーでは、擬似的なトリガを用意していることが多く、RDBMSレベルのトリガを使用することは少ないですが、アプリケーション側を修正したくない場合や、そのような仕組みが存在しない環境での開発においてはトリガは有効な選択肢になります。
ただし、トリガを使用するとアプリケーションのコードを読むだけでは挙動を追跡することができなくなるため、状況に応じて適切に利用するようにしましょう。

さんのブログを読む
トラックバック (0)
閲覧 (79391)

トラックバック

スタッフブログ最新

2月1日 XOOPS Cube勉強会に行ってきました！ (2014/02/04 mik)
1月11日にXOOPS Cube勉強会に参加しました。次回は2月1日ですよ〜 (2014/01/28 Ryuji)
CoreServerでxelfinderで"No input file specified."が表示されたときの対処法 (2014/01/22 Ryuji)
XOOPSは趣味のコミュニティサイトで良く使われてますね。 (2014/01/09 Ryuji)
NetCommons2のAdodb mysqlドライバはトランザクション処理をサポートしてない。 (2013/12/10 Ryuji)

スタッフブログのトップへ

カテゴリ一覧

XOOPS(2)
- 総合的な情報(41)
- モジュール(18)
- プリロード(5)
- TIPS小ネタ(35)
- テーマ/デザイン(11)
NetCommons(4)
CMS(2)
PHP(9)
技術全般(105)
イベント(40)
ツール(55)
ビジネス(12)
その他(46)

〒104-0061　東京都中央区銀座１丁目３番３号 G1ビル７階
お問い合わせ　TEL 03-3524-8860