XPost: comp.databases.oracle.server   
   From: holger.baer@science-computing.de   
      
   Noons wrote:   
   > Holger Baer  wrote in message   
   news:...   
   >   
   >   
   >>However I'm sure instead of trigger where available in 8.0, whereas   
   >   
   >   
   > I don't think so.   
      
   This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:   
      
      
      
   -- 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;   
      
      
      
   And I remember them being available for sure because then I was a developer   
   at a small company and we weren't allowed to use any enterprise feature because   
   it would make the initial licence cost so much higher for our prospective   
   customers.   
      
   Plus, after I moved to my current employer, I started to support an application   
   that made heavily use of views with instead of triggers. They used 8.0.6 too.   
   And if I didn't force them, they would still ...   
      
      
   Cheers,   
      
   Holger   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)