SQL Server 新闻门户网站数据库设计与实现

江南才子

create database sc;
use sc;
create table Admin(
adminId int identity(201301,1)primary key,
adminName varchar(255) unique not null,
pswd varchar(255) not null
);

CREATE TABLE Category (
categoryId int identity(1,1) primary key,
categoryName varchar(255) unique NOT NULL,
counter int DEFAULT 0
);

create table Article(
articleId int identity(1,1) primary key,
title varchar(255) not null,
content ntext ,
time date not null,
categoryId int not null,
foreign key (categoryId) references Category(categoryId) on delete no action on update cascade
);
/*触发器 添加一条新闻，对应新闻项总数加一*/
create trigger articleInsert  
on  Article  
for Insert as  
declare   
 @categoryId int  
Begin   
  select @categoryId = categoryId   
    from inserted  
  update Category  
  set counter = counter + 1  
  where categoryId = @categoryId  
End
   
/*触发器 删除一条新闻，对应新闻项总数减一*/

create trigger articleDelete  
on  Article  
for delete as  
declare   
 @categoryId int  
Begin   
  select @categoryId = categoryId  
    from deleted  
  update Category    
  set counter = counter - 1  
  where categoryId = @categoryId  
End