《数据库系统原理》实验指导书参考答案(计本10、电商10)(9)

实验十一：触发器（1）

一、 实验目的

通过实验使学生加深对数据完整性的理解，学会创建触发器，会用触发器实现复杂的数据库完整性。

二、 原理解析 1、触发器概述

触发器是一种特殊类型的存储过程，用于保证数据完整性。事实上，它是一组SQL语句。当表中的一行被插入、修改或删除时，触发器被执行。

触发器与一般存储过程有一些区别，例如，存储过程可以直接调用，但是触发器不能被直接调用执行，只能由系统自动激活。触发器主要的用途是维护行级数据的完整性，而不是返回结果。 2、触发器的类型

插入INSERT、更新UPDATE、删除DELETE。 3、在SQL中创建触发器的语法 创建触发器的语法为：

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ]

[ NOT FOR REPLICATION ] AS

[ { IF UPDATE ( column )

[ { AND | OR } UPDATE ( column ) ] [ ...n ]

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ]

34

sql_statement [ ...n ] } }

三、实验内容

（1）创建INSERT类型触发器。 （2）创建DELETE类型触发器。 （3）创建UPDATE类型触发器。 四、实验步骤

（1）在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。

分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。 USE StuScore --切换到学生成绩库 GO

CREATE TRIGGER trigger1 ON xscj --创建触发器trigger_9_1 FOR INSERT AS

--定义变量@xh，表示学号，定义变量@xf，表示学分 DECLARE @xh char(8),@xf tinyint

--从inserted表取出新插入的学生选课记录的学号与学分，分别赋给变量@xh和@xf

SELECT @xh=学号,@xf=学分 FROM inserted

--更新学生情况表中指定学号的学生的总学分，其中ISNULL为函数，用0替换原总学分中的空值 UPDATE xsqk

SET 总学分=ISNULL(总学分 , 0 )+@xf

35

WHERE 学号=@xh GO

（2）创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。 USE StuScore GO

CREATE TRIGGER trigger2 ON xskc FOR UPDATE AS

PRINT '学生课程表被修改了' GO

（3）创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。 USE StuScore GO

CREATE TRIGGER trigger3 ON xskc FOR DELETE AS

DELETE xscj FROM xscj,deleted WHERE xscj.课程号=deleted.课程号 GO

（4）创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。 USE StuScore GO

CREATE TRIGGER trigger4 ON xskc

36

FOR UPDATE AS

IF update(课程号) BEGIN UPDATE xscj

SET 课程号=(SELECT 课程号 FROM inserted) FROM xscj,deleted

WHERE xscj.课程号=deleted.课程号 END GO

（5）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。 use stuscore go

create trigger trigger5 on xscj for insert as

declare @xh char(8)

select @xh=学号from inserted

if not exists(select * from dbo.xsqk where 学号=@xh) insert into dbo.xsqk(学号,姓名) values(@xh,'zhangsan') 测试：

insert into xscj values('30','301',89,2)

（6）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，调用实验九中创建的存储过程proc5，实现统计该生的平均成绩功能。 存储过程的创建：

CREATE PROCEDURE proc5

37

@sno char(10),@avgscore int OUTPUT AS

IF @sno=NULL BEGIN

PRINT '学号不能为空！' RETURN END

SELECT @avgscore=AVG(成绩) from xscj

WHERE 学号=@sno RETURN

在触发器中调用存储过程： create trigger trigger6 on xscj for insert as begin

declare @xh char(8),@avgscore int select @xh=学号from inserted exec proc5 @xh,@avgscore output select @avgscore end

38