MySQLトリガーの使用と注意すべき点

トリガーについて

実際の開発では、このような状況によく遭遇します。たとえば、情報の追加、削除、変更を行う際には、ログを記録する必要があります。通常のデータベースロジック操作を完了した後、ログテーブルに書き込む必要があるため、2段階の操作になり、より複雑になります。

たとえば、個人の情報を削除する場合、その人の買い物記録、配送先住所、お気に入りなどを削除する必要があります。この継続的な操作ではエラーが発生しやすく、一貫性と整合性が保証されません。このとき、トリガーを使用する必要があります。これにより、大量のビジネスロジックコードを回避できるだけでなく、データの整合性もより確実に確保できます。

トリガーはテーブルに関連付けられたデータベースオブジェクトです。定義された条件が満たされたときにアクションをトリガーし、トリガーで定義された一連のステートメントを実行します。トリガーのこの機能は、アプリケーションがデータベース側でデータの整合性を確保するのに役立ちます。

これは、テーブルイベントに関連付けられた特別なストアドプロシージャであり、テーブルに対して操作 (挿入、削除、更新) が実行されたときにアクティブ化され、実行されます。

トリガーの使用

トリガーを作成する

トリガーを作成するための構文は次のとおりです。

CREATE TRIGGER トリガー名 トリガー時間 トリガーイベント ON t_name FOR EACH ROW トリガーステートメント

例:

trigger_name: トリガー名

tirgger_time: トリガー実行時点、データ操作前 (BEFORE) またはデータ操作後 (AFTER)

trigger_event: トリガーイベント、追加 (INSERT)、削除 (DELETE)、変更 (UPDATE)

t_name: t_nameテーブルにトリガーを作成することを意味します

trigger_stmt: トリガーの本体。SQL 文または BEGIN と END で囲まれた複数の文になります。

トリガーは永続テーブル (Permanent) にのみ作成でき、一時テーブル (Temporary) には作成できません。

FOR EACH ROW固定式。トリガーイベントを満たすレコードに対する操作はトリガーをトリガーすることを示します。

trigger_time には 2 種類、trigger_event には 3 種類あるため、組み合わせは全部で 6 つあります: BEFORE INSERT、BEFORE DELETE、BEFORE UPDATE、AFTER INSERT、AFTER DELETE、AFTER UPDATE

例 (最初に、トリガーがトリガーされたときに値を入力するログテーブルを作成します):

/*まず、トリガーがトリガーされたときに値を入力するログ テーブルを作成します*/
mysql> `TriggerLog` が存在する場合はテーブルを削除します。
クエリは正常です。影響を受けた行は 0 行です

mysql> テーブル `TriggerLog` を作成します
（
  `id` INT auto_increment 主キー、
  `トリガー時間` VARCHAR(30)、
  `トリガーイベント` VARCHAR(30)、
  `メモ` VARCHAR(200)
);
クエリは正常です。影響を受けた行は 0 行です

挿入タイプトリガー:

マイSQL>
/*ここでSQLスクリプトは次のように終了すると宣言されています // */
区切り文字 //
存在する場合はトリガーを削除します。trig_after_insert;
各行の students に INSERT 後にトリガー trig_after_insert を作成します。
始める
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','insert',concat('新しい学生情報、ID:',cast(new.studentid as char))); を挿入します。
終わり //
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*SQL スクリプトの末尾を次のようにリセットします。*/
区切り文字 ;
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*データを挿入*/
学生に(学生名、スコア、クラスID)値('トリガー1'、100、0)を挿入します。
クエリは正常、1 行が影響を受けました


マイSQL>
/*ログ テーブルをクエリして、トリガー書き込みがあるかどうかを確認します*/
`TriggerLog` から * を選択します。
+----+--------------+--------------+------------------------+
| id | トリガー時間 | トリガーイベント | メモ |
+----+--------------+--------------+------------------------+
| 1 | 後 | 挿入 | 新しい学生情報、ID:21 |
+----+--------------+--------------+------------------------+
セット内の1行

更新タイプトリガー:

マイSQL>
/*ここでSQLスクリプトは次のように終了すると宣言されています // */
区切り文字 //
存在する場合はトリガーを削除します trig_after_update;
各行の students の更新後にトリガー trig_after_update を作成します。
始める
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','update',concat('学生情報の更新、ID:',cast(new.studentid as char))); を挿入します。
終わり //
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*SQL スクリプトの末尾を次のようにリセットします。*/
区切り文字 ;
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*データを更新する*/
学生を更新します。studentname='trigger1' のところ、score=99 を設定します。
クエリは正常、1 行が影響を受けました
一致した行: 1 変更された行: 1 警告: 0

