Ubuntu18.04でMySQLデータベーストリガーを管理および使用する方法

著者は、 Apache Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。

序章

MySQL では、 trigger は、INSERT、DELETE、またはUPDATE中に自動的に呼び出されるユーザー定義のSQLコマンドです。手術。トリガーコードはテーブルに関連付けられており、テーブルが削除されると破棄されます。トリガーアクション時間を指定し、定義されたデータベースイベントの前または後にアクティブにするかどうかを設定できます。

トリガーにはいくつかの利点があります。たとえば、これらを使用して、INSERTステートメント中に派生列の値を生成できます。もう1つのユースケースは、トリガーを使用してレコードを複数の関連テーブルに保存できる参照整合性を適用することです。その他の利点には、監査テーブルへのユーザーアクションのログ記録や、単一障害点を防ぐための冗長性を目的としたさまざまなデータベーススキーマ間でのデータのライブコピーが含まれます。

トリガーを使用して、検証ルールをデータベースレベルで維持することもできます。これは、ビジネスロジックを壊すことなく、複数のアプリケーション間でデータソースを共有するのに役立ちます。これにより、データベースサーバーへのラウンドトリップが大幅に削減されるため、アプリケーションの応答時間が改善されます。データベースサーバーはトリガーを実行するため、RAMやCPUなどの改善されたサーバーリソースを利用できます。

このチュートリアルでは、MySQLデータベースでさまざまなタイプのトリガーを作成、使用、および削除します。

前提条件

始める前に、次のものがあることを確認してください。

Ubuntu 18.04を使用した初期サーバーセットアップに従ってセットアップされた1つのUbuntu18.04サーバー（sudo非rootユーザーを含む）。
次の方法でサーバー上で実行されているMySQLデータベース：Ubuntu18.04にMySQLをインストールする方法
MySQLデータベースのrootユーザーアカウントのクレデンシャル。

ステップ1—サンプルデータベースの作成

このステップでは、MySQLトリガーがどのように機能するかを示すために、複数のテーブルを含むサンプルの顧客データベースを作成します。

MySQLクエリの詳細を理解するには、MySQLのクエリの概要をお読みください。

まず、rootとしてMySQLサーバーにログインします。

mysql -u root -p

プロンプトが表示されたらMySQLルートパスワードを入力し、ENTERを押して続行します。 mysql>プロンプトが表示されたら、次のコマンドを実行してtest_dbデータベースを作成します。

Create database test_db;

OutputQuery OK, 1 row affected (0.00 sec)

次に、次のコマンドでtest_dbに切り替えます。

Use test_db;

OutputDatabase changed

まず、customersテーブルを作成します。このテーブルには、customer_id、customer_name、およびlevelを含む顧客のレコードが保持されます。 BASICとVIPの2つの顧客レベルがあります。

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

次に、customersテーブルにいくつかのレコードを追加します。これを行うには、次のコマンドを1つずつ実行します。

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

各INSERTコマンドを実行すると、次の出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

サンプルレコードが正常に挿入されたことを確認するには、SELECTコマンドを実行します。

Select * from customers;

Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

また、customersアカウントに関する関連情報を保持するための別のテーブルを作成します。テーブルには、customer_idフィールドとstatus_notesフィールドがあります。

次のコマンドを実行します。

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

次に、salesテーブルを作成します。このテーブルには、customer_id列を介してさまざまな顧客に関連する販売データが保持されます。

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

トリガーをテストしながら、次の手順でsalesデータにサンプルデータを追加します。次に、audit_logテーブルを作成して、ステップ5でAFTER UPDATEトリガーを実装したときにsalesテーブルに加えられた更新をログに記録します。

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.02 sec)

test_dbデータベースと4つのテーブルが配置されたら、データベース内のさまざまなMySQLトリガーの操作に進みます。

ステップ2—挿入前トリガーの作成

このステップでは、このロジックを適用する前にMySQLトリガーの構文を調べて、データがsalesに挿入されたときにsales_amountフィールドを検証するBEFORE INSERTトリガーを作成します。テーブル。

MySQLトリガーを作成するための一般的な構文を次の例に示します。

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

