对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;
}
on buyitem
for insert
as
select * from insertd
if @@rowcount<>0
begin
insert blance select * from insertd d where blance.Tid=d.Tid
end
else
insert blance default values不知道对不对。关注中。。
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,100--查看T2結果
select * from T2
/*
id qty
----------- -----------
1 99 --更新
3 20 --沒变
4 100 --插入
2 50 --插入
*/
GO
drop table T1,T2
create trigger tri_buyitem on buyitem for insert
as
update balance set 数量字段=数量+a.数量 from balance a,inserted b
where a.产品id=b.产品id insert into balance(产品id,数量) select 产品id,数量 from inserted a
where not exists(select 1 from balance where 产品id=a.产品id)
go
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;
}