第9章 存储过程和触发器 本章导读： 存储过程和触发器都不是标准的SQL语句，不同的数据库管理系统在具体实现时语法会有所不同。存储过程是一组Transact-SQL语句和可选控制语句的预编译集合，它是按名存储并运行于服务器上，独立于表的数据库对象。而触发器是一种在关系表上定义，伴随用户修改相关数据而自动执行的特殊存储过程，主要用于强化复杂的规则和要求。 知识要点： 存储过程 触发器

9.1 存储过程 存储过程是存储在服务器上的一组预编译好的T-SQL代码。存储过程可以通过输入参数接收调用程序的实参输入，也可以通过输出形参将运行结果返回给调用程序，还可以通过状态参数判断存储过程的执行成功与否。用户自定义的存储过程名存储于系统表sysobjects中，而存储过程中定义的文本内容存储于系统表syscomments中。 9.1.1 存储过程概述 9.1.2 存储过程的创建

9.1.1 存储过程概述 存储过程是一个独立于表之外的数据库对象，可以作为一个单元被用户的应用程序调用。执行存储过程时，只需要将参数传递到数据库中，而不需要将整条SQL语句都提交给数据库，从而减少了网络传送流量，另外，因为省去了执行SQL语句时对SQL进行编译的时间，也提高了程序的运行速度。SQL Server支持5种类型的存储过程： 1．系统存储过程 SQL Server提供了大量的系统存储过程，很多管理活动都是通过系统存储过程实现的。系统存储过程名以sp_为前缀，存储在master数据库中，用户可以在任何数据库中执行系统存储过程。另外，用户可以在master数据库中定义sp_为前缀的自定义系统存储过程。 2．用户存储过程 是指用户自行创建并存储在用户数据库中的存储过程。为了与系统存储过程相区别，一般不要将用户存储过程名定义为以sp_为前缀的名称。如果用户自定义的存储过程和系统存储过程同名，那么用户存储过程永远不执行。

9.1.1 存储过程概述 3．临时存储过程 临时存储过程分为局部临时存储过程和全局临时存储过程。 9.1.1 存储过程概述 3．临时存储过程 临时存储过程分为局部临时存储过程和全局临时存储过程。 局部临时存储过程名称以#为前缀，存放在tempdb数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程。 全局临时存储过程名称以##为前缀，存放在tempdb数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。 4．远程存储过程 位于远程服务器上的存储过程。 5．扩展存储过程 扩展存储过程：利用外部语言（如C语言编写的存储过程，以弥补SQL Server的不足之处，扩展新的功能，扩展存储过程名以xp_为前缀。

9.1.2 存储过程的创建 SQL Server提供了两种创建存储过程的方法：使用企业管理器和使用T-SQL语句的Create Procedure命令。 1．使用Transact-SQL语句 在SQL Server中，使用T-SQL语句的Create Procedure命令创建存储过程，其格式如下： create proc[edure] <存储过程名>[;分组编号] [{@形式参数 数据类型} [ = 默认值 ] [output] [varying]] [,...n] [with { recompile | encryption | recompile,encryption}] [for replication] as sql语句 [...n]

9.1.2 存储过程的创建 功能：在当前数据中创建指定名称的存储过程。 说明： 9.1.2 存储过程的创建 功能：在当前数据中创建指定名称的存储过程。 说明： （1）存储过程名：存储过程名须符合标识符规则，且对于数据库及其所有者是唯一的； （2）分组编号：整数，指明同名存储过程的分组编号，以便于一条drop procedure语句删除一组同名存储过程； （3）@形式参数：指明形式参数（形参）名称，形参有输入参数和输出参数之分； （4）数据类型：指明形参的数据类型，包括text，ntext和image等数据类型，当形参是输入参数时，不能使用cusor（游标）数据类型； （5）默认值：指明输入参数的默认值，可以是常量、NULL或字符匹配运算符like关键字，当输入参数定义了默认值后，调用语句可以省略实参，否则必须提供实参； （6）output：指明形参是输出参数，且允许有返回值，否则为输入参数；

9.1.2 存储过程的创建 （7）varying：指明返回值是可变的，当形参数据类型为cursor时需指定varying选项； 9.1.2 存储过程的创建 （7）varying：指明返回值是可变的，当形参数据类型为cursor时需指定varying选项； （8）recompile|encryption：recompile表示每次重新编译存储过程，而encryption表示加密存储过程文本； （9）for replication：表示创建的存储过程只能在复制过程中执行，而不能在订阅服务器上执行。for replication和with encryption不能联合使用； （10）as：表示指定要执行的操作； （11）sql语句：过程中包含的任意类型和数目的SQL语句，但有一些限制，如不可以使用创建数据库对象的语句。。