トリガーの構造は次のとおりです。

DELIMITER //：デフォルトのMySQL区切り文字は;です。MySQLがカスタム区切り文字に到達するまで次の行を1つのコマンドとして扱うには、これを別の区切り文字に変更する必要があります。この例では、区切り文字が//に変更され、最後に;区切り文字が再定義されています。

[TRIGGER_NAME]：トリガーには名前が必要であり、ここに値を含めます。

[TRIGGER TIME]：トリガーはさまざまなタイミングで呼び出すことができます。 MySQLでは、データベース操作の前または後にトリガーを開始するかどうかを定義できます。

[TRIGGER EVENT]：トリガーは、INSERT、UPDATE、およびDELETE操作によってのみ呼び出されます。ここでは、達成したいことに応じて任意の値を使用できます。

[TABLE]：MySQLデータベースで作成するトリガーはすべて、テーブルに関連付ける必要があります。

FOR EACH ROW：このステートメントは、トリガーが影響するすべての行に対してトリガーコードを実行するようにMySQLに指示します。

[TRIGGER BODY]：トリガーが呼び出されたときに実行されるコードは、トリガー本体と呼ばれます。これは、単一のSQLステートメントまたは複数のコマンドにすることができます。トリガー本体で複数のSQLステートメントを実行している場合は、それらをBEGIN...ENDブロックでラップする必要があることに注意してください。

注：トリガー本体を作成するときに、OLDおよびNEWキーワードを使用して、INSERT中に入力された新旧の列値にアクセスできます。 UPDATE、およびDELETE操作。 DELETEトリガーでは、OLDキーワードのみを使用できます（これはステップ4で使用します）。

次に、最初のBEFORE INSERTトリガーを作成します。このトリガーはsalesテーブルに関連付けられ、sales_amountを検証するためにレコードが挿入される前に呼び出されます。トリガーの機能は、販売テーブルに挿入されているsales_amountが10000より大きいかどうかを確認し、これがtrueと評価された場合にエラーを発生させることです。

MySQLサーバーにログインしていることを確認してください。次に、次のMySQLコマンドを1つずつ入力します。

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

IF...THEN...END IFステートメントを使用して、INSERTステートメント中に供給された量が範囲内にあるかどうかを評価しています。トリガーは、NEWキーワードを使用して、提供されている新しいsales_amount値を抽出できます。

一般的なエラーメッセージを表示するには、次の行を使用してエラーについてユーザーに通知します。

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

次に、sales_amountが11000のレコードをsalesテーブルに挿入して、トリガーが操作を停止するかどうかを確認します。

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

このエラーは、トリガーコードが期待どおりに機能していることを示しています。

次に、値が7500の新しいレコードを試して、コマンドが成功するかどうかを確認します。

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

値が推奨範囲内にあるため、次の出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

データが挿入されたことを確認するには、次のコマンドを実行します。

Select * from sales;

出力は、データがテーブルにあることを確認します。

Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

このステップでは、データベースに挿入する前にデータを検証するトリガーをテストしました。

次に、AFTER INSERTトリガーを使用して、関連情報をさまざまなテーブルに保存します。

ステップ3—挿入後トリガーの作成

AFTER INSERTトリガーは、レコードがテーブルに正常に挿入されたときに実行されます。この機能を使用して、他のビジネス関連のロジックを自動的に実行できます。たとえば、銀行のアプリケーションでは、AFTER INSERTトリガーは、顧客がローンの返済を終了したときにローン口座を閉鎖できます。トリガーは、トランザクションテーブルに挿入されたすべての支払いを監視し、ローン残高がゼロになるとローンを自動的に閉じることができます。

このステップでは、AFTER INSERTトリガーを使用してcustomer_statusテーブルを操作し、関連する顧客レコードを入力します。

AFTER INSERTトリガーを作成するには、次のコマンドを入力します。

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

OutputQuery OK, 0 rows affected (0.00 sec)

ここでは、新しい顧客レコードがcustomersテーブルに挿入されたら、別のレコードをcustomer_statusテーブルに保存するようにMySQLに指示します。

