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
