直接UPDATE VIEW肯定是不行的.如果你的VIEW中不包含GROUP BY等汇总操作的话,那可以用INSTEAD OF TRIGGER来完成,见下例:INSTEAD OF Trigger Example In this example, customer data is stored in two tables. The object view ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values. CREATE TABLE customers_sj ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2) ); CREATE TABLE customers_pa ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2) ); CREATE TRIGGER CREATE TYPE customer_t AS OBJECT ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2), location VARCHAR2(20) ); CREATE VIEW all_customers (cust) AS SELECT customer_t (cust, address, credit, ’SAN_JOSE’) FROM customers_sj UNION ALL SELECT customer_t (cust, address, credit, ’PALO_ALTO’) FROM customers_pa;CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers FOR EACH ROW BEGIN IF (:new.cust.location = ’SAN_JOSE’) THEN INSERT INTO customers_sj VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit); ELSE INSERT INTO customers_pa VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit); END IF; END;
建议还是修改原表,视图的数据也会自动变化。
In this example, customer data is stored in two tables. The object view ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values.
CREATE TABLE customers_sj
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );
CREATE TABLE customers_pa
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );
CREATE TRIGGER
CREATE TYPE customer_t AS OBJECT
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20) );
CREATE VIEW all_customers (cust)
AS SELECT customer_t (cust, address, credit, ’SAN_JOSE’)
FROM customers_sj
UNION ALL
SELECT customer_t (cust, address, credit, ’PALO_ALTO’)
FROM customers_pa;CREATE TRIGGER instrig
INSTEAD OF INSERT ON all_customers
FOR EACH ROW
BEGIN
IF (:new.cust.location = ’SAN_JOSE’) THEN
INSERT INTO customers_sj
VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);
END IF;
END;