1、第7章存储过程、触发器及用户自定义函数第第7章存储过程、触发器及章存储过程、触发器及 用户自定义函数用户自定义函数7.1 存储过程存储过程7.2 触发器的概念触发器的概念 7.3 用户自定义函数用户自定义函数 习题习题7第7章存储过程、触发器及用户自定义函数7.1存存 储储 过过 程程7.1.1存储过程概述存储过程概述1.存储过程的定义存储过程的定义SQL Server的存储过程类似于其他编程语言中的函数，是在数据库服务器上创建、运行的程序和过程。这些过程可由应用程序、数据完整性规则或触发器调用，是独立于表之外的数据库对象。存储过程包含一组经常执行的、逻辑完整的SQL语句，它们被保存在数据库中
2、，并由SQL Server服务器通过过程名来调用。第7章存储过程、触发器及用户自定义函数存储过程在首次运行时被编译和优化，相关信息就保存在高速缓存中，下次调用时可以直接执行，从而加快了运行速度。存储过程可用来进行查询操作，类似于SELECT语句，用于检索数据；还可以对数据进行操作，类似于INSERT、UPDATA和DELETE语句，用于插入、更新和删除数据。存储过程可带有输入和输出参数，应用起来比较灵活。第7章存储过程、触发器及用户自定义函数2.存储过程的类型存储过程的类型SQL Serve中存储过程分为五类：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。1)系统存储过
3、程SQL Server中的许多管理活动是通过一种被称为系统存储过程的特殊过程执行的。系统存储过程用于管理SQL Server和显示有关数据库和用户的信息。系统存储过程在master数据库中创建并存储，带有sp_前缀。建议用户不要创建以sp_为前缀的存储过程。第7章存储过程、触发器及用户自定义函数2)本地存储过程本地存储过程是用户在用户数据库中编写的存储过程。3)临时存储过程SQL Server支持两种临时存储过程：本地临时存储过程和全局临时存储过程。其中，在过程名的前面带有#符号的表示是本地临时存储过程；而带有#符号的表示是全局临时存储过程。与临时表类似，SQL Server 关闭后，这些临时
4、存储过程将不存在。4)远程存储过程远程存储过程是SQL Server的早期功能。仅限于在远程SQL Server上执行远程存储过程。第7章存储过程、触发器及用户自定义函数5)扩展存储过程扩展存储过程是SQL Server可以动态装载并执行的动态链接库(DLL)，以前缀“xp_”标识。3.存储过程的优点存储过程的优点存储过程相对于本地SQL语句，具有执行速度快、安全性高、网络流量小、可编程性强等优点。第7章存储过程、触发器及用户自定义函数1)存储过程具有较高的执行性能存储过程存储在数据库中只需创建一次，以后可在程序中调用该存储过程任意次。存储过程是预编译的，首次运行时，查询优化器会对其进行分析、
5、优化，并给出存在系统表中的执行计划，以后执行时无需再次进行预编译，从而提高了执行性能。第7章存储过程、触发器及用户自定义函数2)存储过程能加强数据库的安全性在SQL Server中，可以只允许用户通过存储过程来查询、插入、更新和删除数据库中的表及数据，而不可以直接对表中数据进行操作，从而保障了数据库中数据的安全性。给不同的存储过程强制不同的安全性，以实现基于函数的表的访问。第7章存储过程、触发器及用户自定义函数3)存储过程能够减少网络流量存储过程存放在服务器上，并在服务器上运行，一条执行过程代码的单独语句就可实现调用，在客户端和服务器间传送的只是该调用语句，而不需要在网络中发送所有相关的源代码
6、和中间过程结果，所以其执行时要比直接使用SQL语句快，减少了网络负载。第7章存储过程、触发器及用户自定义函数4)存储过程具有可编程性在SQL Server中，用户可用流程控制语句编写存储过程。在高级语言，如在Visual C+中可同样创建、修改和执行存储过程，这样就在客户端软件和SQL Server间提供了最佳解决方案。第7章存储过程、触发器及用户自定义函数4.系统存储过程和扩展存储过程系统存储过程和扩展存储过程1)系统存储过程SQL Server的系统表中存放了大量关于数据库对象的信息，大部分是数字数据，难以阅读和修改。SQL Server提供了许多系统存储过程，使用户很容易从系统表中查询修
7、改数据。系统存储过程以“sp_”开头，在master数据库中创建，拥有者是系统管理员。运行系统存储过程时，数据库服务器首先在当前数据库中查找，若未找到，再到master数据库中查找。SQL Server提供了九大类约七百多个系统存储过程。下面是几个常用的系统存储过程：第7章存储过程、触发器及用户自定义函数sp_databases：列出数据库服务器上所有可用的数据库，用于检索数据库名。sp_tables：列出当前数据库中可用的表。sp_indexes：返回指定表的索引信息。sp_stored_procedures：列出当前数据库中可用的存储过程。sp_help：用于检索数据库中对象(表、视图、存
8、储过程等)的信息，包括生成日期、列名、外部关键字等。sp_helptext：用于从系统表syscomments中检索生成的数据库对象(表、视图、存储过程等)的SQL命令。第7章存储过程、触发器及用户自定义函数sp_depends：用于显示和存储过程相关的数据库对象。例例7-1 用sp_addtype 建立用户自定义数据类型。EXEC sp_addtype text_type，datetime2)扩展存储过程扩展存储过程用于扩展SQL Server的功能，使用户可以使用其他编程语言中的命令。扩展存储过程以“xp_”开头，是存放在动态链接库(DLL)中的C+代码。下面介绍几个扩展存储过程。第7章存
9、储过程、触发器及用户自定义函数xp_cmdshell：从命令行提示符下执行DOS命令。xp_sscanf：将数据从字符串读入每个格式参数所给定的变量位置。xp_logininfo：报告账户、账户类型、账户的特权级别、账户的映射登录名和账户访问SQL Server的权限路径。例例7-2 使用扩展存储过程xp_cmdshell从命令行提示符下执行DOS命令，列出当前目录下所有文件。执行结果如图7-1所示。EXEC master.xp_cmdshell dir *.*第7章存储过程、触发器及用户自定义函数图7-1 执行扩展存储的过程第7章存储过程、触发器及用户自定义函数7.1.2创建简单存储过程创建
10、简单存储过程1.创建简单存储过程创建简单存储过程在SQL Server中，可以使用三种方法创建存储过程：使用创建存储过程向导、SQL Server企业管理器和T-SQL语句中的CREATE PROCEDURE命令。1)使用创建存储过程向导创建存储过程 在企业管理器中，选择“工具”“向导”“创建存储过程向导”根据提示可完成存储过程的创建。如图7-2所示。第7章存储过程、触发器及用户自定义函数图7-2 使用向导创建存储过程第7章存储过程、触发器及用户自定义函数2)使用SQL Server 企业管理器创建存储过程 在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程
11、的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项。均会弹出创建存储过程对话框。在文本框中可以输入创建存储过程的T_SQL语句。如图7-3所示。第7章存储过程、触发器及用户自定义函数图7-3 通过企业管理器创建存储过程第7章存储过程、触发器及用户自定义函数3)使用CREATE PROCEDURE命令创建存储过程使用CREATE PROCEDORE命令创建存储过程的语法形式如下：CREATE PROCEDURE procedure_name;number parameter data_type
12、 VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement .n 第7章存储过程、触发器及用户自定义函数其中，procedure_name：用于指定要创建的存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时存储过程，可以在 procedure_name 前面加一个编号符(#procedure_name)，要创建全局临时存储过程，可以在 procedure_name 前面加两个编号符(#procedure_na
13、me)。第7章存储过程、触发器及用户自定义函数number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起删除。parameter：存储过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2100 个参数。使用符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象
14、的名称。第7章存储过程、触发器及用户自定义函数data_type：用于指定参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。VARYING：用于指定作为输出OUTPUT参数支持的结果集，仅适用于游标参数。default：用于指定参数的默认值。如果定义了默认值，不必指定该参数的值即可执行该存储过程。默认值必须是常量或 NULL。如果存储过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配
15、符(%、_、和)。第7章存储过程、触发器及用户自定义函数OUTPUT：表明该参数是一个返回参数。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划，每次执行时需重新翻译。ENCRYPTION：表示 SQL Server 加密了 syscomments 表中text字段保存的是CREATE PROCEDURE 语句的文本内容。sql_statement：是存储过程中要包含的SQL 语句。第7章存储过程、触发器及用户自定义函数创建了存储过程后可以执行该存储
16、过程命令如下：EXEC procedure_name下面举例说明存储过程的建立过程。例例7-3 在pubs库中创建存储过程my_proc1，使其返回国家为USA的记录。CREATE PROC my_proc1ASSELECT *from publishersWHERE country=USA在查询分析器中运行存储过程 EXEC my_proc1，结果如图7-4所示。第7章存储过程、触发器及用户自定义函数图7-4 执行存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-4在pubs库中创建存储过程，从四个表的连接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数
17、。具体命令如下：USE pubsGOCREATE PROCEDURE au_info_allASSELECT au_lname,au_fname,title,pub_name第7章存储过程、触发器及用户自定义函数FROM authors a INNER JOIN titleauthor taON a.au_id=ta.au_id INNER JOIN titles tON t.title_id=ta.title_id INNER JOIN publishers pON t.pub_id=p.pub_idGO在查询分析器中运行存储过程au_info_all，其结果如图7-5所示。第7章存储过程、
18、触发器及用户自定义函数图7-5 执行存储过程示意图第7章存储过程、触发器及用户自定义函数创建存储过程前，应该考虑下列几个事项：(1)不能将 CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。(2)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。(3)存储过程是数据库对象，其名称必须遵守标识符规则。(4)只能在当前数据库中创建存储过程。(5)一个存储过程的最大长度为128 MB，存储过程可以嵌套，最多嵌套32层。第7章存储过程、触发器及用户自定义函数2.浏览存储过程的信息浏览存储过程的信息存储过程被创建之后，它的名字就被存储在系统表sysobject
19、s中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。第7章存储过程、触发器及用户自定义函数1)使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库项，选择要查看存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，或双击该存储过程，此时便可以看到存储过程的源代码。如图7-6所示。第7章存储过程、触发器及用户自定义函数图7-6 查看存储过程示意图第7章存储过程、触发器及用户自定义函数2)使用系统存储过程来查看用户创建的存储
20、过程 也可以使用SQL Server提供的系统存储过程来查看用户存储过程，其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型。sp_help objname=name其中，参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码。sp_helptext objname=name其中，参数name为要查看的存储过程的名称。第7章存储过程、触发器及用户自定义函数sp_depends：用于显示和存储过程相关的数据库对象。sp_depends objname=object其中，参数object为要查看依赖关系的存储过程的名称。sp_stored_proce
21、dures：用于返回当前数据库中的存储过程列表。例例7-5 查看pubs 库中所有存储过程。在查询分析器中输入如下命令：EXEC sp_stored_procedures其运行结果如图7-7所示。第7章存储过程、触发器及用户自定义函数图7-7 查看所有存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-6 查看存储过程my_proc1的相关代码信息。在查询分析器中输入如下命令：EXEC sp_helptext my_proc1运行结果如图7-8所示。第7章存储过程、触发器及用户自定义函数图7-8 查看存储过程相关代码示意图第7章存储过程、触发器及用户自定义函数7.1.3创建和执行含参数
22、存储过程创建和执行含参数存储过程存储过程能够带有一些参数，这不仅增加了灵活性，还扩展了存储过程的功能。存储过程的参数分输入参数和输出参数，可以是任意数据类型。1.创建含参数的存储过程创建含参数的存储过程输入参数是向存储过程传递的参数，在创建存储过程时定义，在执行存储过程时需要给出相应的值。输出参数从存储过程中返回的一个或多个值。参数是一个内存局部变量，存放在内存中。第7章存储过程、触发器及用户自定义函数例例7-7创建带简单输入参数的存储过程，返回pubs数据库中authors表指定作者的信息。在查询分析器中输入如下命令：CREATE PROC my_proc2lastname varchar(
23、40),firstname varchar(20)ASSELCEC *from authorsWHERE au_fname=firstname and au_lname=lastname 第7章存储过程、触发器及用户自定义函数例例7-8创建带有通配符参数的简单存储过程，下面的存储过程从四个表的连接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。在查询分析器中输入如下命令：USE pubsGOCREATE PROCEDURE au_info2 lastname varchar(30)=D%,firstname va
24、rchar(18)=%第7章存储过程、触发器及用户自定义函数AS SELECT au_lname,au_fname,title,pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id INNER JOIN titles t ON t.title_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_idWHERE au_fname LIKE firstname AND au_lname LIKE lastnameGO第7章存储过程、触发器及用户自定义函数
25、例例7-9 创建带有输出参数的存储过程，在pubs数据库中的表titles中创建存储过程my_proc3，返回指定书名的书价。在查询分析器中输入如下命令：CREATE PROC my_proc3 title varchar(40)=%,price money output第7章存储过程、触发器及用户自定义函数AS SELECT titleFROM titlesWHERE title=titleSELECT price=priceFROM titlesWHERE title=title2.执行含参数存储过程执行含参数存储过程执行带有输入参数的存储过程时，可以用参数名传送参数值，也可按参数位置传送
26、参数值。第7章存储过程、触发器及用户自定义函数例例7-10 执行存储过程my_proc2，查找lastname=White，irstname=Johnson的作者。在查询分析器中输入如下命令：EXEC my_proc2lastname=White,firstname=Johnson执行结果如图7-9所示。第7章存储过程、触发器及用户自定义函数图7-9 执行存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-11 执行存储过程au_info2，不给出输入参数，在查询分析器中输入如下命令：EXEC au_info2执行结果如图7-10所示。执行带有输出参数的存储过程需要用DECLARE语句
27、声明接收输出值的局部变量，执行时需要在输出参数后面加OUTPUT关键字。第7章存储过程、触发器及用户自定义函数图7-10 执行存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-12 执行存储过程my_proc3，返回书名为Sushi,Anyone的书价。在查询分析器中输入如下命令：declare sum moneyexec my_proc3 Sushi,Anyone?,sum outputprint The book cost is$+RTRIM(CAST(sum as varchar(20)执行结果如图7-11所示。第7章存储过程、触发器及用户自定义函数图7-11 执行存储过程示意
28、图第7章存储过程、触发器及用户自定义函数7.1.4 修改和删除存储过程修改和删除存储过程1.修改存储过程修改存储过程存储过程可以根据用户的要求进行修改。使用ALTER PROCEDURE语句可以更改先前通过执行CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：第7章存储过程、触发器及用户自定义函数ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,
29、ENCRYPTION AS sql_statement ,.n 第7章存储过程、触发器及用户自定义函数其中，procedure_name：要修改的存储过程的名称。parameter：存储过程中的输入、输出参数。data_type：用于指定参数的数据类型。default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。sql_statement：是存储过程中要包含的SQL 语句。第7章存储过程、触发器及用户自定义函数例例7-13 对存储过程my_proc3进行修改，对其指定重编译及加密处理。ALTER PROC my_proc3 title varchar(40)=%,price
30、money outputWITH RECOMPILE ENCRYPTION第7章存储过程、触发器及用户自定义函数AS SELECT title FROM titles WHERE title=title SELECT price=price FROM titles WHERE title=title修改后，在企业管理器中无法查看存储过程my_proc3，原因是进行了加密处理。第7章存储过程、触发器及用户自定义函数2.删除存储过程删除存储过程删除存储过程的方式有两种，使用T-SQL命令和企业管理器。1)使用T-SQL命令删除存储过程可以使用T-SQL的DROP命令删除存储过程。DROP命令可以将
31、一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：DROP PROCEDURE procedure,n第7章存储过程、触发器及用户自定义函数例例7-14 删除存储过程 my_proc3。DROP PROC my_proc32)使用企业管理器删除存储过程在企业管理器中，打开指定的服务器和数据库项，选择要删除的存储过程所属的数据库，单击存储过程文件夹，在右边的窗口中显示该数据库的所有存储过程。用右键单击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项即可。第7章存储过程、触发器及用户自定义函数7.2触发器的概念触发器的概念7.2.1触发器触发器SQL Server中除可以使
32、用约束和规则强制实施数据完整性外，还可以使用触发器实现数据的完整性。触发器(TRIGGER)是由一组SQL语句构成的特殊类型存储过程，是实施数据完整性的强有力工具。在对表执行UPDATE、INSERT、DELETE命令时，SQL Server就会自动触发执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则，这相当于执行了一个存储过程。第7章存储过程、触发器及用户自定义函数但是触发器与一般的存储过程又有不同，存储过程是数据库对象，一般由用户直接调用执行(除自动执行存储过程外)；而触发器依赖于数据表，只能通过操作UPDATE、INSERT、DELETE等命令触发，
33、用户不能直接调用执行。触发器的主要功能是能够实现由主键与外键所不能保证的复杂的参照完整性和数据的一致性。第7章存储过程、触发器及用户自定义函数1.触发器的优点触发器的优点SQL Server对数据库表进行各种操作(插入、修改和删除)之前，首先检查表上设置的规则和默认值，对操作进行预先过滤，避免对表执行违反规则的修改操作。触发器在该操作通过所有的规则和默认值检查后执行，对操作进行事后过滤。如果数据操作违反了触发器定义的条件则对表的操作请求将被拒绝，并返回错误信息。使用触发器具有以下优点：第7章存储过程、触发器及用户自定义函数1)强化约束触发器能够实现比CHECK语句更为复杂的约束。与CHECK约
34、束不同的是，触发器可以引用其他表中的列。例如，在pubs数据库中，对存储在 discounts 表中的折扣作修改时，限制对价格低于10美元的书(存储在titles表中)的折扣进行修改，由触发器可以完成该约束。2)跟踪执行触发器可以侦测数据库中内在的操作，从而不允许未经许可的插入、更新和删除操作影响数据库。第7章存储过程、触发器及用户自定义函数3)级联运行触发器可以侦测数据库内的操作，并自动地级联影响整个数据库中的各项内容。例如，在pubs数据库中，可以在 titles 表的 title_id 列上写入一个删除触发器，使其他表中的各相应行采取删除操作。该触发器用 title_id 列作为唯一键，
35、在 titleauthor、sales 及 roysched 表中对各匹配行进行定位删除。第7章存储过程、触发器及用户自定义函数4)存储过程的调用为了响应数据库更新，触发器可以调用一个或多个存储过程。因此，触发器可以解决高级形式的业务规则或复杂的行为限制以及实现定制记录等方面的问题。触发器能够根据某表数据修改前后状态所发生的差异，执行相应的处理。同时一个表的同一类型(INSERT、UPDATE、DELETE)的多个触发器能够对同一种数据操作采取多种不同的处理。第7章存储过程、触发器及用户自定义函数2.创建触发器创建触发器创建触发器的方法有多种，可以通过企业管理器或在查询分析器中用T-SQL语句
36、创建。下面分别介绍。1)使用企业管理器创建触发器在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择“所有任务”子菜单下的“管理触发器选项”，则会出现触发器属性对话框，如图7-12所示。第7章存储过程、触发器及用户自定义函数图7-12 创建触发器示意图第7章存储过程、触发器及用户自定义函数在该对话框的名称文本框中选择“新建”，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。第7
37、章存储过程、触发器及用户自定义函数2)使用CREATE TRIGGER 命令创建触发器在查询分析器中使用T-SQL语句创建触发器，其语法形式如下：CREATE TRIGGER trigger_name ONtable|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT ,UPDATE NOT FOR REPLICATION AS第7章存储过程、触发器及用户自定义函数IF UPDATE(column)AND|ORUPDATE(column).n sql_statement.n 其中，trigger_name：触发器名，必须符合SQL S
38、erver命名规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。第7章存储过程、触发器及用户自定义函数table|view：执行触发器的表或视图，有时称为触发器表或触发器视图。WITH ENCRYPTION：对存储在syscommnents表中的创建触发器的正文文本项进行加密。AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。第7章存储过程、触发器及用户自定义函数INSTEAD OF：在表或视图中执行插入、
39、修改或删除操作时，用触发器中的SQL语句代替触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。INSTEAD OF触发器不允许更新带WITH CHECK OPTION选项的视图。第7章存储过程、触发器及用户自定义函数DELETE、INSERT、UPDATE：用于指定激活触发器的操作，DELETE、INSERT、UPDATE分别表示删除数据行、插入数据行、更新数据行时激活触发器。建立触发器时，必须最少指定其中的一项，若选择多项组合，使用逗号分隔。若表中的某列是外关键字，则不允许删除该数据行或修改外关键字列，此时可使用INST
40、EAD OF 触发器。第7章存储过程、触发器及用户自定义函数NOT FOR REPLICATION：在数据库复制过程中对表的修改将不激活触发器。IF UPDATE：指定表中哪些列做插入和更新操作时才激活触发器，此句对删除操作不起作用。sql_statement：是触发器中包含的SQL语句，即触发器被触发后，要执行的数据库操作。触发器旨在根据数据修改语句检查或更改数据，它不应将数据返回给用户。触发器中的T-SQL语句常常包含控制流语言。第7章存储过程、触发器及用户自定义函数7.2.2 INSERT触发器触发器INSERT触发器在每次用户向基表插入数据行时触发，可用于检验要插入的数据行是否符合要求
41、，也可以级联改变数据库中其他的数据。例例7-15 在pubs数据库中的表titles上创建触发器my_trigger1时，当用户在titles表中插入数据后，向客户端显示一条消息。在查询分析器中输入如下命令：第7章存储过程、触发器及用户自定义函数USE pubsGOCREATE TRIGGER my_trigger1ON titlesFOR INSERT AS RAISERROR(在表 titles 中插入了一行数据,16,10)GO 第7章存储过程、触发器及用户自定义函数当用户向titles表中插入数据时，将激活触发器，数据仍能插入到表中。在表中插入一行数据，在查询分析器中输入如下命令：in
42、sert into titlesvalues(TC889,SQL Server2000,TC_book,NULL,20,4000,15,3322,NULL,1995-4-1)运行结果如图7-13所示。查看该表，发现数据行被插入到表中，如图7-14所示。第7章存储过程、触发器及用户自定义函数图7-13 插入数据时触发器被执行示意图第7章存储过程、触发器及用户自定义函数图7-14 插入数据示意图第7章存储过程、触发器及用户自定义函数例例7-16 在pubs数据库中的表titles上创建触发器my_trigger2，当用户在 titles 表中插入数据前，向客户端显示一条消息。在查询分析器中输入如下
43、命令：CREATE TRIGGER my_trigger2ON titlesINSTEAD OF INSERT AS RAISERROR(你无权在表 titles 中插入数据,16,10)查看该触发器的执行，在查询分析器中执行插入数据时，返回的结果如图7-15所示。第7章存储过程、触发器及用户自定义函数图7-15 INSTEAD OF触发器的执行结果第7章存储过程、触发器及用户自定义函数7.2.3 DELETE触发器触发器DELETE触发器在用户删除表中数据行时触发，可用于防止删除数据库中某些数据行或级联删除数据库中其他表中的数据行。通常用户执行DELETE语句后，SQL Server从表中删
44、除有关的数据行，增加DELETE触发器后，SQL Server将删除的数据行保存到DELETED表中。DELETED和INSERTED是逻辑(概念)表。在结构上类似于定义触发器的表(也就是在其中尝试用户操作的表)；这些表用于保存用户操作可能更改的行的旧值或新值。第7章存储过程、触发器及用户自定义函数例例7-17 在pubs数据库中的表employee上创建触发器my_del_trigger1，当用户在employee表中删除数据时，取消该删除操作。在查询分析器中输入如下命令：USE pubsGOCREATE TRIGGER my_del_trigger1ON employee 第7章存储过程、
45、触发器及用户自定义函数FOR DELETE ASBEGIN RAISERROR(你无权删除数据,16,10)ROLLBACK TRANSACTIONENDROLLBACK TRANSACTION语句表示事务回滚，即取消激活该触发器的操作。第7章存储过程、触发器及用户自定义函数测试该触发器，在查询分析器中执行删除操作时，触发器被触发。在查询分析器中输入如下命令：delete from employeewhere pub_id=0877运行结果如图7-16所示。第7章存储过程、触发器及用户自定义函数图7-16 激活触发器第7章存储过程、触发器及用户自定义函数7.2.4UPDATE触发器触发器UPD
46、ATE触发器在用户更新表中数据行时触发，它包括了DELETE触发器和INSERT触发器的功能，用户执行UPDATE触发器后，原数据从基础表中删除，保存在DELETED表中，同时基础表插入更新后的数据行在INSERTED表中存放一备份。使用UPDATE触发器时，用户可以通过定义IF UPDATE来实现当特定列被更新时激活触发器。第7章存储过程、触发器及用户自定义函数例例7-18 在数据库pubs的publishers表中设置UPDATE触发器my_update_trigger1，当更新pub_name字段时，禁止更新，使用事务回滚来撤销该操作。在查询分析器中输入如下命令：USE pubsGOCR
47、EATE TRIGGER my_update_trigger1ON publishersFOR UPDATE 第7章存储过程、触发器及用户自定义函数ASIF UPDATE(pub_name)BEGIN RAISERROR(不能修改 pub_name,16,10)ROLLBACK TRANSACTIONEND第7章存储过程、触发器及用户自定义函数测试该触发器，在查询分析器中执行更新pub_name操作时，触发器被触发。在查询分析器中输入如下命令：UPDATE publishersSET pub_name=STARWHERE pub_id=0736运行结果如图7-17所示。第7章存储过程、触发器及
48、用户自定义函数图7-17 激活触发器示意图第7章存储过程、触发器及用户自定义函数测试该触发器，更新state列时，操作可完成。在查询分析器中输入如下命令：UPDATE publishersSET state=SCWHERE pub_id=0736运行结果如图7-18所示。第7章存储过程、触发器及用户自定义函数图7-18 未激活触发器示意图第7章存储过程、触发器及用户自定义函数在查询分析器中输入如下命令：USE pubsGOCREATE TRIGGER employee_insupdON employeeFOR INSERT,UPDATEAS/*Get the range of level fo
49、r this job type from the jobs table.*/DECLARE min_lvl tinyint,第7章存储过程、触发器及用户自定义函数 max_lvl tinyint,emp_lvl tinyint,job_id smallintSELECT min_lvl=min_lvl,max_lvl=max_lvl,emp_lvl=i.job_lvl,job_id=i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id=i.emp_id 第7章存储过程、触发器及用户自定义函数 JOIN jobs j ON j.job
50、_id=i.job_idIF(job_id=1)and(emp_lvl 10)BEGIN RAISERROR(Job id 1 expects the default level of 10.,16,1)ROLLBACK TRANSACTIONENDELSEIF NOT(emp_lvl BETWEEN min_lvl AND max_lvl)第7章存储过程、触发器及用户自定义函数BEGIN RAISERROR(The level for job_id:%d should be between%d and%d.,16,1,job_id,min_lvl,max_lvl)ROLLBACK TRANS
