库存表(商品id ,数量) 销售表(商品id,数量) create trigger test_t on 销售表 for insert as update 库存表 set 数量=数量-i.数量 from inserted i where i.商品id=库存表.商品id
--建立测试数据环境 create table store(code varchar(10),num int) insert into store select 'AAAA',100 insert into store select 'BBBB',200create table sales(code varchar(10),quantity int) go--创建触发器 create trigger trg_sales on sales for insert as begin update store set num=store.num-inserted.quantity from inserted where store.code=inserted.code end go--查看销售前库存数据 select * from store go/* code num ---------- ----------- AAAA 100 BBBB 200 */--向销售数据表中新增销售记录 insert into sales select 'AAAA',10 insert into sales select 'BBBB',20 go--查看销售后库存数据 select * from store go/* code num ---------- ----------- AAAA 90 BBBB 180 */--删除测试数据环境 drop trigger trg_sales drop table store,sales go
create trigger test_t on saletable for insert as update storetable set 数量=数量-i.数量 from inserted i where i.商品id=storetable.商品id
销售表(商品id,数量)
create trigger test_t on 销售表
for insert
as
update 库存表 set 数量=数量-i.数量
from inserted i where i.商品id=库存表.商品id
create table store(code varchar(10),num int)
insert into store select 'AAAA',100
insert into store select 'BBBB',200create table sales(code varchar(10),quantity int)
go--创建触发器
create trigger trg_sales on sales
for insert
as
begin
update store
set
num=store.num-inserted.quantity
from
inserted
where
store.code=inserted.code
end
go--查看销售前库存数据
select * from store
go/*
code num
---------- -----------
AAAA 100
BBBB 200
*/--向销售数据表中新增销售记录
insert into sales select 'AAAA',10
insert into sales select 'BBBB',20
go--查看销售后库存数据
select * from store
go/*
code num
---------- -----------
AAAA 90
BBBB 180
*/--删除测试数据环境
drop trigger trg_sales
drop table store,sales
go
for insert
as
update storetable set 数量=数量-i.数量
from inserted i where i.商品id=storetable.商品id