create proc temptoshoppingcart
(@cartname nvarchar(50))
asinsert into shoppingcart(pid,quantity,cartname)
select pid, quantity, cartname from tempcart where cartname=@cartname
go
(@cartname nvarchar(50))
asinsert into shoppingcart(pid,quantity,cartname)
select pid, quantity, cartname from tempcart where cartname=@cartname
go
CREATE TRIGGER cart ON shoppingcart
instead of INSERT
AS
declare @pid2 int,@cartname2 nvarchar(50),@quantity2 int
select @pid2=pid,@cartname2=cartname,@quantity2=quantity from inserted
if exists(select pid from shoppingcart where pid=@pid2 and
cartname=@cartname2)
update shoppingcart set quantity=quantity+@quantity2 where
cartname=@cartname2 and pid=@pid2
else
insert into shoppingcart(pid,cartname,quantity)values(@pid2,@cartname2,@quantity2)
就是插入的pid号如果存在就改写quantity字段,如果不存在则执行插入
郁闷,困扰很长时间了,都不知道怎么解决了。谁能帮忙下。谢谢
@cartname nvarchar(50),
@pid int,
@quantity int
as
insert into shoppingcart(pid,quantity,cartname)
values(@pid,@quantity,@cartname)
if exists (select pid from shoppingcart group by pid having count(pid)>1)
update shoppingcart set quantity=1 where pid=@pid
ON shoppingcart
instead of INSERT
AS
if exists(select pid from shoppingcart where pid=(select pid from inserted) and
cartname=(select cartname from inserted))
update shoppingcart set quantity=quantity+inserted.quantity2
from inserted join shoppingcart
on cartname=inserted.cartname2 and pid=inserted.pid2
else
insert into shoppingcart(pid,cartname,quantity)values(@pid2,@cartname2,@quantity2)
if exists(select pid from shoppingcart where pid=(select pid from inserted)
提示:子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
完整代码如下:CREATE TRIGGER cart
ON shoppingcart
instead of INSERT
AS
declare @pid int,@cartname varchar(8),@quantity int
select @pid=pid,@cartname=cartname,@quantity=quantity from inserted
if exists(select pid from shoppingcart where pid in (select pid from inserted)
and cartname in(select cartname from inserted))
update shoppingcart set quantity=b.quantity+a.quantity
from inserted a join shoppingcart b
on a.cartname=b.cartname and a.pid=b.pid
else
insert into shoppingcart(pid,cartname,quantity)values(@pid,@cartname,@quantity)
@pid int,
@cartname nvarchar(50),
@quantity int
as
begin tran
insert into shoppingcart(pid,cartname,quantity)
values(@pid,@cartname,@quantity)
if exists (select pid from shoppingcart group by pid having count(pid)>1)
begin
rollback tran
update shoppingcart set quantity=quantity+@quantity where pid=@pid
end
else
begin
commit tran
end
如果还有问题的话,请贴出数据及表结构