MySQLトリガーの例の詳細な説明

MySQL はバージョン 5.0.2 以降でトリガーをサポートしているようです。このブログではトリガーについて紹介します。まず、その概念について説明します。

トリガーとは何か

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

たとえば、現在 [ユーザー テーブル] と [ログ テーブル] の 2 つのテーブルがある場合、ユーザーを作成すると、作成ログをログ テーブルに挿入する必要があります。トリガーを使用しない場合は、これを実現するためにプログラミング言語のロジックを記述する必要があります。ただし、トリガーを定義する場合、トリガーの役割は、ユーザー テーブルにデータを挿入した後、ログ テーブルにログ メッセージを挿入できるようにすることです。もちろん、トリガーは挿入操作に限定されず、変更や削除も実行できます。

トリガーを作成する

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

CREATE TRIGGER トリガー名 トリガー時間 トリガーイベント ON tb_name FOR EACH ROW トリガーステートメント
trigger_name: トリガーの名前 trigger_time: トリガーの時間。BEFORE または AFTER のいずれかになります。
trigger_event: トリガーイベント、INSERT、DELETE、または UPDATE
tb_name: トリガーが作成されるテーブルを示します。trigger_stmt: トリガーの本体。SQL ステートメント、または BEGIN と END で囲まれた複数のステートメントになります。したがって、MySQL は次の 6 種類のトリガーを作成すると言えます。
挿入前、削除前、更新前
挿入後、削除後、更新後

トリガー名パラメータは、作成するトリガーの名前を参照します。

BEFORE および AFTER パラメータは、トリガーがイベントの前または後に実行されるタイミングを指定します。

FOR EACH ROW は、トリガー イベントを満たすレコードに対するすべての操作がトリガーをトリガーすることを意味します。

複数の実行ステートメントを持つトリガーの作成

CREATE TRIGGER トリガー名 BEFORE | AFTER トリガーイベント ON テーブル名 FOR EACH ROW
始める
  ステートメントリストの実行 END

BEGIN と END の間にある実行ステートメント リスト パラメーターは、実行される複数のステートメントを示し、異なるステートメントはセミコロンで区切られます。

ヒント:一般的に、MySQLはデフォルトで実行文の終わりに;を使用しますが、これはトリガーに必要な分岐と競合します。

この問題を解決するには、DELIMITER を使用できます。たとえば、DELIMITER || を使用すると、終了記号が || に変わります。

トリガーが作成された後、DELIMITER ; を使用して終了記号を ; に変更できます。

mysql> 区切り文字 ||
mysql> CREATE TRIGGER デモ BEFORE DELETE
  -> ON users FOR EACH ROW
  -> 開始
  -> INSERT INTO ログ VALUES(NOW());
  -> INSERT INTO ログ VALUES(NOW());
  -> 終了
  -> ||
クエリは正常、影響を受けた行は 0 行 (0.06 秒)

mysql> 区切り文字 ;

上記の文では、先頭に終了記号が||として定義され、中間にトリガーが定義されています。削除操作が条件を満たすと、

BEGIN と END 内のステートメントが実行され、|| で終了します。

最後に、DELIMITER ; を使用して終了記号を復元します。

ティガーイベント:

ロード データ ステートメントは、ファイルの内容をテーブルに挿入します。これは、挿入ステートメントに相当します。置換ステートメントは、一般的に挿入に似ています。ただし、テーブルに主キーまたは一意のインデックスがある場合、挿入されたデータが元の主キーと同じか一意であれば、元のデータは削除され、新しいデータが追加されます。したがって、置換ステートメントを実行することは、削除および挿入ステートメントを実行することと同等になる場合があります。

トリガーは、単一の SQL ステートメントまたは複数の SQL コード ブロックにすることができます。トリガーを作成するにはどうすればよいでしょうか?

DELIMITER $ #文の区切り文字を$に変更します
始める
sql1;
sql2;
...
sqln
終了 $
DELIMITER ; # ステートメント区切り文字を元のセミコロン「;」に戻します

DECLARE var_name var_type [DEFAULT value] #変数を定義し、デフォルト値を指定します
SET var_name = value #変数に値を割り当てる

上記の表に従って、対応するデータを使用するには次の形式を使用できます。

NEW.columnname: 新しく追加された行のデータの列
OLD.columnname: 行内のデータの列を削除します

表は次のようになります。

ユーザーテーブル

テーブル「users」を作成します（
 `id` int(11) 符号なし NOT NULL AUTO_INCREMENT,
 `name` varchar(255) 文字セット utf8mb4 デフォルト NULL,
 `add_time` int(11) デフォルト NULL,
 主キー (`id`)、
 キー `name` (`name`(250)) BTREEの使用
) ENGINE=MyISAM AUTO_INCREMENT=1000001 デフォルトCHARSET=latin1;

ログテーブル:

