在触发器中使用游标

--在线判题系统Online judge中更新答案表的触发器，学习之用,表作了简化

if db_id('test') is not null
begin
	use master
	drop database test
end

create database test
go
use test

create table [User]
(
    [Id] bigint identity(1,1) primary key,
    UserName nvarchar(20) not null unique, --用户名
    Submit bigint default(0),--提交数
    Accepted bigint default(0),--正确数
    Solved bigint default(0) --解题数
)

insert into [User](UserName) values('u001')
insert into [User](UserName) values('u002')
insert into [User](UserName) values('u003')

create table Question
(
    [Id] bigint identity(1,1) primary key,
    Description ntext,
    SubmitCount bigint default(0), --提交数
    AcceptedCount bigint default(0) --正确数
)

insert into Question(Description) values('A+B')
insert into Question(Description) values('A-B')
insert into Question(Description) values('A*B')

create table Answer
(
    [Id] bigint identity(1,1) primary key,
    QuestionId bigint references Question(Id),
    UserName nvarchar(20)references [User](UserName),
    Status tinyint,--0表示Code产生的答案错，1表示Code产生的答案对
    Addtime datetime default(getdate()),
    Code ntext
)

go
--触发器，当答案表每插入一条记录时，更新用户的解题情况和问题的解题情况
--若考虑到一条插入语句将插入多条记录，则需考虑使用游标
create trigger triggerInsertOneAnswer on answer
for insert
as
begin
	set nocount on
	declare @qid int, @un varchar(20), @st bit
	select @qid=questionid, @un=UserName, @st=status
	from inserted
	update question set submitCount=isnull(submitCount,0)+1
	where id=@qid
	update [user] set submit=isnull(submit,0)+1
	where UserName=@un
	
	if @st=1
	begin
		update question set AcceptedCount=isnull(AcceptedCount,0)+1
		where id=@qid
		update [user] set accepted=isnull(accepted,0)+1
		where UserName=@un
		if (select count(*) from Answer where questionid=@qid
				and UserName=@un and status=1)=1 
			update [user] set solved=isnull(solved,0)+1
			where UserName=@un
	end
end
go
--插入数据，触发插入触发器，更新用户的解题情况和问题的解题情况
insert into Answer(UserName,Questionid, status) values('u001',1,0)
insert into Answer(UserName,Questionid, status) values('u001',1,1)
insert into Answer(UserName,Questionid, status) values('u001',1,1)
insert into Answer(UserName,Questionid, status) values('u002',1,1)
insert into Answer(UserName,Questionid, status) values('u002',2,1)
insert into Answer(UserName,Questionid, status) values('u003',3,0)
insert into Answer(UserName,Questionid, status) values('u003',2,0)
insert into Answer(UserName,Questionid, status) values('u003',1,1)

go
--触发器，当答案表删除记录时，更新用户的解题情况和问题的解题情况
--一条删除语句可能删掉多条记录，故使用游标
create trigger triggerDeleteAnswer on Answer
for delete
as
begin
	set nocount on
    declare cursorDeleteAnswer cursor for
        select [id],status,questionId,UserName from deleted
    open cursorDeleteAnswer    
    declare @id bigint,@cnt int,@qid int,@UserName varchar(20),@status int
    fetch next from cursorDeleteAnswer into @id,@status,@qid,@UserName
    
    while @@fetch_status=0
    begin
        if (@status=1)
            begin
                update Question set SubmitCount=SubmitCount-1,
                AcceptedCount=AcceptedCount-1 where id=@qid

                select @cnt=count(*) from Answer where questionId=@qid
					and UserName=@UserName and status=1
                
                if (@cnt>0)
                    update [user] set submit=submit-1,accepted=accepted-1
						where UserName=@UserName
                else
                    update [user] set submit=submit-1,accepted=accepted-1,solved=solved-1
						where UserName=@UserName
            end
        else
            begin
                update Question set SubmitCount=SubmitCount-1 where id=@qid
                update [user] set submit=submit-1 where UserName=@UserName     
            end
        fetch next from cursorDeleteAnswer into @id,@status,@qid,@UserName
    end
    close cursorDeleteAnswer
    deallocate cursorDeleteAnswer
end
go
 
--查看数据的存储过程
create proc spShow as
begin
	select * from question
	select * from [user]
	select * from answer
end
go

--测试删除触发器
exec spShow
delete from answer where id=2
delete from answer where UserName='u002'
delete from answer where UserName='u003' and questionId=3

exec spShow