06_02 学生毕业离校手续

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--查询Result表中所有Y2学员的考试成绩，保存到新表HistoreResult*/
SELECT Result.* INTO HistoreResult 
FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo
     INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
            WHERE GradeName='Y2'
                  SET @errorSum=@errorSum+@@error

--删除Result表中所有Y2学员的考试成绩
DELETE  Result FROM  Result JOIN Student ON Result.StudentNo=Student.StudentNo
     INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
           WHERE GradeName='Y2'
                 SET @errorSum=@errorSum+@@error

/*--将Student表中所有Y2的学员记录，保存到新表HistoryStudent*/
SELECT Student.* INTO HistoryStudent
     FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
          WHERE GradeName='Y2'
                SET @errorSum=@errorSum+@@error

/*--删除Studet表中所有Y2学员记录*/
DELETE Student FROM Student 
     INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
          WHERE GradeName='Y2'
                SET @errorSum=@errorSum+@@error

/*--根据是否有错误，确定事务是提交还是撤销--*/
IF (@errorSum<>0) --如果有错误
  BEGIN
    PRINT '插入失败，回滚事务'
    ROLLBACK TRANSACTION 
  END  
ELSE
  BEGIN
    PRINT '插入成功，提交事务'
    COMMIT TRANSACTION   
  END
GO