9.1.2 存储过程的创建 【例9-1】 创建一个存储过程，用来求任意一个数的阶乘。 use jxgl 9.1.2 存储过程的创建 【例9-1】 创建一个存储过程，用来求任意一个数的阶乘。 use jxgl if exists(select name from sysobjects where name='fact' and type ='p') drop proc fact go create procedure fact @n int,@f int output as if @n<0 print '你输入了的'+cast(@n as varchar(20))+'，请输入非负数' else begin declare @i int set @i=1 set @f=1 while @i<=@n set @f=@f*@i set @i=@i+1 end print cast(@n as varchar(20))+'的阶乘是：'+cast(@f as varchar(20))

9.1.2 存储过程的创建 2．使用企业管理器创建存储过程 【例9-2】 使用企业管理器创建一个打印9乘9乘法表的存储过程。 操作步骤如下： 9.1.2 存储过程的创建 2．使用企业管理器创建存储过程 【例9-2】 使用企业管理器创建一个打印9乘9乘法表的存储过程。 操作步骤如下： （1）在企业管理器中选择数据库jxgl节点中的“存储过程”节点，右击弹出快捷菜单，如图9-1所示，单击“新建存储过程”命令后，弹出“存储过程属性”对话框，如图9-2所示。

图9-1 “企业管理器”对话框1 图9-2 “存储过程属性”对话框 9.1.2 存储过程的创建 图9-1 “企业管理器”对话框1 图9-2 “存储过程属性”对话框

9.1.2 存储过程的创建 （2）在文本框中输入代码后，如果单击“检查语法”按钮，可以对输入的代码进行语法检查，如图9-3所示。连续单击前后两个 “确定”按钮，自动保存并返回“企业管理器”对话框2，从中可以发现存储过程“multi”，如图9-4所示。 注意：在企业管理器中可以对已创建的存储过程，执行删除、重命名操作，也可通过属性执行查看、修改存储过程等操作。

9.1.2 存储过程的创建 create procedure multi as 9.1.2 存储过程的创建 create procedure multi as declare @i int,@j int,@out varchar(80) set @i=1 while @i<=9 begin set @out =cast(@i as char(1))+') ' set @j=1 while @j<=@i set @out=@out+cast(@i as char(1))+'*'+cast(@j as char(1))+'='+cast(@i*@j as char(2))+space(2) set @j=@j+1 end print @out set @i=@i+1

