You can create an INSTEAD OF DML trigger on an uneditable view to update the view. An INSTEAD OF trigger can determine the expected operations and execute appropriate DML operations on the base table.
An INSTEAD OF trigger can only be a row-level trigger. An INSTEAD OF trigger can read the OLD and NEW values, but cannot modify these values
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Examples
Create an order_list view to display information about customers and their orders. The primary key order_id of the order list is not unique in the result set of the joined view. Therefore, the view cannot be updated. You can create an INSTEAD OF trigger to process an INSERT statement that points to the view. The trigger inserts row data into the base tables customers and orders of the view.
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;
/* Query and display that the row to be inserted does not exist. */
obclient> SELECT COUNT(*) FROM order_list WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set
/* Insert the row into the view. */
obclient> INSERT INTO order_list VALUES (111, 'Smith', 'William', 3500,
'23-MAR-2001', 0);
Query OK, 1 row affected
/* Query and display that the row has been inserted into the view. */
obclient> SELECT COUNT(*) FROM order_list WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
/* Query and display that the row has been inserted into the customers table. */
obclient> SELECT COUNT(*) FROM customers WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
/* Query and display that the row has been inserted into the orders table. */
obclient> SELECT COUNT(*) FROM orders WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set