自動化のための MySQL トリガー

はじめに

この実験では、トリガーを使用して MySQL でタスクを自動化する方法を学びます。この実験では、products テーブルへの挿入操作を別の product_logs テーブルにログするトリガーの作成に焦点を当てています。

この実験では、MySQL サーバーへの接続、mydatabase データベースの作成、および products テーブルと product_logs テーブルの定義を行う手順を案内します。次に、products テーブルに新しい製品が追加されるたびに自動的に product_logs テーブルにレコードを挿入する products_after_insert という名前のトリガーを作成します。その後の手順では、トリガーのテスト、ロジックの更新、最後にトリガーの削除を行います。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("Table Removal") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("Table Modification") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("Data Deletion") subgraph Lab Skills mysql/create_database -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/create_table -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/drop_table -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/alter_table -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/select -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/insert -.-> lab-550919{{"自動化のための MySQL トリガー"}} mysql/delete -.-> lab-550919{{"自動化のための MySQL トリガー"}} end

挿入操作をログするトリガーの作成

このステップでは、MySQL で挿入操作を別のテーブルにログするトリガーを作成する方法を学びます。トリガーは、INSERT、UPDATE、または DELETE などのテーブル上の特定のイベントに応じて自動的に実行される特別なストアドプログラムです。これは、変更の監査、ビジネスルールの適用、またはデータの変更に基づく他のアクションの実行に役立ちます。

まず、MySQL サーバーに接続しましょう。ターミナルを開き、次のコマンドを実行します。

mysql -u root -p

root パスワードの入力を求められます。パスワードを入力し、Enter キーを押します。

次に、mydatabase という名前のデータベースがまだ存在しない場合は作成します。

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

では、products という名前のテーブルを作成しましょう。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

挿入操作をログするためのテーブルも必要です。product_logs という名前のテーブルを作成しましょう。

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(255),
    product_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

では、トリガーを作成しましょう。このトリガーは、products テーブルへの各挿入操作の後に実行されます。トリガーは、挿入された製品の詳細を含むレコードを product_logs テーブルに挿入します。

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price)
    VALUES (NEW.id, NEW.name, NEW.price);
END;

トリガーの定義を分解してみましょう。

CREATE TRIGGER products_after_insert: この文は、products_after_insert という名前のトリガーを作成します。
AFTER INSERT ON products: これは、トリガーが products テーブルへの各挿入操作の後に実行されることを指定します。
FOR EACH ROW: これは、トリガーが products テーブルに挿入される各行に対して実行されることを示します。
BEGIN ... END: このブロックには、トリガーがアクティブ化されたときに実行される SQL 文が含まれています。
NEW.id、NEW.name、NEW.price: これらは、新しく挿入された行の id、name、および price 列の値を参照します。

では、トリガーをテストするために、products テーブルにいくつかのデータを挿入しましょう。

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

最後に、トリガーがログレコードを挿入したかどうかを確認するために、product_logs テーブルを確認しましょう。

SELECT * FROM product_logs;

products テーブルに対して行った 2 つの挿入操作に対応する 2 つのレコードが product_logs テーブルに表示されるはずです。

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
+--------+------------+--------------+---------------+---------------------+
2 rows in set (0.00 sec)

トリガーをテストするためのデータ挿入

このステップでは、products テーブルにデータを挿入します。これにより、前のステップで作成した products_after_insert トリガーが起動します。これを通じて、トリガーが正しく動作し、期待されるデータが product_logs テーブルに挿入されていることを確認できます。

まず、MySQL サーバーに接続し、mydatabase データベースを使用していることを確認します。まだ接続していない場合は、ターミナルを開き、次のコマンドを実行します。

mysql -u root -p

パスワードの入力を求められたら入力します。

USE mydatabase;

では、products テーブルにいくつかのデータを挿入しましょう。異なる名前と価格の 3 つの新しい製品を挿入します。

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);
INSERT INTO products (name, price) VALUES ('Monitor', 300.00);
INSERT INTO products (name, price) VALUES ('Headphones', 100.00);

これらの INSERT 文は、products テーブルに 3 つの新しい行を追加します。作成したトリガーのため、これらの挿入操作のそれぞれは、product_logs テーブルにも新しい行が追加される結果となります。

トリガーが正しく動作していることを確認するために、product_logs テーブルをクエリし、新しいレコードが挿入されているかどうかを確認しましょう。

SELECT * FROM product_logs;

product_logs テーブルに、新しく挿入された製品に対応するレコードが表示されるはずです。出力は次のようになります（log_id と created_at の値は異なります）。

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 |
+--------+------------+--------------+---------------+---------------------+
5 rows in set (0.00 sec)

