Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Tue, 25 Feb 2003 14:32:41 -0000
Message-ID: <b3fuvn$18g2$1_at_sp15at20.hursley.ibm.com>

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message

news:rIu6a.8$i82.99_at_news.oracle.com...

> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> news:b32jok$19em$1_at_sp15at20.hursley.ibm.com...
> > FYI In DB2 we can insert into UNION views, but it's strictly a one tuple
> in
> > the view to one tuple in one table affair. I could use INSTEAD OF
> triggers,
> > but to map the two inserts into one could still be difficult.
>
> Or, is it even possible?

Here is one I prepared eailer.

I wanted to get it to work in a data intergration senerio (aka a federated database). Unfortunalty, as things currently stand I can't define an INSTEAD OF trigger on a view that references a table in another database (a 'nickname' in DB" terminology).

Also FOR EACH STATEMENT is not available for INSTEAD OF triggers, so the trigger is not at simple as it should be. This is a possability that is in the air though
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0210rielau/0210rielau.ht ml#section6

CREATE TABLE phones (

id              INTEGER NOT NULL,
phonetype       VARCHAR(5) NOT NULL CHECK (phonetype IN ('VOICE', 'FAX')),
number          VARCHAR(10) NOT NULL,

UNIQUE (id, phonetype)
)
_at_
CREATE TABLE contacts (

id             INTEGER NOT NULL,
voice          VARCHAR(10) NOT NULL,
fax            VARCHAR(10) NOT NULL,

UNIQUE (id)
)
_at_
CREATE VIEW phonesV
AS (SELECT * FROM phones
WHERE NOT EXISTS
    (SELECT 1 from PHONES GROUP BY id HAVING COUNT(*) <> 2) )
--WITH CASCADED CHECK OPTION
_at_
CREATE TRIGGER phonI INSTEAD OF INSERT ON phonesV REFERENCING NEW_TABLE AS nt
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
 INSERT INTO phones SELECT * FROM nt
  EXCEPT SELECT * FROM phones;
 INSERT INTO contacts
 SELECT v.id, v.number as voice, f.number as fax  FROM nt V, nt F
 WHERE V.id = F.id
 AND V.phonetype = 'VOICE'
 AND F.phonetype = 'FAX'
 EXCEPT SELECT * FROM contacts;
END
_at_

INSERT INTO phonesV
VALUES (1,'VOICE','019221122'), (1,'FAX','010313231') _at_
SELECT * from contacts
_at_

ID VOICE FAX
----------- ---------- ----------

          1 019221122 010313231

  1 record(s) selected.

SELECT * from phones
_at_

ID PHONETYPE NUMBER
----------- --------- ----------

          1 FAX       010313231
          1 VOICE     019221122

  2 record(s) selected.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Tue Feb 25 2003 - 15:32:41 CET