简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
ホームページ
記事
に質問
コース
特集
ダウンロード
ゲーム
辞書
最近の更新

ホームページ  >  記事  >  データベース  >  mysqlトリガーはどのレベルですか?

mysqlトリガーはどのレベルですか?

青灯夜游
青灯夜游オリジナル
2023-03-30 20:05:161479ブラウズ

Mysql トリガーは行レベルです。 SQL 標準によれば、トリガーは次の 2 つのタイプに分類できます: 1. 行レベルのトリガー、変更されたデータ行ごとに 1 回アクティブ化されます。ステートメントが 100 行のデータを挿入すると、トリガーは 100 回呼び出されます。 . ステートメントレベルのトリガー トリガーはステートメントごとに 1 回アクティブ化され、100 行のデータを挿入するステートメントはトリガーを 1 回だけ呼び出します。 MySQL は行レベルのトリガーのみをサポートし、プリペアドステートメントレベルのトリガーはサポートしません。

mysqlトリガーはどのレベルですか?

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

トリガーの概要

MySQL トリガーは、指定したテーブルに関連付けられたストアド プロシージャで、テーブル内のデータが変更されたとき (追加、更新、削除) に自動的に実行されます。 。データ行を変更するこれらの操作はトリガー イベントと呼ばれます。たとえば、データを挿入する INSERT や LOAD DATA などのステートメントは、挿入トリガーをアクティブ化できます。

SQL 標準によれば、トリガーは行レベル トリガー (行レベル トリガー) とステートメント レベル トリガー (ステートメント レベル トリガー) に分類できます。

  • #行レベルのトリガーは、変更されたデータ行ごとに 1 回アクティブ化されます。ステートメントで 100 行のデータが挿入される場合、トリガーは 100 回呼び出されます。

  • ステートメント レベルのトリガーは、ステートメントごとに 1 回アクティブ化されます。100 行のデータを挿入するステートメントでは、トリガーは 1 回だけ呼び出されます。

  • #MySQL は行レベルのトリガーのみをサポートし、プリペアド ステートメント レベルのトリガーはサポートしません。

mysqlトリガーはどのレベルですか?# さまざまなイベントにより、さまざまなタイプのトリガーがアクティブ化されます。 INSERT イベント トリガーは、INSERT、LOAD DATA、REPLACE ステートメントなどのデータの挿入操作に使用され、UPDATE イベント トリガーは、UPDATE ステートメントなどの更新操作に使用され、DELETE イベント トリガーは、次のような削除操作に使用されます。 DELETE ステートメントと REPLACE ステートメントなど、DROP TABLE ステートメントと TRUNCATE TABLE ステートメントは削除トリガーをアクティブにしません。

さらに、MySQL トリガーは、トリガー イベントの前または後に実行できます。これらは、それぞれ BEFORE トリガーおよび AFTER トリガーと呼ばれます。これら 2 つのトリガー タイミングは、BEFORE INSERT トリガーや AFTER UPDATE トリガーなどのさまざまなトリガー イベントと組み合わせることができます。

MySQL トリガーの利点は次のとおりです。

    テーブル内のデータに対するユーザーの変更操作を記録および監査して、監査機能を実現します;

    • 業務時間外のデータ操作の禁止など、チェック制約よりも複雑な整合性制約を実装します。

    • 次のような特定のビジネス ロジックを実装します。従業員を追加または削除するときに部門内の人数を自動的に更新します。

    • テーブル内のデータをリアルタイムで同期的にレプリケートします。

トリガーは強力ですが、いくつかの欠点もあります。

    トリガーにより、データベース構造が複雑になります。トリガーはアプリケーションからは見えず、デバッグが困難です。

    • トリガーは、より多くのデータベース サーバー リソースを占有する必要があります。データベースによって提供される、null 以外の一意の制約やチェック制約を使用するようにしてください。など;

    • トリガーはパラメーターを受け取ることができず、現在のトリガー オブジェクトに基づいてのみ動作できます。

    • 特別なシナリオでトリガーを使用すると、一定の利便性が得られますが、データベースのパフォーマンスの低下やメンテナンスの問題を避けるために、トリガーに頼りすぎないでください。次にトリガーの管理操作を紹介します。

