トリガー

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

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

トリガーの利点

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

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

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

トリガーの種類

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

• 行レベルトリガー

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

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

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

• INSTEAD OFトリガー

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

• 組み合わせDMLトリガー

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

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

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

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

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

• ステートメントの実行前に

• 各データ行がステートメントによって変更される前に

• 各データ行がステートメントによって変更された後に

• ステートメントの実行後に

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

シンプルなトリガーの実行タイミングは以下の4種類です：

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

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

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

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

システムトリガーは、特定のユーザーログイン時やすべてのユーザーログイン時にトリガーするために、スキーマ（user）およびデータベース上に作成できます。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_triggger_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