--产品表
CREATE table product
(
pid int primary key identity,    --产品编号
ptitle varchar(100),    --产品标题
pcontent text,    --产品内容
pcomments int,    --评论数
)

GO

--新闻表
CREATE table news
(
nid int primary key identity,    --新闻编号
ntitle varchar(100),    --新闻标题
ncontent text,    --新闻内容
ncomments int,    --评论数
)
GO


--评论表
CREATE Table comments
(
cid int primary key identity,    --评论编号
ctype int,    --评论类型（1为产品，2为新闻）
cobjId int,    --评论目标ID
ccontent text,    --评论内容
)

---->>TravyLee生成测试数据:
--产品表
CREATE table product
(
pid int primary key identity,    --产品编号
ptitle varchar(100),    --产品标题
pcontent text,    --产品内容
pcomments int,    --评论数
)
GO
insert product(ptitle,pcontent,pcomments)
select 'title1','content1',0 union all
select 'title2','content1',0 union all
select 'title3','content1',0
--新闻表
CREATE table news
(
nid int primary key identity,    --新闻编号
ntitle varchar(100),    --新闻标题
ncontent text,    --新闻内容
ncomments int,    --评论数
)
GO
insert news(ntitle,ncontent,ncomments)
select 'title1','content1',0 union all
select 'title2','content1',0 union all
select 'title3','content1',0
go
--评论表
CREATE Table comments
(
cid int primary key identity,    --评论编号
ctype int,    --评论类型（1为产品，2为新闻）
cobjId int,    --评论目标ID
ccontent text,    --评论内容
)

if OBJECT_ID('tri_test') is not null
drop trigger tri_test
go
create trigger tri_test on comments
for insert
as
;with t
as(
select cobjId,count(1) as pcomments 
from inserted where ctype=1
group by cobjId
)
update product
set product.pcomments=product.pcomments+t.pcomments from t
where t.cobjId=product.pid
;with m
as(
select cobjId,count(1) as ncomments 
from inserted where ctype=2
group by cobjId
)
update news
set news.ncomments=news.ncomments+m.ncomments from m
where m.cobjId=news.nid
go


insert comments(ctype,cobjId,ccontent)
select 1,2,'very good' union all
select 2,1,'very good' union all
select 2,3,'very good' union all
select 2,1,'very good' union all
select 1,2,'very good'

select * from product
/*
pid    ptitle    pcontent    pcomments
--------------------------------------
1    title1    content1    0
2    title2    content1    2
3    title3    content1    0
*/

select * from news
/*
nid    ntitle    ncontent    ncomments
-----------------------------------------
1    title1    content1    2
2    title2    content1    0
3    title3    content1    1
*/