怎样用触发器实现类别表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