テーブルやビューに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