MySQLトリガーの使用方法

はじめに

MySQLトリガーは、テーブル行を追加、更新、または削除するときにテーブルに制限を適用します。

MySQLの列には、わずかな値の制限が適用されます。たとえば、列のデータ型を tiny intとして設定します。 およびnullではない 少数の値の入力が必要です。それでも、データの整合性を維持するには、さらに多くの制限が必要です。

このチュートリアルでは、MySQLトリガーの使用方法を示し、各タイプのトリガーの例を示します。

前提条件

• データベースサーバー上でMySQLを実行しているシステム
• root権限を持つMySQLユーザーアカウント
• 基本的なMySQLコマンドの知識（ダウンロード可能なMySQLコマンドのチートシートを参照）

MySQLのトリガーとは何ですか？

トリガーは、テーブルでイベントが発生したときにアクティブになる名前付きのMySQLオブジェクトです。トリガーは、特定のテーブルに関連付けられた特定のタイプのストアドプロシージャです。

トリガーを使用すると、 NEW を使用して、比較のためにテーブルの値にアクセスできます。 およびOLD 。修飾子を使用できるかどうかは、使用するトリガーイベントによって異なります。

トリガーイベント	古い	新規
INSERT	いいえ	はい
更新	はい	はい
削除	はい	いいえ

データを挿入しようとするときに値をチェックまたは変更すると、NEW。になります 利用可能な修飾子。これは、テーブルが新しいコンテンツで更新されるためです。対照的に、OLD。 事前にその場所に情報が存在しないため、挿入ステートメントの値は存在しません。

テーブル行を更新する場合、両方の修飾子を使用できます。 OLD。があります NEW。に更新するデータ データ。

最後に、データの行を削除するときは、OLD。 修飾子は削除された値にアクセスします。 NEW。<列名> 削除時に古い値を置き換えるものがないため、存在しません。

MySQLトリガーの例

適用されたトリガーの例として、テーブル personに新しい値を挿入します。 元の入力とは異なる結果が得られます：

挿入された名前は最初は小文字であったことに注意してください。テーブルを選択すると、最初の文字は大文字で表示されます。通常の挿入ステートメントと異なる兆候はありませんが、トリガーは挿入ステートメントの前に起動しました。 名前の最初の文字を大文字にします。

MySQLトリガーの使用

テーブルに関連付けられているすべてのトリガーには、次の2つの要素に基づいた一意の名前と機能があります。

1.時間 。 BEFORE またはAFTER 特定の行イベント。

2.イベント 。 INSERT 、 UPDATE またはDELETE 。

MySQLトリガーは、アクティブ化時間とイベントに応じて、合計6つの固有のトリガーの組み合わせで起動します。 beforeステートメントは、コミットメントを行う前にデータをチェックして変更を加えるのに役立ちますが、afterステートメントは、最初にデータをコミットしてからステートメントを実行します。

一連のアクションの実行は自動的に行われ、ステートメント内の挿入、削除、または更新されたすべての行に影響します。

トリガーの作成

CREATE TRIGGERを使用する 新しいトリガーを作成するためのステートメント構文：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
<trigger body>;

ベストプラクティスは、次の情報を使用してトリガーに名前を付けることです。

<trigger time>_<table name>_<trigger event>

たとえば、トリガーが挿入前に起動した場合 従業員という名前のテーブル 、最良の規則はトリガーを呼び出すことです：

before_employee_insert

または、次の形式を使用するのが一般的な方法です。

<table name>_<first letter of trigger time><first letter of trigger name>

挿入前 テーブルのトリガー名従業員 次のようになります：

employee_bi

トリガーは、<テーブル名>で定義されたテーブルのイベントの特定の時間に実行されます 関数の影響を受ける行ごとに。

トリガーの削除

トリガーを削除するには、 DROP TRIGGERを使用します ステートメント：

DROP TRIGGER <trigger name>;

または、次を使用します：

DROP TRIGGER IF EXISTS <trigger name>;

トリガーがないためエラーメッセージは表示されないため、警告は表示されません。

サンプルデータベースの作成

次の構造でトリガーサンプルコードのデータベースを作成します。

1. personというテーブルを作成します 名前 および年齢 列用。

CREATE TABLE person (name varchar(45), age int);

サンプルデータをテーブルに挿入します：

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);

テーブルを選択して結果を確認します：

SELECT * FROM person;

2. average_ageというテーブルを作成します averageという列があります ：

CREATE TABLE average_age (average double);

表に平均年齢値を挿入します：

INSERT INTO average_age SELECT AVG(age) FROM person;

テーブルを選択して結果を確認します：

SELECT * FROM average_age;

3. person_archiveというテーブルを作成します 名前 、年齢 、および時間 列：

CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());

BEFOREINSERTトリガーを作成する

BEFORE INSERTを作成するには トリガー、使用：

CREATE TRIGGER <trigger name> BEFORE INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

挿入前 トリガーは、データベーステーブルにコミットする前にデータの変更を制御します。一貫性を保つために名前を大文字にする、入力の長さを確認する、または BEFORE INSERTを使用して誤った入力をキャッチする トリガーは、新しいデータを入力する前に値の制限をさらに提供します。

トリガーの例を挿入する前に

BEFORE INSERTを作成します 人にデータを挿入する前に年齢の値を確認するトリガー テーブル：

delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

