ALTER TABLE IMAGETYPE
ADD ( PRIMARY KEY (TYPE_ID) ) ;
CREATE TABLE KEYWORD (
KEY_ID VARCHAR2(20) NOT NULL,
KEYWORD VARCHAR2(80) NULL,
KEY_EXPLAIN VARCHAR2(500) NULL
);
ALTER TABLE KEYWORD
ADD ( PRIMARY KEY (KEY_ID) ) ;
CREATE TABLE News (
NEWS_ID VARCHAR2(20) NOT NULL,
NEWS_TITLE VARCHAR2(100) NULL,
NEWS_CONTENT CLOB NULL,
FILE_NAME VARCHAR2(40) NULL,
CREATE_DATE DATE NULL,
NEWS_SOURCE VARCHAR2(100) NULL,
NEWS_AUTHOR VARCHAR2(20) NULL,
NEWS_EDITOR VARCHAR2(20) NULL,
CLICK_NUM NUMBER(4) DEFAULT 0 NULL,
RECOMMAND_TAG NUMBER(2) DEFAULT 0 NULL,
SMALL_INCON VARCHAR2(80) NULL,
BID_INCON VARCHAR2(80) NULL,
CHECK_TAG NUMBER(2) DEFAULT 0 NULL,
STATUS NUMBER(2) NULL,
ADD_INDEX NUMBER(2) DEFAULT 0 NULL,
PICNEWS_TAG NUMBER(2) DEFAULT 0 NULL,
R_CATALOG VARCHAR2(20) NULL,
R_TEMPLATE VARCHAR2(20) NULL
);
ALTER TABLE News
ADD ( PRIMARY KEY (NEWS_ID) ) ;
CREATE TABLE NEWS_KEYWORD (
KEY_ID VARCHAR2(20) NOT NULL,
NEWS_ID VARCHAR2(20) NOT NULL
);
ALTER TABLE NEWS_KEYWORD
ADD ( PRIMARY KEY (KEY_ID, NEWS_ID) ) ;
CREATE TABLE TEMPLATE (
TEMPLATE_ID VARCHAR2(20) NOT NULL,
TEMPLATE_TITLE VARCHAR2(100) NULL,
TEMPLATE_CONTENT CLOB NULL,
CREATE_DATE DATE NULL,
TEMPLATE_TYPE NUMBER(2) DEFAULT 0 NULL,
STATUS NUMBER(2) DEFAULT 0 NULL,
R_CATALOG VARCHAR2(20) NULL
);
ALTER TABLE TEMPLATE
ADD ( PRIMARY KEY (TEMPLATE_ID) ) ;
CREATE TABLE VIDO (
VIDO_NAME VARCHAR2(20) NOT NULL,
SAVE_PATH VARCHAR2(100) NULL,
VIDO_FROM VARCHAR2(100) NULL,
CREATE_DATE DATE NULL,
VIDO_SIZE NUMBER(5) NULL,
VIDO_EXPLAIN VARCHAR2(500) NULL,
KEYWORD VARCHAR2(50) NULL,
TYPE_ID VARCHAR2(20) NULL
);
ALTER TABLE VIDO
ADD ( PRIMARY KEY (VIDO_NAME) ) ;
CREATE TABLE VIDOTYPE (
TYPE_ID VARCHAR2(20) NOT NULL,
TYPE_NAME VARCHAR2(50) NULL,
TYPE_EXPLAIN VARCHAR2(500) NULL
);
ALTER TABLE VIDOTYPE
ADD ( PRIMARY KEY (TYPE_ID) ) ;
ALTER TABLE COMMENTS
ADD ( FOREIGN KEY (R_NEWS)
REFERENCES News ) ;
ALTER TABLE IMAGE
ADD ( FOREIGN KEY (R_TYPE)
REFERENCES IMAGETYPE ) ;
ALTER TABLE News
ADD ( FOREIGN KEY (R_TEMPLATE)
REFERENCES TEMPLATE ) ;
ALTER TABLE News
ADD ( FOREIGN KEY (R_CATALOG)
REFERENCES CATALOG ) ;
ALTER TABLE NEWS_KEYWORD
ADD ( FOREIGN KEY (NEWS_ID)
REFERENCES News ) ;
ALTER TABLE NEWS_KEYWORD
ADD ( FOREIGN KEY (KEY_ID)
REFERENCES KEYWORD ) ;
ALTER TABLE VIDO
ADD ( FOREIGN KEY (TYPE_ID)
REFERENCES VIDOTYPE ) ;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on CATALOG
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* CATALOG R/2 News ON PARENT DELETE CASCADE */
delete from News
where
/* News.R_CATALOG = :old.CATALOG_ID */
News.R_CATALOG = :old.CATALOG_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_CATALOG after UPDATE on CATALOG for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on CATALOG
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* CATALOG R/2 News ON PARENT UPDATE CASCADE */
if
/* :old.CATALOG_ID <> :new.CATALOG_ID */
:old.CATALOG_ID <> :new.CATALOG_ID
then
update News
set
/* News.R_CATALOG = :new.CATALOG_ID */
News.R_CATALOG = :new.CATALOG_ID
where
/* News.R_CATALOG = :old.CATALOG_ID */
News.R_CATALOG = :old.CATALOG_ID;
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tD_IMAGETYPE after DELETE on IMAGETYPE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on IMAGETYPE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* IMAGETYPE R/8 IMAGE ON PARENT DELETE CASCADE */
delete from IMAGE
where
/* IMAGE.R_TYPE = :old.TYPE_ID */
IMAGE.R_TYPE = :old.TYPE_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_IMAGETYPE after UPDATE on IMAGETYPE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on IMAGETYPE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* IMAGETYPE R/8 IMAGE ON PARENT UPDATE CASCADE */
if
/* :old.TYPE_ID <> :new.TYPE_ID */
:old.TYPE_ID <> :new.TYPE_ID
then
update IMAGE
set
/* IMAGE.R_TYPE = :new.TYPE_ID */
IMAGE.R_TYPE = :new.TYPE_ID
where
/* IMAGE.R_TYPE = :old.TYPE_ID */
IMAGE.R_TYPE = :old.TYPE_ID;
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tD_KEYWORD after DELETE on KEYWORD for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on KEYWORD
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* KEYWORD R/6 NEWS_KEYWORD ON PARENT DELETE CASCADE */
delete from NEWS_KEYWORD
where
/* NEWS_KEYWORD.KEY_ID = :old.KEY_ID */
NEWS_KEYWORD.KEY_ID = :old.KEY_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_KEYWORD after UPDATE on KEYWORD for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on KEYWORD
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* KEYWORD R/6 NEWS_KEYWORD ON PARENT UPDATE CASCADE */
if
/* :old.KEY_ID <> :new.KEY_ID */
:old.KEY_ID <> :new.KEY_ID
then
update NEWS_KEYWORD
set
/* NEWS_KEYWORD.KEY_ID = :new.KEY_ID */
NEWS_KEYWORD.KEY_ID = :new.KEY_ID
where
/* NEWS_KEYWORD.KEY_ID = :old.KEY_ID */
NEWS_KEYWORD.KEY_ID = :old.KEY_ID;
end if;
end;
/create trigger tD_News after DELETE on News for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on News
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/7 NEWS_KEYWORD ON PARENT DELETE CASCADE */
delete from NEWS_KEYWORD
where
/* NEWS_KEYWORD.NEWS_ID = :old.NEWS_ID */
NEWS_KEYWORD.NEWS_ID = :old.NEWS_ID; /* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/5 COMMENTS ON PARENT DELETE CASCADE */
delete from COMMENTS
where
/* COMMENTS.R_NEWS = :old.NEWS_ID */
COMMENTS.R_NEWS = :old.NEWS_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_News after UPDATE on News for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on News
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/7 NEWS_KEYWORD ON PARENT UPDATE CASCADE */
if
/* :old.NEWS_ID <> :new.NEWS_ID */
:old.NEWS_ID <> :new.NEWS_ID
then
update NEWS_KEYWORD
set
/* NEWS_KEYWORD.NEWS_ID = :new.NEWS_ID */
NEWS_KEYWORD.NEWS_ID = :new.NEWS_ID
where
/* NEWS_KEYWORD.NEWS_ID = :old.NEWS_ID */
NEWS_KEYWORD.NEWS_ID = :old.NEWS_ID;
end if; /* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/5 COMMENTS ON PARENT UPDATE CASCADE */
if
/* :old.NEWS_ID <> :new.NEWS_ID */
:old.NEWS_ID <> :new.NEWS_ID
then
update COMMENTS
set
/* COMMENTS.R_NEWS = :new.NEWS_ID */
COMMENTS.R_NEWS = :new.NEWS_ID
where
/* COMMENTS.R_NEWS = :old.NEWS_ID */
COMMENTS.R_NEWS = :old.NEWS_ID;
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tI_NEWS_KEYWORD after INSERT on NEWS_KEYWORD for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- INSERT trigger on NEWS_KEYWORD
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/7 NEWS_KEYWORD ON CHILD INSERT RESTRICT */
select count(*) into numrows
from News
where
/* :new.NEWS_ID = News.NEWS_ID */
:new.NEWS_ID = News.NEWS_ID;
if (
/* */
numrows = 0
)
then
raise_application_error(
-20002,
'Cannot INSERT NEWS_KEYWORD because News does not exist.'
);
end if; /* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* KEYWORD R/6 NEWS_KEYWORD ON CHILD INSERT RESTRICT */
select count(*) into numrows
from KEYWORD
where
/* :new.KEY_ID = KEYWORD.KEY_ID */
:new.KEY_ID = KEYWORD.KEY_ID;
if (
/* */
numrows = 0
)
then
raise_application_error(
-20002,
'Cannot INSERT NEWS_KEYWORD because KEYWORD does not exist.'
);
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_NEWS_KEYWORD after UPDATE on NEWS_KEYWORD for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on NEWS_KEYWORD
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* News R/7 NEWS_KEYWORD ON CHILD UPDATE RESTRICT */
select count(*) into numrows
from News
where
/* :new.NEWS_ID = News.NEWS_ID */
:new.NEWS_ID = News.NEWS_ID;
if (
/* */
numrows = 0
)
then
raise_application_error(
-20007,
'Cannot UPDATE NEWS_KEYWORD because News does not exist.'
);
end if;
/* KEYWORD R/6 NEWS_KEYWORD ON CHILD UPDATE RESTRICT */
select count(*) into numrows
from KEYWORD
where
/* :new.KEY_ID = KEYWORD.KEY_ID */
:new.KEY_ID = KEYWORD.KEY_ID;
if (
/* */
numrows = 0
)
then
raise_application_error(
-20007,
'Cannot UPDATE NEWS_KEYWORD because KEYWORD does not exist.'
);
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tD_TEMPLATE after DELETE on TEMPLATE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on TEMPLATE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* TEMPLATE R/4 News ON PARENT DELETE SET NULL */
update News
set
/* News.R_TEMPLATE = NULL */
News.R_TEMPLATE = NULL
where
/* News.R_TEMPLATE = :old.TEMPLATE_ID */
News.R_TEMPLATE = :old.TEMPLATE_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_TEMPLATE after UPDATE on TEMPLATE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on TEMPLATE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* TEMPLATE R/4 News ON PARENT UPDATE CASCADE */
if
/* :old.TEMPLATE_ID <> :new.TEMPLATE_ID */
:old.TEMPLATE_ID <> :new.TEMPLATE_ID
then
update News
set
/* News.R_TEMPLATE = :new.TEMPLATE_ID */
News.R_TEMPLATE = :new.TEMPLATE_ID
where
/* News.R_TEMPLATE = :old.TEMPLATE_ID */
News.R_TEMPLATE = :old.TEMPLATE_ID;
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tD_VIDOTYPE after DELETE on VIDOTYPE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- DELETE trigger on VIDOTYPE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* VIDOTYPE R/10 VIDO ON PARENT DELETE CASCADE */
delete from VIDO
where
/* VIDO.TYPE_ID = :old.TYPE_ID */
VIDO.TYPE_ID = :old.TYPE_ID;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/create trigger tU_VIDOTYPE after UPDATE on VIDOTYPE for each row
-- ERwin Builtin Tue Nov 04 17:38:47 2003
-- UPDATE trigger on VIDOTYPE
declare numrows INTEGER;
begin
/* ERwin Builtin Tue Nov 04 17:38:47 2003 */
/* VIDOTYPE R/10 VIDO ON PARENT UPDATE CASCADE */
if
/* :old.TYPE_ID <> :new.TYPE_ID */
:old.TYPE_ID <> :new.TYPE_ID
then
update VIDO
set
/* VIDO.TYPE_ID = :new.TYPE_ID */
VIDO.TYPE_ID = :new.TYPE_ID
where
/* VIDO.TYPE_ID = :old.TYPE_ID */
VIDO.TYPE_ID = :old.TYPE_ID;
end if;
-- ERwin Builtin Tue Nov 04 17:38:47 2003
end;
/所以的建表语句