实验八 触发器

《数据库系统原理》实验报告八

题目：实验八触发器

学号：xxxxxxxxxxx 姓名：lrm_1036 日期：2010-12-30

一、实验环境：SQL server 2005

二、实验目的：

1. 掌握创建、修改、删除和执行触发器的方法；

2. 完成上机练习。

三、实验内容与完成情况：

（要求：提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。）1.创建和执行触发器

（1）交互式为数据库表Student创建一级联更新触发器TRIGGER_S（要求：若修改Student表中一学生的学号，则SC表中与该学生相关的学号自动修改）

·启动SSMS，在“对象资源管理器”中，展开“数据库”→“实验”→“表”→“dbo.Student对象”。右击“触发器”，在打开的快捷菜单中，选择“新建触发器”，打开触发器编辑窗口，如图1.1所示.

图1.1

·将窗口内模板语句修改为下列SQL语句：

CREATE TRIGGER TRIGGER_S

ON Student FOR UPDATE AS IF UPDATE(SNO)

BEGIN

DECLARE @SNO_NEW INT, @SNO_OLD INT--声明

SELECT @SNO_NEW=SNO FROM INSERTED --查找新数据和

SELECT @SNO_OLD=SNO FROM DELETED --查找旧数据

UPDATE SC SET SNO=@SNO_NEW WHERE SNO=@SNO_OLD

--把新数据更新到SC表中

END

·单击工具栏中的分析按钮，检查语法是否正确。

·单击执行按钮，保存创建的触发器，如图1.2所示。

图1.2

·验证触发器的作用：

a.在SSMS中，打开数据库表Student和SC的数据表，可以看到，在Student中学

号为1001的学生在SC表中有3条记录。

b.查看数据库表Student和SC之间是否已创建外键参照关系，若已创建，则删除。

使此功能用刚创建的触发器TRIGGER_S来代替。

c.把表Student中的学号为1001改为1010，单击“执行”按钮，执行该操作，如图1.3所示。

use实验

update Student

set Sno = 1010

where Sno = 1001

图1.3

d.单击表SC，使其成为当前表，单击工具栏中的“执行”按钮，更新数据库表SC

中的数据，可以看到表SC中的三个1001学号同时自动变成了1010，如图1.4

所示。

图1.4

（2）用SQL为数据库表SC创建一触发器Score_SC_TRI(要求：当插入一个记录或修改成绩时，确保此记录的成绩在0~100分之间)

·打开查询编辑器窗口，在窗口中输入如下SQL语句：

USE实验

GO

CREATE trigger Score_SC_TRI

ON SC FOR Insert,update

As declare @score_read int

Select @score_read=grade from inserted --查找新数据（成绩）

If @score_read>=0 and @score_read<=100

--判断新数据是否在-100之间

Begin

Print'操作完成！'--是，输出

Return

End

Print'成绩超出~100之间！请重新输入。'--否，输出

Rollback Transaction--返回

GO

·单击工具栏中的“执行”按钮，执行SQL语句。

·在“对象资源管理器”中，展开“数据库”→“实验”→“表”→“dbo.SC”→“触发器”，可以看到Score_SC_TRI已经存在，如图1.5所示。

图1.5

·验证触发器的作用：

a.在“对象资源管理器”中，打开表SC；

b.在查询编辑器窗口中输入如下SQL语句：

USE实验

INSERT INTO SC VALUES(1001,4,190)

--成绩为超出范围，而要求重新输入

GO

INSERT INTO SC VALUES(1001,4,100)

--绩为在正常范围内，而插入表中

GO

UPDATE SC SET grade=130 WHERE SNO=1002 AND CNO=1

--成绩为超出范围，而要求重新输入

GO

UPDATE SC SET grade=60 WHERE SNO=1002 AND CNO=1

--成绩为在正常范围内，而修改成功

GO

c.单击工具栏中的“执行”按钮，执行SQL语句。可以看到4调系统信息，表示：

第一条INSERT语句因成绩为190超出范围，而要求重新输入；第二条INSERT 语句因成绩为100在正常范围内，而插入表中；第三条UPDATE语句因成绩为130超出范围，而要求重新输入；第四条UPDATE语句因成绩为60在正常范围内，而

修改成功，如图1.6所示。

图1.6

d.再次查看SC表中的数据，可以看到增加了一个记录，修改了一个记录，如图1.7

所示。

图1.7

（3）请自行完成：用SQL为数据库表Course创建一个级联删除触发器TRIGGER_DC （要求：通过课程名从Course表中删除某课程信息，同时删除SC表中与此课程相关的选课记录）

·打开查询编辑器窗口，在窗口中输入如下SQL语句：

CREATE TRIGGER TRIGGER_DC

ON Course FOR DELETE AS

BEGIN

DECLARE @CNO_OLD INT--声明

SELECT @CNO_OLD=CNO FROM DELETED --查找要删除的数据

DELETE FROM SC WHERE CNO=@CNO_OLD --把新数据更新到SC表中END

·单击工具栏中的“执行”按钮，执行SQL语句。

