当前位置：首页 > news >正文

【SQL Server】华中农业大学空间数据库实验报告实验九触发器

news 来源：原创 2025/8/2 18:58:08

1.实验目的

通过实验课程与理论课的学习深入理解掌握的触发器的原理、创建、修改、删除、基本的使用方法、主要用途，并且可以在练习的基础上，熟练使用触发器来进行数据库的应用程序的设计；
深入学习深刻理解与触发器相关的T-SQL语句的编写的基本原理、编写方法与常见的编写规范，并通过与数据查询、存储过程的T-SQL语句进行比较，充分了解其间的差异，从而进一步理解T-SQL语句编写代码的方法。

2.实验内容

创建并执行触发器：交互式为数据库表S创建一级联更新触发器TRIGGER_S、交互式为数据库表SC创建一个限制更新触发器TRIGGER_SC、用SQL为数据库表SC创建一个触发器Score_SC_TRI、用SQL为数据库表C创建一个级联删除触发器TRIGGER_DC；
修改触发器：交互式修改数据库表S的触发器TRIGGER_S、用SQL修改数据库表C的触发器TRIGGER_DC；
删除触发器：交互式删除数据库表S的触发器TRIGGER_S、用SQL删除数据库表C的触发器TRIGGER_DC。

3.实验步骤

3.1创建并执行触发器

1．交互式为数据库表S创建一级联更新触发器TRIGGER_S

要求：若修改STUDENT表中一位学生的学号，则SC表中与该学生相关的学号自动修改：

（1）点击【对象资源管理器】—>【数据库】—>【jiaoxuedb】—>【任意一数据库表】—>【触发器】，即可打开新建触发器的编辑窗口如下所示：

（2）根据实验要求，输入代码如下所示,其代码主要分为两个部分，上半部分：创建触发器名称与在哪个数据库表上创建触发器，并且是在进行什么操作（包括UPDATE、INSERT、DELETE）时触发触发器；下半部分：首先声明我们需要用到的形参变量，然后利用SQL语句编写更新代码，SELECT为获得数据，UPDATE表名 SET字段=新值，WHERE条件：

（3）验证触发器作用：首先如果STUDENT表与SC表间建立了外键关系，我们需要删除外键，因为外键也会达到跟随变更的效果，我们需要删除外键，仅需要触发器TRIGGER_S单独的效果；

我们将STUDENT表中的S4同学改为S9同学，保存后回到SC表中，可以看到SC表中已经没有S4同学，但是出现了S9同学了：

2．交互式为数据库表SC创建一个限制更新触发器TRIGGER_SC

要求:若修改SC表中一记录的学号，则要检查表STUDENT中是否存在与该学号相同的记录,若存在则不许修改,若不存在则可修改。

（1）新建触发器的编辑窗口，输入代码如下所示，其代码同样分为两部分，上半部分：创建触发器名称：TRIGGER_SC，在数据库表SC上创建触发器,当对列属性SNO有UPDATE指令时触发；下半部分：首先声明我们需要用到的形参变量，然后利用SQL语句编写更新代码，SELECT为获得数据，如果@SNO_CNT不为0，则rollback transaction滚回原操作，即不执行此次更新的操作：

（2）验证触发器作用：在SC表中如果想将SNO值修改为STUDENT表中已有的值，则会自动报错，报错内容显示事物在触发器中结束，说明触发器阻止了此不合规范的操作：

3．用SQL为数据库表SC创建一个触发器Score_SC_TRI

要求：当插入一个记录或修改成绩时，确保此记录的成绩在0—100分内

（1）新建触发器的编辑窗口，输入代码如下所示，其代码含义为：上半部分在数据库表SC上，针对操作UPDATE与INSERT,创建触发器命名为Score_SC_TRI，并声明将会用到的变量；下半部分同SQL语句的编码思路，SELECT语句为我们的形参赋值，IF为触发器触发的条件，且只负责离它最近的一个BEGIN END语句：

（2）验证触发器作用：使用交互式的方法想要将某一位同学的成绩更改为超过100分时，则会自动报错，报错内容显示事物在触发器中结束，说明触发器阻止了此不合规范的操作：

4．用SQL为数据库表C创建一个级联删除触发器TRIGGER_DC

要求：通过课程名从COURESE表中删除某课程信息，同时删除SC表中与此课程相关的选课记录

（1）新建查询，输入代码如下所示，其代码含义为：上半部分在数据库表COURESE上，针对操作DELETE,创建触发器命名为TRIGGER_DC，并声明将会用到的变量；下半部分同SQL语句的编码思路，SELECT语句为形参赋值，DELETE语句进行相应的实验要求操作，此处不需要BEGIN END语句也可以完成触发器的建立：