MySQL でサポートされる 3 つのトリガー実際の使用では、MySQL は INSERT トリガー、UPDATE トリガー、DELETE トリガーの 3 つのトリガーをサポートします。

1) INSERT トリガー

INSERT ステートメントの実行前または後に応答するトリガー。

INSERT トリガーを使用するときは、次の点に注意する必要があります。

    INSERT トリガー コードでは、NEW という名前の仮想テーブルを参照できます (大文字と小文字は区別されません)。 ) 挿入された行にアクセスします。

    • BEFORE INSERT トリガーでは、NEW の値も更新できます。これにより、挿入された値を変更できます (対応する操作権限がある限り)。

    • AUTO_INCREMENT 列の場合、NEW には INSERT が実行される前は値 0 が含まれており、INSERT が実行された後は自動的に生成された新しい値が含まれます。

2) UPDATE トリガー

UPDATE ステートメントの実行前または後に応答するトリガー。

UPDATE トリガーを使用するときは、次の点に注意する必要があります。

    UPDATE トリガー コードでは、NEW という名前の仮想テーブルを参照できます (大文字と小文字は区別されません)。 ) 更新された値にアクセスします。

    • UPDATE トリガー コード内では、OLD という名前の仮想テーブル (大文字と小文字は区別されません) を参照して、UPDATE ステートメントが実行される前の値にアクセスできます。

    • BEFORE UPDATE トリガーでは、NEW の値も更新される可能性があり、これにより、UPDATE ステートメントで使用される値を変更できます (対応する操作権限がある場合に限ります)。 。

  • OLD のすべての値は読み取り専用であり、更新できません。

注: テーブル自体の更新操作をトリガーするようにトリガーが設計されている場合、使用できるのは BEFORE タイプのトリガーのみであり、AFTER タイプのトリガーは許可されません。

3) DELETE トリガー

DELETE ステートメントの実行前または後に応答するトリガー。

DELETE トリガーを使用するときは、次の点に注意する必要があります。

  • DELETE トリガー コードでは、OLD という名前の仮想テーブルを参照できます (大文字と小文字は区別されません)。 ) 削除された行にアクセスします。

  • OLD の値はすべて読み取り専用であり、更新できません。

一般に、トリガーの使用中に、MySQL は次の方法でエラーを処理します。

トランザクション テーブルの場合、トリガー プログラムが失敗し、その結果のステートメント全体が失敗した場合、ステートメントによって実行されたすべての変更がロールバックされます。非トランザクション テーブルの場合、ステートメントが失敗しても、そのようなロールバックは実行できません。失敗しても、失敗前に行われた変更は引き続き有効です。

BEFORE トリガーが失敗した場合、MySQL は対応する行に対して操作を実行しません。

BEFORE または AFTER トリガー プログラムの実行中にエラーが発生した場合、トリガー プログラムを呼び出すステートメント全体が失敗します。

MySQL は、BEFORE トリガーと行操作の両方が正常に実行された場合にのみ AFTER トリガーを実行します。

トリガーの作成

MySQL は CREATE TRIGGRT ステートメントを使用してトリガーを作成します。基本的な構文は次のとおりです: 

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

そのうち、trigger_name はトリガーの名前です。BEFORE と AFTER はトリガーのトリガー時刻を指定するために使用されます。INSERT、UPDATE、および DELETE はトリガーとなるイベントのタイプを定義するために使用されます。table_name は関連付けられたテーブルの名前です。トリガー (一時テーブルまたはビューにすることはできません) FOR EACH ROW これが行レベルのトリガーであることを示します;trigger_body はトリガーによって実行される特定のステートメントです。

