江南才子 发表于 2021-8-4 15:04:35

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
onArticle
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
onArticle
for delete as   
declare   
@categoryId int
Begin   
    select @categoryId = categoryId
      from deleted
    update Category      
    set counter = counter - 1   
    where categoryId = @categoryId   
End

文档来源:51CTO技术博客https://blog.51cto.com/u_15322177/3266607
页: [1]
查看完整版本: SQL Server 新闻门户网站数据库设计与实现