9.1.3 存储过程的执行 在查询分析器中可直接使用存储过程的名字或使用execute语句执行存储过程。执行存储过程的语法格式如下： 9.1.3 存储过程的执行 在查询分析器中可直接使用存储过程的名字或使用execute语句执行存储过程。执行存储过程的语法格式如下： [ [ exec [ ute ] ] { [ @返回状态值 = ] {存储过程名[;分组编号] | @存储过程名变量}} [ [ @形式参数名称 = ] {实际参数值 | @实际参数变量 [output] | [default]}][,...n ] [ with recompile ] 说明：

9.1.3 存储过程的执行 （1）execute：执行存储过程的命令，该命令若是批处理中的第一条，则可以省略。 9.1.3 存储过程的执行 （1）execute：执行存储过程的命令，该命令若是批处理中的第一条，则可以省略。 （2）返回状态值：是一个可选的整型局部变量，保存存储过程的返回状态。这个变量在用于execute语句时，必须已在批处理、存储过程或函数中声明过； （3）存储过程名：要调用的存储过程的名称； （4）@存储过程名变量：局部变量名，代表存储过程的名称； （5）@形式参数名称：是指存储过程中已定义的形式参数名称，使用时注意以下几点： 在使用格式“@形式参数名称=实际参数值 | @实际参数变量”时，形式参数名称及其实际参数值不一定按照create procedure语句中定义的顺序出现。 在使用格式“实际参数值 | @实际参数变量”时，即省略“@形式参数名称”，要求必须按顺序传递给create procedure语句中定义的输入参数；

9.1.3 存储过程的执行 （6）实际参数值：存储过程调用时传递给输入参数的值； 9.1.3 存储过程的执行 （6）实际参数值：存储过程调用时传递给输入参数的值； （7）@实际参数变量：存储过程调用时传递给输入参数的实际参数变量，其用法同（5），或者用来保存并返回输出参数的变量，此时需要配合output选项； （8）output：表示@实际参数变量是用来保存并返回输出参数的变量； （9）default：表示调用存储过程时，使用存储过程定义时指定的默认值作为输入参数，实际调用可以省略。当调用存储过程时，需要的参数值没有事先定义默认值而指定了default关键字，或缺少输入参数值，都会出错； （10）with recompile：表示执行存储过程时强制重新编译，该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源； 【例9-3】 执行存储过程fact。 declare @f as float execute fact -3,@f output --你输入了的-3，请输入非负数 print'' execute fact 3,@f output --3的阶乘是：6

9.1.4 存储过程的查看 使用系统存储过程sp_help，sp_helptext来查看存储过程信息，它们格式与功能如下： 9.1.4 存储过程的查看 使用系统存储过程sp_help，sp_helptext来查看存储过程信息，它们格式与功能如下： 格式1：sp_help <存储过程名> 说明：查看存储过程的概要信息。 格式2：sp_helptext <存储过程名> 说明：查看存储过程的定义文本信息。 【例9-4】 查看存储过程fact的过程名的概要信息和定义文本信息。 use jxgl sp_help fact go sp_helptext fact

9.1.5 存储过程的修改 SQL Server提供了两种修改存储过程的方法：使用企业管理器和使用T-SQL语句的alter procedure 命令。修改存储过程的Transact-SQL语句格式如下： alter proc[edure] 存储过程名[;编号] [{@参数名 数据类型}[varying][= 默认值][output]][,...n] with {recompile|encryption|recompile,encryption}] as sql语句[...n] 说明：各参数含义与create procedure语句相同。

9.1.5 存储过程的修改 【例9-5】 修改存储过程fact为判断一个数是否是水仙花数。 use jxgl go 9.1.5 存储过程的修改 【例9-5】 修改存储过程fact为判断一个数是否是水仙花数。 use jxgl go alter proc fact @n int as if @n<100 or @n>999 print '你输入了的'+cast(@n as varchar(20))+'，请输入3位正数' else begin declare @i int,@j int,@k int set @i=@n/100 set @j=(@n-@I*100)/10 set @k=@n%10 if @n=@i*@i*@i+@j*@j*@j+@k*@k*@k print cast(@n as char(3))+'是水仙花数' print cast(@n as char(3))+'不是水仙花数' end

9.1.6 存储过程的改名 SQL Server提供了两种重命名存储过程的方法：使用企业管理器和使用系统存储过程的sp_rename 命令。使用sp_rename重命名存储过程的语法格式如下： 格式：sp_rename [@objname=]'对象名',[@newname=]'新对象名' 说明： （1）[@objname =] '对象名'：指定存储过程的当前名称。 （2）[@newname =] '新对象名'：指定存储过程的新名称。

9.1.6 存储过程的改名 【例9-6】 将存储过程名fact修改为marquee。 sp_rename fact,marquee 9.1.6 存储过程的改名 【例9-6】 将存储过程名fact修改为marquee。 sp_rename fact,marquee execute marquee 153 运行结果如图9-5所示。 图9-5 例9-6运行结果

9.1.7 存储过程的删除 SQL Server提供了两种删除存储过程的方法：使用企业管理器和使用T-SQL语句的drop procedure命令。使用T-SQL语句删除存储过程的语法格式如下： 格式：drop procedure 存储过程名[;分组编号] 说明：删除指定名称的存储过程，如果同时指定存储过程的分组编号，那么只删除指定编号的存储过程，否则一组同名的存储过程一道删除。

9.1.8 存储过程的应用 存储过程不仅可以封装处理数据的语句，还可以通过参数实现数据传入和传出，但存储过程的返回值不能作为表达式一部分，而必须通过execute语句才能得到存储过程的返回值。 1．使用存储过程封装处理数据的语句 【9-7】 创建一个存储过程，实现查询班级号为090102学生信息的功能。 use jxgl go create proc pro_学生 as select 学号,姓名,性别,year(getdate())-year(出生日期) as 年龄,籍贯 from 学生 where left(学号,6)='090102'

