テーブルまたはビューにDMLトリガーを作成できます。トリガーイベントは、DMLステートメントのDELETE、INSERT、およびUPDATEで構成されます。
トリガー名はストアドプロシージャ名やパッケージ名とは異なり、独立したネームスペースを持っているため、トリガー名はテーブルやストアドプロシージャと同じ名前を持つことができます。トリガーを作成するユーザーは、CREATE TRIGGERシステム権限を持っている必要があります。
行レベルトリガーの作成
OceanBaseデータベースの現行バージョンでは、行レベルトリガーの作成がサポートされており、以下のタイプが含まれます：
BEFORE INSERT FOR EACH ROWAFTER INSERT FOR EACH ROWBEFORE UPDATE FOR EACH ROWAFTER UPDATE FOR EACH ROWBEFORE DELETE FOR EACH ROWAFTER DELETE FOR EACH ROW
行レベルトリガーを作成する構文は次のとおりです：
CREATE [OR REPLACE] TRIGGER trigger_name
{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
[{ FOLLOWS | PRECEDES } other_trigger_name]
[WHEN condition]
BEGIN
...
END;
パラメータの説明は以下の表のとおりです：
| パラメータ | 意味 | 説明 |
|---|---|---|
BEFORE と AFTER |
トリガーのトリガーシーケンスをそれぞれ前トリガーと後トリガー方式として指定します。 | 前トリガーとは、トリガーイベントの実行前に現在作成されたトリガーをトリガーすることです。後トリガーとは、トリガーイベントの実行後に現在作成されたトリガーをトリガーすることです。 |
FOR EACH ROW |
トリガーが行トリガーであることを示します。 | 行トリガーでは、DMLステートメントの操作がデータベース内の複数行のデータに影響を与える場合、それらの各データ行について、トリガー制約条件を満たす限り、トリガーが1回アクティブ化されます。 |
REFERENCING |
関連名前を説明し、行トリガーのPLブロックおよびWHEN句で関連名前を使用して、現在の新旧の列値を参照できます。デフォルトの関連名前はそれぞれOLDとNEWです。 |
トリガーのPLブロックで関連名前を使用する場合、その前にコロン(:)を付ける必要がありますが、WHEN句ではコロンを付けることはできません。 |
WHEN |
トリガー制約条件を説明します。 | Conditionが論理式の場合、その中には関連名前のみを含める必要があり、クエリステートメントやPL関数の呼び出しは含めることができません。WHEN句で指定されたトリガー制約条件は、BEFOREおよびAFTER行トリガーでのみ使用できます。 |
FOLLOWS と PRECEDES |
トリガーの順序を定義するために使用されます。 | OceanBaseデータベースの現行バージョンでは、同一のトリガーイベントとアクションタイムを持つ同じテーブルに複数のトリガーを定義することがサポートされています。デフォルトでは、同一のトリガーイベントとアクションタイムを持つトリガーのトリガー順序は不確定です。トリガーの順序を指定したい場合は、FOLLOWSを使用して新しいトリガーを既存のトリガーの後にアクティブ化するように指定できます。現時点では、PRECEDESを使用して新しいトリガーを既存のトリガーの前にアクティブ化する指定はサポートされておらず、構文上はPRECEDESのみがサポートされています。 |
注意
基表が変更された場合(INSERT、UPDATEまたは DELETE)にストアドプロシージャを実行する必要があります。トリガーの実行は、それが依存する基表の変更に応じて自動的にトリガーされるため、アプリケーションとは無関係です。トリガーを使用することで、データの一貫性と完全性を保証できます。
行レベルトリガーの使用制限
行レベルトリガーには以下の使用制限があります：
行レベルトリガー内部の
SELECTステートメントは、SELECT ... INTO ...構造またはカーソル定義に使用されるSELECTステートメントに限定されます。行レベルトリガーでは、データベースのトランザクション制御ステートメントである
COMMIT、ROLLBACK、およびSAVEPOINTステートメントを使用することはできません。行レベルトリガーによって呼び出されるストアドプロシージャや関数も、データベースのトランザクション制御ステートメントを使用することはできません。
OLDとNEWの擬似レコード
行トリガーがトリガーされた場合、挿入（INSERT）、更新（UPDATE）、または削除（DELETE）されたレコードの列値を参照する必要があり、操作前または操作後の列値を使用する場合があります。構文は以下のとおりです：
:NEWモディファイアは、操作完了後の列の値へのアクセスを示します。:OLDモディファイアは、操作完了前の列の値へのアクセスを示します。
使用の有効性は以下の表のとおりです：
| 特性 | INSERT | UPDATE | DELETE |
|---|---|---|---|
| OLD | NULL | 有効 | 有効 |
| NEW | 有効 | 有効 | NULL |
例
シンプルな行レベルトリガーを作成します。
obclient> CREATE TABLE regions(
region_id NUMBER(5,0),
region_name VARCHAR(50)
);
Query OK, 0 rows affected
obclient>INSERT INTO regions VALUES(1,'Europe'),(4,'Middle East and Africa'),(3,'Asia'),
(2,'Americas');
Query OK, 4 rows affected
obclient> CREATE TABLE reg_his AS SELECT * FROM regions WHERE 1=2;
Query OK, 0 rows affected
obclient> CREATE OR REPLACE TRIGGER del_new_region
BEFORE DELETE ON regions
FOR EACH ROW
WHEN (old.region_id >3)
BEGIN
INSERT INTO reg_his(region_id , region_name )
VALUES( :old.region_id, :old.region_name );
END;
/
Query OK, 0 rows affected
上記の例では、テーブル regions のデータに対する削除操作が実行されると、region_id が3より大きいレコードがテーブル reg_his に移動されます。
obclient>SELECT * FROM regions;
+-----------+------------------------+
| REGION_ID | REGION_NAME |
+-----------+------------------------+
| 1 | Europe |
| 4 | Middle East and Africa |
| 3 | Asia |
| 2 | Americas |
+-----------+------------------------+
4 rows in set (0.00 sec)
obclient>SELECT * FROM reg_his;
Empty set
obclient> DELETE FROM regions WHERE region_id>2;
Query OK, 2 rows affected
obclient> SELECT * FROM regions;
+-----------+-------------+
| REGION_ID | REGION_NAME |
+-----------+-------------+
| 1 | Europe |
| 2 | Americas |
+-----------+-------------+
2 rows in set
obclient> SELECT * FROM reg_his;
+-----------+------------------------+
| REGION_ID | REGION_NAME |
+-----------+------------------------+
| 4 | Middle East and Africa |
+-----------+------------------------+
1 row in set
ステートメントレベルトリガーの作成
ステートメントレベルトリガーとは、テーブルレベルトリガーのことです。ステートメントレベルトリガーは、ステートメント全体をトリガーイベントとし、制約条件に一致した場合にトリガーを一度起動します。テーブル上で特定のDML操作に対してステートメントトリガーを作成した場合、DML操作が実行される際にシステムは自動的にトリガーの対応するステートメントを実行します。
ステートメントレベルトリガーを作成する構文は以下のとおりです：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column ...]]}
ON [schema.] table_name
BEGIN
...
END;
ステートメント型トリガーの使用制限
ステートメント型トリガーでは、
OLDおよびNEWを使用して操作前または操作後のデータにアクセスすることはできません。ステートメント型トリガーでは、
WHEN句を使用することはできません。
例
新しいテーブルから削除されたデータを記録するためのテーブルを作成します。
テーブル
studentとテーブルold_studentを作成します。テーブルold_studentは、テーブルstudentから削除されたデータを記録するために使用されます。obclient> CREATE TABLE student( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex VARCHAR2(15) NOT NULL ); Query OK, 0 rows affected obclient> CREATE TABLE old_student AS SELECT * FROM student; Query OK, 0 rows affected obclient> INSERT INTO student VALUES('01','a',6,'Male'),('02','b',7,'Female'); Query OK, 2 rows affected obclient> SELECT * FROM student; +----+------+-----+--------+ | ID | NAME | AGE | SEX | +----+------+-----+--------+ | 01 | a | 6 | Male | | 02 | b | 7 | Female | +----+------+-----+--------+ 2 rows in set obclient> SELECT * FROM old_student; Empty setステートメント型トリガー
TIG_OLD_STUを作成します。obclient> delimiter / obclient> CREATE OR REPLACE TRIGGER TIG_OLD_STU BEFORE DELETE ON student BEGIN INSERT INTO old_student SELECT * FROM student; END; / Query OK, 0 rows affected obclient> delimiter ;テーブル
studentのデータを削除した後、2つのテーブルのデータを確認します。obclient> DELETE student; Query OK, 0 rows affected obclient> SELECT * FROM student; Empty set obclient> SELECT * FROM old_student; +----+------+-----+--------+ | ID | NAME | AGE | SEX | +----+------+-----+--------+ | 01 | a | 6 | Male | | 02 | b | 7 | Female | +----+------+-----+--------+ 2 rows in set