11111

USE Master
GO

IF EXISTS(Select * from sysdatabases where name='BlogDB')
  DROP DATABASE BlogDB
GO

Create Database BlogDB
On
(
  Name='BlogDB_MDF.mdf',
  FileName='E:/Blog/DB/BlogDB_MDF.mdf',
  Size=1 mb,
  FileGrowth=10%
)
Log on
(
 Name='BlogDB_LDF.ldf',
 FileName='E:/Blog/DB/BlogDB_LDF.ldf',
 Size=1 mb,
 FileGrowth=10%
)
GO

Use BlogDB
GO
--用户表
CREATE TABLE Blogers
(
  BID   int   PRIMARY KEY IDENTITY(1,1)        NOT NULL,  --用户ID
  BLoginName   varchar(16) UNIQUE    NOT NULL,  --登录名
  BPassword   varchar(150)      NOT NULL,  --密码
  BAuthor   varchar(50)      ,     --用户名
  BEmail   varchar(60)      ,     --电子邮箱
  BTitle   varchar(100)  DEFAULT('我的博客')  ,   --博客标题
  BSubTitle   varchar(200)  DEFAULT('欢迎光临')  ,   --博客副标题
  BOriginalCount  int   DEFAULT(0)   NOT NULL,  --原创文章数
  BReprintCount   int  DEFAULT(0)   NOT NULL,  --转载文章数
  BCommentCount  int   DEFAULT(0)   NOT NULL,    --发表评论数
  BRegisterTime  datetime  DEFAULT(GETDATE())  NOT NULL   --注册日期
)
GO

--文章表
CREATE Table Articles
(
  AID    int   PRIMARY KEY IDENTITY(1,1)   NOT NULL,  --文章ID
  ABlogID   int           NOT NULL,  --所属博客ID
  ATypeID  int      NOT NULL,  --类型ID
  ACategoryID   int        NOT NULL,  --类别ID
  ATitle   varchar(100)      NOT NULL,  --文章标题
  AContent   ntext        NOT NULL,  --文章内容
  ASummary  varchar(200),        --摘要
  APublishTime   datetime DEFAULT(GETDATE())  NOT NULL,  --发布时间
  AFeedBackCount  int   DEFAULT(0)   NOT NULL,  --被评论次数
  AClickCount   int   DEFAULT(0)    NOT NULL  --被点击次数
)
GO

--评论表
CREATE Table Comments
(
  CID   int   PRIMARY KEY IDENTITY(1,1)  NOT NULL,  --评论ID
  CArticleID   int       NOT NULL,  --所评论文章的ID
  CBlogID   int       NOT NULL,  --评论者ID
  CTitle   nvarchar(100),        --标题
  CContent   nvarchar(500)     NOT NULL,  --内容
  CCommentTime   datetime       NOT NULL  --评论时间 
)
GO

--类别表
CREATE Table Categories
(
  CID    int   PRIMARY KEY IDENTITY(1,1) NOT NULL,  --类别ID
  CName   nvarchar(50)      NOT NULL,  --类别名
  CBlogID   int   DEFAULT(-1)    NOT NULL  --所属博客
)
GO

Insert Into Categories (CName) Values('.Net')
Insert Into Categories (CName) Values('Java')
Insert Into Categories (CName) Values('数据库')
Insert Into Categories (CName) Values('软件工程')
Insert Into Categories (CName) Values('Web开发')

--类型表
CREATE Table Types
(
  TID   int  PRIMARY KEY IDENTITY(1,1)  NOT NULL,  --类型ID
  TName  nvarchar(50)        NOT NULL,  --类型名
  TImage nvarchar(50)        NOT NULL,  --图片路径
)
GO

Insert Into Types (TName) Values('原创')
Insert Into Types (TName) Values('转帖')

Alter Table Articles
Add Constraint FK_ABlogID FOREIGN KEY(ABlogID) REFERENCES Blogers(BID)

Alter Table Articles
Add Constraint FK_ACategoryID FOREIGN KEY(ACategoryID) REFERENCES Categories(CID)

Alter Table Articles
Add Constraint FK_ATypeID FOREIGN KEY(ATypeID) REFERENCES Types(TID)

Alter Table Comments
Add Constraint FK_CArticleID FOREIGN KEY(CArticleID) REFERENCES Articles(AID)

Alter Table Comments
Add Constraint FK_CBlogID FOREIGN KEY (CBlogID) REFERENCES Blogers(BID)

--Trigger
CREATE TRIGGER trig_AddArticleCount
ON Articles
FOR INSERT
AS
  DECLARE @BID int
  DECLARE @TID int
  SELECT @BID=ABlogID,@TID=ATypeID FROM inserted
  IF(@TID=1)
    Update Blogers Set BOriginalCount=BOriginalCount+1 Where BID=@BID
  IF(@TID=2)
    Update Blogers Set BReprintCount=BReprintCount+1 Where BID=@BID
GO

CREATE TRIGGER trig_ReduceArticleCount
ON Articles
FOR DELETE
AS
  DECLARE @BID int
  DECLARE @TID int
  SELECT @BID=ABlogID,@TID=ATypeID FROM deleted
  IF(@TID=1)
    Update Blogers Set BOriginalCount=BOriginalCount-1 Where BID=@BID
  IF(@TID=2)
    Update Blogers Set BReprintCount=BReprintCount-1 Where BID=@BID
GO

CREATE TRIGGER trig_AddComment
ON Comments
FOR INSERT
AS
  DECLARE @ArticleID int
  DECLARE @BlogID    int
  SELECT @ArticleID=CArticleID,@BlogID=CBlogID FROM inserted
  Update Blogers Set BCommentCount=BCommentCount+1 Where BID=@BlogID
  Update Articles Set AFeedBackCount=AFeedBackCount+1 Where AID=@ArticleID 
GO

CREATE TRIGGER trig_ReduceComment
ON Comments
FOR INSERT
AS
  DECLARE @ArticleID int
  DECLARE @BlogID    int
  SELECT @ArticleID=CArticleID,@BlogID=CBlogID FROM deleted
  Update Blogers Set BCommentCount=BCommentCount-1 Where BID=@BlogID
  Update Articles Set AFeedBackCount=AFeedBackCount-1 Where AID=@ArticleID 
GO