MySQLのトリガーの使い方は次の通りです。

著者は、Write for DOnationsプログラムの一環として、寄付を受けるためにDiversity in Tech Fundを選択しました。

導入

リレーショナルデータベースとStructured Query Language（SQL）を使用して作業する際、データのほとんどの操作は明示的に実行されたクエリ（SELECT、INSERT、またはUPDATEなど）の結果として実行されます。

しかしながら、SQLデータベースでは、トリガを通じて特定のイベントが発生した際に事前定義されたアクションを自動的に実行するよう指示することもできます。たとえば、トリガを使用して、削除ステートメントの全ての操作ログや、行が更新または追加されるたびに集計統計の要約を自動的に更新することができます。

このチュートリアルでは、行が挿入された、更新された、または削除された場合に自動的にアクションを実行するために、異なるSQLトリガーを使用します。

前提条件

このガイドに従うためには、SQLベースの関係データベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されています。

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
• Basic familiarity with executing SELECT, INSERT, UPDATE, and DELETE queries to manipulate data in the database as described in our How To SELECT Rows FROM Tables in SQL, How To Insert Data in SQL, How To Update Data in SQL, and How To Delete Data in SQL guides.
• Basic familiarity with using nested queries as described in our How To Use Nested Queries in SQL guide.
• Basic familiarity with using aggregate mathematical functions as described in our How To Use Mathematical Expressions and Aggregate Functions in SQL guide.

以下のような、いくつかのサンプルデータをロードしたテーブルを持つデータベースも必要です。関数の使用方法を練習するためには、MySQLサーバーに接続し、このガイド全体で使用されるテストデータベースを作成する方法について詳細な手順が、以下の「MySQLへの接続とサンプルデータベースの設定」セクションで説明されていますので、そちらをご参照ください。

MySQLへの接続とサンプルデータベースの設定

このセクションでは、MySQLサーバーに接続し、以下のセクションでの例に従ってサンプルデータベースを作成します。

この案内では、架空のコレクションアイテムを使用します。現在所有しているコレクションアイテムの詳細を保存し、総額をすぐに利用可能にし、コレクションアイテムを削除しても常に痕跡が残るようにします。

もしあなたのSQLデータベースシステムがリモートサーバー上で稼働している場合、ローカルマシンからサーバーにSSH接続してください。

1. ssh sammy@your_server_ip

その後、MySQLサーバープロンプトを開き、MySQLのユーザーアカウント名である「sammy」を置き換えてください。

1. mysql -u sammy -p

「コレクタブル」という名前のデータベースを作成してください。

1. CREATE DATABASE collectibles;

もしデータベースが正常に作成された場合、以下のような出力が表示されます。

Query OK, 1 row affected (0.01 sec)

コレクタブルデータベースを選択するには、以下のUSE文を実行してください。

1. USE collectibles;

以下の出力を受け取ります: (Shita no shutsuryoku o uketorimasu)

Database changed

データベースを選択した後、その中にサンプルのテーブルを作成することができます。テーブル「収集品」は、データベース内の収集品に関する簡略なデータを保持します。次のカラムが含まれます：

• name: This column holds the name for each collectible, expressed using the varchar data type with a maximum of 50 characters.
• value: This column stores the collectible’s market value using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

以下のコマンドを使用して、サンプルテーブルを作成してください。

1. CREATE TABLE collectibles (
2. name varchar(50),
3. value decimal(5, 2)
4. );

もし以下の出力が表示されるならば、テーブルは作成されています。

Query OK, 0 rows affected (0.00 sec)

次のテーブルはcollectibles_statsと呼ばれ、コレクション内のすべてのコレクタブルの累積価値を追跡するために使用されます。このテーブルには、以下のカラムを持つ単一のデータ行が保存されます。

• count: This column holds the number of owned collectibles, expressed using the int data type.
• value: This column stores the accumulated worth of all collectibles using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

以下のコマンドを使用してサンプルテーブルを作成してください。

1. CREATE TABLE collectibles_stats (
2. count int,
3. value decimal(5, 2)
4. );

もし以下の出力が表示されたら、テーブルは作成されています。

Query OK, 0 rows affected (0.00 sec)

