MySQL トリガー: トリガーの作成と使用

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

CREATE TRIGGER ステートメントを使用して新しいトリガーを作成できます。具体的な構文を見てみましょう。

トリガーの作成 トリガー名 トリガー時間 トリガーイベント
 ON テーブル名
 各行ごとに
 始める
 ...
 終わり;

それでは、上記の SQL の具体的な意味を詳しく見てみましょう。

CREATE TRIGGER ステートメントの後にトリガー名を配置します。トリガー名は、[トリガー時刻]_[テーブル名]_[トリガーイベント] という命名規則に従う必要があります (例: before_employees_update)。
トリガーのアクティブ化時間は前または後にすることができます。定義されたトリガーのアクティブ化時間を指定する必要があります。変更前にアクションを処理する場合は BEFORE キーワードを使用し、変更後にアクションを処理する場合は AFTER キーワードを使用します。
トリガーイベントは、INSERT、UPDATE、または DELETE です。このイベントによりトリガーが呼び出されます。トリガーは 1 つのイベントによってのみ呼び出すことができます。複数のイベントによって呼び出されるトリガーを定義するには、イベントごとに 1 つずつ、複数のトリガーを定義する必要があります。
トリガーは特定のテーブルに関連付ける必要があります。トリガーはテーブルがないと存在しないため、ON キーワードの後にテーブル名を指定する必要があります。
BEGIN ブロックと END ブロックの間に SQL ステートメントを配置します。ここでトリガーロジックが定義されます。

大まかな理解ができたので、従業員テーブルの行データの変更を記録するトリガーを作成してみましょう。まず、このテーブルの構造を見てみましょう。

mysql> DESC 従業員;
+----------------+--------------+------+-----+---------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+----------------+--------------+------+-----+---------+-------+
| 従業員番号 | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| 拡張子 | varchar(10) | NO | | NULL | |
| 電子メール | varchar(100) | NO | | NULL | |
| オフィスコード | varchar(10) | NO | MUL | NULL | |
| reportsTo | int(11) | YES | MUL | NULL | |
| 職名 | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8行セット

従業員テーブル内のデータの変更を保存するために、従業員監査という新しいテーブルを作成しましょう。

従業員監査テーブルを作成します（
  id INT AUTO_INCREMENT 主キー、
  従業員番号 INT NOT NULL、
  姓 VARCHAR(50) NOT NULL,
  変更日時 DATETIME DEFAULT NULL、
  アクション VARCHAR(50) デフォルト NULL
);

次に、employees テーブルの行が更新される前に呼び出される BEFORE UPDATE トリガーを作成します。

区切り文字 $$
トリガー before_employee_update を作成する 
  従業員に関するアップデート前
  各行ごとに 
始める
  employees_auditに挿入
  SETアクション = '更新'、
   従業員番号 = OLD.従業員番号、
    姓 = OLD.姓、
    変更日時 = NOW(); 
終わり$$
区切り文字 ;

上記のトリガーの本体では、トリガーの影響を受ける行の employeeNumber 列と lastname 列にアクセスするために OLD キーワードを使用しています。挿入用に定義されたトリガーでは、NEW キーワードのみが使用できることに注意してください。 OLDキーワードは使用できません。ただし、DELETE 用に定義されたトリガーには新しい行がないため、OLD キーワードのみを使用できます。更新トリガーでは、OLD は更新前の行を参照し、NEW は更新後の行を参照します。

次に、SHOW TRIGGERS ステートメントを使用して、データベース内のトリガーを表示します。

mysql> トリガーを表示;
+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------------------+------------------------------------------------------------------------------------+---------------------+
| トリガー | イベント | テーブル | ステートメント | タイミング | 作成 | sql_mode | Definer | character_set_client | collation_connection | データベース照合 |
+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------------------+------------------------------------------------------------------------------------+---------------------+
| before_employee_update | 更新 | 従業員 | 開始
  employees_auditに挿入
  SETアクション = '更新'、
   従業員番号 = OLD.従業員番号、
    姓 = OLD.姓、
    変更日時 = NOW();
終了 | 前 | 2017-08-02 22:06:36.40 | ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci |
+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------------------+------------------------------------------------------------------------------------+---------------------+
セット内の1行

完了したら、従業員テーブルを更新して、トリガーが呼び出されたかどうかを確認できます。

従業員の更新 
セット 
  lastName = 'Maxsu'
どこ
  従業員番号 = 1056;

次のクエリを使用して employees_audit テーブルを照会し、トリガーが UPDATE ステートメントによって呼び出されるかどうかを確認できます。

mysql> employees_audit から * を選択します。
+----+----------------+----------+----------------------+--------+
| id | 従業員番号 | 姓 | 変更日時 | アクション |
+----+----------------+----------+----------------------+--------+
| 1 | 1056 | ヒル | 2017-08-02 22:15:51 | 更新 |
+----+----------------+----------+----------------------+--------+
セット内の1行

上記の出力に示されているように、トリガーは実際に呼び出され、employees_audit テーブルに新しい行が挿入されます。

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

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

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

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