oracle的sql reference文档中的例子,非常经典地回答了instead of触发器的用法和实际用途(就是本贴所要求的)。 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 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;
可更新视图要满足一定要求,比如不要用分组,不要用函数返回等 另 ORACLE系统表中,有一张视图,记录所有可以更新和不可以更新的视图(和数据表):User_Updateable_Columns Select * From user_Updateable_Columns Where Table_name=Upper('Viewname')
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 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;
另
ORACLE系统表中,有一张视图,记录所有可以更新和不可以更新的视图(和数据表):User_Updateable_Columns
Select * From user_Updateable_Columns Where Table_name=Upper('Viewname')