9-2.sql

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

USE Educational
GO
-- 如果已存在同名触发器，则先进行删除操作 
IF exists(SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'TRGrade_Delete')
     and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER TRGrade_Delete
GO
CREATE TRIGGER TRGrade_Delete 	/* 触发器名 */
ON Grade		/* 作用的表 */ 
FOR DELETE		/* 创建DELETE触发器 */
AS
  DECLARE @CreditHour TINYINT 	 /* 学分 */
  DECLARE @Grade TINYINT  		/* 成绩 */
  DECLARE @StuID VARCHAR(10)  		
  DECLARE @CourseID VARCHAR(8)  		
  DECLARE @Gradeo  TINYINT  		
  SELECT * FROM  Deleted 	/* 查看删除的数据，调试成功后该行需删除 */
  SELECT @Grade=D.Grade, @CreditHour=C.CreditHour
  FROM  Deleted D INNER JOIN Course C  /* 按CourseID联接Course */
   ON D.CourseID = C.CourseID
  IF (@Grade>=60)		/* 成绩大于等于60 */
  BEGIN
    UPDATE Student		/* 已修学分累计 */
      SET CreditHour=CreditHour - @CreditHour
    FROM Student S, Deleted D
    WHERE S.StudentID = D.StudentID 
  END
GO