34,593
社区成员
发帖
与我相关
我的任务
分享
--插入竞猜结果触发器
--只在state为0,并且CID有效情况才可竞猜有效
alter trigger OnGuessAdd
on dbo.Guess
instead of insert
as
begin
declare @state int
SET @state = 1 --默认不让竞猜
select @state = a.[State] from dbo.List a where exists(select 1 from inserted where inserted.CID = a.ID)
if @state = 0
begin
--插入竞猜结果
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select [CID], [username] ,[SubmitTime],[Guess],[State] from inserted)
--同时更新表Guessscore
insert into dbo.GuessScore([username],[LastGuessScore],[Score],[State])
(select [username],0,0,[State] from inserted)
end
end
--list表触发器()
alter trigger OnListUpdate
on dbo.List
For Update
as
begin
IF (COLUMNS_UPDATED() & 16) > 0 --Result列(第5列,值为2的(5-1)次幂)被更新时触发guessscore表变更
begin
-- 更新所有竞猜者结果
-- DECLARE @result int
-- DECLARE @ID int
Update dbo.GuessScore set [LastGuessScore] = (case when a.Result = b.Guess then [LastGuessScore]+1 else 0 end),
[Score] = [Score] + [LastGuessScore] + 1
FROM deleted a
join dbo.Guess b on b.CID = a.ID
end
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GuessScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GuessScore]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Guess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Guess]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[List]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[List]
GO
CREATE TABLE [dbo].[GuessScore] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Score] [float] NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Guess] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SubmitTime] [datetime] NULL ,
[Guess] [int] NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[List] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HomeSide] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VisitingSide] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StartTime] [datetime] NULL ,
[Result] [int] NULL ,
[demo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
/*
ID username LastGuessScore Score State
----------- ---------- -------------- ---------------------- -----------
178 路人甲 1 2 0
179 路人乙 0 0 0
180 路人丙 1 2 0
(3 行受影响)
*/
/*
ID username LastGuessScore Score State
----------- ---------- -------------- ---------------------- -----------
178 路人甲 2 5 0
179 路人乙 1 2 0
180 路人丙 0 2 0
(3 行受影响)
*/
use ymw_test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GuessScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GuessScore]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Guess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Guess]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[List]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[List]
GO
CREATE TABLE [dbo].[GuessScore] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[username] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[LastGuessScore] [int] NULL,
[Score] [float] NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Guess] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[username] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[SubmitTime] [datetime] NULL ,
[Guess] [int] NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[List] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HomeSide] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[VisitingSide] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[StartTime] [datetime] NULL ,
[Result] [int] default 0 ,
[demo] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[State] [int] NULL
) ON [PRIMARY]
GO
--插入竞猜结果触发器
--只在state为0,并且CID有效情况才可竞猜有效
create trigger OnGuessAdd
on dbo.Guess
instead of insert
as
begin
declare @state int
SET @state = 1 --默认不让竞猜
select @state = a.[State] from dbo.List a where exists(select 1 from inserted where inserted.CID = a.ID)
if @state = 0
begin
--插入竞猜结果
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select [CID], [username] ,[SubmitTime],[Guess],[State] from inserted)
--同时更新表Guessscore
if (select count(*) from dbo.GuessScore a where exists(select 1 from inserted b where a.[username] = b.[username])) = 0
begin
print '1'
insert into dbo.GuessScore([username],[LastGuessScore],[Score],[State])
(select [username],0,0,[State] from inserted)
end
end
end
go
--list表触发器()
create trigger OnListUpdate
on dbo.List
For Update
as
begin
IF (COLUMNS_UPDATED() & 16) > 0 --Result列(第5列,值为2的(5-1)次幂)被更新时触发guessscore表变更
begin
-- 更新所有竞猜者结果
--规则一:猜对第一场得一分,连续猜对两场1+2得3分,如此类推,如果中间错了,重新开始累积
--规则二:如果猜oneside赢的是1个人,猜otherside赢的是9个人,比赛结果(result)是oneside赢,则附加得分为(9+1)/1
DECLARE @winCount int
DECLARE @AppendScore int
--先算出附加分
select @winCount = count(*) FROM dbo.Guess a join inserted b on a.CID = b.ID where a.[Guess] = 2
print @winCount
select @AppendScore = (case when @winCount > 0 then count(*)/@winCount else 0 end)
FROM dbo.Guess a join inserted b on a.CID = b.ID
--更新表
Update dbo.GuessScore set [LastGuessScore] = (case when b.Result = b.Guess then a.[LastGuessScore]+1 else 0 end),
[Score] = (case when b.Result = b.Guess then a.[Score] + @AppendScore + a.[LastGuessScore] + 1 else a.[Score] end)
FROM GuessScore a
inner join (select [username],[Guess],[Result] from inserted c left join dbo.Guess d on d.CID = c.ID) b on b.[username] = a.[username]
end
end
go
--插入比赛
insert into dbo.List (HomeSide,VisitingSide,StartTime,Result,[State])
values(N'巴西',N'意大利','2010-06-30 10:00:00.000',0,0)
go
--插入竞猜数据(路人甲猜巴西胜)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id, N'路人甲',getdate(),2,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--插入竞猜数据(路人乙猜巴西输)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人乙',getdate(),1,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--插入竞猜数据(路人丙猜荷兰赢)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人丙',getdate(),2,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--更新比赛竞猜状态(停止竞猜)
update dbo.List set [State] = 0 where id = (select top 1 id from dbo.List where HomeSide = '巴西' and VisitingSide = '意大利')
go
--巴西胜
update dbo.List set [Result] = 2 where id = (select top 1 id from dbo.List where HomeSide = '巴西' and VisitingSide = '意大利')
go
select * from dbo.GuessScore
/*
ID username LastGuessScore Score State
----------- ---------- -------------- ---------------------- -----------
178 路人甲 1 2 0
179 路人乙 0 0 0
180 路人丙 1 2 0
(3 行受影响)
*/
--插入比赛2
insert into dbo.List (HomeSide,VisitingSide,StartTime,Result,[State])
values(N'荷兰',N'美国','2010-06-30 10:00:00.000',0,0)
go
--插入竞猜数据(路人甲猜荷兰胜)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id, N'路人甲',getdate(),2,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide =N'美国')
--插入竞猜数据(路人乙猜荷兰输)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人乙',getdate(),2,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
--插入竞猜数据(路人丙猜荷兰赢)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人丙',getdate(),1,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
go
--更新比赛竞猜状态(停止竞猜)
update dbo.List set [State] = 0 where id = (select top 1 id from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
--荷兰胜
update dbo.List set [Result] = 2,[State] = 2 where id = (select top 1 id from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
go
select * from dbo.GuessScore
/*
ID username LastGuessScore Score State
----------- ---------- -------------- ---------------------- -----------
178 路人甲 2 5 0
179 路人乙 1 2 0
180 路人丙 0 2 0
(3 行受影响)
*/