第三テーブルはcollectibles_archiveと呼ばれ、コレクションから削除されたすべてのコレクティブルの追跡を行い、それらが消えないようにします。このテーブルはcollectiblesテーブルと似たデータを保持し、削除された日付が追加されます。以下の列が使用されます。

• name: This column holds the name for each removed collectible, expressed using the varchar data type with a maximum of 50 characters.
• value: This column stores the collectible’s market value at the moment of deletion using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.
• removed_on: This column stores the date and time of deletion for each archived collectible using the timestamp data type with the default value of NOW(), meaning the current date whenever a new row is inserted into this table.

以下のコマンドを使用して、サンプルのテーブルを作成してください。

1. CREATE TABLE collectibles_archive (
2. name varchar(50),
3. value decimal(5, 2),
4. removed_on timestamp DEFAULT CURRENT_TIMESTAMP
5. );

もし以下の出力が表示されるなら、テーブルは作成されています。

Query OK, 0 rows affected (0.00 sec)

その後、次のINSERT INTO操作を実行して、コレクションの初期状態である空のcollectibles_statsテーブルにデータを追加してください。

1. INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

INSERT INTO操作は、集められた収集品のテーブルに単一の行を追加します。この行の値は、収集品テーブルのすべての行をカウントするための集計関数と、value列とSUM関数を使用して収集品の価値の合計を計算した結果です。次の出力は、行が追加されたことを示しています。

Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0

テーブル上でSELECTステートメントを実行することで、それを確認することができます。

1. SELECT * FROM collectibles_stats;

データベースにまだコレクションアイテムがないため、アイテムの初期数は0で、累積した値はNULLと表示されます。

+——-+——-+ | count | value | +——-+——-+ | 0 | NULL | +——-+——-+ 1 row in set (0.000 sec)

それでは、あなたはこのガイドの残りを追い、MySQLでトリガーを使用し始める準備が整いました。

トリガーの理解

トリガーは、特定のテーブルに定義されたステートメントであり、そのテーブル内で特定のイベントが発生するたびにデータベースによって自動的に実行されます。トリガーは、特定のステートメントがテーブルで実行されるたびに、一貫して特定のアクションが実行されることを保証するために使用されます。それを手動で実行する必要はなく、データベースのユーザーが覚える必要があります。

テーブルに関連付けられたすべてのトリガーは、ユーザー定義の名前とトリガーを実行するタイミングを指示する一組の条件で識別されます。これらは、二つの別々のクラスにグループ化することができます。

• Database event: The trigger can be executed when INSERT, UPDATE, or DELETE statements are run on a table.
• Event time: Additionally, triggers can be executed BEFORE or AFTER the statement in question.

2つの条件グループを組み合わせることで、合計6つの異なるトリガーの可能性が発生します。これらのトリガーは、共有の条件が満たされるたびに自動的に実行されます。条件を満たす前に発生するトリガーには、BEFORE INSERT、BEFORE UPDATE、BEFORE DELETEがあります。これらは、データがテーブルに挿入または更新される前にデータを操作および検証するために使用することができます。また、削除された行の詳細を監査またはアーカイブの目的で保存することもできます。

条件に合致する文の実行後に発生するトリガーは、AFTER INSERT、AFTER UPDATE、およびAFTER DELETEです。これらは、文の実行後のデータベースの最終状態に基づいて別のテーブルの集計値を更新するために使用することができます。

トリガー内からデータの値にアクセスすることにより、入力データの検証や操作、削除された行のアーカイブなどのアクションを実行することができます。INSERTトリガーでは、新しく挿入されたデータのみを使用することができます。UPDATEトリガーでは、元のデータと更新後のデータの両方にアクセスできます。最後に、DELETEトリガーでは削除された行の元のデータのみが利用可能です（新しいデータが参照できないため）。

トリガ本体内で使用するデータは、データベース内の現在のデータに対してはOLDレコード、クエリが保存するデータに対してはNEWレコードの下に公開されます。個々の列には、OLD.column_nameおよびNEW.column_nameという構文を使って参照することができます。

次の例は、新しいトリガーを作成するために使用されるSQLステートメントの一般的な構文を示しています。

1. CREATE TRIGGER trigger_name trigger_condition
2. ON table_name
3. FOR EACH ROW
4. trigger_actions;

