新闻发布用到的存储过程和触发器

--作者  尐肥羊
--2011-07-15
--
--注意的问题
--1.select的时候出现聚会函数的话,要用到group by
--2.values 注意要加上s


-- 没有外键约束的情况下
create trigger trigCategoryDelete
on category
after delete
-- after 针对的是先进行category删除之后才执行的,如果有外键约束的话.
-- 可以用instead of
as
begin
  delete news where caId=(select * from deleted)
  --删除出错有外键约束
end

go

--改成用instead of代替delete的操作
--先删除新闻表,再删除类别表
create trigger trigCategoryDelete
on category
instead of delete

as
begin
  declare @caid int
  select @caid=id from deleted
  --删除评论
  delete comment where newsId=(select newsId from news where caId=@caId)
  --删除新闻
  delete news where caId=@caId
  --删除类别
  delete  category where id=@caId
end

go

--存储过程取出最新10条新闻(所属类别,新闻标题,发布时间)

create procedure news_selectNew
as
begin
  select top 8 n.id,n.title,n.createTime,c.name from news n
  inner join  category c on n.caId=c.id
  order by n.createTime desc    
end
go

--取出10热点新闻(评论最多的新闻)

create procedure news_selectHot
as
begin 
  select top 10 n.id,n.title,n.createTime,c.[name],count(com.id) as 评论数
  from news n
  inner join  category c on c.id=n.caId
  inner join comment com on com.newsid=n.id
  group by n.id,n.title,n.createTime,c.[name]
  order by 评论数 desc
end
go


--根据类别id取出类别下的所有新闻
create procedure news_selectByCaid
@caId int
as
begin
  select n.id,n.title,n.createTime,c.[name] from news n
  inner join  category c on c.id=n.caId
  where n.caId=caId
  order by n.createTime desc
end

go

exec news_selectByCaid 10  --执行类别编号为10的


--根据id显示新闻
create procedure news_selectById
@id int
as
begin
  select title,[content],createTime,caId from news where id=@id
end
go

exec news_selectById 4


--根据标题搜索新闻
create procedure news_selectByTitle
@title varchar(100)
as
begin
  select n.id,n.title,n.createTime,c.[name] from news n
  inner join  category c on c.id=n.caId
  where n.title like '%' +@title+ '%'
  order by n.createTime desc
end

go

exec news_selectByTitle 's'


--根据内容搜索新闻

create procedure news_selectByContent
@content varchar(1000)
as
begin
  select n.id,n.title,n.createTime,c.[name] from news n
  inner join  category c on c.id=n.caId
  where n.content like '%' +@content+ '%'
  order by n.createTime desc
end
go


--添加新闻
create procedure news_insert
@title varchar(100),
@content text,
@caid int
as
begin
  insert into news(title,[content],caid) 
  values(@title,@content,@caid)
end
go

exec news_insert 'ttt', 'ttt' , 10


--更新新闻
create procedure news_update
@title varchar(100),
@content text,
@caid int,
@id int
as
begin
  update news 
  set title=@title,[content]=@content,caid=@caid
  where id=@id
end
go


--删除新闻, 连同评论

create procedure news_delete
@id int
as
begin
--先删除新闻下的新闻评论
  delete comment where newsId=@id
--在删除新闻
  delete news where id=@id
end
go

exec news_delete 10