USE Educational
GO
-- 如果已存在同名觸發(fā)器，則先進(jìn)行刪除操作 
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 	/* 觸發(fā)器名 */
ON Grade		/* 作用的表 */ 
FOR INSERT	/* 創(chuàng)建INSERT觸發(fā)器 */
AS
  DECLARE @CreditHour TINYINT  	/* 學(xué)分 */
  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聯(lián)接Course */
   ON C.CourseID = I.CourseID
  IF (@Grade>=60)		/* 成績大于等于60 */
  BEGIN
    UPDATE Student		/* 在已修學(xué)分中加上插入課程的學(xué)分 */
      SET CreditHour=CreditHour + @CreditHour
    FROM Student S, Inserted I
    WHERE S.StudentID = I.StudentID 
  END
GO