Re: Implementing complicated constraints

Noons wrote:
> Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<cjj042$hkm$1_at_news.BelWue.DE>...
>
>

>>However I'm sure instead of trigger where available in 8.0, whereas

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:

<quote>
-- 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;

</quote>

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 Received on Mon Oct 04 2004 - 01:58:06 CDT