You can create an INSTEAD OF 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 DML trigger can read but not modify the OLD and NEW values.
Syntax for creating an INSTEAD OF trigger:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE}
ON [schema.] view_name
[FOR EACH ROW]
BEGIN
...
END;
Statement usage notes:
OR REPLACE: If the name of the trigger to be created already exists, a new trigger is created based on the specified definition.view_name: the name of the view. An INSTEAD OF trigger can be created only on a view.You must have the
CREATE TRIGGERsystem privilege to create triggers.
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_info 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_info 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