9-1(1).sql

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

USE Educational
GO
-- 如果已存在同名触发器，则先进行删除操作 
IF exists(SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'TRGrade_Insert')
     and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER TRGrade_Insert
GO
CREATE TRIGGER TRGrade_Insert 	/* 触发器名 */
ON Grade		/* 作用的表 */ 
FOR INSERT	/* 创建INSERT触发器 */
AS
  DECLARE @CreditHour TINYINT  	/* 学分 */
  DECLARE @Grade TINYINT  		/* 成绩 */
  SELECT @Grade=i.Grade, @CreditHour=C.CreditHour
  FROM  Inserted I,Course C  /* 按CourseID联接Course */
  where C.CourseID = I.CourseID
  IF (@Grade>=60)		/* 成绩大于等于60 */
  BEGIN
    UPDATE Student		/* 在已修学分中加上插入课程的学分 */
      SET CreditHour=CreditHour + @CreditHour
    FROM Student S, Inserted I
    WHERE S.StudentID = I.StudentID 
  END
GO