（2）验证触发器作用：使用交互式的方法在COURESE表中删除课程C5的信息，回到SC表中可以发现选择课程C5的学生的课程信息、成绩都已经被删除了。

3.2修改触发器

1．交互式修改数据库表S的触发器TRIGGER_S

点击【对象资源管理器】—>【数据库】—>【jiaoxuedb】—>【任意一数据库表】—>【触发器】—>【任意一触发器】，点击修改即可打开触发器的编辑界面，根据要求修改触发器即可：

2．用SQL修改数据库表C的触发器TRIGGER_DC

要求：通过课程名从COURESE表中删除某课程信息，同时删除SC表和TC表中与此课程相关的选课记录

新建查询，输入代码如下所示，执行后即可修改触发器，与存储过程的修改相同，与其说是修改触发器，更类似重新编写了一个触发器，因为其基本的修改思路就是重新编写了一个触发器，无论其属性需不需要修改，依然需要写在SQL语句中；

3.3删除触发器

1．交互式删除数据库表S的触发器TRIGGER_S

点击【对象资源管理器】—>【数据库】—>【jiaoxuedb】—>【任意一数据库表】—>【触发器】—>【任意一触发器】，右击后即可看到删除选项

2．用SQL删除数据库表C的触发器TRIGGER_DC

新建查询，输入代码如下所示,T-SQL语句中，DROP语句是非常通用的删除语句，一般的删除数据库、数据库表等都是利用DROP语句：

4.课后习题

基于教学数据库jxsk,创建下面的触发器,并给出正反实例:

1.为数据库表SC创建--触发器:当插入或修改-一个记录时,确保此记录的成绩在0—100分之间：

新建查询，输入代码如下所示，创建一个命名为TRIGGER_SC_1的触发器：

验证：交互式修改S3的C2数据，右图所示为将分数从100改至60，显示成功，而将分数由100改至110，则会显示触发器报错，说明触发器创建成果且符合实际需要：

2.为数据库表C、TC和SC创建参照完整性:级联删除和级联修改触发器：

新建查询，输入代码如下所示，创建一个命名为TRIGGER_SC_1的触发器：

验证：新建查询，输入代码如下所示，利用T-SQL语句修改COURESE表中C6课程的课程号为C7：

结果如下：其中1行受影响为数据库TC，3行受影响为数据库表SC：

3.为数据库表T创建一触发器:当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元;从“副教授”晋升为“教授"时,岗位津贴自动增加900元：

新建查询，输入代码如下所示，创建一个命名为TRIGGER_T的触发器：

验证：新建查询，输入代码如下所示，利用T-SQL语句修改教师号为T4的教师职称由讲师更改为副教授：

结果如下所示，当教师职称发生改变时，薪水将会一起改变：

5.实验总结

1.相关概念的学习与区分

存储过程的相关原理：

存储过程代码的编写，其实本质上就是利用SQL语句编写代码的一个过程，只是将比较复杂的过程封装了起来，并且引入了形参作为我们实际使用的变量，形参是存储过程与触发器中都非常重要的一部分，编写存储过程的代码，只有在声明变量时，输出数据的形参后不需要加OUTPUT，其余部分都需要加OUTPUT。因此虽然创建存储过程的编辑窗口看起来很复杂（尤其是交互式方法），但其实原理是非常好理解的，代码的编写也十分简单直接。

而调用存储过程，在我们此次实验过程中可以明显感受到，相比起交互式方法与T-SQL语句的方法，存储过程似乎并没有给我们带来多少的便捷，其根本原因还是在于我们目前数据库中的数据量太少了，这也导致我们无法很好的体验到存储过程设计的目的与优势，事实上，存储过程最大的优点就在于它提高SQL语句的重用性，不仅仅是简化了我们反复编译的操作，同时减少了我们表与表、数据库与服务器的连接次数，从而提高了查询数据库的效率。

触发器的相关原理：

触发器的根本目的，是为了保证实现数据库的完整性，简单理解来说就是保证数据库表中数据可以同步更新，这样看来，触发器的作用与效果与我们之前实验课接触的约束十分相似。

但触发器这一工具，主要运用的是数据库中DELETED表与INSERTED表：DELETED表用于存储 DELETE和UPDATE语句所影响的行的副本；INSETED表用于存储 INSERT和UPDATE语句所影响的行的副本。触发器更像是利用我们自行编写的SQL语句来对数据库进行一定的‘约束’，相比于之前所学的内容，逻辑上更方便于理解。

