SQL Server实验-触发器的使用（设计触发器，确保表Avg_sno中数据与表SC中数据的一致性）

一.实验分析

题目：表SC(Sno，Cno，Grade)用来存放学生成绩，表Avg_sno(Sno,Avg_grade)用来存放学生的平均成绩，设计触发器，确保表Avg_sno中数据与表SC中数据的一致性。

题目分析：

        ①.需新建一张表Avg_sno表

        ②.新建Avg_sno表并初始同步原SC表中的内容

        ③.编写触发器

        ④.验证触发器

附: 初始Sno表参考：

Sno为学生学号，Cno为课程号，Grade为课程成绩

 

---

二.代码编写

        1.创建Avg_sno表

此处为了方便，create时就不考虑完整性的定义了。

         2.同步Avg_sno表数据

由于创建Avg_sno表之前SC表已存在，所以需先进行数据同步。使用INSERT语句可以实现

INSERT
INTO Avg_sno(Sno,Avg_grade)
SELECT Sno,AVG(Grade) Avg_grade
FROM SC
GROUP BY Sno

        3.编写触发器

编写前分析：本次应该为SC表编写触发器，当SC表中的数据发生变化时，Avg_sno表中数据应该同步变化。

可能影响SC表中数据的操作由有：INSERT/UPDATE/DELETE 。

（本人在写该博客时也是第一次学习触发器。编写的思路是为这三个操作分别编写三个触发器来实现数据一致性。若你想到了更好的方法，欢迎交流）

UPDATE和DELETE触发器的逻辑差不多，因此本文只以编写INSERT和UPDATE触发器为例

        ①.UPDATE触发器编写

        只考虑功能实现，不考虑性能优化

USE wlw2020(这里换成你自己的数据库名称)
CREATE TRIGGER UPDATE_TEST(为触发器起名为UPDATE_TEST)
ON SC（换成自己的表的名称，这里为SC表）
AFTER UPDATE （以AFTER触发为例）
AS
BEGIN
		DECLARE @upGrade SMALLINT;
		DECLARE @upSno CHAR(9);
		DECLARE @newAvg SMALLINT;
		SELECT @upSno = Sno FROM inserted
		SELECT @upGrade = AVG(Grade) FROM SC GROUP BY Sno
		SELECT @newAvg = AVG(Grade) FROM SC WHERE Sno = @upSno
		UPDATE Avg_sno
		SET Avg_grade = @newAvg
		WHERE Sno = @upSno
	END
GO

        触发器验证： 

        以修改201215122号学生为例，修改前平均成绩为(90+80)/2=85

        现修改其Cno=2的课程成绩为100，观察两表数据变化

        可看到Avg_sno表中平均成绩已同步变为(100+80)/2=90,说明触发器功能正常。

        ②.INSERT触发器编写

        提供一个针对题目编写INSERT(AFTER)触发器的思路。

        同样只考虑功能实现，不考虑性能优化。

USE wlw2020(换成你的数据库名称) 
GO
CREATE TRIGGER INSERT_TEST(创建一个名为INSERT_TEST的触发器)
ON SC(换成你的表名称，这里以SC表为例)
AFTER INSERT (以AFTER触发为例)
AS
	BEGIN
		DECLARE @upGrade SMALLINT;
		DECLARE @upSno CHAR(9);
		DECLARE @newAvg SMALLINT;
		DECLARE @flagCount SMALLINT; 
		SELECT @upSno = Sno FROM inserted
		SELECT @upGrade = AVG(Grade) FROM SC GROUP BY Sno
		SELECT @newAvg = AVG(Grade) FROM SC WHERE Sno = @upSno
		SELECT @flagCount = COUNT(Sno) FROM SC WHERE Sno = @upSno
		IF(@flagCount = '1')
			BEGIN
				INSERT
				INTO Avg_sno(Sno,Avg_grade)
				VALUES(@upSno,@upGrade)
			END
		ELSE
			BEGIN
				UPDATE Avg_sno
				SET Avg_grade = @newAvg
				WHERE Sno = @upSno
			END
	END

                 触发器验证：

                仍然以201215122号学生为例，修改前其平均成绩为(100+80)/2 = 90

         现向SC表中插入一条记录

INSERT
INTO SC
VALUES(201215122,1,60)

新INSERT的记录Grade为60，因此新平均成绩应该为(60+100+80)/3=80,实际Avg_sno表中的数据也已同步为80，触发器工作正常。 

 

---

三.小结

        触发器的重点需理解触发时机 Instead of / After / For 的机制和对应的Inserted表和Deleted的运用。其次需掌握SQL Server中临时变量的定义和使用。