トリガー

トリガーはOceanBaseデータベースが提供する機能であり、ストアドプロシージャや関数と同様に、宣言、実行、例外処理を含むPLブロックであり、PL言語で記述されたコンパイル済みの保存ユニットです。

トリガーは独立したオブジェクトであり、特定のトリガー文が実行されると自動的かつ暗黙的に実行されます。また、トリガーはパラメータを受け取ることができません。ここでいうトリガー文とは、データベースのテーブルに対するINSERT、UPDATE、およびDELETE操作を指します。

トリガーの利点

トリガーを正しく使用することで、アプリケーションの構築とデプロイがよりシンプルかつ堅牢になります。

トリガーを使用して、すべてのクライアントプログラムに基盤となる業務ロジックの実行を強制できます。例えば、複数のクライアントアプリケーションが同一のテーブルにアクセスする場合、そのテーブル上のトリガーがデータ挿入時に実行すべきロジックを保証していれば、各クライアントで個別に業務ロジックを実行する必要はありません。アプリケーションはトリガーを回避することができないため、自動的にトリガー内の業務ロジックが適用されます。

必要に応じてトリガーを使用し、過度な使用は避けてください。トリガーを過剰に使用すると、複雑な相互依存関係が生じ、大規模なアプリケーションでは保守が困難になります。例えば、トリガーが発火すると、その際に実行されるSQL文が他のトリガーを引き起こし、カスケードトリガーが発生する可能性があります。これにより予想外の結果が生じることもあります。

トリガーの種類

OceanBaseデータベースでは、以下の種類のトリガーを作成できます：

• 行レベルトリガー

  行レベルトリガーはエンティティテーブルに作成され、テーブルがトリガー文の影響を受けるたびに1回の行レベルトリガーが実行されます。例えば、1つのステートメントが複数行のデータを更新する場合、影響を受ける各データごとに1回ずつトリガーが実行されます。トリガー文がいずれの行データにも影響を与えない場合、トリガーは実行されません。

• ステートメントレベルトリガー

  ステートメントレベルトリガーはエンティティテーブルに作成され、トリガー文が実行されるたびに自動的に1回実行されます。そのトリガー文がテーブル内のいずれの行データにも影響を与えたかどうかに関係ありません。例えば、1つのステートメントがテーブル内の100件のデータを更新した場合、ステートメントレベルのUPDATEトリガーは1回だけ実行されます。

• INSTEAD OFトリガー

  INSTEAD OFトリガーはビューに作成され、ビューに対してトリガー文が実行されると自動的に実行されます。INSTEAD OFトリガーは、DMLステートメントでは直接変更できないビューを変更するために使用できます。

• 組み合わせDMLトリガー

  組み合わせDMLトリガーはテーブルまたはビューに作成でき、複数の時点でのトリガーをサポートします。組み合わせトリガーは各時間点ごとにセグメントに分割され、各時間帯には独立した実行可能部分と例外処理部分（オプション）が含まれます。

• システムトリガー トリガーイベントによって、システムトリガーは大きく2つのカテゴリに分類されます。DDLイベントトリガーとデータベースイベントトリガーです。DDLイベントトリガーとは、DDLの実行によってトリガーされるトリガーを指し、データベースイベントトリガーとは、データベースイベントによってトリガーされるトリガーを指します。ログオンとログオフはデータベースイベントに属するため、これらの2種類のトリガーはデータベースイベントトリガーに分類され、それぞれユーザーのログイン後とユーザーのログアウト前に実行されます。

トリガーの実行タイミング

トリガーの実行タイミングを定義できます。実行タイミングとは、トリガー操作がトリガー文の前に実行されるか後に実行されるかを指します。

行レベルトリガーとステートメントレベルトリガーでは、以下の実行タイミングを指定できます：

• トリガー文の実行前

• 各データ行がトリガー文によって変更される前

• 各データ行がトリガー文によって変更された後

• トリガー文の実行後

ステートメントレベルおよび行レベルトリガーについて、BEFOREトリガーはデータの変更前にセキュリティを強化し、業務ルールを実行するのに適しています。AFTERトリガーは、操作ログを記録するのに非常に適しています。

シンプルなトリガーの実行タイミングには、以下の4種類があります：

• イベントの実行前（ステートメントレベルのBEFOREトリガー）

• イベントの実行後（ステートメントレベルのAFTERトリガー）

• 各行がイベントの実行によって影響を受ける前（行レベルのBEFOREトリガー）

• 各行がイベントの実行によって影響を受けた後（行レベルのAFTERトリガー）

システムトリガーは、スキーマ（ユーザー）およびデータベース上に作成でき、それぞれユーザーのログイン時およびすべてのユーザーのログイン時にトリガーを実行するように指定します。ON user_name.SCHEMA を使用して、特定のユーザーのログイン時にトリガーを実行するように指定できます。user_name を指定しない場合、デフォルトではトリガーを作成したユーザーのログイン時にトリガーが実行されます。

