关于触发器捕获数据异动
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.GameScoreInfoChange 101 ( 102 [ChangeUserID], 103 [ChangeColumn], 104 [ChangeTime], 105 [OldColumn], 106 [NewColumn], 107 [EventInfo] 108 ) 109 SELECT Old.UserID,'InsureScore',GETDATE(),Old.InsureScore,New.InsureScore 110 ,(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 END 113 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