MySQLトリガーの使用例の詳細

MySQL トリガー構文の詳細:

   トリガーは、特定のテーブル内のデータが挿入、削除、または更新されるときに実行される特別なストアド プロシージャです。データ自体の標準機能よりも高度で複雑なデータ制御機能を提供します。トリガーはプログラムによって呼び出されるのではなく、イベントによってトリガーされます。データが変更されたときにビジネス ルールを自動的に適用します。これは、データ整合性の制約とビジネス ルールを強化するためによく使用されます。トリガーは他のテーブルをクエリし、複製された SQL ステートメントを含めることができます。トリガーは参照整合性を強制するためにも使用できます。トリガーは、チェック制約で定義された制約よりも複雑な制約を適用できます。

(I) CREATE TRIGGER構文

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
トリガーは、テーブルに関連付けられた名前付きデータベース オブジェクトであり、テーブルで特定のイベントが発生したときにアクティブになります。
トリガーは、tbl_name という名前のテーブルに関連付けられています。 tbl_name は永続テーブルを参照する必要があります。トリガーを TEMPORARY テーブルまたはビューに関連付けることはできません。
trigger_time はトリガー プログラムのアクション時間です。トリガーをアクティブにするステートメントの前または後にトリガーを起動することを示すために、BEFORE または AFTER を指定できます。
trigger_event は、トリガーをアクティブにするステートメントのタイプを指定します。 trigger_event は次のいずれかの値になります。

（１）INSERT：INSERT、LOAD DATA、REPLACEなどによってテーブルに新しい行が挿入されたときにトリガーがアクティブになります。
声明。
（2）UPDATE：トリガーは、たとえばUPDATE文によって行が変更されたときにアクティブになります。
(3).DELETE: トリガーは、たとえばDELETEステートメントやREPLACEステートメントによってテーブルから行が削除されたときにアクティブになります。

trigger_event は、テーブル操作としてトリガーをアクティブ化する SQL ステートメントとはあまり似ていないことに注意することが重要です。たとえば、INSERT の BEFORE トリガーは、INSERT ステートメントだけでなく、LOAD DATA ステートメントによってもアクティブ化できます。混乱を招く可能性がある例の 1 つは、INSERT INTO .. ​​ON DUPLICATE UPDATE ... 構文です。行に重複キーがあるかどうかに応じて、各行に対して BEFORE INSERT トリガーがアクティブになり、その後に AFTER INSERT トリガー、または BEFORE UPDATE トリガーと AFTER UPDATE トリガーがアクティブになります。

特定のテーブルに対して、同じトリガー アクション時間とイベントを持つ 2 つのトリガーが存在することはできません。たとえば、テーブルに対して 2 つの BEFORE UPDATE トリガーを設定することはできません。ただし、BEFORE UPDATE トリガーを 1 つと BEFORE INSERT トリガーを 1 つ、または BEFORE UPDATE トリガーを 1 つと AFTER UPDATE トリガーを 1 つ持つことはできます。 trigger_stmt は、トリガーがアクティブになったときに実行されるステートメントです。複数のステートメントを実行する予定の場合は、BEGIN ... END 複合ステートメント構造を使用します。これにより、ストアドサブプログラムで許可されているのと同じステートメントを使用できます。

(II) DROP TRIGGER構文

DROP TRIGGER [schema_name.] trigger_name はトリガーを削除します。スキーマ名 (schema_name) はオプションです。スキーマを省略すると、トリガーは現在のスキーマから削除されます。

注意: MySQL 5.0.10 より前の MySQL バージョンから MySQL 5.0.10 以降 (すべての MySQL 5.1 バージョンを含む) にアップグレードする場合は、アップグレード前にすべてのトリガーを削除し、アップグレード後に再作成する必要があります。そうしないと、アップグレード後に DROP TRIGGER が機能しなくなります。 DROP TRIGGER ステートメントには SUPER 権限が必要です。

(III) トリガープログラムの使用

このセクションでは、MySQL 5.1 でトリガーを使用する方法と、トリガーの使用に関する制限について説明します。

トリガーは、テーブルに関連付けられた名前付きデータベース オブジェクトであり、テーブルで特定のイベントが発生したときにアクティブになります。トリガーの用途によっては、テーブルに挿入された値をチェックしたり、更新に関係する値に対して計算を実行したりするために使用できます。