9.1.8 存储过程的应用 2．使用带输入参数的存储过程 【9-8】 创建一个存储过程，实现查询指定学生姓名成绩信息的功能。 9.1.8 存储过程的应用 2．使用带输入参数的存储过程 【9-8】 创建一个存储过程，实现查询指定学生姓名成绩信息的功能。 use jxgl go create proc pro_学生_选修 @xm char(6) as select 学生.学号,姓名,课程号,成绩 from 学生,选修 where 学生.学号=选修.学号 and 姓名=@xm

9.1.8 存储过程的应用 3．使用输入参数带默认值的存储过程 【9-9】 创建一个存储过程，实现查询指定课程号的课程及其选修信息。 9.1.8 存储过程的应用 3．使用输入参数带默认值的存储过程 【9-9】 创建一个存储过程，实现查询指定课程号的课程及其选修信息。 use jxgl go --创建存储过程代码如下： create proc pro_课程_选修 @kch char(2)='01' as select 课程.课程号,课程名称,学号,成绩 from 课程,选修 where 课程.课程号=选修.课程号 and 课程.课程号=@kch --调用存储过程代码如下： exec pro_课程_选修 exec pro_课程_选修 default exec pro_课程_选修 '02'

9.1.8 存储过程的应用 4．使用带输出参数的存储过程 【9-10】 创建一个存储过程，实现查询教师的平均工资。 use jxgl go 9.1.8 存储过程的应用 4．使用带输出参数的存储过程 【9-10】 创建一个存储过程，实现查询教师的平均工资。 use jxgl go --以下是创建存储过程代码： create proc pro_avg_教师 @avgscore float output as select @avgscore=avg(基本工资) from 教师 --以下是执行存储过程代码： declare @avgscore float exec pro_avg_教师 @avgscore output print '教师的平均工资：'+cast(@avgscore as varchar(6)) 运行结果如下： 警告: 聚合或其它 SET 操作消除了空值。 教师的平均工资：2226

9.1.8 存储过程的应用 5．使用带输入输出参数的存储过程 9.1.8 存储过程的应用 5．使用带输入输出参数的存储过程 【9-11】 创建一个存储过程，实现指定姓名时，查询该生所有选修课程的平均成绩。 use jxgl go create proc pro_avg_成绩 @xm char(6),@avgscore float output as select @avgscore=avg(成绩) from 学生,选修 where 学生.学号=选修.学号 and 姓名=@xm --以下是执行存储过程代码： declare @xm char(6), @avgscore float set @xm='储兆雯' exec pro_avg_成绩 '储兆雯',@avgscore output print @avgscore

9.1.8 存储过程的应用 6．使用输出参数是游标类型的存储过程 【9-12】 创建一个存储过程，实现逐行显示表“学生”中的数据。 9.1.8 存储过程的应用 6．使用输出参数是游标类型的存储过程 【9-12】 创建一个存储过程，实现逐行显示表“学生”中的数据。 （1）创建存储过程代码： use jxgl go if exists (select name from sysobjects where name='cursor_选修'and type='p') drop proc cursor_选修 create proc cursor_选修 @xh char(8)='08010101', @js_cursor cursor varying output as set @js_cursor=cursor forward_only static for select * from 选修 where 学号=@xh open @js_cursor

9.1.8 存储过程的应用 （2）调用存储过程代码 declare @xh char(8),@my cursor 9.1.8 存储过程的应用 （2）调用存储过程代码 declare @xh char(8),@my cursor set @xh='08010101' exec cursor_选修 @xh,@my output while(@@fetch_status=0) begin fetch next from @my --提取数据 end close @my --关闭游标 deallocate @my --删除游标

9.1.8 存储过程的应用 （3）运行结果如图9-5所示。 图9-6 例9-12运行结果 图9-6 例9-12运行结果

9.1.8 存储过程的应用 7．使用带返回状态值的参数，返回值只能是整数 9.1.8 存储过程的应用 7．使用带返回状态值的参数，返回值只能是整数 【9-13】 创建存储过程AvgScore，根据给定的班级名称计算该班级的平均成绩，并将结果使用输出参数返回。如果指定的班级名称存在，则返回1，否则返回0。 create procedure avgscore @class varchar(20), @score float output as declare @classid int set @classid = 0 -- 根据参数中指定的班级名称class, 获取班级编号 select @classid = 班级号 from 班级 where 班级名称=@class if @classid = 0 return 0 else begin select @score = avg(成绩) from 选修 where left(学号,6)=@classid return 1 end go --调用存储过程 declare @score float declare @result int exec @result = avgscore '08会计（1）班', @score output -- 检查返回值 if @result = 1 print'平均成绩:'+ cast(@score as varchar(20)) print '没有对应的记录'

