INSTEAD OFトリガーは、編集可能なビューまたは編集不可能なビューに作成でき、ビューを更新するために使用されます。INSTEAD OFトリガーは、予想される操作を決定し、ベーステーブル上で適切なDML操作を実行できます。
INSTEAD OFトリガーは行レベルのトリガーのみです。INSTEAD OF DMLトリガーはOLDとNEWの値を読み取ることができますが、OLDとNEWの値を変更することはできません。
INSTEAD OFトリガーを作成する構文は次のとおりです：
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE}
ON [schema.] view_name
[FOR EACH ROW]
[{ FOLLOWS | PRECEDES } other_trigger_name]
BEGIN
...
END;
ステートメントの使用方法：
トリガーを作成するユーザーは、
CREATE TRIGGERシステム権限を持っている必要があります。OR REPLACE：作成しようとしているトリガーの名前が既に存在する場合、新しい定義を使用してトリガーを再作成します。view_name：ビュー名。INSTEAD OFトリガーはビューに対してのみ作成できます。FOLLOWS | PRECEDES：トリガーの順序を定義するために使用されます。OceanBaseデータベースの現行バージョンでは、同一のトリガーイベントとアクションタイムを持つ同じテーブルに複数のトリガーを定義できます。デフォルトでは、同一のトリガーイベントとアクションタイムを持つトリガーのトリガー順序は不確定です。トリガーの順序を指定したい場合は、FOLLOWSを使用して新しいトリガーを既存のトリガーの後に有効化するように指定できます。PRECEDESを使用して新しいトリガーを既存のトリガーの前に有効化することは現在サポートされておらず、構文的にはPRECEDESのみサポートされています。
INSTEAD OFトリガーを使用してビューを更新する例：
obclient> CREATE TABLE customers(
customer_id NUMBER(20) NOT NULL ,
cust_last_name VARCHAR(25) DEFAULT NULL,
cust_first_name VARCHAR(25) DEFAULT NULL,
cust_address VARCHAR(40) DEFAULT NULL,
cust_nationkey NUMBER(20) DEFAULT NULL,
cust_phone CHAR(15) DEFAULT NULL,
cust_acctbal DECIMAL(10,2) DEFAULT NULL,
cust_mktsegment CHAR(10) DEFAULT NULL,
cust_comment VARCHAR(117) DEFAULT NULL,
PRIMARY KEY(customer_id));
Query OK, 1 row affected
obclient> CREATE TABLE orders(
order_id NUMBER(20) NOT NULL ,
customer_id NUMBER(20) NOT NULL ,
order_status CHAR(1) DEFAULT NULL,
total_price DECIMAL(10,2) DEFAULT NULL,
order_date DATE NOT NULL,
order_priority CHAR(15) DEFAULT NULL,
order_clerk CHAR(15) DEFAULT NULL,
order_shippriority NUMBER(20) DEFAULT NULL,
order_comment VARCHAR(79) DEFAULT NULL,
PRIMARY KEY(order_id,order_date,customer_id));
Query OK, 0 rows affected
obclient> CREATE OR REPLACE VIEW order_list AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Query OK, 0 rows affected
obclient> delimiter /
obclient> CREATE OR REPLACE TRIGGER order_list_insert
INSTEAD OF INSERT ON order_list
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
-20107,
'Duplicate customer or order ID');
END order_list_insert;
/
Query OK, 0 rows affected
obclient> delimiter ;
/* 挿入する行が存在しないことを照会して表示する */
obclient> SELECT COUNT(*) FROM order_list WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set
/* ビューに行データを挿入する */
obclient> INSERT INTO order_list VALUES (111, 'Smith', 'William', 3500,
'23-MAR-2001', 0);
Query OK, 1 row affected
/* その行がビューに挿入されたことを照会して表示する */
obclient> SELECT COUNT(*) FROM order_list WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
/* その行がcustomersテーブルに挿入されたことを照会して表示する */
obclient> SELECT COUNT(*) FROM customers WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
/* その行がordersテーブルに挿入されたことを照会して表示する */
obclient> SELECT COUNT(*) FROM orders WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set