instead of?

[bigprobs]

Not being an administrator but a humble programmer,
could someone tell me how to turn on "instead-of" trigger functionality in Oracle?

Thanks

 

[alexhu]

From Oracle 8 SQL Reference

INSTEAD OF Triggers
Use INSTEAD OF triggers to perform DELETE, UPDATE, or INSERT operations on views, which are not inherently modifiable. "The View Query" for a list of constructs that prevent inserts, updates, or deletes on a view. In the following 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.location = 'SAN_JOSE') THEN
INSERT INTO customers_sj
VALUES new.cust, :new.address, :new.credit);
ELSE
INSERT INTO customers_pa
VALUES new.cust, :new.address, :new.credit);
END IF;
END;



Alex

 

[bigprobs]

I know HOW to use them - they are turned off and I need to know how to enable them!

Performing select * from v$option

.. ..
Instead-of triggers FALSE
.. ..


Thanks