INSTEAD OF DML triggers are created on non-updatable views and can be used to update the view. INSTEAD OF triggers can determine the intended operation and execute the appropriate DML operation on the underlying table.
INSTEAD OF triggers can only be row-level triggers. INSTEAD OF triggers can read but cannot modify the OLD and NEW values.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Examples
The following example creates a view named order_list to display information about customers and their orders. Because the primary key order_id of the orders table is not unique in the result set of the joined view, the view is not inherently updatable. This example creates an INSTEAD OF trigger to handle INSERT statements directed at 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 the rows to be inserted do not exist */
obclient> SELECT COUNT(*) FROM order_list WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set
/* Insert row data 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 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 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 the row has been inserted into the orders table */
obclient> SELECT COUNT(*) FROM orders WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set