データを挿入するとトリガーがアクティブになり、 ageの値がチェックされます 情報をコミットする前に：

INSERT INTO person VALUES ('John', 14);

コンソールに説明的なエラーメッセージが表示されます。トリガーチェックに失敗したため、データはテーブルに挿入されません。

AFTERINSERTトリガーを作成する

AFTER INSERTを作成します トリガー：

CREATE TRIGGER <trigger name> AFTER INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

AFTER INSERT トリガーは、入力された行が別のテーブルを更新するために必要な値を生成する場合に役立ちます。

AFTERINSERTトリガーの例

人に新しい行を挿入する テーブルはaverage_ageの平均を自動的に更新しません テーブル。 AFTER INSERTを作成します 人でトリガー average_ageを更新するテーブル 挿入後のテーブル：

delimiter //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

人に新しい行を挿入する テーブルがトリガーをアクティブにします：

INSERT INTO person VALUES ('John', 19);

データは人に正常にコミットされます テーブルを作成し、 average_ageを更新します 正しい平均値を持つテーブル。

更新前のトリガーを作成する

更新前を作成します トリガー：

CREATE TRIGGER <trigger name> BEFORE UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

更新前 トリガーはBEFORE INSERTと一緒に実行されます トリガー。データを挿入する前に制限がある場合は、更新する前にも制限があるはずです。

更新前のトリガーの例

人に年齢制限がある場合 データを挿入する前のテーブルでは、情報を更新する前に年齢制限も存在する必要があります。 更新前なし トリガー、年齢チェックトリガーは簡単に回避できます。編集を誤った値に制限するものはありません。

更新前を追加します 人へのトリガー BEFORE INSERTと同じ本文のテーブル トリガー：

delimiter //
CREATE TRIGGER person_bu BEFORE UPDATE
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

既存の値を更新すると、トリガーチェックがアクティブになります：

UPDATE person SET age = 17 WHERE name = 'John';

年齢を更新する 18未満の値にすると、エラーメッセージが表示され、情報は更新されません。

更新後のトリガーを作成する

次のコードブロックを使用して、 AFTER UPDATEを作成します トリガー：

CREATE TRIGGER <trigger name> AFTER UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

更新後 トリガーは、データへのコミットされた変更を追跡するのに役立ちます。ほとんどの場合、情報を挿入した後の変更は、データの更新後にも発生します。

更新後のトリガーの例

年齢の更新が成功した場合 テーブルのデータperson average_ageで計算された中間平均値も更新する必要があります テーブル。

更新後を作成します average_ageを更新するトリガー personの行を更新した後のテーブル テーブル：

delimiter //
CREATE TRIGGER person_au AFTER UPDATE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

既存のデータを更新すると、 personの値が変更されます テーブル：

UPDATE person SET age = 21 WHERE name = 'John';

テーブルの更新person average_ageの平均も更新します テーブル。

BEFOREDELETEトリガーを作成する

BEFORE DELETEを作成するには トリガー、使用：

CREATE TRIGGER <trigger name> BEFORE DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

削除前 トリガーはセキュリティ上の理由から不可欠です。親テーブルに子がアタッチされている場合、トリガーは削除をブロックし、孤立したテーブルを防ぎます。トリガーを使用すると、削除する前にデータをアーカイブすることもできます。

トリガーの例を削除する前に

BEFORE DELETE を作成して、削除されたデータをアーカイブします テーブルのトリガーperson 値をperson_archiveに挿入します テーブル：

delimiter //
CREATE TRIGGER person_bd BEFORE DELETE
ON person
FOR EACH ROW
INSERT INTO person_archive (name, age)
VALUES (OLD.name, OLD.age); //
delimiter ;

テーブルからのデータの削除person データをperson_archiveにアーカイブします 削除する前のテーブル：

DELETE FROM person WHERE name = 'John';

値をpersonに挿入し直します テーブルは、削除されたデータのログを person_archiveに保持します テーブル：

INSERT INTO person VALUES ('John', 21);

削除前 トリガーは、テーブル変更の試行をログに記録するのに役立ちます。

AFTERDELETEトリガーを作成する

AFTER DELETEを作成します トリガー：

CREATE TRIGGER <trigger name> AFTER DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

AFTER DELETE トリガーは、更新を行う前にデータ行を非表示にする必要がある情報の更新を維持します。

AFTERDELETEトリガーの例

AFTER DELETEを作成します テーブルのトリガーperson average_ageを更新します 新しい情報を含むテーブル：

delimiter //
CREATE TRIGGER person_ad AFTER DELETE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //
delimiter ;

テーブルからレコードを削除するperson average_ageを更新します 新しい平均の表：

AFTER DELETEなし トリガーすると、情報は自動的に更新されません。

複数のトリガーを作成する

MySQLは、複数のトリガーを同時に起動することをサポートしていません。ただし、同じトリガーに複数の論理演算を追加することは可能です。 BEGINを使用します およびEND トリガー本体を示す区切り文字：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
BEGIN
<trigger body>;
END;

複数の操作でトリガーを作成する前に、必ずデフォルトの区切り文字を変更してください。

トリガーを表示

データベース内のすべてのトリガーを次のように一覧表示します：

SHOW triggers;

出力には、名前とステートメントの内容を含む、すべてのトリガーのリストが表示されます。

作成時間やトリガーを作成したユーザーなど、その他の情報も表示されます。