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

序章

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

トリガーにはいくつかの利点があります。 たとえば、それらを使用して、 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

プロンプトが表示されたらMySQLrootパスワードを入力し、 ENTER 続ける。 あなたが見るとき mysql> プロンプトが表示されたら、次のコマンドを実行して作成します test_db データベース：

Create database test_db;

Output
Query OK, 1 row affected (0.00 sec)

次に、に切り替えます test_db と：

Use test_db;

Output
Database changed

まず、を作成します customers テーブル。 このテーブルには、 customer_id, customer_name、 と level. 2つの顧客レベルがあります： BASIC と VIP.

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

Output
Query 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 コマンド：

Output
Query 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;

Output
Query OK, 0 rows affected (0.01 sec)

サンプルデータをに追加します sales トリガーのテスト中の次のステップのデータ。 次に、を作成します audit_log に行われた更新をログに記録するテーブル sales を実装するときのテーブル AFTER UPDATE ステップ5でトリガー：

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;

Output
Query OK, 0 rows affected (0.02 sec)

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

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

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

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 ブロック。

今、あなたはあなたの最初のものを作成します 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 ステートメントはあなたの範囲内です。 トリガーは新しいものを抽出することができます sales_amount を使用して提供される値 NEW キーワード。

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

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');

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

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

次に、値が 7500 コマンドが成功するかどうかを確認するには：

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

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

Output
Query 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 トリガーは、顧客がローンの返済を終えたときにローン口座を閉鎖することができます。 トリガーは、トランザクションテーブルに挿入されたすべての支払いを監視し、ローン残高がゼロになるとローンを自動的に閉じることができます。

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

を作成するには 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 ;

Output
Query OK, 0 rows affected (0.00 sec)

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

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

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

Output
Query 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)

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

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

次の手順では、 UPDATE トリガー。

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

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

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

次の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 一般的なエラーステートメントをユーザーに通知するステートメント。

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

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

次の出力が表示され、 SET MESSAGE_TEXT:

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

同じコマンドを実行した場合 BASIC レベルの顧客、およびアカウントをにアップグレードしてみてください VIP レベルでは、コマンドは正常に実行されます。

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

Output
Rows matched: 1  Changed: 1  Warnings: 0

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

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

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

の更新アクティビティをログに記録するトリガーを作成します sales テーブル。 私たちの audit_log 表には、MySQLユーザーが更新する情報が含まれます。 sales テーブル、 date 更新の、および new と old 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 テーブル。

ランダムで新しい販売記録を作成しましょう sales_id の 5 更新してみてください。 まず、次のように販売レコードを挿入します。

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

Output
Query OK, 1 row affected (0.00 sec)

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

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

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

Output
Rows 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 表はに関連しています customer_id から customers テーブル。 データベースユーザーがレコードを削除した場合 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';

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

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

The 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 ;

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

Delete from sales where customer_id='2';

Output
Query OK, 1 row affected (0.00 sec)

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

Select * from customers where customer_id='2';

あなたは受け取ります Empty Set に関連付けられた顧客レコード以降の出力 customer_id の 2 トリガーによって削除されました：

Output
Empty set (0.00 sec)

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

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

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

Drop trigger [TRIGGER NAME];

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

Drop trigger delete_related_info;

Output
Query OK, 0 rows affected (0.00 sec)

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

結論

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

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

• Ubuntu18.04でクエリキャッシュを使用してMySQLを最適化する方法
• Ubuntu18.04でPHPを使用してMySQLにページネーションを実装する方法
• MySQLの問題をトラブルシューティングする方法