C#笔记之触发器的创建

--给商品入库表(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