INSTEAD OF DMLトリガーは、編集不可なビュー上に作成され、ビューの更新に使用できます。INSTEAD OF トリガーは、予想される操作を特定し、基礎テーブル上で適切なDML操作を実行することができます。
INSTEAD OF トリガーは行レベルのトリガーのみです。INSTEAD OF トリガーは OLD および NEW 値を読み取ることはできますが、変更することはできません。
適用対象
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
例
以下の例では、顧客およびその注文に関する情報を表示するために、ビューorder_listを作成します。注文テーブルの主キーorder_idは、ビューに結合された結果セット内で一意ではないため、ビュー自体は本質的に更新不可能です。この例では、ビューへのINSERTステートメントを処理するためにINSTEAD OFトリガーを作成します。トリガーは、行データをビューのベーステーブルcustomersとordersに挿入します。
obclient> CREATE TABLE customers(
customer_id NUMBER(30) NOT NULL ,
cust_last_name VARCHAR(30) DEFAULT NULL,
cust_first_name VARCHAR(30) DEFAULT NULL,
cust_address VARCHAR(50) DEFAULT NULL,
cust_nationkey NUMBER(20) DEFAULT NULL,
cust_phone CHAR(15) DEFAULT NULL,
cust_acctbal DECIMAL(10,3) DEFAULT NULL,
cust_mktsegment CHAR(10) DEFAULT NULL,
cust_comment VARCHAR(200) DEFAULT NULL,
PRIMARY KEY(customer_id));
Query OK, 1 row affected
obclient> CREATE TABLE orders(
order_id NUMBER(30) NOT NULL ,
customer_id NUMBER(30) NOT NULL ,
order_status CHAR(5) DEFAULT NULL,
total_price DECIMAL(10,3) DEFAULT NULL,
order_date DATE NOT NULL,
order_priority CHAR(10) DEFAULT NULL,
order_clerk CHAR(20) DEFAULT NULL,
order_shippriority NUMBER(10) DEFAULT NULL,
order_comment VARCHAR(100) DEFAULT NULL,
PRIMARY KEY(order_id,order_date,customer_id));
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