评论 - 存储过程 ----------------------------------- 在线等 ------------------------------

SQL code

--产品表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,    --评论目标IDccontent text,    --评论内容)

求个存储过程，或思路，要求如下：当插入一条评论时，判断该评论属于产品或新闻，如果是产品，则查询出对应产品的评论数，并在对应产品中现有的评论数里加1 （pcomments产品评论数字段）---感谢！



[解决办法]
可以在评论表里写个insert触发器，判断插入的评论类型的值，如果是1，则将产品表中的评论数加1，如果是2，则将新闻表中的评论数加1
[解决办法]
虽然触发器有诸多弊端，但还是建议写触发器，如果涉及到批量插入数据好处理点

SQL code

---->>TravyLee生成测试数据:--产品表CREATE table product(pid int primary key identity,    --产品编号ptitle varchar(100),    --产品标题pcontent text,    --产品内容pcomments int,    --评论数)GOinsert product(ptitle,pcontent,pcomments)select 'title1','content1',0 union allselect 'title2','content1',0 union allselect 'title3','content1',0--新闻表CREATE table news(nid int primary key identity,    --新闻编号ntitle varchar(100),    --新闻标题ncontent text,    --新闻内容ncomments int,    --评论数)GOinsert news(ntitle,ncontent,ncomments)select 'title1','content1',0 union allselect 'title2','content1',0 union allselect 'title3','content1',0go--评论表CREATE Table comments(cid int primary key identity,    --评论编号ctype int,    --评论类型（1为产品，2为新闻）cobjId int,    --评论目标IDccontent text,    --评论内容)if OBJECT_ID('tri_test') is not nulldrop trigger tri_testgocreate trigger tri_test on commentsfor insertas;with tas(select cobjId,count(1) as pcomments from inserted where ctype=1group by cobjId)update productset product.pcomments=product.pcomments+t.pcomments from twhere t.cobjId=product.pid;with mas(select cobjId,count(1) as ncomments from inserted where ctype=2group by cobjId)update newsset news.ncomments=news.ncomments+m.ncomments from mwhere m.cobjId=news.nidgoinsert comments(ctype,cobjId,ccontent)select 1,2,'very good' union allselect 2,1,'very good' union allselect 2,3,'very good' union allselect 2,1,'very good' union allselect 1,2,'very good'select * from product/*pid    ptitle    pcontent    pcomments--------------------------------------1    title1    content1    02    title2    content1    23    title3    content1    0*/select * from news/*nid    ntitle    ncontent    ncomments-----------------------------------------1    title1    content1    22    title2    content1    03    title3    content1    1*/