·在“对象资源管理器”中，展开“数据库”→“实验”→“表”→“dbo.SC”→“触发器”，可以看到Score_SC_TRI已经存在，如图1.8所示。

图1.8

·验证触发器的作用：

a.在“对象资源管理器”中，打开表SC；

b.在查询编辑器窗口中输入如下SQL语句：

use实验

delete

from Course

where Cname ='C语言程序设计'--相当于Cno=4

c.单击工具栏中的“执行”按钮，执行SQL语句。可以看到2调系统信息，表示：

SC表有两行信息删除成功，Course表有一行信息删除成功，如图1.9所示。

图1.9

d.再次查看SC表中的数据，可以看到删除的两行记录，删除前表SC如图1.7所示，

删除后表SC如图1.10所示。

图1.10

2.修改触发器

（1）交互式修改数据库表Student的触发器TRIGGER_S（要求：若删除数据库表Student 中某一个学生的记录信息，则自动删除数据库表SC中与该学生相关的记录信息）·在“对象资源管理器”中，展开“数据库”→“实验”→“表”→“dbo.Student”

→“触发器”，右击“TRIGGER_S”，在打开的快捷菜单中，选择“修改”选项，如图

2.1所示，打开触发器修改窗口，该窗口中显示了此触发器的定义，如图2.2所示。

图2.1

图2.2

·把窗口中的SQL语句修改成下面内容，以实现题目要求：

ALTER TRIGGER TRIGGER_S

ON Student FOR DELETE

AS DECLARE @SNO_DEL int

SELECT @SNO_DEL=SNO FROM DELETED

DELETE FROM SC WHERE SNO=@SNO_DEL --删除表SC中相关信息

·单击工具栏中的“分析”按钮，检查语法是否正确。

·单击“执行”按钮，保存创建的触发器。

·展开“数据库”→“实验”→“表”→“dbo.Student”→“触发器”，双击TRIGGER_S，打开触发器修改窗口，可以看到TRIGGER_S已经修改，如图2.3所示。

图2.3

·验证触发器TRIGGER_S的功能：在数据库表Student中选择任意一个学生记录，将其删除，查看数据库表SC中与其对应的选课记录的变化。

a.在“对象资源管理器”中，打开表SC；

b.在查询编辑器窗口中输入如下SQL语句：

use实验

delete

from Student

where Sno ='1002'

c.单击工具栏中的“执行”按钮，执行SQL语句。可以看到2调系统信息，表示：

SC表有三行信息删除成功，Student表有一行信息删除成功，如图2.4所示。

图2.4

d.再次查看SC表中的数据，可以看到删除的两行记录，删除前表SC如图1.10所

示，删除后表SC如图2.5所示。

图2.5

（2）请自行完成：用SQL修改数据库表Course的触发器TRIGGER_DC（要求：通过课程号从数据库表Course中删除某课程信息，同时删除数据库表SC中与此课程相关的记录）·打开触发器修改窗口，把窗口中的SQL语句修改成下面内容，以实现题目要求：ALTER TRIGGER TRIGGER_DC

ON Course FOR DELETE

AS DECLARE @CNO_OLD INT--声明

SELECT @CNO_OLD=CNO FROM DELETED --查找要删除的数据

DELETE FROM SC WHERE CNO=@CNO_OLD --把新数据更新到SC表中

·单击工具栏中的“分析”按钮，检查语法是否正确。

·单击“执行”按钮，保存创建的触发器。

·展开“数据库”→“实验”→“表”→“dbo.Course”→“触发器”，双击TRIGGER_S，打开触发器修改窗口，可以看到TRIGGER_S已经修改，如图2.6所示。

图2.6

·验证触发器TRIGGER_S的功能：在数据库表Course中选择任意一个学生记录，将其删除，查看数据库表SC中与其对应的选课记录的变化。

a.在“对象资源管理器”中，打开表SC；

b.在查询编辑器窗口中输入如下SQL语句：

use实验

delete

from Course

where Cno = 2

c.单击工具栏中的“执行”按钮，执行SQL语句。可以看到2调系统信息，表示：

SC表有一行信息删除成功，Course表有一行信息删除成功，如图2.7所示。

图2.7

d.再次查看SC表中的数据，可以看到删除的两行记录，删除前表SC如图2.5所示，

删除后表SC如图2.8所示。

图2.8

3.删除触发器

（1）交互式删除数据库表Student的触发器TRIGGER-S：

·在“对象资源管理器”中，展开“数据库”→“实验”→“表”→“dbo.Student”

→“触发器”，右击“TRIGGER_S”，在打开的快捷菜单中选择“删除”选项，如图3.1所示，弹出“删除对象”对话框，如图3.2所示。

图3.1

图3.2

·单击“确定”按钮，触发器TRIGGER_S即被删除。

（2）用SQL删除数据库表Course的触发器TRIGGER_DC ·在查询编辑器窗口中输入下列SQL语句

USE实验

GO

DROP TRIGGER TRIGGER_DC

GO

·单击工具栏中的“执行”按钮，执行SQL语句

·查看数据库“实验”中触发器对象的变化，如图3.3所示。

图3.3