マイSQL>
/* 更新時にトリガー書き込みがあるかどうかを確認するためにログ テーブルをクエリします */
`TriggerLog` から * を選択します。
+----+--------------+--------------+----------------------------+
| id | トリガー時間 | トリガーイベント | メモ |
+----+--------------+--------------+----------------------------+
| 1 | 後 | 挿入 | 新しい学生情報、ID:21 |
| 2 | 後 | 更新 | 学生情報を更新、ID:21 |
+----+--------------+---------------+----------------------------+
2行セット

削除タイプのトリガー:

マイSQL>
/*ここでSQLスクリプトは // で終わると宣言されています */
区切り文字 //
存在する場合はトリガーを削除します trig_after_delete;
各行の students に対して、DELETE 後にトリガー trig_after_delete を作成します。
始める
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','update',concat('delete student info,id:',cast(old.studentid as char))) を挿入します。
終わり //
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*SQL スクリプトの末尾を次のようにリセットします。*/
区切り文字 ;
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/* データを削除 */
studentid=21 の students から削除します。
クエリは正常、1 行が影響を受けました

マイSQL>
/*ログを照会して、削除によって書き込みがトリガーされるかどうかを確認します*/
`TriggerLog` から * を選択します。
+----+--------------+---------------+----------------------------+
| id | トリガー時間 | トリガーイベント | メモ |
+----+--------------+--------------+----------------------------+
| 1 | 後 | 挿入 | 新しい学生情報、ID:21 |
| 2 | 後 | 更新 | 学生情報を更新、ID:21 |
| 3 | 後 | 更新 | 学生情報を削除、ID:21 |
+----+--------------+---------------+----------------------------+
3行セット

トリガーを表示

すべてのトリガーを表示

トリガーを表示; --構文

mysql> トリガーを表示します。
+-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+---------------------+--------------------+
| トリガー | イベント | テーブル | ステートメント | タイミング | 作成 | sql_mode | Definer | character_set_client | collation_connection | データベース照合 |
+-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+---------------------+--------------------+
| trig_after_insert | 挿入 | 学生 | 開始
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','insert',concat('新しい学生情報、ID:',cast(new.studentid as char))); を挿入します。
END | AFTER | NULL | STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_update | 更新 | 学生 | 開始
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','update',concat('学生情報の更新、ID:',cast(new.studentid as char))); を挿入します。
END | AFTER | NULL | STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_delete | 削除 | 学生 | 開始
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','update',concat('delete student info,id:',cast(old.studentid as char))) を挿入します。
END | AFTER | NULL | STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+---------------------+--------------------+
3行セット

トリガーの作成ステートメントを表示する

show create trigger trigger_name; --構文

mysql> トリガー trig_after_insert の作成を表示します。
+-------------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| トリガー | sql_mode | SQL オリジナル ステートメント | character_set_client | collation_connection | データベース照合 |
+-------------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| trig_after_insert | STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
始める
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','insert',concat('新しい学生情報、ID:',cast(new.studentid as char))); を挿入します。
終了 | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
セット内の1行

トリガーの削除

トリガートリガ名を削除します。 --構文

mysql> トリガー trig_after_insert を削除します。
クエリは正常です。影響を受けた行は 0 行です
 
mysql> トリガー trig_after_insert の作成を表示します。
1360 - トリガーが存在しません

使用上の注意

新旧の違い

トリガーは、データベース内の各レコード行を対象とします。各データ行には、操作の前後に対応する状態があります。トリガーは、操作前の状態を古いキーワードに保存し、操作後の状態を新しいキーワードに保存します。

 new.cname -- 新しく追加された行 (または変更前の行) のデータの列 old.cname -- 削除された行 (または変更後の行) のデータの列

すべてのトリガーに古いものと新しいものがあるわけではないことに注意してください。

トリガータイプ	新旧の活用
INSERTトリガー	古いものはなく、新しいものだけがあります。新しいとは、追加されるデータ（挿入前）または追加されたデータ（挿入後）を意味します。
更新トリガー	古いものと新しいものがあります。古いものは更新前のデータを表し、新しいものは更新後のデータを表します。
DELETEトリガー	新しいものはなく、古いものだけです。古いものは、削除されようとしているデータ (削除前) または削除されたデータ (削除後) を示します。

