MySQL トリガー: 複数のトリガー操作の作成例の分析

この記事では、例を使用して、MySQL で複数のトリガー操作を作成する方法について説明します。ご参考までに、詳細は以下の通りです。

今回記録した MySQL バージョンは 5.7.2 以上である必要があり、それ以前のバージョンでは動作しません。さっそく始めましょう。

MySQL 5.7.2+ より前では、テーブル内のイベントに対してのみトリガーを作成できました。たとえば、BEFORE UPDATE または AFTER UPDATE イベントに対してのみトリガーを作成できました。 MySQL 5.7.2 以降のバージョンではこの制限が解決され、テーブル内の同じイベントとアクション時間に対して複数のトリガーを作成できるようになりました。イベントが発生すると、トリガーが順番にアクティブになります。最初のトリガーの作成の構文を参照してみましょう。テーブル上に同じイベントのトリガーが複数ある場合、MySQL は作成された順序でトリガーを呼び出します。トリガーの順序を変更するには、FOR EACH ROW 句の後に FOLLOWS または PRECEDES を指定します。これら2つの単語の説明を見てみましょう。

FOLLOWS オプションを使用すると、既存のトリガーの後に新しいトリガーをアクティブにすることができます。
PRECEDES オプションを使用すると、既存のトリガーの前に新しいトリガーをアクティブにすることができます。

最後に、明示的な順序を使用して新しい追加トリガーを作成するための構文を見てみましょう。

区切り文字 $$
CREATE TRIGGER トリガー名
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON テーブル名
各行ごとに[FOLLOWS|PRECEDES] existing_trigger_name
始める
…
終わり$$
区切り文字 ;

次に、テーブル内の同じイベントとアクションに対して複数のトリガーを作成する例を見てみましょう。製品テーブルに基づいてデモンストレーションしてみましょう。まず、新しい price_logs テーブルを作成します。製品の価格 (MSRP 列) が変更されると、古い価格が price_logs というテーブルに記録されます。SQL を見てみましょう。

テーブルprice_logsを作成します（
 id INT(11) NOT NULL AUTO_INCREMENT,
 製品コード VARCHAR(15) NOT NULL、
 価格 DOUBLE NOT NULL、
 updated_at TIMESTAMP NOT NULL デフォルト 
       現在のタイムスタンプ 
       CURRENT_TIMESTAMPの更新時、
 主キー (id)、
 キー製品コード (製品コード)、
 制約 price_logs_ibfk_1 外部キー (product_code) 
 参照製品 (productCode) 
 削除カスケード 
 アップデートカスケード
);

これで完了です。テーブルの BEFORE UPDATE イベントが発生したときに新しいトリガーを作成します。トリガー名は before_products_update で、具体的な実装は次のとおりです。

区切り文字 $$
トリガー before_products_update を作成する 
  アップデート前の製品 
  各行ごとに 
始める
   price_logs(製品コード、価格)にINSERTします
   VALUES(古い製品コード、古いMSRP);
終わり$$
区切り文字 ;

次に、製品の価格を変更するときは、次の更新ステートメントを使用して、最後に price_logs テーブルをクエリします。

アップデート製品
希望小売価格 = 95.1
ここで、productCode = 'S10_1678';
-- クエリ結果の価格レコード SELECT * FROM price_logs;

上記のクエリステートメントを実行すると、次の結果が得られます。

+----+--------------+-------+---------------------+
| id | 製品コード | 価格 | 更新日時 |
+----+--------------+-------+---------------------+
| 1 | S10_1678 | 95.7 | 2017-08-03 02:46:42 |
+----+--------------+-------+---------------------+
セット内の1行

結果からわかるように、期待どおりに機能しています。

ここで、古い価格を確認するだけでなく、価格が変更されたときに誰が変更したかも記録したいとします。これを実現するには、price_logs テーブルに列を追加することもできますが、複数のトリガーを示すために、変更を行ったユーザーに関するデータを格納する新しいテーブルを作成します。この新しいテーブルの名前は user_change_logs で、その構造は次のとおりです。

テーブルuser_change_logsを作成します（
 id int(11) NOT NULL AUTO_INCREMENT、
 product_code varchar(15) デフォルト NULL,
 updated_at タイムスタンプ NOT NULL デフォルト CURRENT_TIMESTAMP 
 CURRENT_TIMESTAMPの更新時、
 updated_by varchar(30) NOT NULL、
 主キー (id)、
 キー製品コード (製品コード)、
 制約 user_change_logs_ibfk_1 外部キー (product_code) 
 参照製品 (productCode) 
 削除カスケード 更新カスケード
);

ここで、products テーブルの BEFORE UPDATE イベントでアクティブ化される 2 番目のトリガーを作成します。このトリガーは、変更されたユーザー情報を user_change_logs テーブルに更新します。 before_products_update が実行された後にアクティブになります。

区切り文字 $$
トリガー before_products_update_2 を作成する 
  アップデート前の製品 
  各行は before_products_update に従います
始める
  user_change_logs(product_code,updated_by) に INSERT します。
  VALUES(古い製品コード、ユーザー());
終わり$$
区切り文字 ;

次に、更新ステートメントを使用して、指定された製品の価格を更新します。

アップデート製品
希望小売価格 = 95.3
ここで、productCode = 'S10_1678';

次に、それぞれ price_logs テーブルと user_change_logs テーブルからデータをクエリします。

mysql> price_logs から * を選択します。
+----+--------------+-------+---------------------+
| id | 製品コード | 価格 | 更新日時 |
+----+--------------+-------+---------------------+
| 1 | S10_1678 | 95.7 | 2017-08-03 02:46:42 |
| 2 | S10_1678 | 95.1 | 2017-08-03 02:47:21 |
+----+--------------+-------+---------------------+
2行セット
mysql> user_change_logs から * を選択;
+----+--------------+---------------------+----------------+
| id | 製品コード | 更新日時 | 更新者 |
+----+--------------+---------------------+----------------+
| 1 | S10_1678 | 2017-08-03 02:47:21 | root@localhost |
+----+--------------+---------------------+----------------+
セット内の1行

上記のように、2 つのトリガーは関連する操作を実行するために予想される順序でアクティブ化されます。ここで、information_schema データベースの triggers テーブルの action_order 列を見て、同じイベントとアクションがトリガーされる順序を確認しましょう。

mysql> 選択 
  トリガー名、アクション順序
から
  情報スキーマトリガー
どこ
  トリガースキーマ = 'yiibaidb'
イベントオブジェクトテーブルによる順序付け、 
     アクションタイミング、 
     イベント操作;
+--------------------------+--------------+
| トリガー名 | アクション順序 |
+--------------------------+--------------+
| 従業員更新前 | 1 |
| 製品アップデート前 | 1 |
| before_products_update_2 | 2 |
+--------------------------+--------------+
3行セット

さて、この記録についてはこれですべてです。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL トランザクション操作スキル」、「MySQL ストアドプロシージャスキル」、「MySQL データベースロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: