Prev: how many records contribute to a page.
Next: Please help! set integrity using system tablespace
From: --CELKO-- on 1 Nov 2009 00:21
The est answer I got was:
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
customer_duns_nbr CHAR(9) NOT NULL
REFERENCES Companies (duns_nbr),
…);

CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
item_nbr INTEGER NOT NULL,
PRIMARY KEY (order_nbr, item_nbr)
specifier_duns_nbr CHAR(1) DEFAULT 'C' NOT NULL
CHECK(specifier_duns_nbr IN ('C', 'U')),
…);
Where C = customer and U = Us in the specifier_duns_nbr. Then we make
a VIEW of Order_Details with a CASE expression :

CASE specifier_duns_nbr
WHEN 'U' THEN '999999999'
WHEN 'C' THEN (SELECTcustomer_duns_nbr
FROM Orders AS O
WHERE O.order_nbr =
Order_Details.order_nbr)
ELSE NULL END

and add an INSTEAD OF TRIGGER and we are done.
First  |  Prev  | 
Pages: 1 2
Prev: how many records contribute to a page.
Next: Please help! set integrity using system tablespace