トリガー

OceanBaseデータベースのMySQLモードは、MySQL 5.6と互換性のあるトリガーをサポートしています。トリガーはテーブルに関連付けられたデータベースオブジェクトであり、関連するテーブルの行に対する挿入、更新、削除の各ステートメントが実行される際に有効化されます。例えば、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
    [DEFINER = user]
    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を指定すると、エラーは発生せず、警告情報が1件生成されるだけです。

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

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

• BEFORE または AFTER はアクションが実行されるタイミングを示します。例えば、トリガーがテーブルへの各行挿入前に、または挿入後に実行されるかどうかを示します。

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

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

• trigger_order 句では、キーワード FOLLOWS と PRECEDES を使用して、同じトリガーイベントと実行タイミングを持つトリガーの順序を指定できます。FOLLOWS キーワードは、新しいトリガーが既存のトリガーの後に有効化されることを示します。PRECEDES キーワードは、新しいトリガーが既存のトリガーよりも前に有効化されることを示します。

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

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

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

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

• OLD.columnName は読み取り専用ですが、NEW.columnName はトリガー内で SET を使用した代入が可能です。

例：トリガー 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

さらに、定義するトリガーに複数の実行ステートメントが必要な場合は、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モードのトリガーには、以下の制限事項があります：

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

  説明

  • OceanBase データベース V4.1.0 BP4 以降、MySQLモードでは一時テーブル機能が無効になりました。
  • V4.4.x バージョンでは、V4.4.2 以降、MySQLモードで一時テーブル機能をサポートしています。

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

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

  • 外部キーはトリガーを有効化しません。

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