文法を小さな部分に分解してみましょう。

• CREATE TRIGGER is the name of the SQL statement used to create a new trigger in the database.
• trigger_name is the user-defined name of the trigger, used to describe its role, similar to how table names and column names are used to describe their meaning.
• ON table_name tells the database that the trigger should monitor events happening on the table_name table.
• trigger_condition is one of the six possible choices defining when the trigger should run, for example, BEFORE INSERT.
• FOR EACH ROW tells the database that the trigger should be run for each row affected by the triggering event. Some databases support additional patterns of execution other than FOR EACH ROW; however, in the case of MySQL, running the statements from the trigger body for each row affected by the statement that caused the trigger to execute is the only option.
• trigger_actions is the trigger’s body and defines what happens when the trigger executes. It’s typically a single valid SQL statement. It is possible to include multiple statements in the trigger body to perform complex data operations using the BEGIN and END keywords to enclose the list of statements in a block. This is, however, out of the scope of this tutorial. Check out the official documentation for triggers to learn more about the syntax used to define triggers.

以下のセクションでは、INSERTおよびUPDATE操作前にデータを操作するトリガーを作成します。

BEFORE INSERT と BEFORE UPDATE トリガーを使ってデータを操作する。

このセクションでは、INSERTおよびUPDATE文が実行される前に、トリガーを使用してデータを操作します。

この例では、トリガーを使用してデータベース内のすべてのコレクションが一貫性のある大文字の名称を使用するようにします。トリガーを使用しない場合、各INSERTおよびUPDATEステートメントで大文字のコレクション名を使用することを覚えなければなりません。忘れると、データベースはデータをそのまま保存し、データセットに可能なミスを引き起こします。

最初に、12.50ドルの価値がある宇宙船モデルという例のコレクターアイテムを挿入します。問題を示すために、アイテム名は小文字で書かれます。以下のステートメントを実行してください。

1. INSERT INTO collectibles VALUES (‘spaceship model‘, 12.50);

以下のメッセージは、アイテムが追加されたことを確認しています。

Query OK, 1 row affected (0.009 sec)

SELECTクエリを実行して、行が挿入されたことを確認できます。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます。

+—————–+——-+ | name | value | +—————–+——-+ | spaceship model | 12.50 | +—————–+——-+ 1 row in set (0.000 sec)

そのコレクションアイテムは、そのままの形で保存され、名前はすべて小文字でスペルされました。

すべての将来のコレクションアイテムが常に大文字で書かれることを確認するために、BEFORE INSERTトリガーを作成します。トリガーは、トリガリングステートメントが実行される前にデータをデータベースに渡す前に操作することができるため、トリガリングステートメントの前に実行されるトリガーを使用します。

以下の文を実行してください。

1. CREATE TRIGGER uppercase_before_insert BEFORE INSERT
2. ON collectibles
3. FOR EACH ROW
4. SET NEW.name = UPPER(NEW.name);

このコマンドは、collectiblesという名前のテーブル上のすべてのINSERT文の前に実行されるtriggerであるuppercase_before_insertを作成します。

トリガー内の文「SET NEW.name = UPPER(NEW.name)」は、挿入される行ごとに実行されます。SET SQLコマンドは右辺の値を左辺に割り当てます。この場合、NEW.nameは挿入文に保存される名前列の値を表します。収集可能な名前に対してUPPER関数を適用し、カラムの値に戻していることで、データベースに保存される値の大文字・小文字を変換しています。

MySQLは、トリガーが正常に作成されたことを確認するために、次のメッセージを表示します。

Query OK, 1 row affected (0.009 sec)

今度は、小文字の引数を使用してINSERTクエリに新しいコレクタブルを挿入してみてください。

1. INSERT INTO collectibles VALUES (‘aircraft model‘, 10.00);

そしてもう一度、コレクションテーブルの結果行を確認してください。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます。

+—————–+——-+ | name | value | +—————–+——-+ | spaceship model | 12.50 | | AIRCRAFT MODEL | 10.00 | +—————–+——-+ 2 rows in set (0.000 sec)

今回は、新規登録に「AIRCRAFT MODEL」と全て大文字で書かれた項目が追加されましたが、それはあなたが挿入しようとした項目とは異なります。トリガーがバックグラウンドで実行され、行がデータベースに保存される前に文字の大文字小文字を変換しました。

