实 验 七

实验名称：触发器的创建和使用 实验学时：2 实验目的：

1） 了解触发器和一般存储过程的主要区别 2） 了解使用触发器的优点 3） 掌握创建触发器的方法 4） 掌握查看触发器信息的方法 5） 了解删除触发器的方法 实验内容及步骤： （一） 触发器的概念

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

（二） 触发器的优点

? 触发器是自动的执行的。当对表中的数据做了任何修改之后立即被激活。 ? 触发器可以通过数据库中的相关表进行层叠更改。 ? 触发器可以强制限制。 （三） 触发器的类型

? AFTER触发器：这种类型的触发器将在数据变动完成后才被触发，AFTER触发器只能在

表上定义。在同一个数据表中可以创建多个AFTER触发器。

? INSTEAD OF触发器：SQL Server 2000新增功能。这种类型的触发器将在数据变动以前

被触发，并取代变动数据的操作，而去执行触发器定义的操作。INSTEAD OF触发器可以在表或视图上定义。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。 （四） 触发器的创建

? 使用企业管理器创建：在企业管理器中，展开指定的服务器和数据库，右击某个表，从弹出的快捷菜单中依次选择“所有任务|管理触发器”选项，会出现触发器属性对话框。在该对话框的“名称”文本框中选择“新建”，然后在文本框中输入创建触发器的文本。

当创建一个触发器时必须指定以下几项内容： ? 触发器的名称

? 在其定义触发器的表 ? 触发器将何时激发

? 执行触发操作的编程语句 例如：创建一个INSERT触发器，当在表s中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。在文本框中输入以下文本： CREATE TRIGGER Stu_Insert on [dbo].[s] FOR INSERT AS

DECLARE @msg char(30)

SET @msg=”你插入了一条新记录！”print @msg

32

单击“检查语法”按钮，可以检查语法是否正确。然后单击“应用”按钮，在名称下拉列表中出现新创建的Stu_Insert触发器的名称，单击“确定”按钮，即可关闭该对话框，成功创建触发器。

创建该触发器后，查看向t_student表中插入数据时此触发器所完成的功能。在查询分析器中输入以下SQL语句： use student go

insert into t_student (sno,sname,sex)

values(‘20030125’,’王帆’，‘男’) 执行后结果如下：

你插入了一条新记录！ （所影响的行数为1行）

? 使用T-SQL创建：使用Transact-SQL语言中的CREATE TRIGGER命令可以创建触发器，其中需要指定定义触发器的基表、触发器执行的事件和触发器的所有指令。创建触发器类似创建存储过程，语法形式如下： CREATE TRIGGER trigger-name ON{table│view} [WITH ENCRYPTION] {

{{FOR│AFTER│INSTEAD OF}{[DELETE][,][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]

}]

sql_statement [?n] } }

说明：

trigger -name：触发器的名称，必须唯一且符合标识符命名规则。

AFTER：用于规定此触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。若仅指定FOR关键字，则AFTER是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义该触发器。

INSTEAD OF：用于规定执行的是触发器而不是执行触发SQL语句，从而用触发器替代触发语句的操作。每个INSERT、UPDATE、DELETE语句最多可以定义一个INSTEAD OF触发器。INSTEAD OF触发器不能在WITH CHECK OPTION 的可更新视图上定义。 {[DELETE][,][INSERT][,][UPDATE]}：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。

33

例1：创建一个AFTER触发器，要求实现以下功能：在sc表上创建一个插入、更新类型的触发器scoreCheck，当在grade字段中插入或修改考试分数后，触发该触发器，检查分数是否在0~100之间。 Use student

if exists (select name from sysobjects where name = ‘scoreCheck’ and type =’TR’)

drop trigger scoreCheck /*创建触发器*/

creat trigger scoreCheck on sc

for insert,update as

if update(score)

print ‘AFTER触发器开始执行??’ begin

declare @ScoreValue real

select @ScoreValue=(select score from inserted) if @ScoreValue>100 or @ScoreValue<0

print ‘输入的分数有误，请确认输入的考试分数！’ end

创建了scoreCheck触发器之后，在查询分析器中输入以下SQL语句： use student

print ‘在sc中插入记录时触发器执行结果：’ /*在屏幕上显示引号中内容*/ print ‘’ /*在屏幕上显示一空行*/ insert into sc

values(‘20030156’,’01’,-40) /*在屏幕上显示输入错误信息*/ update sc set score=123

where sno=’20030101’and cno=’1’ /*在屏幕上显示输入错误信息*/

例2：创建一个INSTEAD OF触发器，要求实现以下功能：在c表上创建一个删除类型的触发器NotAllowDelete，当在c表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。 Use student

if exists (select name from sysobjects

where name = ‘NotAllowDelete’ and type =’TR’) drop trigger NotAllowDelete /*创建触发器*/

creat triggerNotAllowDelete on c

instead of delete as

print ‘INSTEAD OF触发器开始执行??’

print ‘本表中的数据不允许被删除！不能执行删除操作！’ NotAllowDelete触发器后，在查询分析器中输入以下SQL语句：

34

use student

delete from c where cno=20030101’

/*屏幕上显示NSTEAD OF触发器开始执行??*/

（五） 触发器的查看

? 使用企业管理器查看用户创建的存储过程

在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，并右击要查看的表，从弹出的快捷菜单中选择“所有任务”│“管理触发器”选项，在“触发器属性”对话框中，从名称下拉列表框中选择要查看的触发器名称，在下面的文本框中就会显示该触发器的定义语句。

? 使用系统存储过程查看用户创建的存储过程： sp_help ‘触发器名称’ 用于查看触发器的一般信息 sp_helptext ‘触发器名称’ 用于查看触发器的正文信息 sp_depends ‘触发器名称’

用于查看指定触发器所引用的表或指定的表涉及到的所有触发器。 （六） 触发器的删除

? 使用企业管理器删除存储过程

在企业管理器中，右击要删除的触发器，从弹出的快捷菜单中选择“所有任务”│“管理触发器”选项，在“触发器属性”对话框中，从名称下拉列表框中选择要删除的触发器，然后单击“删除”按钮，即可删除该触发器。 ? 使用T_SQl删除

drop TRIGGER {触发器名} [,?n] 实验习题：

创建一个INSTEAD OF触发器，要求实现以下功能：在sc表上创建一个删除类型的触发器NotAllowDelete，当在sc表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。 实验报告要求: ? 实验目的 ? 实验内容及步骤 ? 实验习题的源程序清单

? 写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

35