mysqlトリガーはどのレベルですか?-mysql チュートリアル-php.cn

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 トリガーの利点は次のとおりです。

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

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

1) INSERT トリガー

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

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

2) UPDATE トリガー

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

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 サイトの他の関連記事を参照してください。