新しい行はすべて、トリガーによって保護されるようになりました。これにより、名前がすべて大文字で保存されることが保証されます。ただし、UPDATE文を使用して制限のないデータを保存することもまだ可能です。同じ効果でUPDATE文を保護するには、別のトリガーを作成してください。

1. CREATE TRIGGER uppercase_before_update BEFORE UPDATE
2. ON collectibles
3. FOR EACH ROW
4. SET NEW.name = UPPER(NEW.name);

二つのトリガーの違いは、トリガーの基準にあります。今回は、BEFORE UPDATEという意味で、トリガーはテーブルに対してUPDATE文が発行されるたびに実行されます。これにより、前のトリガーでカバーされた新しい行に加えて、既存の行も更新の都度影響を受けます。

MySQLは、トリガーが正常に作成されたことを確認するメッセージを出力します。

Query OK, 0 row affected (0.009 sec)

新しいトリガーの振る舞いを検証するために、宇宙船モデルの価格値を更新してみてください。

1. UPDATE collectibles SET value = 15.00 WHERE name = ‘spaceship model‘;

WHERE句は名前によってアップデートする行をフィルタリングし、SET句は値を15.00に変更します。

次の出力を受け取ります。その結果、ステートメントが一行だけ変更されたことが確認されます。

Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0

コレクタブルテーブルの結果行をチェックしてください。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます。

+—————–+——-+ | name | value | +—————–+——-+ | SPACESHIP MODEL | 15.00 | | AIRCRAFT MODEL | 10.00 | +—————–+——-+ 2 rows in set (0.000 sec)

現在、実行されたステートメントによって価格が15.00に更新されるだけでなく、名前は現在「SPACESHIP MODEL」と表記されています。UPDATEステートメントを実行すると、トリガーが実行され、更新された行の値に影響を与えます。名前のカラムは保存前に大文字に変換されました。

このセクションでは、データをデータベースに保存する前に、INSERTおよびUPDATEクエリの前で動作する2つのトリガを作成して、データを整形しました。次のセクションでは、BEFORE DELETEトリガを使用して、削除された行を別のテーブルにコピーしてアーカイブします。

行を削除する前にアクションを実行するためにBEFORE DELETEトリガーを使用すること。

アイテムを持っていなくても、削除に関するエントリを別のテーブルに残したい場合があります。このチュートリアルの始めに、削除された収集品の履歴を追跡するためにcollectibles_archiveという2番目のテーブルを作成しました。このセクションでは、DELETE文の実行前にトリガーを使用して削除されたエントリをアーカイブします。

次の文を実行して、アーカイブテーブルが完全に空であるかどうかを確認してください。

1. SELECT * FROM collectibles_archive;

以下の出力は、collectibles_archiveテーブルが空であることを画面に表示します。

Empty set (0.000 sec)

今、コレクタブルテーブルに対してDELETEクエリを発行すると、テーブルの任意の行が痕跡なく削除される可能性があります。

それを解決するために、コレクタブルテーブルのすべてのDELETEクエリの前に実行されるトリガーを作成します。このトリガーの目的は、削除が行われる前に削除されるオブジェクトのコピーをアーカイブテーブルに保存することです。

以下のコマンドを実行してください。

1. CREATE TRIGGER archive_before_delete BEFORE DELETE
2. ON collectibles
3. FOR EACH ROW
4. INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

トリガーは「archive_before_delete」という名前であり、collectiblesテーブル上のDELETEクエリの前に発生します。削除される各行に対して、INSERT文が実行されます。そのINSERT文は、削除対象の古いレコード、つまりOLDレコードから取得したデータ値でcollectibles_archiveテーブルに新しい行を挿入します。OLD.nameはname列として、OLD.valueはvalue列として使用されます。

データベースはトリガーの作成が確認されます。 (Dētabēsu wa torigā no sakusei ga kakunin sa remasu.)

Query OK, 0 row affected (0.009 sec)

トリガーが設定された状態で、メインのコレクションテーブルからコレクションアイテムを削除してみてください。

1. DELETE FROM collectibles WHERE name = ‘SPACESHIP MODEL‘;

出力は、クエリが正常に実行されたことを確認しています。

