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
上一篇:SQL数据库字段添加说明文字


下一篇:SQL Server 跨数据库查询数据的方法