SQL Server实验-触发器的使用(设计触发器
一.实验分析 题目:表SC(Sno,Cno,Grade)用来存放学生成绩Mssq触发器,表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中临时变量的定义和使用。 (编辑:鄂州站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- sql-server – 将SQLServer 2000数据库导出到XML的最简单方
- sql – 在关系数据库中维护子类完整性
- sqlserver 中一些常看的指标和清除缓存的方法
- MSSQL教程:配置MSSQL Server
- Transactional replication(事务复制)详解之如何跳过一个
- 数据库 – LevelDB是否支持热备份(或等效)?
- sqlserver下将数据库记录的列记录转换成行记录的方法
- 在sqlserver2005中安装sql server 2000的示例数据库northwi
- 数据库 – 在Microsoft Access(2010)中计算年和月的年龄
- ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排