OceanBaseデータベースのMySQLモードは、MySQL 5.6のトリガーと互換性があります。トリガーはテーブルに関連付けられたデータベースオブジェクトであり、関連するテーブルの行に対する挿入、更新、または削除のステートメントが実行されるときに有効化されます。例えば、INSERTまたはLOAD DATAステートメントを使用して行を挿入すると、挿入するたびにINSERTトリガーが1回有効化されます。2行のデータを一括挿入すると、トリガーは2回実行されます。
トリガーは、イベントが発生する前または後に有効化されるよう設定できます。例えば、テーブルの各行を挿入する前、または更新される各行の後にトリガーを有効化することができます。
トリガーの種類
OceanBaseデータベースのMySQLモードは、現在主に以下の種類のトリガーをサポートしています：
INSERTトリガー：特定の行が挿入されたときにトリガーが有効になります。INSERT、LOAD DATA、REPLACEステートメントでトリガーを発生させることができます。UPDATEトリガー：特定の行が変更されたときにトリガーが有効になります。UPDATEステートメントでトリガーを発生させることができます。DELETEトリガー：特定の行が削除されたときにトリガーが有効になります。DELETE、REPLACEステートメントでトリガーを発生させることができます。
特別な場合として、INSERT INTO ... ON DUPLICATE KEY UPDATEステートメントでは、各行に対して1つの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はトリガーの本体を定義するために使用されます。トリガーが有効化されるたびにこのステートメントが実行され、トリガーイベントの影響を受ける各行に対して1回ずつ実行されます。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 コマンドの後のステートメントでセミコロンを使用してもエラーは発生せず、設定された終端文字（#）に遭遇するまで、そのステートメントが終了したとはみなされません。
注意
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ステートメントを使用する必要があります。