トリガーはテーブルに関連付けられたデータベースオブジェクトであり、関連するテーブルの行に対する挿入、更新、または削除のステートメントが実行されるときに有効化されます。また、トリガーイベントの前後に有効化されるよう設定することもできます。
例えば、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ステートメントで、各行に対してBEFORE INSERTトリガーが1回ずつ有効になり、その後AFTER INSERTトリガー、またはBEFORE UPDATEとAFTER UPDATEのトリガーが有効になります。AFTER INSERTトリガーが有効になるか、BEFORE UPDATEとAFTER UPDATEのトリガーが有効になるかは、行に重複するキーが存在するかどうかによって決まります。
トリガーの作成
CREATE TRIGGER ステートメントを使用してトリガーを作成できます。
トリガーを作成するユーザーには、以下の権限が必要です：
現在のトリガーが関連付けられているテーブルに対する権限。
SELECT、INSERT、UPDATE、DELETEなどの権限が含まれます。トリガー権限、すなわち
CREATE権限。トリガーが有効化された後に実行されるステートメントに対する権限。
トリガーを作成するSQL構文は以下のとおりです：
CREATE
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は一意である必要があります。table_nameは、トリガーを作成するテーブル名を表します。つまり、どのテーブルにトリガーを作成するかを示します。BEFOREまたはAFTERは、トリガー動作のタイミングを示します。例えば、トリガーがテーブルへの各行挿入の前に、または後にアクティブになるかどうかを示します。INSERT、UPDATEまたはDELETEは、トリガーイベントを示します。つまり、トリガーを有効化する操作のタイプを示します。FOR EACH ROWは、トリガーの本体を定義するために使用されます。トリガーが有効化されるたびにこのステートメントが実行され、トリガーイベントの影響を受ける各行に対して1回ずつ実行されます。
OceanBaseデータベースでは、NEW.columnName と OLD.columnName も定義されています：
INSERT型トリガーでは、NEW.columnNameは挿入される新しいデータ（BEFORE時）または既に挿入されたデータ（AFTER時）を表します。ここで、columnNameは対応するデータテーブルの列名です。UPDATE型トリガーでは、OLD.columnNameは変更前の元のデータを表し、NEW.columnNameは変更後の新しいデータを表します。DELETE型トリガーでは、OLD.columnNameは削除される元のデータを表します。OLD.columnNameは読み取り専用ですが、NEW.columnNameはトリガー内でSETを使用した代入が可能です。
例1：トリガー 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
例2：トリガー trg2_t と trg3_t を作成し、トリガー trg1_t の後に実行します。トリガー trg4_t をトリガー trg1_t の前に実行します。
CREATE TABLE t(c1 INT);
CREATE TABLE msg(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100));
CREATE TRIGGER trg1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg1_t');
END;
/
CREATE TRIGGER trg2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg2_t');
END;
/
CREATE TRIGGER trg3_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg3_t');
END;
/
CREATE TRIGGER trg4_t BEFORE INSERT ON t FOR EACH ROW PRECEDES trg1_t
BEGIN
INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg4_t');
END;
/
INSERT INTO t VALUES (1);
obclient> SELECT * FROM msg;
結果は以下のようになります：
+----+----------------------+
| c1 | c2 |
+----+----------------------+
| 1 | BEFORE INSERT trg4_t |
| 2 | BEFORE INSERT trg1_t |
| 3 | BEFORE INSERT trg3_t |
| 4 | BEFORE INSERT trg2_t |
+----+----------------------+
4 rows in set
さらに、定義するトリガーに複数の実行ステートメントが必要な場合、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モードでは、トリガーには以下の制限事項があります：
トリガーは永久テーブルにのみ作成でき、一時テーブルには作成できません。
トリガーでは
CALLステートメントを使用してクライアントにデータを返すことはできず、動的SQLを含むストアドプロシージャも使用できません。ただし、ストアドプロシージャや関数がOUTまたはIN OUT型のパラメータを通じてデータをトリガーに返すことは許可されています。トリガー内では、トランザクションを開始または終了するステートメントを使用できません。例えば、トランザクションの開始（
START TRANSACTION）、コミット（COMMIT）、ロールバック（ROLLBACK）などです。ただし、セーブポイントへのロールバックは可能です。これは、セーブポイントへのロールバックはトランザクションを終了しないためです。外部キーはトリガーを有効化しません。
トリガーでは戻り値を返すことができないため、トリガー内には戻りステートメントを含めることはできません。トリガーを即座に停止する必要がある場合は、
LEAVEステートメントを使用する必要があります。
トリガーメタデータの確認
トリガー関連のメタデータを取得するには、以下の操作を実行します：
INFORMATION_SCHEMAのTRIGGERSテーブルをクエリします。詳細については、INFORMATION_SCHEMA TRIGGERSを参照してください。SHOW CREATE TRIGGERステートメントを使用します。詳細については、SHOW CREATE TRIGGERを参照してください。SHOW TRIGGERSステートメントを使用します。詳細については、SHOW TRIGGERSを参照してください。