9-1.sql

来自「SQL2000跟课件学习的源代码 分享共同学习」· SQL 代码 · 共 28 行

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=G.Grade, @CreditHour=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 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