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 に関連付けて、挿入操作を有効化します。また、トリガーはアキュムレータとして機能し、テーブルの列に挿入された値の合計を求めます。

  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 ステートメントを使用する必要があります。