对buyitem这个表建立触发器(主要是更新库存),然后buyitem插入的数据有肯能是一条,有可能是几条,
create trigger updatebalance
on buyitem
for insert
as
declare @atrow int;
set @atrow = 1; select * from inserted;// 这是插入的数据,怎么循环一条一条的判断,
while @atrow <@rowcount
{
//取出一条记录的产品ID,和数量保存到临时变量,不知道怎么写
//产品id跟库存表balance的产品id判断,如果此产品在库存中有的话。就更新数量,否则就是新建数据
@atrow +=1;
}
create trigger updatebalance
on buyitem
for insert
as
declare @atrow int;
set @atrow = 1; select * from inserted;// 这是插入的数据,怎么循环一条一条的判断,
while @atrow <@rowcount
{
//取出一条记录的产品ID,和数量保存到临时变量,不知道怎么写
//产品id跟库存表balance的产品id判断,如果此产品在库存中有的话。就更新数量,否则就是新建数据
@atrow +=1;
}
Create table T1(id int,qty int)
Create table T2(id int,qty int)
insert into T2 values(1,10)
insert into T2 values(3,20)
GOCreate trigger tri_t1 on T1
for insert
as
begin
Update T2 set qty=inserted.qty
from inserted
where T2.id=inserted.id insert into T2
select id,qty from inserted
where not exists(select 1 from T2 where id=inserted.id)
end
GOinsert into T1
select 1 as id,99 as qty
union all
select 2,50
union all
select 4,100select * from T2
GO
drop table T1,T2