9.1.8 存储过程的应用 8．创建用户自定义的系统存储过程 9.1.8 存储过程的应用 8．创建用户自定义的系统存储过程 【例9-14】 创建一个自定义存储过程，显示指定表名的索引，如果没有指定表名，返回学生表的索引信息。 use master go if exists(select name from sysobjects where name ='sp_showtableindex' and type='p') drop proc sp_showtableindex create proc sp_showtableindex @tablename varchar(30)='学生' as select tab.name as 表名,inx.name as 索引名,indid as 索引标识号 from sysindexes inx join sysobjects tab on tab.id=inx.id where tab.name like @tablename use jxgl exec sp_showtableindex

9.1.8 存储过程的应用 9．创建带编号的存储过程 【例9-15】 创建一组存储过程score，显示选修表中各班级的最高分和最低分。 9.1.8 存储过程的应用 9．创建带编号的存储过程 【例9-15】 创建一组存储过程score，显示选修表中各班级的最高分和最低分。 if exists(select name from sysobjects where name ='proc_score' and type ='p') drop proc score go create procedure proc_score;1 as select left(学号,6) as 班级号,max(成绩) as 最高分 from 选修 group by left(学号,6) create procedure proc_score;2 select left(学号,6) as 班级号,min(成绩) as 最低分 from 选修 group by left(学号,6) exec proc_score;1 exec proc_score;2

9.2 触发器 触发器是由用户定义的一类特殊存储过程，常常用于对表实施复杂的完整性约束。它不能被显示地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。这一点也是触发器与存储过程不同的地方，而存储过程是由命令调用执行的。 9.2.1 触发器概述 9.2.2 触发器的创建

9.2.1 触发器概述 触发器是一个功能强大的工具，用于保证表中数据的变化遵循数据库设计者确定的完整性约束和规则。如当向表插入、更新、删除数据后，对其关联表的数据同时进行调整，以实时同步反映数据的变化。 1．触发器功能 使用触发器有助于保持数据库的数据完整性，在触发器中可以完成以下几个功能： （1）不允许删除或更新特定的数据记录； （2）不允许插入不符合逻辑关系的记录； （3）删除参照表的一条记录的同时级联删除被参照表的相关记录； （4）更新参照表的一条记录的同时级联更新被参照表的相关记录。

9.2.1 触发器概述 2．触发器类型 根据激活触发器执行的时机不同，常将触发器分为两类：instead of和after触发器。 9.2.1 触发器概述 2．触发器类型 根据激活触发器执行的时机不同，常将触发器分为两类：instead of和after触发器。 （1）instead of触发器：在触发事件（Insert、Delete和Update）之前激活触发器，其功能是不执行触发事件语句，而是执行instead of触发器本身。instead of触发器可以在表和视图上定义，一个表或视图上的每一个Insert、Delete和Update操作只能有一个instead of触发器。 （2）after触发器：在触发事件（Insert、Delete和Update）成功执行之后激活触发器，其功能是除非触发体中有回滚语句，否则即使触发事件违反约束规则，也不能阻止触发事件语句执行。after触发器只能在表上定义，一个表或视图上的每一个Insert、Delete和Update操作可以有多个after触发器。

9.2.1 触发器概述 3．inserted表和deleted表 9.2.1 触发器概述 3．inserted表和deleted表 使用触发器时，SQL Server会为每个触发器建立两个特殊的临时表：inserted表和deleted表。这两表存储在内存中，与被该触发器应用的表结构完全相同，而且由系统维护和管理，用户只能读取数据而不能修改数据。每个触发器只能访问自己的临时表，触发器执行完毕，两表也自动释放。 （1）inserted表：用于存储insert和update语句所影响的行副本。当执行insert和update操作时，新的数据行同时被添加基本表和inserted表中。 （2）deleted表：用于存储delete和update语句所影响的行副本。当执行delete和update操作时，指定的原数据行被用户从基本表中删除，然后被转移到deleted表中。一般来说，在基本表和deleted表中不会存在相同的数据行。 注意：update操作可以看成两个步骤：首先，将基本表中要更新的原数据行移到deleted表中，然后从inserted表中复制更新后的新数据行到基本表中。

