トリガー

トリガーはテーブルに関連付けられたデータベースオブジェクトであり、関連するテーブルの行に対して挿入、更新、または削除のステートメントが実行されるときにアクティブ化されます。また、トリガーはイベントの前または後にアクティブ化されるように設定することもできます。

例えば、INSERT または LOAD DATA ステートメントを使用して行を挿入すると、挿入される各行ごとに INSERT トリガーが一度アクティブ化されます。2行のデータを一括挿入する場合、トリガーは2回トリガーされます。また、テーブルの各行を挿入する前や、更新された各行の後にトリガーをアクティブ化することも可能です。

トリガーの種類

現在のバージョンのOceanBaseデータベースMySQLモードでは、主に以下の種類のトリガーがサポートされています：

• INSERT型トリガー：特定の行を挿入したときにトリガーが有効化されることを示します。これはINSERT、LOAD DATA、REPLACEステートメントでトリガーを発火させることができます。

• UPDATE型トリガー：特定の行を変更したときにトリガーが有効化されることを示します。これはUPDATEステートメントでトリガーを発火させることができます。

• DELETE型トリガー：特定の行を削除したときにトリガーが有効化されることを示します。これはDELETE、REPLACEステートメントでトリガーを発火させることができます。

特に特殊なのはINSERT INTO ... ON DUPLICATE KEY UPDATEステートメントであり、各行に対してBEFORE INSERTトリガーが有効化され、その後にAFTER INSERTトリガーまたはBEFORE UPDATEとAFTER UPDATEトリガーが有効化されます。AFTER INSERTトリガーかBEFORE UPDATEとAFTER UPDATEトリガーかは、行に重複するキーが存在するかどうかによって決まります。

トリガーの作成

CREATE TRIGGER ステートメントを使用してトリガーを作成できます。

トリガーを作成するユーザーには、以下の権限が必要です：

• 現在のトリガーが関連付けられているテーブルに対する権限。これには、SELECT、INSERT、UPDATE、DELETE などの権限が含まれます。

• トリガー権限、すなわち CREATE 権限。

• トリガーが有効化された後に実行されるステートメントに対する権限。

トリガーを作成するSQL構文は次のとおりです：

CREATE
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

構文の説明は以下のとおりです：

• IF NOT EXISTS：作成しようとしているトリガーの名前が既に存在し、IF NOT EXISTS オプションを使用しない場合、エラーメッセージがトリガーされます。IF NOT EXISTS を指定した場合、エラーは発生せず、警告メッセージが生成されるだけです。

• トリガー名 trigger_name は一意である必要があります。

• table_name は、トリガーを作成するテーブル名を表します。つまり、どのテーブルにトリガーを作成するかを示します。

• BEFORE または AFTER は、トリガー動作のタイミングを示します。例えば、トリガーはテーブルに各行が挿入される前に有効化されるか、それとも後に有効化されるかを示します。

• INSERT、UPDATE または DELETE は、トリガーイベント、つまりトリガーを有効化する操作タイプを示します。

• FOR EACH ROW は、トリガー本体を定義するために使用されます。トリガーが有効化されるたびにこのステートメントが実行され、トリガーイベントの影響を受ける各行に対して一度ずつ実行されます。

OceanBaseデータベースでは、NEW.columnName と OLD.columnName も定義されています：

• INSERT 型トリガーでは、NEW.columnName は挿入される新しいデータ（BEFORE）または既に挿入されたデータ（AFTER）を表します。ここで、columnName は対応するデータテーブルの列名です。

• UPDATE 型トリガーでは、OLD.columnName は変更される元のデータを表し、NEW.columnName は変更される新しいデータを表します。

• DELETE 型トリガーでは、OLD.columnName は削除される元のデータを表します。

• OLD.columnName は読み取り専用であり、NEW.columnName はトリガー内で SET によって代入できます。

例1：トリガー test_trg を作成し、テーブル test に関連付けて、INSERT 操作を有効化します。また、トリガーはアキュムレーターとして機能し、テーブルの列に挿入される値の合計を求めます。

obclient> CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

obclient> CREATE TRIGGER test_trg BEFORE INSERT ON test
       FOR EACH ROW SET @sum = @sum + NEW.user_num;
Query OK, 0 rows affected

