この記事は 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テーブルの更新前に発火します。

ここでのポイントは

1. trigger_bodyにBEGIN ... END構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できる¹⁾トリガー定義内で;をステートメント区切り文字として使用できるように、mysql ステートメント区切り文字を再定義(delimiter //)しています。
2. 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テーブルは下記の状態になります。

また、下記のようなデータの不整合が発生する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;

下記の状態になります。

先の仕様を満たしたトリガーが実装できていそうですね。

トリガーのテスト

トリガーが仕様どおりの挙動となっていることを自動テストでも確かめましょう。

今回はRubyとMySQLクライアントであるmysql2とテストフレームワークのtest/unitを使い、テスト用スクリプトを作成しました²⁾ただし行いたいことはデータベースの振る舞いに関するテストなので、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構文内の条件式については、テストケースを実行して問題が発覚するということが何度もありました。これはトリガーに限らずプロシージャの定義などにも当てはまると思いますが、特に三値論理の扱いに不安がある場合には先にテストケースを書いておくことが大幅な実装時間の短縮することに繋がると感じています³⁾SELECT (NULL AND 1), (NULL AND 0), (NULL OR 1), (NULL OR 0)の実行結果スラスラ言えますか？。

まとめ

ここまでデータベーストリガーの機能紹介とテスト手法について紹介してきました。

トリガーを使う際に強く意識すべきことはデータベースにビジネスロジック持たせているという点です。

データベースに共通ロジックを持たせることで各々のアプリケーションの実装が不要になるという点はメリットの1つでしょう。例えばカラム名の変更などのデータベースリファクタリングを行う際には、データベース側に共通ロジックがあることでアプリケーションの修正工数が少なくなり、リファクタリングの総工数を下げられます。

しかし、トリガーの存在を知らないアプリケーション開発者からすると、データベースの挙動が想定外に感じるはずです⁴⁾トリガーにより別のトリガーが呼び出され、そのトリガーでまた別のトリガーが呼び出されるような状況はアプリケーション側からすると怪奇現象に見えるかもしれません。。データベースではデータの保存のみを扱い、アプリケーションでビジネスロジックを扱うという基本原則には反している状態は、後々負債となって自分たちを苦しめることになるかもしれません。

個人的な見解としては、

1. トリガーは基本的に使用せずビジネスロジックはアプリケーションで実装する
2. ただしリファクタリング時などトリガーの使用期間が決まっており、トリガーにより総工数が下がる場合には導入を検討する

という結論に至っています。その場合でもトリガーのテストは必ず書く、トリガーは不要になった段階で即時削除する、アプリケーション開発者に全員に周知するなどは必ず行うべきでしょう。

便利なデータベーストリガー、用法用量を守って使っていきましょう！