9.2.2 触发器的创建 SQL Server提供了两种创建触发器的方法：使用企业管理器和使用T-SQL语句的Create Trigger命令。 1．创建触发器的注意事项 （1）create trigger语句必须是批处理中的第一条语句； （2）创建触发器的权限默认分配给表的所有者，且不能将该权限转移给其它用户； （3）触发器为数据库对象，其名称必须遵循标识符的命名规则； （4）只能在当前数据库中创建触发器，但可以引用其它数据库中的对象； （5）不能在临时表或系统表上创建触发器，可以引用临时表，但不能引用系统表； （6）如果已经给一个表的外键定义了级联删除或级联更新，则不能在该表上定义instead of delete或instead of update 触发器； （7）虽然truncate table语句类似于没有where子句的delete语句，但它并不会引发delete触发器； （8）writetext语句（更新text、ntext或image类型的列）不会引发insert或update触发器； （9）触发器不能返回任何结果，应避免使用select语句给变量赋值，除非设置set nocount； （10）每个表或视图只有一个instead of触发器，但可以有多个after触发器。

9.2.2 触发器的创建 2．使用T-SQL语句创建触发器 9.2.2 触发器的创建 2．使用T-SQL语句创建触发器 在SQL Server中，使用T-SQL语句的Create Trigger命令创建触发器，其格式如下： create trigger <触发器名> on {表名|视图名} [with encryption]{ {for|after|instead of}{[delete][,][insert][,][update]} as [if update(列)[{and|or}update(列)][...n]] |[if columns_updated(){bitwise_operator}update_bitmask {comparison_operator}column_bitmask[...n] sql语句[...n]} 功能：在指定的表上创建一个指定名称的触发器。

9.2.2 触发器的创建 说明： （1）表名|视图名：是指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。 （2）with encryption：在syscomments表中加密create trigger语句定义的文本内容。 （3）after：只有在引起触发器的T-SQL语句成功执行后才激活触发器，且在所有级联操作和约束检查成功完成后。如果仅指定for关键字，则默认为after触发器。 （4）instead of：执行触发器本身而不执行引起触发器激活的T-SQL语句（替代引起触发器执行的T-SQL语句）。 （5）{ [delete] [,] [insert] [,] [update] }：定义触发器激活的触发事件，各选项自由组合，中间用逗号隔开。 （6）as：引入触发器要执行的操作。 （7）if update (列)：判断指定的列（计算列除外）是否进行了insert或update操作（delete操作除外），可以指定多列，列名不需要指定表别名。对于insert操作和update操作，返回true值时表示这些列插入或更新了数据。 （8）if columns_update ()：用于判断指定列是否进行了insert或update操作，返回值为二进制位。若指定列进行了插入或更新操作，则返回值为二进制位1，否则为二进制位0； （9）bitwise_operator：二进制运算符； （10）update_bitmask：二进制位串； （11）SQL语句：当尝试delete、insert或update操作时要执行的transact-sql语句。

9.2.2 触发器的创建 【9-16】 在学生表上定义一个触发器，当学生表上数据变化时，显示表中所有内容。 9.2.2 触发器的创建 【9-16】 在学生表上定义一个触发器，当学生表上数据变化时，显示表中所有内容。 （1）打开查询分析器，输入创建触发器的代码如下： use jxgl go create trigger 学生_chang on 学生 after insert,update,delete as select * from 学生

9.2.2 触发器的创建 （2）单击运行按钮后，从右侧的对象浏览器的“触发器”节点中可以发现“学生_chang”触发器，运行结果如图9-7所示。 图9-7 例9-15运行结果2

9.2.2 触发器的创建 （3）输入针对学生表的插入、删除、更新操作命令，代码如下所示： 9.2.2 触发器的创建 （3）输入针对学生表的插入、删除、更新操作命令，代码如下所示： insert into 学生(学号,姓名,性别) values ('09060101','王昭君','女') （4）单击运行按钮后，运行结果如图9-8所示。 图9-8 例9-15运行结果2

9.2.2 触发器的创建 3．使用企业管理器创建触发器 【9-17】 在“学生”表上创建一个after类型的插入触发器“ins_stu”，当插入记录时，给予提示禁止插入记录的信息。 操作步骤如下： （1）展开企业管理器控制台目录，直至出现数据库（jxgl）的“表”节点，在右侧窗口中选择表“学生”，右击之弹出快捷菜单，选择“管理触发器”选项，如图9-9所示。 （2）单击释放后，弹出“触发器属性”界面，如图9-10所示。

图9-9 “企业管理器”对话框 图9-10 “触发器属性”界面 图9-9 “企业管理器”对话框 图9-10 “触发器属性”界面

图9-11 “企业管理器”对话框 图9-12 “触发器属性”界面 （3）在“触发器属性”界面的“文本”窗口中，输入触发器输入代码，如图9-12所示。如果单击“检查语法”按钮，可以进一步进行“语法检查”。 （4）连续两次单击“确定”按钮，返回“企业管理器”，重复步骤（1），再次弹出“触发器属性”界面，单击“名称”下拉列表框，发现刚才创建的触发器名称，如图9-13所示。 图9-11 “企业管理器”对话框 图9-12 “触发器属性”界面

代码如下： create trigger ins_stu on 学生 for insert as raiserror('禁止插入记录',10,1) 验证触发器的语句如下： insert into 学生(学号,姓名,性别,出生日期) values('09050102','刘飞翔','男','1991-6-7') 注意：用户打开学生表时，可以发现上述记录已经插入表中，这是由于触发器的for选项没有指定after关键字，默认值是after，即指定的insert操作成功时，执行触发触发器。

【9-18】 将“学生”表的插入触发器“ins_stu”修改为instead of类型，当插入记录时，给予提示禁止插入记录的信息。 use jxgl go alter trigger ins_stu on 学生 instead of insert as raiserror('禁止插入记录',10,1) 验证触发器的语句如下： insert into 学生(学号,姓名,性别,出生日期) values('09050103','刘飞娜','女','1992-7-17')

9.2.3 触发器的修改 SQL Server提供了两种修改触发器的方法：使用企业管理和使用T-SQL语句。使用T-SQL语句修改触发器语法格式如下： alter trigger <触发器名> on {表名 | 视图名 } [ with encryption ]{ {for|after|instead of }{[delete] [,] [insert] [,] [update]} as [if update(列)[{and|or}update(列)][...n ] ] |[if columns_updated(){bitwise_operator}update_bitmask) {comparison_operator}column_bitmask[...n] sql语句[...n]} 说明：各子句的含义同创建触发器中的子句一样。

9.2.3 触发器的修改 【9-19】 修改触发器“学生_change”，使之满足以下要求：实现向选修表插入某门课程的成绩时，检查课程表中是否存在该门课程，如没有就显示提示信息并禁止插入该记录。 use jxgl go alter trigger 学生_chang on 选修 after insert as if (select count(*) from 课程,inserted where 课程.课程号=inserted.课程号)=0 begin raiserror('没有此课程',16,1) rollback transaction end return 验证触发器的作用，可以向选修表中加入以下记录： Insert into 选修 (学号,课程号,成绩) values ('08010101','15',60) 运行结果如下： 服务器: 消息 50000，级别 16，状态 1，过程 学生_chang，行 7 没有此课程

9.2.4 触发器的禁用和启动 在操作表和使用触发器的时候，有时可能需要临时禁用某个触发器，使用完毕后可能需要继续启用触发器，这就需要禁用或启动触发器的命令。 格式：alter table <表名>{enable|disable}<all|触发器名> 说明： （1）enable是启动触发器，而disable是禁用触发器； （2）all代表所有触发器，而触发器名则表示指定触发器。

9.2.5 触发器的删除 当不需要某个触发器的时，可以删除它。触发器被删除后，触发器所在的表中数据不会因此而改变。另外，当某个表被删除时，定义与该表相关的所有触发器也会自动删除。 格式：drop trigger <触发器名> 说明：删除指定名称的触发器

9.2.6 触发器的管理 触发器是特殊的存储过程，所有适合存储过程的管理方式都适用于触发器。 9.2.6 触发器的管理 触发器是特殊的存储过程，所有适合存储过程的管理方式都适用于触发器。 （1）sp_helptrigger <表名>：查看指定表中定义的当前数据库的触发器类型； （2）sp_help <触发器名>：查看触发器概要信息，如名称，属性，类型和创建时间； （3）sp_helptext <触发器名>：查看触发器详细定义文本信息； （4）sp_depends <表名>|<触发器名>：查看指定表涉及到的触发器或触发器涉及到的表。 （5）sp_rename <触发器旧名> <触发器新名>

9.2.7 触发器的应用 触发器可以很好地维护数据，当向表中添加数据或更改记录后，对其关联表的数据进行调整，以实现反映数据的变化。如果触发器执行的一些动作可以通过约束来实现，则首先考虑约束，因为触发器比约束占用更多的系统资源，但对于强制各种规范化和强制实施复杂的业务规则，必须使用触发器。

9.2.7 触发器的应用 1．级联更新 【9-20】 在学生表上创建一个update触发器，当更新学生学号时，同时更新选修表中的学生学号。 9.2.7 触发器的应用 1．级联更新 【9-20】 在学生表上创建一个update触发器，当更新学生学号时，同时更新选修表中的学生学号。 create trigger up_学生 on 学生 for update as declare @oldid char(8),@newid char(8) select @oldid=deleted.学号,@newid=inserted.学号 from deleted,inserted where deleted.姓名=inserted.姓名 update 选修 set 学号=@newid where 学号=@oldid

9.2.7 触发器的应用 2．级联删除 【9-21】 在学生表上创建一个delete触发器，当删除学生记录时，同时删除选修表中对应的学生记录。 create trigger del_学生 on 学生 after delete as delete from 选修 where 学号 in (select 学号 from deleted)

9.2.7 触发器的应用 3．禁止插入（级联限制） 【9-22】 在学生表上创建一个触发器，当向选修表中插入学号时，同时检查学生表中是否存在该学号，若不存在，不允许插入该记录。 create trigger ins_选修 on 选修 after insert as if (select count(*) from 学生,inserted where 学生.学号=inserted.学号)=0 begin print '学号不存储在，不能插入该记录' rollback transaction end

9.2.7 触发器的应用 4．禁止更新特定列 【9-23】 在班级表上创建一个update触发器，禁止对班级表中的班级号进行修改。 9.2.7 触发器的应用 4．禁止更新特定列 【9-23】 在班级表上创建一个update触发器，禁止对班级表中的班级号进行修改。 create trigger up_班级 on 班级 after insert as if update(班级号) begin print '课程表的班级号不能修改' rollback transaction end

9.2.7 触发器的应用 5．禁止删除特定行 【9-24】 在选修表上创建一个delete触发器，禁止删除成绩表中的成绩大于60的记录。 9.2.7 触发器的应用 5．禁止删除特定行 【9-24】 在选修表上创建一个delete触发器，禁止删除成绩表中的成绩大于60的记录。 create trigger del_删除 on 选修 for delete as declare @score int select @score = 成绩 from deleted if @score>60 begin rollback transaction raiserror('不允许删除成绩大于60的记录',16,1) end

9.2.7 触发器的应用 【9-25】 在学生表上创建一个update触发器，使用update()函数测试：当更新学生学号时，同时更新选修表中的学生学号。 create trigger up_学生 on 学生 for update as if update(学号) update 选修 set 选修.学号=inserted.学号 --选择inserted表 from 选修,inserted,deleted where 选修.学号=deleted.学号 --选择deleted表

9.2.7 触发器的应用 【9-26】 在选修表上创建一个update触发器，使用update()函数测试：当更新学生成绩时，显示修改过的记录信息。 if exists(select * from sysobjects where name='up_选修' and type ='tr') drop trigger up_选修 go create trigger up_选修 on 选修 for update as if update(成绩) begin select inserted.学号, inserted.课程号, deleted.成绩 as 原成绩, inserted.成绩 as 新成绩 from deleted,inserted where deleted.学号=inserted.学号 and deleted.课程号=inserted.课程号 end

9.2.7 触发器的应用 【9-27】 在选修表上创建一个update触发器，使用columns_updated()函数测试：当更新学生成绩时，显示修改过的记录信息。 分析：由于选修表共有4列，其中从左到有右依次为学号、课程号、成绩和备注列，因此columns_updated()函数测试将返回4位二进制值，且该值的最低位代表学号列，最高位位代表备注列。若返回0100则表示只更新成绩列。 if exists(select * from sysobjects where name='up_选修' and type ='tr') drop trigger up_选修 go create trigger up_选修 on 选修 for update as if columns_updated()&0100=4 begin select inserted.学号,inserted.课程号,deleted.成绩 as 原成绩,inserted.成绩 as 新成绩 from deleted,inserted where deleted.学号=inserted.学号 and deleted.课程号=inserted .课程号 end

本章小结 存储过程是一段在服务器上执行的程序，它在服务器端对数据库记录进行处理，再把结果返回到客户端。存储过程必须由用户、应用程序或者触发器显示调用并执行。而触发器是当特定事件发生的时候，自动执行或者激活的，与连接数据库的用户或应用程序无关。