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

トリガーとは何か

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

たとえば、現在 [ユーザーテーブル] と [ログテーブル] の 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 ; を使用して終了記号を復元します。

tigger_event：

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

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

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

BEGIN...END ステートメントで変数を定義することもできますが、変数は BEGIN...END ステートメント内でのみ使用できます。

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

NEWとOLDの使用:

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

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元未満の場合、警告データがすぐに財務担当者に送信されます。

どのような業界にいても、2 つのことをうまくやれば十分です。1 つは職業、もう 1 つは人格です。職業はあなたの存在を決定し、人格はあなたのネットワークを決定します。残りは粘り強さであり、親切、プロ意識、誠実さでより多くの信頼を獲得します。

この記事はこれで終わりです。皆さんのお役に立てれば幸いです。また、123WORDPRESS.COM のその他のコンテンツにも注目していただければ幸いです。

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