実際、トリガーを作成してデータを取得するために、すでに new/old を使用しています。このテーブルに基づいて更新トリガー (trig_after_update) を変更し、変更前と変更後の出力を比較してみましょう。

マイSQL>
/*ここでSQLスクリプトは次のように終了すると宣言されています // */
区切り文字 //
存在する場合はトリガーを削除します trig_after_update;
各行の students の更新後にトリガー trig_after_update を作成します。
始める
 `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) に値 ('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score)) を挿入します。
終わり //
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*SQL スクリプトの末尾を次のようにリセットします。*/
区切り文字 ;
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*成績と名前を更新*/
学生を更新する set score=106,studentname='trigger2' where studentid=17;
クエリは正常、1 行が影響を受けました
一致した行: 1 変更された行: 1 警告: 0

マイSQL>
/*更新前と更新後の値を古い値と新しい値に基づいて比較します*/
`TriggerLog` から * を選択します。
+----+--------------+--------------+--------------------------------------+
| id | トリガー時間 | トリガーイベント | メモ |
+----+--------------+--------------+--------------------------------------+
| 1 | 後 | 挿入 | 新しい学生情報、ID:21 |
| 2 | 後 | 更新 | 学生情報を更新、ID:21 |
| 3 | 後 | 更新 | 学生情報を削除、ID:21 |
| 4 | 更新後 | から:test2,101.00 へ:trigger2,106.00 |
+----+--------------+--------------+--------------------------------------+
4行セット

同じテーブルへの変更をトリガーできません

MySQL トリガーはこのテーブルに対して挿入、更新、または削除操作を実行できません。実行した場合はエラーが報告されます。

マイSQL>
/*ここでSQLスクリプトは次のように終了すると宣言されています // */
区切り文字 //
存在する場合はトリガーを削除します。trig_after_insert;
各行の students に INSERT 後にトリガー trig_after_insert を作成します。
始める
 生徒を更新します。score = score+1 を設定します。ただし、studentid は new.studentid です。
終わり //
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*SQL スクリプトの末尾を次のようにリセットします。*/
区切り文字 ;
クエリは正常です。影響を受けた行は 0 行です

マイSQL>
/*データを挿入した後、このテーブルの変更がトリガーされるため、エラーが報告されます*/
学生に(学生名、スコア、クラスID)値('trigger2'、101、0)を挿入します。
1442 - このストアド関数/トリガーを呼び出したステートメントによって既に使用されているため、ストアド関数/トリガー内のテーブル 'students' を更新できません。

まとめ

1. トリガーは、データベース内の関連テーブルを通じてカスケード変更を実装できます。つまり、データの変更、データ統計、データ複製など、1 つのテーブル内のデータの変更が他のテーブル内のデータに影響します。
2. データのセキュリティを確保し、セキュリティチェックを実行し、データベースを操作するユーザーの権限を制限できます。
3. 複雑なロジックの実装に対して、データ整合性チェックと制約を実行できます。
4. トリガーは必要な場合にのみ使用してください。トリガーに頼りすぎると、データベースの構造に影響を及ぼし、データベースの実行および保守コストが増加します。
5. トリガーは BEFORE トリガーと AFTER トリガーに分かれており、その実行手順は、最初に BEFORE トリガーを実行し、次にビジネススクリプトを実行し、最後に AFTER トリガーを実行するということになります。ステップが失敗すると、それ以上実行されなくなることに注意してください。トランザクションテーブルの場合はロールバックされます。非トランザクションテーブルの場合はロールバックできず、データの不整合が発生する可能性があります。

トリガーの2つの制限

1. トリガーは、クライアントにデータを返すストアドプロシージャを呼び出すことも、CALL ステートメントを使用する動的 SQL ステートメントを使用することもできません。ただし、ストアドプロシージャは、パラメーターを介してトリガーにデータを返すことができます。つまり、ストアドプロシージャまたは関数は、OUT または INOUT タイプのパラメーターを介してトリガーにデータを返すことができますが、データを直接返すプロシージャを呼び出すことはできません。
2. START TRANS-ACTION、COMMIT、ROLLBACK など、トランザクションを明示的または暗黙的に開始または終了するステートメントは、トリガーでは使用できません。

以上がMySQLトリガーの使い方と注意すべき点の詳しい内容です。MySQLトリガーの詳細については、123WORDPRESS.COMの他の関連記事にも注目してください！

以下もご興味があるかもしれません: