--给商品入库表(ProductStorage)创建Insert触发器（入库的时候,增加库存数量(ProductInventory, TotalCount) ）
use SaleManagerDB
go
if exists(select * from sysobjects where name = 'ProductStorage_Insert')
drop trigger ProductStorage_Insert
go
create trigger ProductStorage_Insert
on ProductStorage after Insert
as
declare @ProductId varchar(50),@Addcount int
--从插入的inserted临时表中获取对应的数据
select @ProductId=ProductId,@Addcount = AddedCount from Inserted
--更新库存表中对应的商品库存数据
update ProductInventory set TotalCount=TotalCount+@Addcount where ProductId=@ProductId
go
--给商品入库表创建【update】触发器（修改了对应商品的入库数量）,先删除,在插入
if exists (select * from sysobjects where name= 'ProductStorage_Update')
drop trigger ProductStorage_Update
go
create trigger ProductStorage_Update
on ProductStorage after update
as
declare @ProductId varchar(50),@DeletCount int,@Addcount int
--从deleted表找到删除的数据
select @ProductId = ProductId,@DeletCount = AddCount from deleted
--从inserted表获取新数据
select @Addcount = AddCount from from inserted
--更新库存表中的库存数据 (先减去删除的数量,在加上新增的数量)
update ProductInventory set TotalCount = TotalCount-@DeletCount+@Addcount where ProductId = @ProductId
go
--给商品入库表创建【delete】触发器（删除了入库信息）
if exists (select * from sysobjects where name='ProductStorage_Delete')
drop trigger ProductStorage_Delete
go
create trigger ProductStorage_Delete
on ProductStorage after delete
as
@declare @ProductId varchar(50), @DeleteCount int
--从deleted临时表获取删除的数据
select @ProductId = ProductId,@DeleteCount=AddCount from deleted
--更新库存表中对应的商品的库存数量
update ProductInventory set TotalCount = TotalCount-@DeleteCount where ProductId = @ProductId
go
--给库存表创建update触发器,(更新商品的库存状态)
if exists (select * from sysobjects where name = 'ProductInventory_Update')
drop trigger ProductInventory_Update
go
create trigger ProductInventory_Update
on ProductInventory after update
as
declare @ProductId varchar(50),@TotalCount int
--从inserted 临时表获取商品id 和 数量
select @ProductId = ProductId ,@TotalCount=TotalCount from inserted
--根据当前的库存数量更新商品库存状态
update ProductInventory set StatusId = case
when @TotalCount > MaxCount then 2
when @TotalCount < MinCount and @TotalCount > 0 then -1
when @TotalCount = 0 then -2
else 1
end where ProductId=@ProductId
go
网友评论