product_logs テーブルに新しいレコードが表示されれば、トリガーが正しく動作していることを意味します。product_id は products テーブル内の対応する製品の id と一致し、product_name と product_price も挿入した値と一致するはずです。

トリガーロジックの更新

このステップでは、既存のトリガー products_after_insert を変更して、product_logs テーブルに追加情報を含めます。具体的には、product_logs テーブルに operation_time という名前の新しい列に現在のタイムスタンプを追加します。

まず、product_logs テーブルに operation_time 列を追加する必要があります。MySQL サーバーに接続し、mydatabase データベースを使用します。

mysql -u root -p

パスワードの入力を求められたら入力します。

USE mydatabase;

では、product_logs テーブルに operation_time 列を追加しましょう。

ALTER TABLE product_logs ADD COLUMN operation_time TIMESTAMP;

次に、更新されたロジックでトリガーを再作成する前に、既存のトリガー products_after_insert を削除する必要があります。

DROP TRIGGER IF EXISTS products_after_insert;

では、operation_time を含む更新されたロジックでトリガーを再作成しましょう。トリガーが実行されたときに operation_time を現在のタイムスタンプに設定します。

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price, operation_time)
    VALUES (NEW.id, NEW.name, NEW.price, CURRENT_TIMESTAMP);
END;

この更新されたトリガーでは、以下のことを行っています。

INSERT 文の product_logs テーブルの列リストに operation_time を追加しました。
operation_time の値を CURRENT_TIMESTAMP に設定しました。これにより、トリガーが実行されたときの現在の日付と時刻が挿入されます。

では、更新されたトリガーをテストするために、products テーブルにいくつかの新しいデータを挿入しましょう。

INSERT INTO products (name, price) VALUES ('Printer', 200.00);

最後に、トリガーが operation_time を含むログレコードを挿入したかどうかを確認するために、product_logs テーブルを確認しましょう。

SELECT * FROM product_logs;

product_logs テーブルに、operation_time 列に現在のタイムスタンプが入力された新しいレコードが表示されるはずです。出力は次のようになります（log_id、created_at、および operation_time の値は異なります）。

+--------+------------+--------------+---------------+---------------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          | operation_time      |
+--------+------------+--------------+---------------+---------------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 | NULL                |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 | NULL                |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 | NULL                |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 | NULL                |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 | NULL                |
|      6 |          6 | Printer      |        200.00 | 2024-10-27 12:10:00 | 2024-10-27 12:10:00 |
+--------+------------+--------------+---------------+---------------------+---------------------+
6 rows in set (0.00 sec)

新しいエントリの operation_time は入力されていますが、古いエントリは NULL です。これは、列が追加され、トリガーが更新される前に挿入されたためです。

トリガーの削除

このステップでは、前のステップで作成および更新したトリガー products_after_insert を削除します。トリガーを削除すると、それがデータベースから削除されるため、関連するイベントが発生しても実行されなくなります。

mysql -u root -p

パスワードの入力を求められたら入力します。

USE mydatabase;

トリガーを削除するには、次の SQL コマンドを使用します。

DROP TRIGGER IF EXISTS products_after_insert;

DROP TRIGGER 文は、指定されたトリガーをデータベースから削除します。IF EXISTS 句は、トリガーが存在しない場合にコマンドがエラーを返さないようにします。

トリガーが削除されたことを確認するには、SHOW TRIGGERS コマンドを使用できます。

SHOW TRIGGERS LIKE 'products_after_insert';

トリガーが正常に削除された場合、SHOW TRIGGERS コマンドは何も結果を返さないはずです。

トリガーが実際に削除され、新しい挿入操作がもはやログに記録されないことを確認するために、products テーブルにもう 1 つのレコードを挿入しましょう。

INSERT INTO products (name, price) VALUES ('Webcam', 50.00);

では、product_logs テーブルを確認しましょう。トリガーが正常に削除された場合、新しい挿入操作はログに記録されないはずです。

SELECT * FROM product_logs;

product_logs テーブルに 'Webcam' の新しいレコードが表示されないはずです。これにより、トリガーが正常に削除されたことが確認できます。

まとめ

この実験では、挿入操作を自動的に別のテーブルにログする MySQL トリガーの作成方法を学びました。これには、MySQL サーバーへの接続、データベースと 2 つのテーブル（products と product_logs）の作成が含まれていました。products テーブルは商品情報を格納し、product_logs は各挿入された商品の詳細を記録します。

このステップの核心は、products テーブルへの各挿入操作の後に実行される products_after_insert という名前のトリガーを定義することでした。このトリガーは、product_logs テーブルに新しいレコードを挿入し、NEW キーワードを使用して新しく挿入された商品の id、name、および price を取得します。