テーブル `logs` を作成します (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `log` varchar(255) DEFAULT NULL COMMENT 'ログの説明',
 主キー (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ログテーブル';

要件は、ユーザーにデータが挿入されると、ログにログ メッセージが生成されることです。

トリガーを作成します。

区切り文字 $
各行に対してusersにINSERTした後にトリガーuser_logを作成します。
始める
s1 VARCHAR(40) 文字セット utf8 を宣言します。
DECLARE s2 VARCHAR(20) 文字セット utf8;#後に中国語の文字エンコードが文字化けしていることが判明したため、ここで文字セットを設定しました SET s2 = " が作成されます";
SET s1 = CONCAT(NEW.name,s2); #CONCAT 関数は文字列を接続できます INSERT INTO logs(log) values(s1);
終了 $
区切り文字 ;

ここではnavicatを使用します:

トリガー情報を表示するには、SHOW TRIGGERS ステートメントを使用します。

ヒント：

私は直接Navicatを使って作成しました。mysqlフロントを使う場合は名前が異なります。トリガーを削除してMySQLフロントでテストしてみましょう。

drop trigger user_log;#トリガーを削除

MySQLフロントを開きます:

mysql フロントが sql をコンパイルする場合、終了区切り文字を定義する必要はありません。変更された sql は次のように直接実行できます。

#区切り文字 $
各行に対してusersにINSERTした後にトリガーuser_logを作成します。
始める
s1 VARCHAR(40) 文字セット utf8 を宣言します。
s2 VARCHAR(20) 文字セット utf8 を宣言します。
SET s2 = "が作成されます";
SET s1 = CONCAT(NEW.name,s2); #CONCAT 関数は文字列を接続できます INSERT INTO logs(log) values(s1);
終了 #$
#区切り文字;

さらにいくつかの単語を挙げます。

ヒント: SHOW TRIGGERS文は指定されたトリガーを照会できません

トリガーテーブルでトリガー情報を表示する

information_schema.triggers から * を選択します。

結果にはすべてのトリガーの詳細情報が表示されます。同時に、このメソッドは指定されたトリガーの詳細情報も照会できます。

information_schema.triggers から * を選択し、TRIGGER_NAME='user_log' を指定します。

ヒント:すべてのトリガー情報は、information_schemaデータベースのトリガーテーブルに保存されます。

SELECT文を使用してクエリを実行できます。トリガー情報が多すぎる場合は、TRIGGER_NAMEフィールドを使用してクエリを指定するのが最適です。

先頭に戻り、トリガーを作成し、引き続きユーザーにデータを挿入してデータを表示します。

insert into users(name,add_time) values('周伯通',now());

さて、ログテーブルをもう一度確認してみましょう。

上記の例から、users にユーザーの情報を挿入するだけで、ログが logs テーブルに自動的に記録されることがわかります。これがトリガーがもたらす利便性かもしれません。

制限と考慮事項

トリガーには次の 2 つの制限があります。

1. トリガーは、クライアントにデータを返すストアド プロシージャを呼び出すことも、CALL ステートメントを使用する動的 SQL ステートメントを使用することもできません。ただし、ストアド プロシージャは、パラメーターを介してトリガーにデータを返すことができます。つまり、ストアド プロシージャまたは関数は、OUT または INOUT タイプのパラメーターを介してトリガーにデータを返すことができますが、データを直接返すプロシージャを呼び出すことはできません。

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

注意: MySQL トリガーは、BEFORE トリガー、行操作、AFTER トリガーの順序で実行されます。いずれかのステップでエラーが発生した場合、残りの操作は実行されません。トランザクション テーブルでの操作中にエラーが発生した場合、操作はロールバックされます。非トランザクション テーブルで操作を実行すると、ロールバックできず、データが正しくなくなる可能性があります。

要約する

トリガーは行に基づいて起動されるため、削除、追加、または変更操作によってトリガーがアクティブになる可能性があります。したがって、過度に複雑なトリガーを記述したり、トリガーを追加しすぎたりしないでください。これは、データの挿入、変更、または削除に重大な影響を与え、移植性も低下します。したがって、トリガーを設計するときは、この点を考慮する必要があります。

トリガーは、特定のテーブル内のデータが挿入、削除、または変更されたときに実行される特別なストアド プロシージャです。データベース自体の標準機能よりも高度で複雑なデータ制御機能を備えています。

データベース トリガーには次の機能があります。

1. セキュリティ。データベースの値に基づいて、ユーザーにデータベースを操作するための特定の権限を与えることができます。

# 時間に基づいてユーザー操作を制限できます。たとえば、仕事が終わった後や休日にはデータベースのデータを変更できません。

# データベース内のデータに基づいて、株価が一度に 10% 以上上昇しないようにするなど、ユーザー操作を制限できます。

2. 監査。データベース上のユーザー操作を追跡できます。

# ユーザーがデータベースを操作するために使用する監査ステートメント。

# ユーザーによるデータベースの更新を監査テーブルに書き込みます。

3. 複雑なデータ整合性ルールを実装する

# 非標準のデータ整合性チェックと制約を実装します。トリガーはルールよりも複雑な制限を作成できます。ルールとは異なり、トリガーは列またはデータベース オブジェクトを参照できます。たとえば、トリガーにより、マージンを超える先物取引の試みが取り消される可能性があります。

# 変更可能なデフォルト値を提供します。

4. 複雑で非標準のデータベース関連の整合性ルールを実装します。トリガーは、データベース内の関連テーブルに対して連続更新を実行できます。たとえば、auths テーブルの author_code 列の削除トリガーにより、他のテーブル内の一致する行が削除される可能性があります。

# 変更または削除する場合、他のテーブル内の一致する行もカスケード変更または削除します。

# 変更または削除する場合、他のテーブル内の一致する行を NULL 値に設定します。

# 変更または削除する場合、他のテーブル内の一致する行をカスケードでデフォルト値に設定します。

# トリガーは、関連する整合性を破壊する変更を拒否またはロールバックし、データを更新しようとするトランザクションをキャンセルできます。このトリガーは、主キーと一致しない外部キーが挿入されたときにトリガーされます。たとえば、books.author_code 列に挿入トリガーを作成して、新しい値が auths.author_code 列の値と一致しない場合は挿入がロールバックされるようにすることができます。

5. テーブル内のデータをリアルタイムで同期的にコピーします。

6. データ値を自動的に計算し、データ値が特定の要件に達した場合は特定の処理を実行します。例えば、会社の口座の資金が50,000元未満の場合、警告データがすぐに財務担当者に送信されます。

MySQL トリガー例の詳細な説明に関するこの記事はこれで終わりです。より関連性の高い MySQL トリガー例のコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。