SqlServer try catch 捕获触发器\存储过程异常,结合 transaction 事务
SoEasy~，贴上代码看所有
1 ALTER trigger [dbo].[tgr_SG_Gathering_update] 2 on [dbo].[SG_Gathering] 3 for update --更新触发 4 as 5 --定义变量 6 declare @fMoney float, 7 @fQuantity float, 8 @vShop varchar(20), 9 @vEmpCode varchar(20), 10 @vSPosition varchar(20), 11 @vSPCode varchar(20), 12 @SetID int, 13 @Amount float, 14 @Coupon varchar(20), 15 @RJ varchar(10), 16 @DiscountNum int; 17 18 begin try 19 begin transaction 20 if (update(bCancel)) --bCancel日结动作 21 begin 22 --变量取值 23 select @RJ=bTotal,@fMoney=fMoney,@vShop=vShop,@vEmpCode=vEmpCode,@vSPCode=vSPCode,@fQuantity=fQuantity,@vSPosition=vSPosition 24 from inserted; 25 26 INSERT INTO [NoahERP].dbo.DiscountCouponPool( SetID,Coupon,CardType,Type,PP,MadeType,Rate,Price,Amount,StartDate,EndDate, 27 CreateDate,UseDate,ModifyPerson,IsDel,ImgUrl,UsedImgUrl,OverImgUrl,ClothesCounts) 28 SELECT S.ID,@Coupon,S.CardType,S.[Type],S.[PP],S.MadeType,P.Rate,P.Price,S.Amount,S.StartDate,S.EndDate, 29 GETDATE(),NULL,'',0,P.ImgUrl,P.UsedImgUrl,P.OverImgUrl,S.ClothesCounts 30 FROM [NoahERP].[dbo].[DiscountCouponSet] S 31 left join NoahERP.dbo.DiscountCouponPrice P on S.ID=P.SetID 32 where CONVERT(date, GETDATE()) >= CONVERT(date,S.StartDate) AND DATEADD(DAY,-1,CONVERT(DATE,GETDATE())) < CONVERT(date,S.EndDate) 33 AND S.IsStop = 0 And S.Type=1 AND S.ID=@SetID; 34 end 35 commit transaction --提交事务 36 end try 37 begin catch 38 39 if(@@TRANCOUNT>0) 40 begin 41 rollback transaction;--出现错误回滚 42 end 43 --记录错误信息到[SG_GatheringLog]表 44 insert into [dbo].[SG_GatheringLog](ErrorNumber, 45 ErrorSeverity, 46 ErrorState, 47 ErrorProcedure, 48 ErrorLine, 49 ErrorMessage) 50 select ERROR_NUMBER() ErrorNumber, --返回导致运行 CATCH 块的错误消息的错误号 51 ERROR_SEVERITY() ErrorSeverity, --返回导致 CATCH 块运行的错误消息的严重级别 52 ERROR_STATE() ErrorState, --返回导致 CATCH 块运行的错误消息的状态号 53 ERROR_PROCEDURE() ErrorProcedure, --返回出现错误的存储过程或触发器名称 54 ERROR_LINE() ErrorLine, --返回发生错误的行号 55 ERROR_MESSAGE() ErrorMessage --返回导致 CATCH 块运行的错误消息的完整文本 56 end catch
