便利なデータベーストリガーを正しく使おう!
kuranari
この記事は RECRUIT MARKETING PARTNERS Advent Calendar 2018 の投稿記事です。
キッズリー開発を担当している倉成です。
皆さんデータベーストリガーを使ったことはあるでしょうか?
先日参加したbuildersconのなかでトリガー機能について知る機会があり、トリガーについてテストや運用を含めて調査を行いました。この記事ではトリガー機能に馴染みのないアプリケーションエンジニア向けにトリガーの概要とトリガーのテスト方法にフォーカスをあてて紹介します。
最後にまとめとしてどのような場合トリガーを使うべきでどのような場合には使うべきではないかを考察をしていきます。
今回の想定RDBMSはMySQL 5.6です。Oracle Databaseなど他のRDBMSではCREATE TRIGGER
などの構文が異なりますので適宜読み替えを行なってください。
トリガーの概要
トリガーとはテーブルに対するINSERT/UPDATE/DELETEなどのイベントが発生した際に実行される操作を登録できる機能です。
実際のトリガーの例を2つ見てみましょう。
例1. INSERT後に他テーブルにレコードを挿入する
usersテーブルにレコードが挿入されたことをイベントログとして記録したい場合、トリガーの実装は下記のようになります。
CREATE TRIGGER insert_log AFTER INSERT ON users
FOR EACH ROW
INSERT INTO event_logs (table, action, user_id) VALUES ("users", "INSERT", NEW.id);
AFTER INSERT
を指定しているのでこのトリガーはusersテーブルの挿入後に発火します。
ここでのポイントは、トリガーに対するMySQLの拡張のNEW
とOLD
の2つのキーワードです。
OLD
はOLD.col_name
の形で使用し、更新される前のレコードのカラムを参照できます。
NEW
はNEW.col_name
の形で使用し、更新された後のレコードのカラムを参照できます。
今回の例ではNEW.id
は新しく挿入するレコードのid
カラムを参照していることになります。
例2: UPDATE前にカラムの値を変更する
emailカラムに変更があった差異、email_changedに値を設定するトリガーは下記で定義できます。
delimiter //
CREATE TRIGGER update_check BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF NEW.email <> OLD.email THEN
SET NEW.email_changed = 1;
END IF;
END;
delimiter ;
BEFORE UPDATE
を指定しているのでこのトリガーはusersテーブルの更新前に発火します。
ここでのポイントは
- trigger_bodyに
BEGIN ... END
構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できる1)トリガー定義内で;
をステートメント区切り文字として使用できるように、mysql ステートメント区切り文字を再定義(delimiter //
)しています。 SET NEW.email_changed = 1
のように、更新対象のレコードのカラムの値を操作できる
の2点です。
より詳細なトリガー構文の仕様と具体的な例についてはMySQLのリファレンスマニュアルも併せてご覧ください。
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.19 CREATE TRIGGER 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.30 DROP TRIGGER 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.3.1 トリガーの構文と例
トリガー実践
より実践的なトリガーの例として、下記の要件を考えていきましょう。
- usersテーブルにはmail_addressとemailアドレスが存在する
- mail_addressとemailが常に同じ値になるように、いずれかを更新した際にもう一方のカラムも更新したい
- mail_addressとemailが異なる値となるようなクエリはエラーを表示したい
usersテーブルのDDLは下記とします。
CREATE TABLE `users` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`mail_address` VARCHAR(255),
`email` VARCHAR(255),
PRIMARY KEY (`id`)
);
トリガー実装
仕様を満たすトリガーを実装していきましょう。
INSERTトリガー
delimiter //
CREATE TRIGGER SynchronizeEmailOnInsert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.email IS NOT NULL AND NEW.mail_address IS NOT NULL AND NOT(NEW.email <=> NEW.mail_address) THEN
SIGNAL sqlstate '45000' SET message_text = 'emailとmail_addressの値が異なります';
END IF;
IF NEW.email IS NOT NULL THEN
SET NEW.mail_address = NEW.email;
END IF;
IF NEW.mail_address IS NOT NULL THEN
SET NEW.email = NEW.mail_address;
END IF;
END;
delimiter ;
実際にmail_addressまたはemailのみを指定してINSERTを実行した結果、
INSERT INTO users (name, mail_address) VALUES ('アリス', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('ボブ', 'bob@example.com');
usersテーブルは下記の状態になります。
id | name | mail_address | |
---|---|---|---|
1 | アリス | alice@example.com | alice@example.com |
2 | ボブ | bob@example.com | bob@example.com |
また、下記のようなデータの不整合が発生するINSERTを実行しようとした場合には、データベースレベルでのエラーを発生させることができます。
INSERT INTO users (mail_address, email) VALUES ('aaa@example.com', 'bbb@example.com');
# => emailとmail_addressの値が異なります
UPDATEトリガー
delimiter //
CREATE TRIGGER SynchronizeEmailOnUpdate BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF NOT(NEW.email <=> OLD.email) AND NOT(NEW.mail_address <=> OLD.mail_address) AND NOT(NEW.email <=> NEW.mail_address) THEN
SIGNAL sqlstate '45000' SET message_text = 'emailとmail_addressの値が異なります';
END IF;
IF NOT(NEW.email <=> OLD.email) THEN
SET NEW.mail_address = NEW.email;
END IF;
IF NOT(NEW.mail_address <=> OLD.mail_address) THEN
SET NEW.email = NEW.mail_address;
END IF;
END;
delimiter ;
mail_addressまたはemailのみを指定してUPDATEを実行した結果は
UPDATE users SET mail_address = 'alice@example.jp' where id = 1;
UPDATE users SET email = 'bob@example.jp' where id = 2;
下記の状態になります。
id | name | mail_address | |
---|---|---|---|
1 | アリス | alice@example.jp | alice@example.jp |
2 | ボブ | bob@example.jp | bob@example.jp |
先の仕様を満たしたトリガーが実装できていそうですね。
トリガーのテスト
トリガーが仕様どおりの挙動となっていることを自動テストでも確かめましょう。
今回はRubyとMySQLクライアントであるmysql2
とテストフレームワークのtest/unit
を使い、テスト用スクリプトを作成しました2)ただし行いたいことはデータベースの振る舞いに関するテストなので、Rubyのテストフレームワークを使いつつも生のSQLを使った抽象度の低いテスト手法を採用しています。。
INSERTトリガーの振る舞いに対するテストの一部を下に示します。
require 'test/unit'
require "mysql2"
class InertTriggerTest < Test::Unit::TestCase
setup do
@client = Mysql2::Client.new(host: "localhost", username: "root", database: 'trigger_sandbox')
@client.query("TRUNCATE TABLE users")
end
def assert_column_values(expected)
user = @client.query("SELECT mail_address, email FROM users WHERE id = 1").first
assert_equal expected, user["email"]
assert_equal expected, user["mail_address"]
end
test "mail_addressのみ値を設定" do
@client.query("INSERT INTO users (mail_address) VALUES ('alice@example.com')")
assert_column_values "alice@example.com"
end
test "emailのみ値を設定" do
@client.query("INSERT INTO users (email) VALUES ('alice@example.com')")
assert_column_values "alice@example.com"
end
test "mail_address/emailの両方に同じ値を設定" do
@client.query("INSERT INTO users (email, mail_address) VALUES ('alice@example.com', 'alice@example.com')")
assert_column_values "alice@example.com"
end
test "mail_addressとemailに異なる値を設定" do
e = assert_raises Mysql2::Error do
@client.query("INSERT INTO users (email, mail_address) VALUES ('alice@example.com', 'bob@example.com')")
end
assert_equal 'emailとmail_addressの値が異なります', e.message
end
end
各テストケースのINSERT文とassert_column_values
の対応から、仕様を満たした挙動となっていることがなんとなく理解できるのではないでしょうか。
今回トリガー機能を実装する際にはTDD的にテストケースを先に用意して実装を進めていきましたが、これはかなり有効な方法でした。特にIF構文内の条件式については、テストケースを実行して問題が発覚するということが何度もありました。これはトリガーに限らずプロシージャの定義などにも当てはまると思いますが、特に三値論理の扱いに不安がある場合には先にテストケースを書いておくことが大幅な実装時間の短縮することに繋がると感じています3)SELECT (NULL AND 1), (NULL AND 0), (NULL OR 1), (NULL OR 0)
の実行結果スラスラ言えますか?。
まとめ
ここまでデータベーストリガーの機能紹介とテスト手法について紹介してきました。
トリガーを使う際に強く意識すべきことはデータベースにビジネスロジック持たせているという点です。
データベースに共通ロジックを持たせることで各々のアプリケーションの実装が不要になるという点はメリットの1つでしょう。例えばカラム名の変更などのデータベースリファクタリングを行う際には、データベース側に共通ロジックがあることでアプリケーションの修正工数が少なくなり、リファクタリングの総工数を下げられます。
しかし、トリガーの存在を知らないアプリケーション開発者からすると、データベースの挙動が想定外に感じるはずです4)トリガーにより別のトリガーが呼び出され、そのトリガーでまた別のトリガーが呼び出されるような状況はアプリケーション側からすると怪奇現象に見えるかもしれません。。データベースではデータの保存のみを扱い、アプリケーションでビジネスロジックを扱うという基本原則には反している状態は、後々負債となって自分たちを苦しめることになるかもしれません。
個人的な見解としては、
- トリガーは基本的に使用せずビジネスロジックはアプリケーションで実装する
- ただしリファクタリング時などトリガーの使用期間が決まっており、トリガーにより総工数が下がる場合には導入を検討する
という結論に至っています。その場合でもトリガーのテストは必ず書く、トリガーは不要になった段階で即時削除する、アプリケーション開発者に全員に周知するなどは必ず行うべきでしょう。
便利なデータベーストリガー、用法用量を守って使っていきましょう!
脚注
↑1 | トリガー定義内で; をステートメント区切り文字として使用できるように、mysql ステートメント区切り文字を再定義(delimiter // )しています。 |
---|---|
↑2 | ただし行いたいことはデータベースの振る舞いに関するテストなので、Rubyのテストフレームワークを使いつつも生のSQLを使った抽象度の低いテスト手法を採用しています。 |
↑3 | SELECT (NULL AND 1), (NULL AND 0), (NULL OR 1), (NULL OR 0) の実行結果スラスラ言えますか? |
↑4 | トリガーにより別のトリガーが呼び出され、そのトリガーでまた別のトリガーが呼び出されるような状況はアプリケーション側からすると怪奇現象に見えるかもしれません。 |