You can create an INSTEAD OF trigger on an editable or 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 DML trigger can read but not modify the OLD and NEW values. Before you create triggers, make sure that you have the CREATE TRIGGER system privilege.
The syntax for creating an INSTEAD OF trigger is as follows:
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;
where
OR REPLACEindicates that if the name of the trigger to be created already exists, a new trigger is created based on the specified definition.view_namespecifies the name of the view. An INSTEAD OF trigger can be created only on a view.FOLLOWS | PRECEDESspecifies the order of triggers. OceanBase Database allows you to associate multiple triggers of the same triggering event and activating time to the same table. By default, triggers with the same triggering event and activating time are activated in a random order. You can specifyFOLLOWS, so that a new trigger is activated after the current trigger. At present, you can specifyPRECEDES, which means that a new trigger is activated before the current trigger, but the configuration does not take effect.
Example: Use an INSTEAD OF trigger to update a view.
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;
/* 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_info 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