日期:2014-05-18  浏览次数:20514 次

一条简单的insert触发怎么写不好?
CREATE   TRIGGER   ins_sales   ON   [dbo].[sales]  
FOR   INSERT  
AS
update   products   set   cpsl   =   cpsl   +   (select   sum(inserted.xssl)  
from   inserted   where   products.cpid   =   inserted.cpid   )  

update   products   set   cpsl   =   cpsl   +   (select   sum(inserted.xssl)  
from   inserted   group   by   cpid   having     products.cpid   =   inserted.cpid   )  

意思是在sales表中添一条记录,相应的把xssl累加到products表的cpsl中
上面两条都不行,应该怎么写?

------解决方案--------------------
CREATE TRIGGER ins_sales ON [dbo].[sales]
FOR INSERT
AS
BEGIN
update p
set
cpsl = isnull(p.cpsl,0)+i.xssl
from
products p,inserted i
where
p.cpid=i.cpid

insert into products(cpid,cpsl)
select cpid,xssl from inserted
where
not exists(select 1 from products where cpid=inserted.cpid)
END
GO
------解决方案--------------------
CREATE TRIGGER ins_sales ON [dbo].[sales]
FOR INSERT
AS
update products set cpsl = t.xssl
from procucts ,
(select cpid,sum(inserted.xssl) xssl from sales group by cpid) t
where products.cpid = t.cpid
go