Re: Implementing complicated constraints
Date: Mon, 04 Oct 2004 08:58:06 +0200
Message-ID: <cjqs9v$pui$1_at_news.BelWue.DE>
Noons wrote:
> Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<cjj042$hkm$1@news.BelWue.DE>...
>
>
>>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:
<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.
Cheers,
Holger Received on Mon Oct 04 2004 - 08:58:06 CEST