MySQLでトリガーを使用してINSERT・UPDATEを停止する方法【SIGNAL SQLSTATE】

MySQLでトリガーを使ってINSERT・UPDATEを停止する方法（SIGNAL SQLSTATE）

MySQLでは、トリガー内でSIGNAL SQLSTATEコマンドを使用することで、特定の条件を満たした場合にINSERTやUPDATE処理を強制的に中止できます。SQLSTATE値「45000」はユーザー定義エラーを表す汎用的な値で、独自のエラーメッセージを設定してクライアントに通知することが可能です。

トリガーの基本構文

INSERTやUPDATEを中止するトリガーの基本構文は以下の通りです。

DELIMITER //
CREATE TRIGGER トリガー名 BEFORE INSERT ON テーブル名 FOR EACH ROW
BEGIN
  IF 条件式 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'ユーザーに表示するメッセージ';
  END IF;
END //
DELIMITER ;

それでは、実際に条件を満たした場合にレコードの挿入を阻止するトリガーを作成してみましょう。

サンプルテーブルの作成

まず、動作確認用のテーブルを作成します。Idカラムには1〜5の値のみを挿入できるようにします。

mysql> CREATE TABLE Insert_Prevent
    -> (
    ->   Id INT
    -> );
Query OK, 0 rows affected (0.62 sec)

挿入を防止するトリガーの作成

次に、Idが1未満または5より大きい場合にエラーを発生させてINSERTを中断するトリガー「InsertPreventTrigger」を作成します。

mysql> DELIMITER //
mysql> CREATE TRIGGER InsertPreventTrigger BEFORE INSERT ON Insert_Prevent
    -> FOR EACH ROW
    -> BEGIN
    ->   IF(new.Id < 1 OR new.Id > 5) THEN
    ->     SIGNAL SQLSTATE '45000'
    ->     SET MESSAGE_TEXT = 'You can not insert record';
    ->   END IF;
    -> END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

このトリガーにより、Idが1〜5の範囲外の値を挿入しようとすると、処理が自動的に中止される仕組みです。

範囲外の値を挿入した場合（エラーになる）

試しに、範囲外の値である「0」と「6」を挿入してみます。トリガーが作動し、エラーメッセージとともにINSERTが失敗します。

mysql> INSERT INTO Insert_Prevent VALUES(0);
ERROR 1644 (45000): You cannot insert record
mysql> INSERT INTO Insert_Prevent VALUES(6);
ERROR 1644 (45000): You cannot insert record

範囲内の値を挿入した場合（正常に登録される）

一方、1〜5の範囲内の値であればトリガーの条件に引っかからず、問題なく挿入できます。

mysql> INSERT INTO Insert_Prevent VALUES(1);
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO Insert_Prevent VALUES(5);
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO Insert_Prevent VALUES(2);
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO Insert_Prevent VALUES(3);
Query OK, 1 row affected (0.23 sec)

テーブルの内容を確認する

最後にSELECT文でテーブルの中身を確認しましょう。

mysql> SELECT * FROM Insert_Prevent;

実行結果は以下の通りです。

+------+
| Id   |
+------+
|    1 |
|    5 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

まとめ

このように、BEFORE INSERTトリガーとSIGNAL SQLSTATE '45000'を組み合わせることで、アプリケーション側のロジックに頼らず、データベースレベルで不正なデータの投入を防ぐことができます。UPDATEを制御したい場合も、BEFORE UPDATEトリガーで同じ手法を適用すれば同様に処理を中止できます。