トリガーの実行順序は以下のとおりです：

• 同一タイプのトリガーの実行順序は不確定であり、現在トリガーの実行順序を指定することはサポートされていません。

• 1つのDML文が複数のシンプルなトリガーをトリガーする可能性があります。トリガーの実行順序は次のとおりです：ステートメントレベルBEFOREトリガー -> 行レベルBEFOREトリガー -> 行レベルAFTERトリガー -> ステートメントレベルAFTERトリガー。

  注意

  OceanBaseデータベースV2.2.7x以降のバージョンでは、テーブル上の行レベルトリガーのみがサポートされています。

トリガーの作成

トリガーを作成するための基本的な構文は以下のとおりです：

CREATE [OR REPLACE] TRIGGER trigger_name triggering_statement
  [trigger_restriction]
BEGIN
 triggered_action;
END

triggering_statementL:
  {BEFORE | AFTER }
  {INSERT | DELETE | UPDATE [OF column [, column ...]]}
  ON [schema.] table_name
  [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
  FOR EACH ROW
  [WHEN condition]
  [FOLLOWS | PRECEDES] other_trigger_name

トリガーの基本的な構造は以下のとおりです：

• トリガー名（Trigger Name）

  同一データベース内で、トリガー名は一意である必要があります。例えば、トリガー名はrow_triggler_on_employeesとなります。

• トリガー句（Triggering Statement）

  トリガー句とは、トリガーを呼び出すSQL文を指します。例えば、ユーザーがテーブルを更新する場合です。

• トリガー制限（Trigger Restriction）

  トリガー制限とは、ブール式を定義し、その式が真である場合にのみトリガーを発火させることを指します。例えば、従業員テーブル上のトリガーでは、北京在住の従業員のみがこのトリガーを発火させるという制限を定義します。

• トリガー動作（Trigger Action）

  トリガー動作とは、トリガー句が実行され、かつトリガー制限が真である場合に実行されるトリガー内部のコードブロックを指します。例えば、従業員テーブルにデータを挿入する場合です。

トリガーの作成例

テーブル emp_msg にトリガーを作成し、テーブルに対して INSERT、UPDATE、DELETE ステートメントが実行されるときにこのトリガーがトリガーされます。

emp_msg テーブルにデータを挿入すると同時に、employees テーブルにも1件のデータが挿入されます。emp_msg テーブルからデータを削除すると同時に、employees テーブルからも id が同じデータが削除されます。emp_msg テーブルのデータを更新すると同時に、employees テーブルの id が同じデータも更新されます。

CREATE TABLE employees (id INT, name VARCHAR2(20), WORK_YEAR int);

CREATE TABLE emp_msg (id INT PRIMARY KEY, name VARCHAR2(20), address VARCHAR2(100));

CREATE OR REPLACE TRIGGER tri_emp_msg BEFORE INSERT OR UPDATE OR DELETE ON emp_msg
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO employees VALUES (:NEW.id, :NEW.name, 0);
  ELSIF DELETING THEN
    DELETE FROM employees WHERE id = :OLD.id;
  ELSE
    UPDATE employees SET name = :NEW.name WHERE id = :NEW.id;
  END IF;
END;
/

トリガーの作成後、以下のサンプルのDMLステートメントを実行します：

obclient> INSERT INTO emp_msg VALUES (1, 'Curry', 'BeiJing');
Query OK, 1 row affected

obclient> SELECT * FROM emp_msg WHERE id = 1;
+----+-------+---------+
| ID | NAME  | ADDRESS |
+----+-------+---------+
|  1 | Curry | BeiJing |
+----+-------+---------+
1 row in set

obclient> SELECT * FROM  employees WHERE id = 1;
+------+-------+-----------+
| ID   | NAME  | WORK_YEAR |
+------+-------+-----------+
|    1 | Curry |         0 |
+------+-------+-----------+
1 row in set

obclient> UPDATE emp_msg SET name = 'Stephen Curry' WHERE id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM emp_msg WHERE id = 1;
+----+---------------+---------+
| ID | NAME          | ADDRESS |
+----+---------------+---------+
|  1 | Stephen Curry | BeiJing |
+----+---------------+---------+
1 row in set

obclient> SELECT * FROM  employees WHERE id = 1;
+------+---------------+-----------+
| ID   | NAME          | WORK_YEAR |
+------+---------------+-----------+
|    1 | Stephen Curry |         0 |
+------+---------------+-----------+
1 row in set

obclient> DELETE FROM emp_msg WHERE id = 1;
Query OK, 1 row affected

obclient> SELECT * FROM emp_msg WHERE id = 1;
Empty set

obclient> SELECT * FROM  employees WHERE id = 1;
Empty set