34,590
社区成员
发帖
与我相关
我的任务
分享
--list表触发器()
CREATE trigger OnListUpdate
on dbo.Js_WorldCupList
For Update
as
begin
IF SUBSTRING(COLUMNS_UPDATED(),2,1)&1=1
begin
DECLARE @winCount int
DECLARE @AppendScore int
--先算出附加分
select @winCount = count(*) FROM dbo.Js_WorldCupGuess a join inserted b on a.CID = b.ID where a.[Guess] = b.Result
print @winCount
select @AppendScore = (case when @winCount > 0 then Convert(Numeric(7,2),count(*))/Convert(Numeric(7,2),@winCount) else 0.00 end)
--(关键地方:得到保留两位小数的数)
FROM dbo.Js_WorldCupGuess a join inserted b on a.CID = b.ID
--更新表
Update dbo.Js_GuessScore set [LastScore] = (case when b.Result = b.Guess then a.[LastScore]+1 else 0 end),
[Score] = Convert(Numeric(7,2),(case when b.Result = b.Guess then Convert(Numeric(7,2),(a.[Score] + @AppendScore + a.[LastScore] + 1))
else Convert(Numeric(7,2),a.[Score]) end))
--需要得到的[Score]是有两位小数的数
FROM dbo.Js_GuessScore a
inner join (select [TelNum],[Guess],[Result] from inserted c left join dbo.Js_WorldCupGuess d on d.CID = c.ID) b on b.[TelNum] = a.[TelNum]
end
end