テーブルやビューに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 テーブルに記録します。