DMLトリガーの作成

本記事では、トリガーの作成方法について説明します。

前提条件

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

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

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

構文

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

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

CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body;

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

構文の説明：

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

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

• BEFORE | AFTER：トリガーが発生するタイミングを示します。トリガーは、各行がテーブルに挿入される前に有効化されるか、後に有効化されるかを示します。

• INSERT | UPDATE | DELETE：トリガーイベントを示します。トリガーを有効化する操作のタイプを示します。

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

• FOLLOWS | PRECEDES：トリガーの順序を定義するために使用されます。OceanBaseデータベースの現在のバージョンでは、同じトリガーイベントとアクションタイミングを持つ同一テーブルに複数のトリガーを定義することがサポートされています。デフォルトでは、同じトリガーイベントとアクションタイミングを持つトリガーは、作成順に従って有効化されます。トリガーの順序を変更したい場合は、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 を使用して代入できます。

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

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

BEGIN
[statement_list]
END

ここで、statement_list は1つ以上のステートメントのリストを表します。リスト内の各ステートメントはセミコロン（;）で終わらなければなりません。SQLステートメントでは、セミコロン（;）がステートメント終了の識別子であるため、セミコロンに遭遇するとそのセグメントのステートメントが終了したことを意味し、システムはそのセグメントのステートメントの実行を開始します。結果として、実行プロセス中にインタプリタが BEGIN に対応する END を見つけられずにエラーが発生します。このようなエラーを避けるために、DELIMITER コマンドを使用してステートメント終端文字を変更できます。

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

DELIMITER new_delimiter

ここで、new_delimiter は1つまたは複数の長さの記号に設定できます。デフォルトはセミコロン（;）ですが、# など他の記号に変更できます。

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

例

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

• 複数ステートメントのトリガーを作成します。

  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 または INOUT 型のパラメータを通じてデータをトリガーに返すことは許可されています。

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

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

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