本文发表在 rolia.net 枫下论坛CREATE TABLE T_Order (
Order_ID NUMBER NOT NULL,
Order_Status VARCHAR2(5) NULL
);
ALTER TABLE T_Order
ADD ( PRIMARY KEY (Order_ID) ) ;
CREATE TABLE T_Book (
Book_ID NUMBER NOT NULL,
Book_Name VARCHAR2(200) NULL,
Book_Desc VARCHAR2(500) NULL,
Book_Price NUMBER(7,2) NULL
);
ALTER TABLE T_Book
ADD ( PRIMARY KEY (Book_ID) ) ;
CREATE TABLE T_OrderItem (
OrderItem_ID NUMBER NOT NULL,
Order_ID NUMBER NULL,
Book_ID NUMBER NULL,
Item_Quantity NUMBER NULL,
Item_Status VARCHAR2(5) NULL
);
ALTER TABLE T_OrderItem
ADD ( PRIMARY KEY (OrderItem_ID) ) ;
ALTER TABLE T_OrderItem
ADD ( FOREIGN KEY (Order_ID)
REFERENCES T_Order
ON DELETE CASCADE ) ;
ALTER TABLE T_OrderItem
ADD ( FOREIGN KEY (Book_ID)
REFERENCES T_Book
ON DELETE CASCADE ) ;
create trigger tD_T_Order after DELETE on T_Order for each row
-- DELETE trigger on T_Order
declare numrows INTEGER;
begin
/* T_Order R/2 T_OrderItem ON PARENT DELETE CASCADE */
delete from T_OrderItem
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Order_ID = :old.Order_ID;
end;
/
create trigger tU_T_Order after UPDATE on T_Order for each row
-- UPDATE trigger on T_Order
declare numrows INTEGER;
begin
/* T_Order R/2 T_OrderItem ON PARENT UPDATE CASCADE */
if
/* %JoinPKPK(:%Old,:%New," <> "," or ") */
:old.Order_ID <> :new.Order_ID
then
update T_OrderItem
set
/* %JoinFKPK(T_OrderItem,:%New," = ",",") */
T_OrderItem.Order_ID = :new.Order_ID
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Order_ID = :old.Order_ID;
end if;
end;
/
create trigger tD_T_Book after DELETE on T_Book for each row
-- DELETE trigger on T_Book
declare numrows INTEGER;
begin
/* T_Book R/1 T_OrderItem ON PARENT DELETE CASCADE */
delete from T_OrderItem
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Book_ID = :old.Book_ID;
end;
/
create trigger tU_T_Book after UPDATE on T_Book for each row
-- UPDATE trigger on T_Book
declare numrows INTEGER;
begin
/* T_Book R/1 T_OrderItem ON PARENT UPDATE CASCADE */
if
/* %JoinPKPK(:%Old,:%New," <> "," or ") */
:old.Book_ID <> :new.Book_ID
then
update T_OrderItem
set
/* %JoinFKPK(T_OrderItem,:%New," = ",",") */
T_OrderItem.Book_ID = :new.Book_ID
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Book_ID = :old.Book_ID;
end if;end;
/
to get all order item detail:
select * from t_order o, t_orderitem oi
where o.order_ID = oi.order_ID
to get one order’s full price:
select sum(oi.item_quantity*b.book_price) from t_order o, t_orderImte oi, t_book b
where o.order_ID = oi.order_ID
and oi.book_ID = b.book_ID更多精彩文章及讨论，请光临枫下论坛 rolia.net