新聞發布用到的存儲過程和觸發器

--作者  尐肥羊
--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