OceanBaseデータベースのMySQLモードは、MySQL 5.6のトリガーと互換性があります。トリガーはテーブルに関連付けられたデータベースオブジェクトであり、関連するテーブルの行に対してINSERT、UPDATE、またはDELETE文が実行されるときにアクティブ化されます。例えば、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を指定した場合、エラーは発生せず、警告メッセージが生成されるだけです。トリガー名
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 コマンドの後のステートメントでセミコロンを使用してもエラーは報告されません。設定された終了文字（#）に遭遇するまで、そのステートメントは終了したとはみなされません。
注意
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モードでは一時テーブル機能が無効になっています。
- OceanBaseデータベースV4.3.5 BP4バージョン以降、MySQLモードでは一時テーブル機能がサポートされています。
トリガーは
CALLステートメントを使用してクライアントにデータを返す、または動的SQLを使用するストアドプロシージャを呼び出すことはできません。ただし、ストアドプロシージャや関数がOUTまたはIN OUT型のパラメータを使用してトリガーにデータを返すことは許可されています。トリガー内では、トランザクションを開始または終了するステートメントセグメントを使用することはできません。例えば、トランザクションの開始(
START TRANSACTION)、コミット(COMMIT)、またはロールバック(ROLLBACK)はできません。ただし、セーブポイントへのロールバックは許可されています。これは、セーブポイントへのロールバックはトランザクションを終了しないためです。外部キーはトリガーを有効にしません。
トリガーでは値を返すことができないため、トリガー内には返却ステートメントを含めることはできません。トリガーを即座に停止する必要がある場合は、
LEAVEステートメントを使用する必要があります。