Query OK, 1 row affected (0.004 sec)

今、全ての収集品をリストアップしてください。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます：

+—————-+——-+ | name | value | +—————-+——-+ | AIRCRAFT MODEL | 10.00 | +—————-+——-+ 1 row in set (0.000 sec)

現在は航空機のモデルのみが残っています。宇宙船のモデルは削除され、もはやテーブルに存在しません。ただし、以前に作成されたトリガーによって、この削除は収集品アーカイブテーブルに登録されるはずです。それを確認しましょう。

もう一つのクエリを実行してください。 (Mō hitotsu no kueri o jikkō shite kudasai.)

1. SELECT * FROM collectibles_archive;

以下の結果が画面に表示されます。

+—————–+——-+———————+ | name | value | removed_on | +—————–+——-+———————+ | SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 | +—————–+——-+———————+ 1 row in set (0.000 sec)

トリガーによって、削除は自動的にそのテーブルに記録されました。名前列と値列は、削除された行のデータで埋められました。定義されたトリガーによって明示的に設定されていないため、3番目の列であるremoved_onは、テーブル作成時に決定されたデフォルト値を取ります。このデフォルト値は、新しい行が作成される日付です。そのため、トリガーを使用して追加されるすべてのエントリーは、常に削除日付で注釈されます。

このトリガーを設定することで、すべてのDELETEクエリによって所有していたコレクタブルに関する情報がcollectibles_archiveにログエントリとして残されることが保証されます。

次のセクションでは、トリガー文の後に実行されるトリガーを使用して、全てのコレクタブルに基づいて集計された値でサマリーテーブルを更新します。

データの操作後にアクションを実行するために、AFTER INSERT、AFTER UPDATE、AFTER DELETE トリガーを使用します。

過去の2つのセクションでは、メインのステートメントの前に実行されるトリガーを使用して、データベースの更新前の元のデータに基づいて操作を行いました。このセクションでは、意図したステートメントの後に実行されるトリガーを使用して、常に最新のカウントとすべてのコレクタブルの蓄積価値を更新します。これにより、サマリーテーブルのデータがデータベースの現在の状態を反映していることが確認できます。

まず、collectibles_statsテーブルを調べてみてください。 (Mazu, collectibles_statsテーブルをしらべてみてください。)

1. SELECT * FROM collectibles_stats;

このテーブルにまだ情報を追加していないため、所持しているコレクションアイテムの数は0であり、そのため、評価値もNULLです。

+——-+——-+ | count | value | +——-+——-+ | 0 | NULL | +——-+——-+ 1 row in set (0.000 sec)

このテーブルにはトリガーが存在しないため、以前に発行されたコレクションの挿入や更新のクエリはこのテーブルには影響を与えませんでした。

collectibles_statsテーブル内の1行の値を設定して、コレクションの個数と総価値に関する最新情報を表示することが目標です。INSERT、UPDATE、またはDELETE操作の後にテーブルの内容が更新されるようにしたいです。

それは、対応するクエリの後に実行される3つの別々のトリガーを作成することで行えます。最初に、AFTER INSERTトリガーを作成してください。

1. CREATE TRIGGER stats_after_insert AFTER INSERT
2. ON collectibles
3. FOR EACH ROW
4. UPDATE collectibles_stats
5. SET count = (
6. SELECT COUNT(name) FROM collectibles
7. ), value = (
8. SELECT SUM(value) FROM collectibles
9. );

トリガーは「stats_after_insert」という名前であり、collectiblesテーブルへの全てのINSERTクエリーの後に実行され、トリガー本文のUPDATEステートメントが実行されます。このUPDATEクエリーはcollectibles_statsを更新し、count列とvalue列にネストされたクエリーから返された値をセットします。

• SELECT COUNT(name) FROM collectibles will get the collectibles count.
• SELECT SUM(value) FROM collectibles will get the total worth of all collectibles.

データベースはトリガーの作成を確認します。 (The database will confirm the creation of the trigger.)

Query OK, 0 row affected (0.009 sec)

では、以前削除された宇宙船の模型を収集品のテーブルに再挿入して、要約のテーブルが正しく更新されるかどうかを確認してみてください。

1. INSERT INTO collectibles VALUES (‘spaceship model’, 15.00);