たとえば、従業員の給与は重要な情報であるため、給与変更履歴を記録する必要があります。まず、監査テーブルを作成します: 

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

このうち、audit_id は自動インクリメント主キー、emp_id は従業員番号、old_salary と new_salary はそれぞれ変更前と変更後の月給を保存するために使用されます。変更時刻を記録します;change_by 変更操作を行ったユーザーが記録されます。

次に、従業員の月給の変更記録を記録するために使用されるトリガー tri_audit_salary を作成します。 

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

このうち、DELIMITER は SQL ステートメントの終了文字を変更するために使用されます。ストアド プロシージャを導入するときにすでに用意されています。ご存知のとおり、AFTER はデータを変更した後にトリガーを実行することを意味します。UPDATE は更新操作の場合のみデータ変更を記録することを意味します。トリガー本体の NEW と OLD は MySQL トリガーの特別な変数であり、後のレコードも含まれます。 INSERT トリガーには OLD 変数はなく、DELETE トリガーには NEW 変数はありません; CURRENT_TIMESTAMP と USER() は、現在の時刻とログイン ユーザーを返す MySQL システム関数です。

トリガーを作成した後、いくつかのデータ変更操作を実行してトリガーの効果を検証します。 

UPDATE employee
SET email = &#39;sunqian@shuguo.net&#39;
WHERE emp_name = &#39;孙乾&#39;;

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = &#39;孙乾&#39;;

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

最初の UPDATE ステートメントは「Sun Qian」の電子メール アドレスのみを変更したため、Tri_audit_salary は2 番目の UPDATE ステートメントは彼の月給を変更し、tri_audit_salary をトリガーします。したがって、監査テーブルsalary_auditには、月給変更前後の状況を記録するデータが含まれています。

従業員の追加と削除の操作を同時に監査したい場合は、INSERT トリガーと DELETE トリガーを作成できます。

さらに、MySQL は、同じトリガー タイミングおよび同じイベントに対して複数のトリガーを定義し、その実行順序を指定することをサポートしています。 

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

その中で、FOLLOWS はトリガーが次の場所にあることを示します。 other_trigger の後に実行されます。PRECEDES は、トリガーが other_trigger より前に実行されることを示します。オプションが指定されていない場合、デフォルトでは、トリガーは作成された順序で実行されます。

トリガーの表示

SHOW TRIGGERS ステートメントを使用して、データベース内のトリガーのリストを表示します: 

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE &#39;pattern&#39; | WHERE expr]

その中、db_name 指定したデータベース内のトリガーを表示するために使用されます。デフォルトは現在のデータベースです。LIKE はストアド プロシージャの名前と一致するために使用され、WHERE はより多くのフィルター条件を指定できます。たとえば、次のステートメントは、現在のデータベース内のトリガーを返します。 

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

さらに、MySQL システム テーブル INFORMATION_SCHEMA.TRIGGERS には、より詳細なトリガー情報が含まれています。

トリガーを作成する DDL ステートメントを取得したい場合は、SHOW CREATE TRIGGER ステートメントを実行できます。例: 

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

トリガーの削除

MySQL にはトリガーを変更するステートメントが用意されていません。トリガーを削除して再度作成することしかできません。 DROP TRIGGER ステートメントを介してデバイス。たとえば、次のステートメントを使用して、トリガー tri_audit_salary を削除できます。 

DROP TRIGGER IF EXISTS tri_audit_salary;

IF EXISTS は、トリガー tri_audit_salary が存在しない場合のエラーを回避します。

[関連する推奨事項: mysql ビデオ チュートリアル ]

以上がmysqlトリガーはどのレベルですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

sql mysql if for delete 对象 事件 table 数据库
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：mysqlにはvarchar2がありますか?次の記事：mysqlにはvarchar2がありますか?

関連記事

続きを見る