if exists(select 1 from sysobjects where name='bbsuser') DROP TABLE bbsuser GOCREATE TABLE bbsuser( username VARCHAR(20) PRIMARY KEY, password VARCHAR(20) NOT NULL, nickname VARCHAR(20) NOT NULL, sex VARCHAR(2) NOT NULL, birthyear VARCHAR(4) NOT NULL, birthmonth VARCHAR(4) NOT NULL, birthday VARCHAR(4) NOT NULL, e_mail VARCHAR(50) NOT NULL, mobile VARCHAR(20) NULL, signature VARCHAR(250) NULL, usergrade VARCHAR(20) NULL, userscore INT NULL ) GOif exists(select 1 from sysobjects where name='sort') DROP TABLE sort GOCREATE TABLE sort( id INT PRIMARY KEY, sortname VARCHAR(20) NOT NULL , master VARCHAR(20) NULL REFERENCES bbsuser(username) ON DELETE CASCADE ) GO CREATE TRIGGER TRG_sort ON sort FOR DELETE AS DELETE topic WHERE sortid IN(SELECT ID FROM DELETED) GO if exists(select 1 from sysobjects where name='topic') DROP TABLE topic GOCREATE TABLE topic( id INT PRIMARY KEY, topicname VARCHAR(20) NOT NULL , topiccontent VARCHAR(2000) NULL, owner VARCHAR(20) NOT NULL REFERENCES bbsuser(username) ON DELETE CASCADE, time DATETIME NOT NULL, sortid INT NOT NULL --REFERENCES sort(id) ON DELETE CASCADE ) GOCREATE TRIGGER TRG_topic ON topic FOR DELETE AS DELETE responses WHERE topicid IN(SELECT ID FROM DELETED) GO if exists(select 1 from sysobjects where name='responses') DROP TABLE responses GOCREATE TABLE responses( id INT PRIMARY KEY, title VARCHAR(20) NOT NULL , content VARCHAR(2000) NULL, owner VARCHAR(20) NOT NULL REFERENCES bbsuser(username) ON DELETE CASCADE, time DATETIME NOT NULL, topicid INT NOT NULL --REFERENCES topic(id) ON DELETE CASCADE ) GOinsert into bbsuser values('manager','manager','管理员','男','1970','12','10','[email protected]','1391000000000','管理员','管理员',10) insert into sort values(1,'Struts','manager') insert into sort values(2,'Eclipse','manager') GOselect * from bbsuser select * from sortdelete from bbsuserselect * from bbsuser select * from sort GODROP TABLE responses DROP TABLE topic DROP TABLE sort DROP TABLE bbsuser GO
DROP TABLE bbsuser
GOCREATE TABLE bbsuser(
username VARCHAR(20) PRIMARY KEY,
password VARCHAR(20) NOT NULL,
nickname VARCHAR(20) NOT NULL,
sex VARCHAR(2) NOT NULL,
birthyear VARCHAR(4) NOT NULL,
birthmonth VARCHAR(4) NOT NULL,
birthday VARCHAR(4) NOT NULL,
e_mail VARCHAR(50) NOT NULL,
mobile VARCHAR(20) NULL,
signature VARCHAR(250) NULL,
usergrade VARCHAR(20) NULL,
userscore INT NULL
)
GOif exists(select 1 from sysobjects where name='sort')
DROP TABLE sort
GOCREATE TABLE sort(
id INT PRIMARY KEY,
sortname VARCHAR(20) NOT NULL ,
master VARCHAR(20) NULL
REFERENCES bbsuser(username) ON DELETE CASCADE
)
GO
CREATE TRIGGER TRG_sort ON sort
FOR DELETE
AS
DELETE topic WHERE sortid IN(SELECT ID FROM DELETED)
GO
if exists(select 1 from sysobjects where name='topic')
DROP TABLE topic
GOCREATE TABLE topic(
id INT PRIMARY KEY,
topicname VARCHAR(20) NOT NULL ,
topiccontent VARCHAR(2000) NULL,
owner VARCHAR(20) NOT NULL
REFERENCES bbsuser(username) ON DELETE CASCADE,
time DATETIME NOT NULL,
sortid INT NOT NULL
--REFERENCES sort(id) ON DELETE CASCADE
)
GOCREATE TRIGGER TRG_topic ON topic
FOR DELETE
AS
DELETE responses WHERE topicid IN(SELECT ID FROM DELETED)
GO
if exists(select 1 from sysobjects where name='responses')
DROP TABLE responses
GOCREATE TABLE responses(
id INT PRIMARY KEY,
title VARCHAR(20) NOT NULL ,
content VARCHAR(2000) NULL,
owner VARCHAR(20) NOT NULL
REFERENCES bbsuser(username) ON DELETE CASCADE,
time DATETIME NOT NULL,
topicid INT NOT NULL
--REFERENCES topic(id) ON DELETE CASCADE
)
GOinsert into bbsuser values('manager','manager','管理员','男','1970','12','10','[email protected]','1391000000000','管理员','管理员',10)
insert into sort values(1,'Struts','manager')
insert into sort values(2,'Eclipse','manager')
GOselect * from bbsuser
select * from sortdelete from bbsuserselect * from bbsuser
select * from sort
GODROP TABLE responses
DROP TABLE topic
DROP TABLE sort
DROP TABLE bbsuser
GO