BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
--向分数表中插入数据
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),70)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),67)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),55)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(3,1,GETDATE(),102)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
--插入数据时使用@@error系统函数,当没有发生错误时返回0,如果发生错误时@@error<>0,并返回错误号
VALUES(3,4,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),56)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),88)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),40)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectId,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),65)
SET @errorSum=@errorSum+@@error
--使用 IF-ELSE 判断是否插入成功
--成功打印插入成功,提交事务
--失败打印插入失败,回滚事务
IF(@errorSum>0)
BEGIN
PRINT '插入失败,回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功,提交事务'
COMMIT TRANSACTION
END
GO