著者は、 Apache Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。
序章
MySQL では、 trigger は、INSERT
、DELETE
、またはUPDATE
の間に自動的に呼び出されるユーザー定義のSQLコマンドです。手術。 トリガーコードはテーブルに関連付けられており、テーブルが削除されると破棄されます。 トリガーアクション時間を指定し、定義されたデータベースイベントの前または後にアクティブにするかどうかを設定できます。
トリガーにはいくつかの利点があります。 たとえば、これらを使用して、INSERT
ステートメント中に派生列の値を生成できます。 別のユースケースは、トリガーを使用してレコードを複数の関連テーブルに保存できる参照整合性を適用することです。 その他の利点には、監査テーブルへのユーザーアクションのログ記録や、単一障害点を防ぐための冗長性を目的としたさまざまなデータベーススキーマ間でのデータのライブコピーが含まれます。
トリガーを使用して、検証ルールをデータベースレベルで維持することもできます。 これは、ビジネスロジックを壊すことなく、複数のアプリケーション間でデータソースを共有するのに役立ちます。 これにより、データベースサーバーへのラウンドトリップが大幅に削減されるため、アプリケーションの応答時間が改善されます。 データベースサーバーはトリガーを実行するため、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データベースの使用の詳細については、以下を確認してください。