1 -- =============================================  2 -- Author:        
      <华仔>
         3 -- Create date: <2016,5,20>  4 -- Description:    
       <元宝&分数异动日志>
          5 -- 维护日志：  6 -- =============================================  7 ALTER TRIGGER [dbo].[trGameScoreInfo_Change]  8    ON [dbo].[GameScoreInfo]  9    AFTER UPDATE 10 AS  11     SET NOCOUNT ON; 12      13     DECLARE @Gold INT,@Score INT,@InsureScore INT,@InsureGold INT 14     SELECT @Score = MAX(CASE WHEN New.Score = Old.Score  THEN 0 ELSE 1 END) 15         ,@InsureScore = MAX(CASE WHEN New.InsureScore = Old.InsureScore  THEN 0 ELSE 1 END) 16         ,@Gold = MAX(CASE WHEN New.Gold = Old.Gold THEN 0 ELSE 1 END) 17         ,@InsureGold = MAX(CASE WHEN New.InsureGold = Old.InsureGold THEN 0 ELSE 1 END) 18     FROM Deleted AS Old JOIN Inserted AS New ON New.UserID = Old.UserID 19      20     BEGIN TRY 21     BEGIN TRAN -- 事务 22     IF @Gold = 1 23     BEGIN 24         CREATE TABLE #Gold 25         ( 26             [EventType] NVARCHAR(30), 27             [Parameters] INT, 28             [EventInfo] NVARCHAR(255) 29         )  30         INSERT #Gold EXEC('DBCC INPUTBUFFER ('+@@SPID+')') 31         INSERT dbo.GameScoreInfoChange 32         ( 33             [ChangeUserID], 34             [ChangeColumn], 35             [ChangeTime], 36             [OldColumn], 37             [NewColumn], 38             [EventInfo] 39         ) 40         SELECT Old.UserID,'Gold',GETDATE(),Old.Gold,New.Gold 41             ,(SELECT TOP 1 [EventType] +' | '+ CONVERT(NVARCHAR(5),[Parameters]) +' | '+ [EventInfo] FROM #Gold) 42         FROM Deleted Old JOIN Inserted New ON New.UserID = Old.UserID AND New.Gold <> Old.Gold 43     END 44  45     IF @InsureGold = 1 46     BEGIN 47         CREATE TABLE #InsureGold 48         ( 49             [EventType] NVARCHAR(30), 50             [Parameters] INT, 51             [EventInfo] NVARCHAR(255) 52         )  53         INSERT #InsureGold EXEC('DBCC INPUTBUFFER ('+@@SPID+')') 54         INSERT dbo.GameScoreInfoChange 55         ( 56             [ChangeUserID], 57             [ChangeColumn], 58             [ChangeTime], 59             [OldColumn], 60             [NewColumn], 61             [EventInfo] 62         ) 63         SELECT Old.UserID,'InsureGold',GETDATE(),Old.InsureGold,New.InsureGold 64             ,(SELECT TOP 1 [EventType] +' | '+ CONVERT(NVARCHAR(5),[Parameters]) +' | '+ [EventInfo] FROM #InsureGold) 65         FROM Deleted Old JOIN Inserted New ON New.UserID = Old.UserID AND New.InsureGold <> Old.InsureGold 66     END 67  68     IF @Score = 1 69     BEGIN 70         CREATE TABLE #Score 71         ( 72             [EventType] NVARCHAR(30), 73             [Parameters] INT, 74             [EventInfo] NVARCHAR(255) 75         )  76         INSERT #Score EXEC('DBCC INPUTBUFFER ('+@@SPID+')') 77         INSERT dbo.GameScoreInfoChange 78         ( 79             [ChangeUserID], 80             [ChangeColumn], 81             [ChangeTime], 82             [OldColumn], 83             [NewColumn], 84             [EventInfo] 85         ) 86         SELECT Old.UserID,'Score',GETDATE(),Old.Score,New.Score 87             ,(SELECT TOP 1 [EventType] +' | '+ CONVERT(NVARCHAR(5),[Parameters]) +' | '+ [EventInfo] FROM #Score) 88         FROM Deleted Old JOIN Inserted New ON New.UserID = Old.UserID AND New.Score <> Old.Score     89     END 90  91     IF @InsureScore = 1 92     BEGIN 93         CREATE TABLE #InsureScore 94         ( 95             [EventType] NVARCHAR(30), 96             [Parameters] INT, 97             [EventInfo] NVARCHAR(255) 98         )  99         INSERT #InsureScore EXEC('DBCC INPUTBUFFER ('+@@SPID+')')100         INSERT dbo.GameScoreInfoChange101         (102             [ChangeUserID],103             [ChangeColumn],104             [ChangeTime],105             [OldColumn],106             [NewColumn],107             [EventInfo]108         )109         SELECT Old.UserID,'InsureScore',GETDATE(),Old.InsureScore,New.InsureScore110             ,(SELECT TOP 1 [EventType] +' | '+ CONVERT(NVARCHAR(5),[Parameters]) +' | '+ [EventInfo] FROM #InsureScore)111         FROM Deleted Old JOIN Inserted New ON New.UserID = Old.UserID AND New.InsureScore <> Old.InsureScore    112     END113 114     COMMIT TRAN -- 提交事务115     END TRY -- 结束捕获116     BEGIN CATCH --错误表述117     ROLLBACK TRAN -- 回滚事务118         --SELECT CONVERT(CHAR(23),GETDATE(),21)+'->'+'QPTreasureDB.trGameScoreInfo_Change'+'->'+ERROR_MESSAGE()119         INSERT INTO [QPAccountsDB].[dbo].[QA_ErrorLog]120             ([ErrorNumber]121             ,[ErrorSeverity]122             ,[ErrorState]123             ,[ErrorProcedure]124             ,[ErrorLine]125             ,[ErrorMessage]126             ,[ErrorTime])127         SELECT ERROR_NUMBER()128             ,ERROR_SEVERITY()129             ,ERROR_STATE()130             ,'QPTreasureDB.trGameScoreInfo_Change'131             ,ERROR_LINE()132             ,ERROR_MESSAGE()133             ,GETDATE()134     END CATCH -- 结束表述135 136     137