例2：トリガー trg2_t と trg3_t を作成し、トリガー trg1_t の後に実行されるようにします。トリガー trg4_t はトリガー trg1_t の前に実行されます。

CREATE TABLE t(c1 INT);
CREATE TABLE msg(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100));
CREATE TRIGGER trg1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg1_t');
END;
/
CREATE TRIGGER trg2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg2_t');
END;
/
CREATE TRIGGER trg3_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg3_t');
END;
/

CREATE TRIGGER trg4_t BEFORE INSERT ON t FOR EACH ROW PRECEDES trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg4_t');
END;
/
INSERT INTO t VALUES (1);

obclient> SELECT * FROM msg;

実行結果は次のとおりです：

+----+----------------------+
| c1 | c2                   |
+----+----------------------+
|  1 | BEFORE INSERT trg4_t |
|  2 | BEFORE INSERT trg1_t |
|  3 | BEFORE INSERT trg3_t |
|  4 | BEFORE INSERT trg2_t |
+----+----------------------+
4 rows in set

さらに、定義するトリガーに複数の実行ステートメントが必要な場合は、BEGIN ... END ステートメントを使用して、コードブロック全体の開始と終了をそれぞれ示すことができます。

BEGIN ... END ステートメントの構文は次のとおりです：

BEGIN
[statement_list]
END

ここで、statement_list は1つまたは複数のステートメントのリストを表します。リスト内の各ステートメントは、セミコロン（;）で終了する必要があります。SQLステートメントでは、セミコロン（;）はステートメント終了の識別子であり、セミコロンに遭遇するとそのセクションのステートメントが終了したことを意味し、システムはそのセクションのステートメントの実行を開始します。最終的に、実行プロセス中にインタプリターが BEGIN にマッチする END を見つからないためエラーが報告されます。このようなエラーを回避するために、DELIMITER コマンドを使用してステートメント終了文字を変更できます。

DELIMITER コマンドの例は次のとおりです：

DELIMITER new_delemiter

ここで、new_delemiter は1バイト以上の長さの記号に設定でき、デフォルトはセミコロン（;）です。これを他の記号、例えば # に変更できます。

DELIMITER コマンドを追加した後、DELIMITER コマンドの後のステートメントでセミコロンを使用してもエラーは報告されません。設定された終了文字（#）に遭遇するまで、そのステートメントが終了したとはみなされません。

例：

obclient> CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

obclient> DELIMITER //

obclient> CREATE TRIGGER test_trg BEFORE UPDATE ON test
               FOR EACH ROW
               BEGIN
               IF NEW.user_num < 1 THEN
              SET NEW.user_num  = 1;
              ELSEIF NEW.user_num > 45 THEN
              SET NEW.user_num= 45;
             END IF;
             END //
Query OK, 0 rows affected

obclient> DELIMITER ;

トリガーの使用制限

MySQLモードでは、トリガーに以下の使用制限があります：

• トリガーは永続テーブルにのみ作成でき、一時テーブルには作成できません。

• トリガーはCALLステートメントを使用してクライアントにデータを返したり、動的SQLを含むストアドプロシージャを使用したりすることはできません。ただし、ストアドプロシージャや関数がOUTまたはIN OUT型のパラメータを使用してトリガーにデータを返すことは許可されています。

• トリガー内では、トランザクションを開始または終了するステートメントセグメントを使用することはできません。例えば、トランザクションの開始(START TRANSACTION)、コミット(COMMIT)、またはロールバック(ROLLBACK)などです。ただし、セーブポイントへのロールバックは許可されています。これは、セーブポイントへのロールバックはトランザクションを終了しないためです。

• 外部キーはトリガーをアクティブにしません。

• トリガーでは戻り値を返すことができないため、トリガー内には戻り値ステートメントを含めることはできません。トリガーを即座に停止する必要がある場合は、LEAVEステートメントを使用する必要があります。

トリガーのメタデータの表示

トリガー関連のメタデータを取得するには、次の操作を実行します：

• INFORMATION_SCHEMAのTRIGGERSテーブルを照会します。詳細については、INFORMATION_SCHEMA TRIGGERSを参照してください。

• SHOW CREATE TRIGGERステートメントを使用します。詳細については、SHOW CREATE TRIGGERを参照してください。

• SHOW TRIGGERSステートメントを使用します。詳細については、SHOW TRIGGERSを参照してください。