データベースは以下の成功メッセージを表示します。

Query OK, 1 row affected (0.009 sec)

「所有しているコレクションアイテムをすべてリストアップするには、実行してください。」

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます。

+—————–+——-+ | name | value | +—————–+——-+ | AIRCRAFT MODEL | 10.00 | | SPACESHIP MODEL | 15.00 | +—————–+——-+ 2 rows in set (0.000 sec)

合計25.00ドル分の貴重なコレクターアイテムが2つあります。新たに挿入されたアイテムの後に、以下のクエリを実行して要約テーブルを調べてください。

1. SELECT * FROM collectibles_stats;

今回の要約テーブルでは、所有するコレクターアイテムの数が2で、累積価値が25.00となります。これは前回の出力と一致しています。

+——-+——-+ | count | value | +——-+——-+ | 2 | 25.00 | +——-+——-+ 1 row in set (0.000 sec)

INSERTクエリの後にstats_after_insertトリガーが実行され、collectibles_statsテーブルが現在のデータ（数と値）でコレクションに関する統計を更新します。統計は、最後の挿入だけでなく、コレクション全体の内容について収集されます。コレクションには現在、航空機と宇宙船のモデルの2つのアイテムが含まれているため、サマリーテーブルには2つのアイテムとその合計値がリストされます。この時点で、collectiblesテーブルに新しいコレクションアイテムを追加すると、正しい値でサマリーテーブルが更新されます。

既存のアイテムを更新したり、コレクションアイテムを削除しても、概要には全く影響を与えません。その隙間を埋めるために、同じ操作を行うが異なるイベントによってトリガーされる2つの追加トリガーを作成します。

1. CREATE TRIGGER stats_after_update AFTER UPDATE
2. ON collectibles
3. FOR EACH ROW
4. UPDATE collectibles_stats
5. SET count = (
6. SELECT COUNT(name) FROM collectibles
7. ), value = (
8. SELECT SUM(value) FROM collectibles
9. );
11. CREATE TRIGGER stats_after_delete AFTER DELETE
12. ON collectibles
13. FOR EACH ROW
14. UPDATE collectibles_stats
15. SET count = (
16. SELECT COUNT(name) FROM collectibles
17. ), value = (
18. SELECT SUM(value) FROM collectibles
19. );

あなたは現在、stats_after_updateとstats_after_deleteという2つの新しいトリガーを作成しました。これらのトリガーは、collectiblesテーブルでUPDATEまたはDELETEステートメントを実行する度に、collectible_statsテーブル上で実行されます。

それらのトリガーの成功した作成により、以下の出力が表示されます。

Query OK, 0 row affected (0.009 sec)

今、コレクタブルの価格を更新してください。

1. UPDATE collectibles SET value = 25.00 WHERE name = ‘AIRCRAFT MODEL’;

WHERE句は名前によって更新対象の行をフィルタリングし、SET句は値を25.00に変更します。

出力は、そのステートメントが1行だけ変更されたことを確認しています。

Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0

アップデート後には、改めて要約テーブルの内容を確認してください。

1. SELECT * FROM collectibles_stats;

現在の値は40.00となっており、これがアップデート後の正しい値です。

+——-+——-+ | count | value | +——-+——-+ | 2 | 40.00 | +——-+——-+ 1 row in set (0.000 sec)

最後のステップは、要約テーブルがコレクションの削除を正しく反映するかどうかを確認することです。次のステートメントで航空機のモデルを削除してみてください。

1. DELETE FROM collectibles WHERE name = ‘AIRCRAFT MODEL’;

次の出力は、クエリが正常に実行されたことを確認しています。

Query OK, 1 row affected (0.004 sec)

今、全てのコレクターアイテムをリストにしてください。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます：

+—————–+——-+ | name | value | +—————–+——-+ | SPACESHIP MODEL | 15.00 | +—————–+——-+ 1 row in set (0.000 sec)

現在、宇宙船の模型のみが残っています。次に、要約テーブルの値を確認してください。

1. SELECT * FROM collectibles_stats;

以下の出力が表示されます：

+——-+——-+ | count | value | +——-+——-+ | 1 | 15.00 | +——-+——-+ 1 row in set (0.000 sec)