次に、customersテーブルに新しいレコードを挿入して、トリガーコードが呼び出されることを確認します。

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

OutputQuery OK, 1 row affected (0.01 sec)

レコードが正常に挿入されたので、新しいステータスレコードがcustomer_statusテーブルに挿入されたことを確認します。

Select * from customer_status;

Output+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

出力は、トリガーが正常に実行されたことを確認します。

AFTER INSERTトリガーは、顧客のライフサイクルを監視するのに役立ちます。本番環境では、顧客のアカウントは、アカウントの開設、一時停止、閉鎖など、さまざまな段階を経る場合があります。

次の手順では、UPDATEトリガーを操作します。

ステップ4—更新前のトリガーを作成する

BEFORE UPDATEトリガーは、BEFORE INSERTトリガーに似ています。違いは、呼び出されるタイミングです。 BEFORE UPDATEトリガーを使用して、レコードが更新される前にビジネスロジックをチェックできます。これをテストするには、すでにいくつかのデータを挿入したcustomersテーブルを使用します。

データベースには、顧客向けの2つのレベルがあります。この例では、顧客アカウントがVIPレベルにアップグレードされると、アカウントをBASICレベルにダウングレードすることはできません。このようなルールを適用するには、次のようにUPDATEステートメントの前に実行されるBEFORE UPDATEトリガーを作成します。データベースユーザーが顧客をVIPレベルからBASICレベルにダウングレードしようとすると、ユーザー定義の例外がトリガーされます。

次のSQLコマンドを1つずつ入力して、BEFORE UPDATEトリガーを作成します。

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

OLDキーワードを使用して、UPDATEコマンドの実行時にユーザーが指定しているレベルをキャプチャします。ここでも、IF...THEN...END IFステートメントを使用して、一般的なエラーステートメントをユーザーに通知します。

次に、3のcustomer_idに関連付けられているカスタマーアカウントをダウングレードしようとする次のSQLコマンドを実行します。

Update customers set level='BASIC' where customer_id='3';

SET MESSAGE_TEXTを提供する次の出力が表示されます。

OutputERROR 1644 (45000): A VIP customer can not be downgraded.

BASICレベルのお客様に対して同じコマンドを実行し、アカウントをVIPレベルにアップグレードしようとすると、コマンドは正常に実行されます。

Update customers set level='VIP' where customer_id='1';

OutputRows matched: 1  Changed: 1  Warnings: 0

BEFORE UPDATEトリガーを使用して、ビジネスルールを適用しました。次に、監査ログにAFTER UPDATEトリガーを使用します。

ステップ5—更新後のトリガーを作成する

データベースレコードが正常に更新されると、AFTER UPDATEトリガーが呼び出されます。この動作により、トリガーは監査ログに適したものになります。マルチユーザー環境では、管理者は監査目的で特定のテーブルのレコードを更新したユーザーの履歴を表示したい場合があります。

salesテーブルの更新アクティビティをログに記録するトリガーを作成します。 audit_logテーブルには、salesテーブル、更新のdate、およびnewとoldを更新するMySQLユーザーに関する情報が含まれます。 ]sales_amountの値。

トリガーを作成するには、次のSQLコマンドを実行します。

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

audit_logテーブルに新しいレコードを挿入します。 NEWキーワードを使用して、sales_idと新しいsales_amountの値を取得します。また、監査目的で両方の金額をログに記録するため、OLDキーワードを使用して前のsales_amountを取得します。

コマンドSELECT USER()は、操作を実行している現在のユーザーを取得し、NOW()ステートメントは、MySQLサーバーから現在の日付と時刻の値を取得します。

これで、ユーザーがsalesテーブルのレコードの値を更新しようとすると、log_sales_updatesトリガーがaudit_logテーブルに新しいレコードを挿入します。

5のランダムなsales_idを使用して新しい販売レコードを作成し、それを更新してみましょう。まず、次のように販売レコードを挿入します。

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

OutputQuery OK, 1 row affected (0.00 sec)

次に、レコードを更新します。

Update sales set sales_amount='9000' where sales_id='5';

次の出力が表示されます。

