比如说id为电脑的商品,原来库存20,然后新进10台,在insert into store的时候把num改为30
你这表结构有问题,如果要做统计应该把流通数据和统计数据分开,兴建一张表 CREATE TRIGGER NAME ON this_insert FOR INSERT AS declare @sum int declare @id intselect @id = id from inserted select @sum =suM(computer) from table a where a.id = @id update secondtable set sumcomputer = @sum where id = @id go 试试
--楼主是这个意思吧? 如果表中有的商品,就增加数量,没有的,就增加一条记录--下面是例子:--创建测试的表 create table 表(Gno int,Gname varchar(10),Gnumber int) insert into 表 select 100,'cpu',15 union all select 101,'***',12 go--创建处理的触发器 create trigger t_insert on 表 instead of insert as update 表 set Gnumber=a.Gnumber+b.Gnumber from 表 a join inserted b on a.Gno=b.Gnoinsert into 表 select a.* from inserted a left join 表 b on a.Gno=b.Gno where b.Gno is null go--插入数据测试 insert into 表 select 101,'***',100 union all select 102,'硬盘',100 union all select 100,'cpu',100 go--显示结果 select * from 表 go--删除测试 drop table 表/*--测试结果Gno Gname Gnumber ----------- ---------- ----------- 100 cpu 115 101 *** 112 102 硬盘 100(所影响的行数为 3 行) --*/
CREATE TRIGGER NAME ON [test]
FOR INSERT
AS
update test
set num=a.col1*a.col2
from inserted a
go
你想怎么更新法?
CREATE TRIGGER NAME ON this_insert
FOR INSERT
AS
declare @sum int
declare @id intselect @id = id from inserted
select @sum =suM(computer) from table a where a.id = @id update secondtable
set sumcomputer = @sum
where id = @id
go
试试
create table 表(Gno int,Gname varchar(10),Gnumber int)
insert into 表
select 100,'cpu',15
union all select 101,'***',12
go--创建处理的触发器
create trigger t_insert on 表
instead of insert
as
update 表 set Gnumber=a.Gnumber+b.Gnumber
from 表 a join inserted b on a.Gno=b.Gnoinsert into 表 select a.*
from inserted a left join 表 b on a.Gno=b.Gno
where b.Gno is null
go--插入数据测试
insert into 表
select 101,'***',100
union all select 102,'硬盘',100
union all select 100,'cpu',100
go--显示结果
select * from 表
go--删除测试
drop table 表/*--测试结果Gno Gname Gnumber
----------- ---------- -----------
100 cpu 115
101 *** 112
102 硬盘 100(所影响的行数为 3 行)
--*/