はじめに
この実験では、MySQL トリガーを使用してデータベースアクションを自動化する方法を学びます。トリガーは、INSERT、UPDATE、DELETE操作などのテーブルに対する特定のイベントに応答して自動的に実行される格納プログラムです。
アイテム情報を格納するためのproductsテーブルと、変更を監査するためのproduct_logsテーブルを作成します。次に、productsテーブルに新しい製品が挿入されるたびにproduct_logsにログエントリを自動的に追加するトリガーを実装します。この実験では、トリガーのテスト、更新、削除についても説明します。
データベースとテーブルのセットアップ
トリガーを作成する前に、データベースと必要なテーブルが必要です。このステップでは、mydatabaseという名前のデータベースと、製品データを格納するproductsテーブル、監査ログを格納するproduct_logsテーブルの 2 つのテーブルを作成します。
まず、デスクトップからターミナルを開きます。
rootユーザーとして MySQL サーバーに接続します。これは実験環境であるため、パスワードなしで接続するためにsudoを使用できます。
sudo mysql -u root
接続すると、MySQL プロンプト（mysql>）が表示されます。
次に、mydatabaseデータベースを作成し、それに切り替えます。IF NOT EXISTS句は、データベースが既に存在する場合のエラーを防ぎます。
CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;
次に、productsテーブルを作成します。このテーブルは、各製品の ID、名前、価格を格納します。
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,
action_description VARCHAR(255),
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
以下のコマンドを実行して、両方のテーブルが正常に作成されたことを確認できます。
SHOW TABLES;
出力にproductsとproduct_logsテーブルがリストされているはずです。
+--------------------+
| Tables_in_mydatabase |
+--------------------+
| product_logs |
| products |
+--------------------+
2 rows in set (0.00 sec)
これでデータベースとテーブルの準備が整いました。次のステップに進むことができます。
トリガーの作成とテスト
テーブルが準備できたので、トリガーを作成できます。このトリガーは、productsテーブルに新しい行が挿入された後にアクティブになり、対応するエントリをproduct_logsテーブルに追加します。
データベースに切り替えます。
USE mydatabase;
次に、トリガーを作成します。DELIMITERコマンドは、トリガー本体内のセミコロンがCREATE TRIGGERステートメントの終了として解釈されないように、標準の区切り文字（;）を//に変更するために使用されます。
DELIMITER //
CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
INSERT INTO product_logs (product_id, action_description)
VALUES (NEW.id, CONCAT('New product added: ', NEW.name));
END//
DELIMITER ;
このステートメントの内訳を見てみましょう。
CREATE TRIGGER products_after_insert:products_after_insertという名前の新しいトリガーを定義します。AFTER INSERT ON products: トリガーがproductsテーブルに対するINSERTイベントの後に発生することを示します。FOR EACH ROW: トリガーのロジックが挿入された各行に対して実行されることを示します。BEGIN...END: トリガーの SQL ステートメントを囲みます。NEW.id、NEW.name:NEWキーワードは、挿入されたばかりの行を参照します。NEW.idは新しい製品のidであり、NEW.nameはその名前です。
次に、新しい製品を挿入してトリガーをテストしましょう。
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
このINSERT操作により、トリガーがアクティブになったはずです。これを確認するには、product_logsテーブルの内容を確認します。
SELECT * FROM product_logs;
product_logsテーブルには、トリガーによって自動的に作成された 1 つのレコードが表示されるはずです。出力はこれに似たものになります（log_idとlog_timeは異なります）。
+--------+------------+-----------------------------+---------------------+
| log_id | product_id | action_description | log_time |
+--------+------------+-----------------------------+---------------------+
| 1 | 1 | New product added: Laptop | 2024-05-27 10:30:00 |
+--------+------------+-----------------------------+---------------------+
1 row in set (0.00 sec)
これにより、トリガーが正しく機能していることが確認できます。
トリガーロジックの更新
要件は時間とともに変更される可能性があります。たとえば、より詳細な情報をログに記録したい場合があります。このステップでは、トリガーを更新して、新しい製品の価格もログに記録するようにします。
MySQL では、トリガーを直接変更することはできません。既存のトリガーを削除してから、更新されたロジックで新しいトリガーを作成する必要があります。
データベースに切り替えます。
USE mydatabase;
まず、既存のトリガーを削除します。トリガーが既に削除されている場合、IF EXISTS句はエラーを防ぎます。
DROP TRIGGER IF EXISTS products_after_insert;
次に、更新されたロジックでトリガーを再作成します。この新しいバージョンでは、製品の価格がaction_descriptionに含まれます。
DELIMITER //
CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
INSERT INTO product_logs (product_id, action_description)
VALUES (NEW.id, CONCAT('New product added: ', NEW.name, ' with price ', NEW.price));
END//
DELIMITER ;
更新されたトリガーをテストするために、別の製品を挿入します。
INSERT INTO products (name, price) VALUES ('Mouse', 25.00);
次に、product_logsテーブルをクエリして、すべてのログエントリを表示します。
SELECT * FROM product_logs;
出力には、2 つのログエントリが表示されるはずです。「Mouse」のエントリには、説明に価格が含まれており、更新されたトリガーが機能していることを確認できます。
+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description | log_time |
+--------+------------+----------------------------------------------+---------------------+
| 1 | 1 | New product added: Laptop | 2024-05-27 10:30:00 |
| 2 | 2 | New product added: Mouse with price 25.00 | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)
トリガーを正常に更新しました。
トリガーの削除
トリガーが不要になった場合は、不要な処理や潜在的なメンテナンスの問題を回避するために削除する必要があります。この最後のステップでは、products_after_insertトリガーを削除します。
データベースに切り替えます。
USE mydatabase;
トリガーを削除するには、DROP TRIGGERステートメントを使用します。
DROP TRIGGER IF EXISTS products_after_insert;
SHOW TRIGGERSコマンドを実行して、トリガーが削除されたことを確認できます。
SHOW TRIGGERS;
このコマンドは空のセットを生成し、トリガーが削除されたことを確認します。
Empty set (0.00 sec)
確実に確認するために、もう 1 つ製品を挿入します。
INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);
次に、product_logsテーブルを確認します。
SELECT * FROM product_logs;
テーブルには、最初の 2 つのログエントリのみが含まれているはずです。トリガーは挿入前に削除されたため、「Keyboard」製品のログエントリは存在しないはずです。
+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description | log_time |
+--------+------------+----------------------------------------------+---------------------+
| 1 | 1 | New product added: Laptop | 2024-05-27 10:30:00 |
| 2 | 2 | New product added: Mouse with price 25.00 | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)
これにより、トリガーが正常に削除されたことが確認できます。これで MySQL シェルを終了できます。
exit;
まとめ
この実験では、MySQL トリガーを使用した自動化の基本を学びました。データベース環境のセットアップ、テーブルの作成、およびデータ挿入イベントをログに記録するためのトリガーの実装に成功しました。また、トリガーの作成、テスト、更新、削除を含む、トリガーのライフサイクル全体を実践しました。
これらのスキルは、監査証跡の作成、複雑なビジネスルールの強制、データベース内でのデータ整合性の自動維持などのタスクに不可欠です。