OutputRows matched: 1  Changed: 1  Warnings: 0

次に、次のコマンドを実行して、AFTER UPDATEトリガーがaudit_logテーブルに新しいレコードを登録できたかどうかを確認します。

Select * from audit_log;

トリガーは更新をログに記録しました。出力には、レコードを更新したユーザーに登録された以前のsales_amountおよびnew amountが表示されます。

Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

また、更新が実行された日時もあります。これは監査目的に役立ちます。

次に、DELETEトリガーを使用して、データベースレベルで参照の整合性を強制します。

ステップ6—削除前トリガーの作成

BEFORE DELETEトリガーは、DELETEステートメントがテーブルで実行される前に呼び出しをトリガーします。これらの種類のトリガーは通常、関連するさまざまなテーブルに参照整合性を適用するために使用されます。たとえば、salesテーブルの各レコードは、customersテーブルのcustomer_idに関連しています。データベースユーザーがcustomersテーブルから、salesテーブルに関連レコードがあるレコードを削除した場合、そのレコードに関連付けられている顧客を知る方法はありません。

これを回避するには、BEFORE DELETEトリガーを作成してロジックを適用します。次のSQLコマンドを1つずつ実行します。

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

次に、関連する販売記録を持つ顧客を削除してみます。

Delete from customers where customer_id='2';

その結果、次の出力が表示されます。

OutputERROR 1644 (45000): The customer has a related sales record.

BEFORE DELETEトリガーは、データベース内の関連情報の誤った削除を防ぐことができます。

ただし、状況によっては、特定のレコードに関連付けられているすべてのレコードを、さまざまな関連テーブルから削除したい場合があります。この状況では、AFTER DELETEトリガーを使用します。これは、次のステップでテストします。

ステップ7—削除後のトリガーを作成する

AFTER DELETEトリガーは、レコードが正常に削除されるとアクティブになります。 AFTER DELETEトリガーの使用方法の例は、特定の顧客が受け取る割引レベルが、定義された期間中に行われた販売数によって決定される状況です。顧客のレコードの一部がsalesテーブルから削除された場合、顧客割引レベルをダウングレードする必要があります。

AFTER DELETEトリガーの別の使用法は、ベーステーブルのレコードが削除された後、別のテーブルから関連情報を削除することです。たとえば、関連するcustomer_idの販売レコードがsalesテーブルから削除された場合に、顧客レコードを削除するトリガーを設定します。次のコマンドを実行して、トリガーを作成します。

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

次に、以下を実行して、2のcustomer_idに関連付けられているすべての販売レコードを削除します。

Delete from sales where customer_id='2';

OutputQuery OK, 1 row affected (0.00 sec)

次に、salesテーブルから顧客のレコードがあるかどうかを確認します。

Select * from customers where customer_id='2';

2のcustomer_idに関連付けられた顧客レコードがトリガーによって削除されたため、Empty Set出力を受け取ります。

OutputEmpty set (0.00 sec)

これで、さまざまな形式のトリガーをそれぞれ使用して、特定の機能を実行できました。次に、トリガーが不要になった場合にデータベースからトリガーを削除する方法を説明します。

ステップ8—トリガーを削除する

他のデータベースオブジェクトと同様に、DROPコマンドを使用してトリガーを削除できます。トリガーを削除するための構文は次のとおりです。

Drop trigger [TRIGGER NAME];

たとえば、最後に作成したAFTER DELETEトリガーを削除するには、次のコマンドを実行します。

Drop trigger delete_related_info;

OutputQuery OK, 0 rows affected (0.00 sec)

トリガーを削除する必要があるのは、その構造を再作成する場合です。このような場合、トリガーをドロップして、別のトリガーコマンドを使用して新しいトリガーを再定義できます。

結論

このチュートリアルでは、MySQLデータベースからさまざまな種類のトリガーを作成、使用、および削除しました。サンプルの顧客関連データベースを使用して、データ検証、ビジネスロジックアプリケーション、監査ログ、参照整合性の適用など、さまざまなユースケースのトリガーを実装しました。

MySQLデータベースの使用の詳細については、以下を確認してください。