2.代码执行与编写思路总结

在此次实验中，正确理解如何利用SQL语句编写创建存储过程与触发器的代码是最为重要也是最困难的一步，部分同学如果实验过程中只是对着实验指导书上的内容照抄，而忽略了进行自我的思考与总结的话，就很难学到此次实验课中的精髓，因此我将我对代码的理解以文字的内容总结如下：

存储过程：以下图的存储过程的代码为例

基础（上半）部分为：CREATE PROCEDURE+【存储过程名】+【形参名】+【形参类型】，其中多个形参用逗号隔开；

下半部分为：AS起手，作为上半与下半的分界线，AS后的所有内容，即符合我们SQL语句编写代码的原理与思路。

触发器：以下图的触发器的代码为例

基础部分为：CREATE TRIGGER 【触发器名】 ON 【数据库表名】 FOR 【UPDATE、INSERT、DELETE】（在进行什么操作时触发触发器）；

重点编写部分：AS起手，作为分界线

首先+DECLARE，声明我们要用到的所有形参变量及其数据类型；

其次+SELECT FROM WHERE语句，为我们声明的形参变量赋值；

而后的内容，就是根据实验要求需要，利用SQL语句进行代码编写：

IF+通常加在什么样的情况下触发器触发；

BEGIN END 语句：BEGIN+在IF的条件下数据库表进行什么样的修改，可以有多个BEGIN END 语句

ROLLBACK TRANSACTION语句：表示不执行该操作，回滚至原来值。

3.常见问题总结

调用存储过程，SELECT获取的查询结果不符合实际需要：

在创建成功存储过程后，为验证存储过程是否符合我们的需求，一般都会调用存储过程以进行验证，但在此次存储过程的第一个实验步骤进行验证时，就发现得到的结果不符合预期，存储过程返回的结果并不是一个表，而是一个单独的值。

经过老师讲解后我们可以知道，其根本原因在于在调用存储过程的代码中，我们使用的是SELECT的调用函数，此函数在我们目前编写的代码逻辑中，得到的第二个结果会将第一个结果覆盖，因此导致：如果我们应该得到多个结果，最后得到结果却只有一个，而且固定为结果中的最后一个。

为得到准确的结果，根据老师讲解，我可以用到理论课学习的与后续实验课程会练习的游标(Cursor)，循环输SELECT调用函数的查询结果，此次实验中就不再进行深入的探讨与研究，在后续的实验过程中希望有更详细具体的学习。

4.实验心得

（1）本次存储过程与触发器实验的实验操作其实都较为简单，是思路非常清晰的创建、修改、删除一条龙式的实验过程，同时因为有实验指导书的辅助与参考，对于大部分同学来说完成实验内容应该并不是一件困难的事，但其实验比较关键的一点在于：真正理解代码编写的原理，与如何解决验证过程中常出现的一些的错误。因为与往次实验不同的是，在存储过程与触发器编写过程中，我们很少出现代码的报错错误，但在交互式或T-SQL语句编写调用存储过程或触发器的代码时，却常出现得到结果不符合我们预期的情况，考虑到交互式方法的缺点、主外键的影响、约束的限制等等因素，这类情况出现的原因有很多。但归根结底，只要理清其中的逻辑与原理就不难解决；

（2）此次实验中，我感受最为明显的一点就是SQL语句相比于交互式方法的优点：首先是在创建存储过程或触发器的过程中，我们可以很直接的感受到，就算是交互式方法，其底层思路依然是要编写SQL语句，而且交互式方法打开的编辑界面还较为复杂，并不像SQL方法新建查询简洁；除此之外，在调用存储过程或触发器时，如果用交互式直接修改，往往不能立即得到触发器的反馈，而交互式则会直接提示（几行受到影响）；同时，如果调用存储过程或触发器显示报错，SQL语句的方法显示的报错原因也比交互式方法更为详细，更方便于我们针对报错原因进行修改；

（3）在存储过程与触发器整个的实验过程中，我再一次体会到了大一时学习C++课程编写代码的感觉，虽然无论是语句的复杂程度还是思路的创建难度目前我们学习到的难度还不及C++的课程，但我能深刻的感受到，二者其最基本的原理与思路都是互通的，我们首先需要明确我们的需求需要获得什么，其次明确在什么样的条件下执行我们的语句，最后也就是我们最重要的部分，根据实验要求编写相应的应执行的代码，我也非常感谢过去的自己与老师，虽然在大学之前从未有过计算机语言的基础，但通过努力认真的学习，已经为现在的自己深入学习打下了良好的基础。