現在、カウントの列にはメインテーブルにあるコレクタブルが1つだけ表示されます。合計の価値は15.00で、SPACESHIP MODELの価値と一致しています。

INSERT、UPDATE、DELETEクエリの後、これらの3つのトリガーは協調して動作し、概要テーブルと全てのコレクションリストを同期させます。

次のセクションでは、データベース上の既存のトリガーを操作する方法について学びます。

トリガーの一覧表示と削除

前のセクションでは、新しいトリガーを作成しました。トリガーはデータベース上で定義された名前付きオブジェクトであり、テーブルと同様に必要に応じてリストアップしたり操作したりすることもできます。

すべてのトリガーをリストアップするには、SHOW TRIGGERS ステートメントを実行してください。

1. SHOW TRIGGERS;

出力には、トリガーのすべての要素が含まれます。それには、トリガーの名前、発生イベントとその実行前または実行後の時間、そしてトリガー本体のステートメントやその他の詳細な定義が含まれます。

+————————-+——–+————–+——–(…)+——–+(…) | Trigger | Event | Table | Statement | Timing |(…) +————————-+——–+————–+——–(…)+——–+(…) | uppercase_before_insert | INSERT | collectibles | SET (…)| BEFORE |(…) | stats_after_insert | INSERT | collectibles | UPDATE (…)| AFTER |(…) | uppercase_before_update | UPDATE | collectibles | SET (…)| BEFORE |(…) | stats_after_update | UPDATE | collectibles | UPDATE (…)| AFTER |(…) | archive_before_delete | DELETE | collectibles | INSERT (…)| BEFORE |(…) | stats_after_delete | DELETE | collectibles | UPDATE (…)| AFTER |(…) +————————-+——–+————–+——–(…)+——–+(…) 6 rows in set (0.001 sec)

既存のトリガーを削除するには、DROP TRIGGERSQL文を使用できます。おそらく、コレクション名に大文字を強制する必要がなくなったため、uppercase_before_insertとuppercase_before_updateはもう必要ありません。以下のコマンドを実行して、これらの2つのトリガーを削除してください。

1. DROP TRIGGER uppercase_before_insert;
2. DROP TRIGGER uppercase_before_update;

両方のコマンドに対し、MySQLは成功メッセージを返します。

Query OK, 0 rows affected (0.004 sec)

今、2つのトリガーがなくなったので、小文字の新しい収集品を追加しましょう。

1. INSERT INTO collectibles VALUES (‘ship model‘, 10.00);

データベースによって挿入が確認されます。

Query OK, 1 row affected (0.009 sec)

SELECTクエリを実行することで、行が挿入されたことを確認できます。

1. SELECT * FROM collectibles;

以下の出力は画面に表示されます。

+—————–+——-+ | name | value | +—————–+——-+ | SPACESHIP MODEL | 15.00 | | ship model | 10.00 | +—————–+——-+ 2 rows in set (0.000 sec)

新たに追加されたコレクションは小文字で表示されます。元の出力と同じ名前であることから、以前の文字の大文字変換のトリガーが使用されなくなったことを確認しました。

名前を使ってトリガーを一覧表示して削除する方法がわかりました。

結論

このガイドに従うことで、SQLトリガーとは何か、そしてMySQLでデータをINSERTとUPDATEのクエリーの前に操作するためにどのように使用するかを学びました。削除された行を別のテーブルにアーカイブするためにBEFORE DELETEトリガーを使用する方法、またはAFTERステートメントトリガーを使用してサマリーを常に最新の状態に保つ方法も学びました。

以下のように日本語で表現することができます：

データの操作や検証の一部をデータベースエンジンにオフロードするために、関数を使用することができます。これにより、データの整合性が保たれ、また、デイリーユーザーからはデータベースの一部の振る舞いを隠すことができます。このチュートリアルでは、その目的でトリガーの基本的な使用方法のみをカバーしました。複数のステートメントで構成される複雑なトリガーを作成し、さらに細かい条件ロジックを使用してアクションを実行することも可能です。その詳細については、MySQLのトリガーに関するドキュメントを参照してください。

SQL言語やそれに関わる概念についてもっと学びたい方は、ぜひ「SQLの使い方シリーズ」の他のガイドをご覧ください。