楼主这种情况适合利用触发器处理单条记录 insert into ID_Lista select 1,'tb' union all select 2,'ab' union all select 3,'cd' 上面插入三次,但触发器只工作一次
CREATE TABLE ID_Tprice(Pid int,Tqty int) go CREATE TABLE ID_Lista(Uid int identity(1,1) primary key,Pid int,num VARCHAR(20)) GO insert into ID_Tprice select 1,0 union all select 2,0 union all select 3,0 union all select 4,0 ; go create TRIGGER auto_update_IDprice_Tqty on dbo.ID_Lista for insert as begin update t set Tqty = Tqty+1 from ID_Tprice t,inserted i where t.Pid=i.Pid end go insert into ID_Lista select 1,'tb' union all select 2,'ab' union all select 3,'cd' ; select * from ID_Tprice select * from ID_Listadrop TRIGGER auto_update_IDprice_Tqty drop table ID_Tprice,ID_Lista/**********Pid Tqty ----------- ----------- 1 1 2 1 3 1 4 0(4 行受影响)Uid Pid num ----------- ----------- -------------------- 1 1 tb 2 2 ab 3 3 cd(3 行受影响)
或者这句 update t set Tqty=Tqty+1 from ID_Tprice t,insertd i where t.Pid=i.Pid 能改造一下
create TRIGGER auto_update_IDprice_Tqty on dbo.ID_Lista for insert as begin update ID_Tprice set Tqty = Tqty+isnull((select count(*) from inserted where ID_Tprice.pid = pid),0) end go insert into ID_Lista select 1,'tb' union all select 1,'tc' union all select 2,'ab' union all select 3,'cd' ; select * from ID_Tprice select * from ID_Listadrop TRIGGER auto_update_IDprice_Tqty/************Pid Tqty ----------- ----------- 1 2 2 1 3 1 4 0(4 行受影响)Uid Pid num ----------- ----------- -------------------- 1 1 tb 2 1 tc 3 2 ab 4 3 cd(4 行受影响)
--这样试试 declare @i int set @i=0 update t set @i=@i+1,Tqty = Tqty+@i from ID_Tprice t,inserted i where t.Pid=i.Pid
insert into ID_Lista
select 1,'tb' union all
select 2,'ab' union all
select 3,'cd'
上面插入三次,但触发器只工作一次
CREATE TABLE ID_Tprice(Pid int,Tqty int)
go
CREATE TABLE ID_Lista(Uid int identity(1,1) primary key,Pid int,num VARCHAR(20))
GO
insert into ID_Tprice
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,0
;
go
create TRIGGER auto_update_IDprice_Tqty
on dbo.ID_Lista
for insert
as
begin
update t
set Tqty = Tqty+1
from
ID_Tprice t,inserted i
where t.Pid=i.Pid
end
go
insert into ID_Lista
select 1,'tb' union all
select 2,'ab' union all
select 3,'cd'
;
select * from ID_Tprice
select * from ID_Listadrop TRIGGER auto_update_IDprice_Tqty
drop table ID_Tprice,ID_Lista/**********Pid Tqty
----------- -----------
1 1
2 1
3 1
4 0(4 行受影响)Uid Pid num
----------- ----------- --------------------
1 1 tb
2 2 ab
3 3 cd(3 行受影响)
from
ID_Tprice t,insertd i
where t.Pid=i.Pid
能改造一下
create TRIGGER auto_update_IDprice_Tqty
on dbo.ID_Lista
for insert
as
begin
update ID_Tprice
set Tqty = Tqty+isnull((select count(*) from inserted where ID_Tprice.pid = pid),0)
end
go
insert into ID_Lista
select 1,'tb' union all
select 1,'tc' union all
select 2,'ab' union all
select 3,'cd'
;
select * from ID_Tprice
select * from ID_Listadrop TRIGGER auto_update_IDprice_Tqty/************Pid Tqty
----------- -----------
1 2
2 1
3 1
4 0(4 行受影响)Uid Pid num
----------- ----------- --------------------
1 1 tb
2 1 tc
3 2 ab
4 3 cd(4 行受影响)
declare @i int
set @i=0
update t
set @i=@i+1,Tqty = Tqty+@i
from
ID_Tprice t,inserted i
where t.Pid=i.Pid