トリガーはテーブルに関連付けられており、テーブルに対して INSERT、DELETE、または UPDATE ステートメントが実行されるとアクティブになります。トリガーは、ステートメントの実行前または実行後にアクティブになるように設定できます。たとえば、トリガーは、テーブルから各行が削除される前、または各行が更新された後にアクティブ化できます。トリガーを作成または削除するには、CREATE TRIGGER または DROP TRIGGER ステートメントを使用します。トリガーは、クライアントにデータを返すストアド プロシージャを呼び出すことはできません。また、CALL ステートメント (ストアド プロシージャがパラメーターを通じてトリガーにデータを返すことを可能にする) を使用して動的 SQL を使用することもできません。

トリガーは、START TRANSACTIONなどのトランザクションを明示的または暗黙的に開始または終了するステートメントを使用することはできません。
COMMIT または ROLLBACK。

トリガーの影響を受ける行の列にアクセスするには、OLD および NEW キーワードを使用します (OLD および NEW は大文字と小文字を区別しません)。

INSERT トリガーでは、古い行ではなく、NEW.col_name のみを使用できます。 DELETE トリガーでは、OLD.col_name のみ使用でき、新しい行は使用できません。 UPDATE トリガーでは、OLD.col_name を使用して更新前の行の列を参照し、NEW.col_name を使用して更新後の行の列を参照できます。

OLD で名前が付けられた列は読み取り専用です。参照することはできますが、変更することはできません。 NEW で名前が付けられた列の場合、SELECT 権限があれば参照できます。 BEFORE トリガーでは、UPDATE 権限がある場合は、「SET NEW.col_name = value」を使用して値を変更できます。つまり、トリガーを使用して、新しい行に挿入される値、または行の更新に使用される値を変更できるということです。 BEFORE トリガーでは、AUTO_INCREMENT 列の NEW 値は 0 であり、新しいレコードが実際に挿入されたときに自動的に生成されるシーケンス番号ではありません。

BEGIN ... END 構造を使用すると、複数のステートメントを実行するトリガーを定義できます。 BEGIN ブロックでは、条件文やループなど、ストアド サブルーチンで許可されている他の構文も使用できます。ただし、ストアド サブルーチンの場合と同様に、複数のステートメントを実行するトリガーを定義するときに、mysql プログラムを使用してトリガーを入力する場合は、トリガー定義で文字 ";" を使用できるようにステートメント区切り文字を再定義する必要があります。次の例では、これらの点が実証されています。この例では、各行が更新されるときに使用される新しい値をチェックし、その値を 0 ～ 100 の範囲に変更する UPDATE トリガーが定義されています。行を更新する前に値をチェックする必要があるため、BEFORE トリガーである必要があります。

mysql> 区切り文字 //
mysql> アカウントの更新前にトリガー upd_check を作成します
   -> 各行ごとに
   -> 開始
   -> IF NEW.amount < 0 の場合
   -> SET NEW.amount = 0;
   -> ELSEIF NEW.金額 > 100 THEN
   -> SET NEW.amount = 100;
   -> 終了IF;
   -> 終了;//
mysql> 区切り文字;

より簡単な方法は、ストアド プロシージャを個別に定義し、単純な CALL ステートメントを使用してトリガーからストアド プロシージャを呼び出すことです。この方法は、複数のトリガー内から同じサブルーチンを呼び出す予定がある場合にも役立ちます。トリガーの実行中、MySQL は次のようにエラーを処理します。

（１）BEFOREトリガーが失敗した場合、対応する行に対する操作は実行されません。
（２）AFTERトリガーは、BEFOREトリガー（存在する場合）と行操作が正常に実行された場合にのみ実行されます。
（３）BE​​FOREトリガーまたはAFTERトリガーの実行中にエラーが発生した場合、トリガーを呼び出したステートメント全体が失敗します。
（4）トランザクションテーブルの場合、トリガーが失敗した場合（したがってステートメント全体が失敗した場合）、ステートメントによって実行されたすべての変更がロールバックされます。非トランザクション テーブルの場合、このタイプのロールバックは実行できないため、ステートメントが失敗しても、失敗前に行われた変更は有効なままになります。

例1:

mysql> アカウントテーブルを作成します (acct_num INT、金額 DECIMAL(10,2));
mysql> account の各行に INSERT する前に、トリガー ins_sum を作成します。SET @sum = @sum + NEW.amount;

 

読んでいただきありがとうございます。お役に立てれば幸いです。このサイトをサポートしていただきありがとうございます。