テーブルやビューに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 がサポートされていますが、実行順序を指定するためには FOLLOWS を使用してください。 |
注意
ベーステーブルが変更(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