Noons wrote:
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

Holger Baer <holger.baer (AT) science-computing (DOT) de> wrote


Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.

Takes all kinds, doesn't it?
My last 8.0.6 was Feb 03. After I sent them
a memo in early 01 explaining the dangers of staying
with old releases, they finally decided to upgrade in
Feb 03. Peoplesoft site. What else...

Noons wrote:
I believe in his book (which I haven't got at hand right now) Tom explains why,
maybe I get the chance for a little digging myself. My rather clumsy recollection
is that this way you've declarative referential integrity and don't have to dig
through the code.

Take any customized software or worse, one of the famous home grown applications
the customer wrote himself (and the original developers as well as the documentation
are lost in the mist of time)... Oh, and in Feb 03 I could convince another customer
at least to move from 7.3.4 to 8.1.6 which got finally disposed this summer.

Did I already tell that today(!) I was called because at another site their hp-ux 10.20/ Oracle
8.0.4 server died? Lucky ol' us that we didn't support that one. Backups exist. But no
Hardware to fix or replace the old one....


Cheers,

Holger

Holger Baer <holger.baer (AT) science-computing (DOT) de> wrote


Nnow the feeling only too well. Had a customer for a few years on
7.3.4 NT4. They never bothered to upgrade even though their PS HR
app was carking everywhere with 16-bit code. Then they kicked us out:
apparently we were "too expensive" even though we were charging one
tenth what the competition does.

Then they had the nerve to call us last year to "come and
fix it" when the server (a Pentium 1!) finally went up in flames.
And they sort of expected us to do this under some presumed
"warranty"! Of course. Look, there goes another pig...

Noons wrote:
I went through chapter 7 of Tom Kyte's book again. It makes perfect sense in
the cases he described, because with the trigger you either lock the
tables in question, or you end up with constraint violations:

Session A:
insert into view //trigger fires check constraints ok
Session B:
insert into view // trigger fires check
// constraints ok

Session A:
commit; // trigger does not reevalute constraints
Session B:
commit; // now we violated our constraint

I don't have the time at hand to check the proposed solution if it
behaves any different, but at least this is something to watch out.
And it's the reason why Tom chooses FBI over trigger.


Cheers,

Holger

Holger Baer <holger.baer (AT) science-computing (DOT) de> wrote

Thanks, going to re-read that one.