テーブルまたはビューにDMLトリガーを作成できます。トリガーイベントは、DMLステートメントDELETE、INSERT、およびUPDATEで構成されます。
適用対象
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
OceanBaseデータベースの現行バージョンでは、DML行トリガーの作成がサポートされており、含まれるタイプは以下のとおりです：
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
トリガー名はストアドプロシージャ名やパッケージ名とは異なり、独立したネームスペースを持っているため、トリガー名はテーブルやストアドプロシージャと同じ名前を持つことができます。
DMLトリガーの作成構文
DMLトリガーを作成する構文は以下のとおりです：
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]
[WHEN condition]
[FOLLOWS | PRECEDES] other_trigger_name
trigger_body;
パラメータの説明は以下の表のとおりです：
| パラメータ | 意味 | 説明 |
|---|---|---|
| BEFOREとAFTER | トリガーのトリガーシーケンスがそれぞれ前トリガーと後トリガー方式であることを示します。 | 前トリガーとは、トリガーイベントの実行前に現在作成されているトリガーをトリガーすることです。後トリガーとは、トリガーイベントの実行後に現在作成されているトリガーをトリガーすることです。 |
| FOR EACH ROW | トリガーが行トリガーであることを示します。 | 行トリガーでは、DMLステートメントの操作がデータベース内の複数行に影響を与える場合、それらの各データ行について、トリガー制約条件を満たしている限り、トリガーが1回アクティブ化されます。 |
| REFERENCING | 関連する名前について説明します。行トリガーのPLブロックおよびWHEN句では、関連する名前を使用して現在の新しい列値と古い列値を参照できます。デフォルトの関連名前はそれぞれOLDとNEWです。 |
トリガーのPLブロックで関連名前を使用する場合、その前にコロン(:)を付ける必要がありますが、WHEN句ではコロンを付けることはできません。 |
| FOLLOWSとPRECEDES | 同じ時点のトリガーのトリガー順序を定義します。指定されたトリガーは存在し、正常にコンパイルされていなければなりませんが、トリガーを有効にする必要はありません。 |
|
| WHEN | トリガー制約条件について説明します。 | Conditionが論理式の場合、関連する名前を含める必要がありますが、クエリステートメントやPL関数の呼び出しは含めることができません。WHEN句で指定されたトリガー制約条件は、BEFOREおよびAFTER行トリガーでのみ使用できます。 |
注意
ベーステーブルが変更された場合(INSERT、UPDATEまたはDELETE)にストアドプロシージャを実行し、トリガーはそれに依存するベーステーブルの変更に応じて自動的にトリガーされるため、アプリケーションとは無関係です。トリガーを使用することで、データの一貫性と完全性を保証できます。
トリガーの使用制限
トリガーには以下の使用制限があります：
トリガー内部の
SELECTステートメントはSELECT ... INTO ...構造、またはカーソル定義に使用されるSELECTステートメントに限定されます。トリガー内では、データベースのトランザクション制御ステートメントである
COMMIT、ROLLBACK、およびSAVEPOINTステートメントを使用することはできません。トリガーによって呼び出されるストアドプロシージャや関数も、データベースのトランザクション制御ステートメントを使用することはできません。
OLDとNEW疑似レコード
トリガーが発火した際、挿入（INSERT）、更新（UPDATE）、または削除（DELETE）されたレコードの列値を参照する必要がある場合、操作前または操作後の列の値を使用することがあります。構文は以下のとおりです：
:NEW修飾子は、アクセス操作完了時の列の値を示します。:OLD修飾子は、アクセス操作完了時の列の値を示します。
使用の有効性は以下の表に示されています：
| 特性 | INSERT | UPDATE | DELETE |
|---|---|---|---|
| OLD | NULL | 有効 | 有効 |
| NEW | 有効 | 有効 | NULL |
例1：行レベルのトリガーを作成します。
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
Records: 4 Duplicates: 0 Warnings: 0
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
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
例2：ステートメントレベルのトリガーを作成します。
obclient> create table message (id int, mes varchar2(100));
Query OK, 0 rows affected (0.078 sec)
obclient> create table stmt_t (id int, name varchar2(10), grade int);
Query OK, 0 rows affected (0.064 sec)
obclient> create or replace trigger stmt_tri before insert or delete or update of name, grade
on stmt_t
begin
case
when inserting then
insert into message values (1, 'before inserting...');
when deleting then
insert into message values (2, 'before deleting...');
when updating ('name') then
insert into message values (3, 'before updating name ...');
when updating ('grade') then
insert into message values (4, 'before updating grade...');
end case;
end;
/
Query OK, 0 rows affected (0.151 sec)
上記の例では、このトリガーはstmt_tテーブルに対する挿入、削除、または更新操作の前に、対応するメッセージをmessageテーブルに記録します。