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