怎样用触发器实现类别表Class自动统计标题(Title)的数量?
有类别表(Class),一级分类和二级分类都在这张表里面(根据字段type=1为一级,2为二级),根据ParentID判断二级属于哪个一级.
有标题表(Title),每个标题有一个BigClassID(所属一级大类)和一个SmallClassID(二级小类),来自Class表的ID字段值.
还有一个图片表(Pic),目的是存放一个标题下的多张图片,用它的字段(TitleID)与Title表关联起来.
要求根据这些用SQLSERVER2000触发器自动统计Class表里的一个字段Titlenum(标题数量),用来自动统计某一个类别中有多少个标题,以及有多少张图片(这个已经实现,见下面的代码)
本人刚开始使用SQLSERVER,请帮忙将Titlenum的触发器实现,实现立即散分!!!!!!!
下面是统计Title表和Class表的Picnum(图片数量),还有统计Class表中的Titlenum用触发器怎样实现?????
CREATE TRIGGER [ins_pic] ON [dbo].[Pic]
FOR INSERT
AS
declare @nTitleID int,@nBigClassID int,@nSmallClassID int
select @nTitleID=titleID from inserted
select @nBigClassID=BigClassID,@nSmallClassID=SmallClassID from title where id=@nTitleID
update title set picnum=picnum+1 where id=@nTitleID
update class set picnum=picnum+1 where id=@nBigClassID or id=@nSmallClassID
[解决办法]
写个触发器你参考下:
create trigger tr_Title
on Title
for insert,update,delete
as
set nocount on
update a
set TitleNum=a.TitleNum+b.TitleNum --b.membercount是这次操作的增量，可能为负数
from class a,(
select id,Type,sum(TitleNum) as TitleNum --增量由统计得到
from (
select BigClassID as id,1 as type,1 as TitleNum
from inserted
union all
select SmallClassID as id,2 as type,1 as TitleNum
from inserted
union all
select BigClassID as id,1 as type,-1 as TitleNum
from deleted
union all
select SmallClassID as id,2 as type,-1 as TitleNum
from deleted
) as t
group by id,Type
having sum(TitleNum) <> 0 --只取 <> 0的数据减少更改的记录数
) as b
where a.id=b.id --连接条件
and a.type=b.type
go
