9-3.sql

SQL2000跟课件学习的源代码 分享共同学习

USE Educational
GO
-- 如果已存在同名触发器，则先进行删除操作 
IF exists(SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'TRGrade_Update')
     and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER TRGrade_Update
GO

CREATE TRIGGER TRGrade_Update 	/* 触发器名 */
ON Grade			/* 作用的表 */ 
FOR UPDATE		/* 创建UPDATE触发器 */
AS
  DECLARE @OldStuID VARCHAR(10), @OldCourseID VARCHAR(8)	/* 修改前的旧数据 */
  DECLARE @OldGrade TINYINT, @OldCreditHour TINYINT
  DECLARE @NewStuID VARCHAR(10), @NewCourseID VARCHAR(8)	/* 修改后的新数据 */
  DECLARE @NewGrade TINYINT, @NewCreditHour TINYINT
-- 取修改后的数据
  SELECT @NewStuID=G.StudentID, @NewCourseID=G.CourseID, @NewGrade=G.Grade, @NewCreditHour=C.CreditHour
  FROM Grade G INNER JOIN Inserted I
   ON G.StudentID = I.StudentID
    and G.CourseID = I.CourseID INNER JOIN Course C  /* 按CourseID联接Course */
   ON I.CourseID = C.CourseID
  print '修改后的数据:'+ @NewStuID+' '+@NewCourseID+' '+CONVERT(VARCHAR(10), @NewGrade)+' '+CONVERT(VARCHAR(10), @NewCreditHour) /* 输出修改后的数据，调试成功后需删除 */
-- 取修改前的数据
  SELECT @OldStuID=D.StudentID, @OldCourseID=D.CourseID, @OldGrade=D.Grade, @OldCreditHour=C.CreditHour
  FROM  Deleted D INNER JOIN Course C  /* 按CourseID联接Course */
   ON D.CourseID = C.CourseID
  print '修改前的数据:'+ @OldStuID+' '+@OldCourseID+' '+CONVERT(VARCHAR(10), @OldGrade)+' '+CONVERT(VARCHAR(10), @OldCreditHour) /* 输出修改前的数据，调试成功后需删除 */
  IF (@NewStuID=@OldStuID)  /* 同一个学生 */
   BEGIN
    IF (@NewGrade>=60 AND @OldGrade>=60)  /* 新成绩>=60而旧成绩<=60*/
      UPDATE Student		/* 从已修学分中上加上新课程的学分，再减去旧课程的学分*/
        SET CreditHour=CreditHour + @NewCreditHour - @OldCreditHour
      WHERE StudentID = @NewStuID 
    ELSE
     IF (@NewGrade>=60 AND @OldGrade<60)  /* 新成绩>=60而旧成绩<60*/
      UPDATE Student		/* 从已修学分中加上新课程的学分*/
        SET CreditHour=CreditHour + @NewCreditHour
      WHERE StudentID = @NewStuID 
     ELSE
       IF (@NewGrade<60 AND @OldGrade>=60)  /* 新成绩<60而旧成绩>=60*/
        UPDATE Student		/* 从已修学分中减去旧课程的学分*/
          SET CreditHour=CreditHour - @OldCreditHour
        WHERE StudentID = @NewStuID 
   END
  ELSE /* 不同的学生 */
   BEGIN
     IF (@NewGrade>=60)		/* 成绩大于等于60 */
       UPDATE Student	/* 从已修学分中加上修改后课程的学分*/
         SET CreditHour=CreditHour + @NewCreditHour
       FROM Student
       WHERE StudentID = @NewStuID
     IF (@OldGrade>=60)		/* 成绩大于等于60 */
       UPDATE Student	/* 从已修学分中减去修改前课程的学分*/
         SET CreditHour=CreditHour - @OldCreditHour
       FROM Student
       WHERE StudentID = @OldStuID
   END
GO