触发器20170215
USE [WlData] GO /****** Object: Trigger [dbo].[updatekucun1] Script Date: 02/15/2017 15:06:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[updatekucun1] ON [dbo].[Tbl_OperateInventory] for insert AS BEGIN -- 修改 -- 修改 declare @style as char(10) ,@StockType as char(10),@base_code as char(20),@quantity as decimal(10,0),@account as char(20),@account2 as char(20),@stockId as char(20),@PiNO as nvarchar(50),@QR_Code as nvarchar(200),@SterilizaDate as datetime,@ExpiryDate as datetime,@Provider_Code as char(10),@ProductCode as char(10),@inprice as decimal(18,3),@inDate as datetime,@odate as datetime,@stockTOKS as char(10) declare @intCount int DECLARE c11 CURSOR FOR select style,StockType,Base_Code,quantity,Account,Account2,StockID,PiNO,QR_Code,SterilizaDate,ExpiryDate,Provider_Code,ProductCode,inprice,inDate,odate,stockTOKS from inserted order by odate open c11 fetch next from c11 into @style,@StockType,@base_code,@quantity,@account,@account2,@stockId,@PiNO,@QR_Code,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@inprice,@inDate,@odate,@stockTOKS while @@fetch_status=0 begin select @intCount = COUNT(base_code) from inserted if @intCount >0 -- 修改 begin --插入批次库存数据,科室卫材手动入库。这里没用到。 if @style in('b') begin select @intCount = COUNT(a.Base_Code) from Tbl_Inventory a where a.Base_Code=@base_code and a.stockid=@stockId and a.stocktype =@StockType if @intCount = 0 insert into wldata..Tbl_Inventory(Base_Code,Num,StockID,StockType) values (@base_code,@quantity,@stockId,@StockType) else --总库更新, update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次插入注释韩 select @intCount= count(Base_Code) from tbl_InventoryPerBatch where Base_Code=@base_code and stockid=@stockId and stocktype =@StockType and inprice=@inprice if @intCount != -1 begin INSERT INTO [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice) values (@account,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end ---- 修改Han where account =@account2 --update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId end if @style in('9') --科室卫材溢余 begin --总库更新, update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次插入 select @intCount=COUNT(a.Base_Code) from tbl_InventoryPerBatch a where a.Base_Code=@base_code and a.stockid=@stockId and a.stocktype =@StockType and a.inprice= @inprice and a.Account= @account if @intCount =0 begin INSERT INTO [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice)values (@account,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end else begin update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and inprice= @inprice end end if @style in('2') --科室卫材手动入库 begin --总库更新, select @intCount = COUNT(a.Base_Code) from Tbl_Inventory a where a.Base_Code=@base_code and a.stockid=@stockId and a.stocktype =@StockType if @intCount = 0 insert into WlData..Tbl_Inventory (Base_Code,Num,StockID,StockType) values (@base_code,@quantity,@stockId,@StockType) else update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次插入 --待修改 select @intCount=COUNT(a.Base_Code) from tbl_InventoryPerBatch a where a.Base_Code=@base_code and a.stockid=@stockId and a.stocktype =@StockType and a.inprice= @inprice if @intCount =0 begin INSERT INTO [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice) values (@account2,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end else begin update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and inprice = @inprice end end if @style in('二') --科室高值手动入库 begin --总库更新, update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次插入 select @intCount=COUNT(a.Base_Code) from tbl_InventoryPerBatch a where a.Base_Code= @base_code and a.pino=@PiNO and a.ExpiryDate=@ExpiryDate and a.stockid=@stockId and a.ProductCode=@ProductCode and a.stocktype =@StockType and a.inprice= @inprice and a.Account= @account2 if @intCount=0 begin INSERT INTO [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice) values (@account2,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end else begin update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and PiNO = @PiNO and ExpiryDate=@ExpiryDate and ProductCode=@ProductCode and inprice= @inprice end end if @style in('六') begin --更新总库存表 update wldata..Tbl_Inventory set Num=Num+abs(@quantity) where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次表更新 update wldata..Tbl_InventoryPerBatch set Num=Num+abs(@quantity) where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and PiNO = @PiNO and ExpiryDate=@ExpiryDate and ProductCode=@ProductCode and inprice= abs(@inprice) update WlData..Tbl_QRManage SET mastflag ='0' where QR_code = @QR_Code end --修改批次库存数据(减)(高值,含批号) if @style in ('五','V','四') begin --总库更新, update wldata..Tbl_Inventory set Num=Num-@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次数量更新 update wldata..Tbl_InventoryPerBatch set Num=Num-@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and PiNO = @PiNO if @style in ('五','四') begin update wldata..Tbl_QRManage set StockID=@stockTOKS where StockID=@stockId and Account=@account2 and Base_code=@base_code and QR_code=@QR_Code and PiNO = @PiNO end if @style in ('V') begin delete from WlData..Tbl_QRManage where QR_code=@QR_Code end if @style in ('四') begin update WlData..Tbl_QRManage SET mastflag ='1' where QR_code = @QR_Code end end -- 卫生材料管理系统 出库未接收撤回 标识 if @style in ('withdraw') begin update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where id =(select top(1) ID from wldata..Tbl_InventoryPerBatch where Base_Code=@base_code and StockType=@StockType and StockID=@stockId order by odate) end --修改批次库存数据(减)(普值,不含批号) if @style in ('4','r','5','e') begin --总库更新, update wldata..Tbl_Inventory set Num=Num-@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次数量更新 --修改韩 update wldata..Tbl_InventoryPerBatch set Num=Num-@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId end if @style in('d') begin declare @PiCiCount int declare @Account1 varchar(20) declare @Base_Code1 varchar(20) declare @num1 int declare @CountID int declare @pcID int declare @Odate1 datetime update wldata..Tbl_Inventory set Num=Num-@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId SELECT @PiCiCount = COUNT(*) FROM [WlData].[dbo].[Tbl_InventoryPerBatch] where Base_Code=@base_code and StockType=@StockType and StockID=@stockId and num >0 IF @PiCiCount>0 SET @CountID =0 WHILE @CountID< @PiCiCount begin select top(1) @pcID = ID, @account= Account,@Base_Code1=Base_Code,@num1 =num,@odate1=odate from [WlData].[dbo].[Tbl_InventoryPerBatch] where Base_Code=@base_code and StockType=@StockType and StockID=@stockId and num >0 order by odate IF @quantity <@num1 begin Update [WlData].[dbo].[Tbl_InventoryPerBatch] set num = num -@quantity where ID = @pcID break end else begin set @quantity = @quantity-@num1 Update [WlData].[dbo].[Tbl_InventoryPerBatch] set num =0 where ID = @pcID set @CountID =@CountID+1 end end end if @style in ('r','4') begin select @intCount = COUNT(a.Base_Code) from WlData..Tbl_InventoryPerBatch as a where @base_code = a.Base_Code and @stockId =StockID if @intCount =0 begin declare @quantity1 as decimal(10,0) set @quantity1 = @quantity *-1 insert into WlData..Tbl_Inventory(Base_Code,Num,StockID,StockType) values (@base_code,@quantity1,@stockId,@StockType) insert into WlData..Tbl_InventoryPerBatch(Account,PiNO,Base_Code,num,StockID,StockType,inprice,ExpiryDate,ProductCode,Provider_Code,inData,odate) values ('qxkb123','123123',@base_code,@quantity1,@stockId,@StockType,@inprice,@ExpiryDate,@ProductCode,@Provider_Code,@inDate,@odate) end end --修改批次库存数据(加)(高值,需要加批号判定) if @style in('VIII') begin update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次数量更新 select @intCount=COUNT(a.Base_Code) from tbl_InventoryPerBatch a where a.Base_Code=@base_code and a.pino=@PiNO and a.ExpiryDate=@ExpiryDate and a.stockid=@stockId and a.ProductCode=@ProductCode and a.stocktype =@StockType and a.inprice= @inprice and a.Account=@account2 and a.PiNO =@PiNO update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId and PiNO = @PiNO if @intCount =0 begin insert into [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice) values (@account2,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end end --修改批次库存数据(加)(普值,不需要判定批号) if @style in('h') begin update wldata..Tbl_Inventory set Num=Num+@quantity where Base_Code=@base_code and StockType=@StockType and StockID=@stockId --批次数量更新 select @intCount=COUNT(a.Base_Code) from tbl_InventoryPerBatch a where a.Base_Code=@base_code and a.pino=@PiNO and a.ExpiryDate=@ExpiryDate and a.stockid=@stockId and a.ProductCode=@ProductCode and a.stocktype =@StockType and a.inprice= @inprice and a.Account= @account2 update wldata..Tbl_InventoryPerBatch set Num=Num+@quantity where Account=@account2 and Base_Code=@base_code and StockType=@StockType and StockID=@stockId if @intCount =0 begin insert into [wldata].[dbo].[Tbl_InventoryPerBatch]([Account],[PiNO],[Base_Code] ,[num],[SterilizaDate],[ExpiryDate] ,[Provider_Code],[ProductCode],[StockID] ,[StockType],[inData],[odate],inprice) values (@account2,@PiNO,@base_code,@quantity,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@stockId,@StockType,@inDate,@odate,@inprice) end end -- 修改 end FETCH NEXT FROM c11 into @style,@StockType,@base_code,@quantity,@account,@account2,@stockId,@PiNO,@QR_Code,@SterilizaDate,@ExpiryDate,@Provider_Code,@ProductCode,@inprice,@inDate,@odate,@stockTOKS end close c11 DEALLOCATE c11 -- 修改 END
2.触发器实例2
注:根据主表添加的数据类型 判断库存表的数量是否满足出库需求 出库数量>库存数量 不执行任何操作 出库数量<库存数量 减掉相应的库存 相等直接删除
USE [WldataShow] GO /****** Object: Trigger [dbo].[updatekucun1] Script Date: 03/22/2017 10:59:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[updatekucun1] ON [dbo].[记录表] for insert AS declare @BaseCode as varchar(20) ,@BaseName as varchar(20),@BaseNum as decimal(10,0),@BaseDS as varchar(20),@BaseDW as varchar(20),@Ptype as varchar(20), @BaseColor as varchar(20),@OpStyle as varchar(20),@BaseID AS varchar(20),@BaseMS as varchar(20),@Provider as varchar(20) --declare @Provider as char(20) declare @intCount int declare @KuNum int select top 1 @BaseCode= BaseCode ,@BaseName = BaseName,@BaseNum=BaseNum,@BaseDS =BaseDS,@BaseID = BaseID, @BaseDW =BaseDW,@Ptype = Ptype,@BaseMS=BaseMS,@BaseColor =BaseColor,@OpStyle=OpStyle,@Provider= Provider from inserted order by RuTime begin if @OpStyle in('C') begin if @BaseCode in ('ZZY') select @intCount=COUNT(BaseCode) from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID select @KuNum = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID begin if (@intCount >0 and @BaseNum <= @KuNum) begin update Tbl_KuCun1 set BaseNum = @KuNum - @BaseNum where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID declare @Max decimal(10,0) select @Max = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID if(@Max = 0) begin delete from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID end return end end if @BaseCode in ('BHWZ') select @intCount=COUNT(BaseCode) from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseMS = @BaseMS select @KuNum = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseMS = @BaseMS begin if (@intCount >0 and @BaseNum <= @KuNum) begin update Tbl_KuCun1 set BaseNum = @KuNum -@BaseNum where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseMS = @BaseMS declare @Max1 decimal(10,0) select @Max1 = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseMS = @BaseMS if(@Max1 = 0) begin delete from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseMS = @BaseMS end return end end if @BaseCode in ('WZ') select @intCount=COUNT(BaseCode)from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID and BaseColor =@BaseColor select @KuNum = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID and BaseColor =@BaseColor begin if (@intCount >0 and @BaseNum <= @KuNum) begin update Tbl_KuCun1 set BaseNum = @KuNum - @BaseNum where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID and BaseColor =@BaseColor declare @Max11 decimal(10,0) select @Max11 = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID and BaseColor =@BaseColor if(@Max11 = 0) begin delete from Tbl_KuCun1 where BaseCode =@BaseCode and BaseDS = @BaseDS and BaseID = @BaseID and BaseColor =@BaseColor end return end end if @BaseCode in ('ZGS') select @intCount=COUNT(BaseCode) from Tbl_KuCun1 where BaseCode =@BaseCode and baseDS = @BaseDS and BaseID = @baseID select @KuNum = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and baseDS = @BaseDS and BaseID = @baseID begin if (@intCount >0 and @BaseNum <= @KuNum) begin update Tbl_KuCun1 set BaseNum = @KuNum - @BaseNum where BaseCode =@BaseCode and baseDS = @BaseDS and BaseID = @baseID declare @Max111 decimal(10,0) select @Max111 = BaseNum from Tbl_KuCun1 where BaseCode =@BaseCode and baseDS = @BaseDS and BaseID = @baseID if(@Max111 = 0) begin delete from Tbl_KuCun1 where BaseCode =@BaseCode and baseDS = @BaseDS and BaseID = @baseID end return end end end end
触发器理解:触发器是依附表格进行操作数据的工具,触发器无法像程序一样设置断点进行测试。要求在进行编写的时候先设计好判断的结构,然后再往里面添加操作。 declare 定义全局变量 begin end 基本结构操作 while 循环