《第十章 存储过程和触发器(清华大学)》由会员分享，可在线阅读，更多相关《第十章 存储过程和触发器(清华大学)（51页珍藏版）》请在金锄头文库上搜索。
1、SQL ServerSQL Server实用教程实用教程第第1010章章 存储过程和触发器存储过程和触发器q10.1 存储过程的概念存储过程的概念q10.2 建立和执行存储过程建立和执行存储过程q10.3 存储过程的管理与维护存储过程的管理与维护q10.4 触发器的概念触发器的概念q10.5 创建和应用创建和应用DML触发器触发器q10.6 触发器的管理与维护触发器的管理与维护q10.7 DDL触发器触发器q10.8 事务事务7/29/20241SQL ServerSQL Server实用教程实用教程第第1010章章 存储过程和触发器存储过程和触发器q教学要求：通过本章学习，读者应掌握以下内容
2、：教学要求：通过本章学习，读者应掌握以下内容：v存储过程和触发器的作用存储过程和触发器的作用v存储过程的创建、修改和删除存储过程的创建、修改和删除v存储过程的灵活运用存储过程的灵活运用v触发器的创建、修改和删除触发器的创建、修改和删除7/29/20242SQL ServerSQL Server实用教程实用教程10.1 10.1 存储过程的概念存储过程的概念q1011 基本概念基本概念q存储过程是一组编译在单个执行计划中的存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给语句，将一些固定的操作集中起来交给SQL Server数据数据库服务器完成，以实现
3、某个任务。库服务器完成，以实现某个任务。 q1012 存储过程的优点存储过程的优点（1）与其他应用程序共享应用程序逻辑，因而确保了数据）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。访问和修改的一致性。（2）防止数据库中表的细节暴露给用户。）防止数据库中表的细节暴露给用户。（3）提供安全机制。）提供安全机制。（4）改进性能。）改进性能。（5）减少网络流量。）减少网络流量。7/29/20243SQL ServerSQL Server实用教程实用教程q1013 存储过程的类型存储过程的类型（1）系统存储过程。）系统存储过程。（2）本地存储过程。）本地存储过程。（3）临时存储过程
4、。）临时存储过程。（4）远程存储过程。）远程存储过程。（5）扩展存储过程。）扩展存储过程。 7/29/20244SQL ServerSQL Server实用教程实用教程10.2 10.2 建立和执行存储过程建立和执行存储过程q简单存储过程类似于将一组简单存储过程类似于将一组SQL语句起个名字，然后就可语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。以在需要时反复调用。复杂一些的则要有输入和输出参数。q1021 创建和执行简单存储过程创建和执行简单存储过程创建存储过程的基本语法如下：创建存储过程的基本语法如下：CREATE PROCEDURE 存储过程名存储过程名WIT
5、H ENCRYPTIONWITH RECOMPILEASSQL 语句语句q其中各参数如下：其中各参数如下：WITH ENCRYPTION：对存储过程进行加密。：对存储过程进行加密。WITH RECOMPILE：对存储过程重新编译。：对存储过程重新编译。7/29/20245SQL ServerSQL Server实用教程实用教程q【例例10.1】从从xsgl数据库的三个表中查询，返回学生学数据库的三个表中查询，返回学生学号、姓名、课程名、成绩。该存储过程实际上只返回一个号、姓名、课程名、成绩。该存储过程实际上只返回一个查询信息。查询信息。q-建立存储过程建立存储过程USE xsglGOCREAT
6、E PROCEDURE stu_cjASSELECT xs.学号学号,姓名姓名,课程名课程名,成绩成绩 FROM xs INNER JOIN cj ON xs.学号学号=cj.学号学号 INNER JOIN kcON cj.课程号课程号=kc.课程号课程号GO7/29/20246SQL ServerSQL Server实用教程实用教程q1022 存储过程的执行存储过程的执行q执行存储过程的基本语法如下：执行存储过程的基本语法如下：qEXECUTE 存储过程名存储过程名q同时同时EXECUTE命令除了可以执行存储过程外还可以执行命令除了可以执行存储过程外还可以执行存放存放Transact-SQL
7、语句的字符串变量，或直接执行语句的字符串变量，或直接执行Transact-SQL语句字符串。此时语句字符串。此时EXECUTE语句的语法语句的语法格式如下。格式如下。qEXECUTE(字符串变量字符串变量| NSQL语句字符串语句字符串+.nq其中其中“字符串变量字符串变量”是局部字符串变量名，最大值为服是局部字符串变量名，最大值为服务器的可用内存。务器的可用内存。N SQL语句字符串语句字符串的语句字符串是的语句字符串是一个由一个由SQL语句构成的字符串常量。如果包含语句构成的字符串常量。如果包含N，则该字，则该字符串将解释为符串将解释为nvarchar数据类型。数据类型。7/29/2024
8、7SQL ServerSQL Server实用教程实用教程q【例例10.2】建立一个批处理，查询相应表中的信建立一个批处理，查询相应表中的信息。息。DECLARE tab_name varchar(20)SET tab_name=xsEXECUTE(SELECT * FROM +tab_name)GO7/29/20248SQL ServerSQL Server实用教程实用教程q1023 带输入参数的存储过程带输入参数的存储过程q1建立存储过程建立存储过程q一个存储过程可以带一个或多个参数，输入参数是一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建指由调用
9、程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相存储过程语句中被定义，在执行存储过程中给出相应的参数值。应的参数值。q声明带输入参数的存储过程的语法格式如下：声明带输入参数的存储过程的语法格式如下：qCREATE PROCEDURE 存储过程名存储过程名q参数名参数名 数据类型数据类型=默认值默认值 ，nqWITH ENCRYPTIONqWITH RECOMPILEqASqSQL 语句语句7/29/20249SQL ServerSQL Server实用教程实用教程q其中其中“参数名参数名”和定义局部变量一样，必须以符号和定义局部变量一样，必须以符号为前为前缀，要
10、指定数据类型，多个参数定义要用缀，要指定数据类型，多个参数定义要用“，”隔开。在隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则使用时须定义默认值（默行时未提供该参数的参数值，则使用时须定义默认值（默认值可以是常量或空值认值可以是常量或空值null），否则将产生错误。），否则将产生错误。q【例例10.3】从从xsgl数据库的三个表中查询某人指定课程数据库的三个表中查询某人指定课程的成绩。的成绩。USE xsglGOIF EXISTS (SELECT name FROM SYSOBJECTSWHERE nam
11、e=stu_cj1 AND type=P) DROP PROCEDURE stu_cj1GO7/29/202410SQL ServerSQL Server实用教程实用教程CREATE PROCEDURE stu_cj1 name char(10),cname char(16)ASSELECT xs.学号学号,姓名姓名,课程名课程名,成绩成绩 FROM xs INNER JOIN cj ON xs.学号学号=cj.学号学号INNER JOIN kcON cj.课程号课程号=kc.课程号课程号 WHERE xs.姓名姓名=name AND kc.课程课程=cnameGO7/29/202411SQL
12、 ServerSQL Server实用教程实用教程q2执行存储过程执行存储过程q在执行存储过程的语句中，有两种方式传递参数值，分别是在执行存储过程的语句中，有两种方式传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。使用参数名传递参数值和按参数位置传递参数值。q使用参数名传递参数值，是通过语句使用参数名传递参数值，是通过语句“参数名参数名=参数值参数值”给给参数传递值。当存储过程含有多个输入参数时，对数值可以参数传递值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值，其语法
13、格式为：以不给参数值，其语法格式为：qEXECUTE 存储过程名存储过程名 参数名参数名=参数值参数值 ，nq按参数位置传递参数值，不显式地给出按参数位置传递参数值，不显式地给出“参数名参数名”，而是按，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时使用关键字数的指定顺序。必要时使用关键字“DEFAULT”作为参数值的作为参数值的占位。占位。7/29/202412SQL ServerSQL Server实用教
14、程实用教程q【例例10.4】从三个表的连接中返回指定学生的学号、姓名、从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设置的默认值。式匹配，如果没有提供参数，则使用预设置的默认值。qUSE xsglqGOqCRAETE PROCEDURE stu_cj2q name char(10)=刘刘%qASqSELECT xs.学号学号,姓名姓名,课程名课程名,成绩成绩qFROM xs INNER JOIN cj qON xs.学号学号=cj.学号学号 INNER JO
15、IN kcqON cj.课程号课程号=kc.课程号课程号q WHERE 姓名姓名 LIKE nameqGO7/29/202413SQL ServerSQL Server实用教程实用教程q上面存储过程有多种执行形式，下面列了一部分：上面存储过程有多种执行形式，下面列了一部分：EXECUTE stu_cj2 /*参数使用默认值参数使用默认值*/q或或EXECUTE stu_cj2 王王%7/29/202414SQL ServerSQL Server实用教程实用教程10102 24 4 带输出参数的存储过程带输出参数的存储过程q如果我们需要从存储过程中返回一个或多个值，可如果我们需要从存储过程中返回
16、一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在创建存储过程的命现，为了使用输出参数，需要在创建存储过程的命令中使用令中使用OUTPUT关键字。关键字。q声明带输出参数的存储过程的语法格式如下：声明带输出参数的存储过程的语法格式如下：CREATE PROCEDURE 存储过程名存储过程名参数名参数名 数据类型数据类型VARYING=默认值默认值 OUTPUT ，nWITH ENCRYPTIONWITH RECOMPILEASSQL 语句语句7/29/202415SQL ServerSQL Server实用教程实
17、用教程q【例例10.5】创建一个存储过程用于计算指定学生各科成创建一个存储过程用于计算指定学生各科成绩的总分，存储过程中使用了一个输入参数和一个输出参绩的总分，存储过程中使用了一个输入参数和一个输出参数。数。USE xsglGOCREATE PROCEDURE stu_sum name char(10),total int OUTPUTASSELECT total=SUM(成绩成绩) FROM xs,cj WHERE 姓名姓名=name AND xs.学号学号=cj.学号学号 GROUP BY xs.学号学号GO7/29/202416SQL ServerSQL Server实用教程实用教程q注
18、意：注意：OUTPUT变量必须在定义存储过程和使用该变量必须在定义存储过程和使用该变量时都进行定义。定义时的参数名和调用时的变变量时都进行定义。定义时的参数名和调用时的变量名不一定相同，不过数据类型和参数的位置必须量名不一定相同，不过数据类型和参数的位置必须匹配。匹配。q例例10.5中存储过程的执行方法为：中存储过程的执行方法为：USE xsglDECLARE total intEXECUTE stu_sum 程明程明, total OUTPUTSELECT 程明程明,totalGOq游标可以作为输出参数，返回存储过程中产生的结游标可以作为输出参数，返回存储过程中产生的结果集，但是不能作为输入
19、参数。用关键字果集，但是不能作为输入参数。用关键字VARYING指定参数是结果集，专门用于游标作为指定参数是结果集，专门用于游标作为输出参数。输出参数。7/29/202417SQL ServerSQL Server实用教程实用教程q【例例10.6】使用存储过程在使用存储过程在xsgl数据库的数据库的xs表上声明并打开表上声明并打开一个游标。一个游标。qUSE xsglqGOqCREATE PROCEDURE stu_cursorq stu_cursor CURSOR VARYING OUTPUTqASqSET stu_cursor=CURSOR FORWARD_ONLY STATIC FORq
20、SELECT *q FROM xsqOPEN stu_cursorqGO7/29/202418SQL ServerSQL Server实用教程实用教程USE xsglGODECLARE MyCursor CURSOREXECUTE stu_cursor stu_cursor=MyCursor OUTPUTWHILE(FETCH_STATUS=0) BEGIN FETCH NEXT FROM MyCursor ENDCLOSE MyCursorDEALLOCATE MyCursorGO7/29/202419SQL ServerSQL Server实用教程实用教程10.3 10.3 存储过程的管理
21、与维护存储过程的管理与维护q1031 查看存储过程的定义信息查看存储过程的定义信息q在在SQL Server Management Studio的的“对象对象资源管理器资源管理器”中，可以在要查看信息的存储过程中，可以在要查看信息的存储过程上单击鼠标右键，在快捷菜单中选择上单击鼠标右键，在快捷菜单中选择“属性属性”，弹出弹出“存储过程属性存储过程属性”窗口窗口.q也可以通过系统存储过程也可以通过系统存储过程sp_helptext查看存储查看存储过程的定义；通过过程的定义；通过sp_help查看存储过程的参数；查看存储过程的参数；通过通过sp_depends查看存储过程的相关性。查看存储过程的相
22、关性。7/29/202420SQL ServerSQL Server实用教程实用教程q【例例10.7】在在SQL Server Management Studio服务器中新建查询，使用系统存储过程，服务器中新建查询，使用系统存储过程，查看例查看例10.6中所创建存储过程的定义、参数和相中所创建存储过程的定义、参数和相关性。关性。q运行如下运行如下SQL语句。语句。qEXECUTE sp_helptext stu_cursorqEXECUTE sp_help stu_cursorqEXECUTE sp_depends stu_cursorq运行后得到存储过程的定义、参数和依赖信息。运行后得到存储
23、过程的定义、参数和依赖信息。7/29/202421SQL ServerSQL Server实用教程实用教程10103 32 2 存储过程的重编译存储过程的重编译q存储过程所采用的执行计划，只在编译时优化生存储过程所采用的执行计划，只在编译时优化生成，以后便驻留在高速缓存中。当用户对数据库成，以后便驻留在高速缓存中。当用户对数据库新增了索引或其他影响数据库逻辑结构的更改后，新增了索引或其他影响数据库逻辑结构的更改后，已编译的存储过程执行计划可能会失去效率。通已编译的存储过程执行计划可能会失去效率。通过对存储过程进行重新编译，可以重新优化存储过对存储过程进行重新编译，可以重新优化存储过程的执行计划
24、。过程的执行计划。qSQL Server为用户提供了为用户提供了3种重新编译的方法。种重新编译的方法。7/29/202422SQL ServerSQL Server实用教程实用教程q1在创建存储过程时设定在创建存储过程时设定q在创建存储过程时，使用在创建存储过程时，使用WITH RECOMPILE子句时子句时SQL Server不将该存储过程的查询计划保存在缓存中，而是在每不将该存储过程的查询计划保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。次运行时重新编译和优化，并创建新的执行计划。q2在执行存储过程时设定在执行存储过程时设定q通过在执行存储过程时设定重新编译，可以让通过
25、在执行存储过程时设定重新编译，可以让SQL Server在在执行存储过程时重新编译该存储过程，这一次执行完成后，执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被保存在缓存中。这样用户就可以根据需要新的执行计划又被保存在缓存中。这样用户就可以根据需要进行重新编译。进行重新编译。qEXECUTE stu_cj1 WITH RECOMPILEq3通过系统存储过程设定重编译通过系统存储过程设定重编译q通过系统存储过程通过系统存储过程sp_recompile设定重新编译标记，使存设定重新编译标记，使存储过程在下次运行时重新编译。储过程在下次运行时重新编译。q其语法格式如下：其语法格式
26、如下：qEXECUTE sp_recompile 数据库对象数据库对象7/29/202423SQL ServerSQL Server实用教程实用教程10103 33 3 修改和删除存储过程修改和删除存储过程q1修改存储过程修改存储过程q存储过程的修改是由存储过程的修改是由ALTER语句来完成的，基本语句来完成的，基本语法如下：语法如下：ALTER PROCEDURE 存储过程名存储过程名WITH ENCRYPTIONWITH RECOMPILEASSQL语句语句7/29/202424SQL ServerSQL Server实用教程实用教程q2删除存储过程删除存储过程q存储过程的删除是通过存储过
27、程的删除是通过DROP语句来实现的，在语句来实现的，在SQL Server Management Studio的的“对象资对象资源管理器源管理器”中也同样可以进行删除。命令方式删中也同样可以进行删除。命令方式删除存储过程的方法也很简单，例如，使用除存储过程的方法也很简单，例如，使用DROP PROCEDURE stu_sum命令，即可删除该存储命令，即可删除该存储过程。过程。7/29/202425SQL ServerSQL Server实用教程实用教程10.4 10.4 触发器的概念触发器的概念q触发器是一类特殊的存储过程，它是在执行某些特定的触发器是一类特殊的存储过程，它是在执行某些特定的T
28、-SQL语句时可以自动执行的一种存储过程。语句时可以自动执行的一种存储过程。q1041 基本概念基本概念q1042 触发器的功能触发器的功能 SQL Server2005提供了两种方法来保证数据的有效性提供了两种方法来保证数据的有效性和完整性：约束和触发器。和完整性：约束和触发器。q触发器的常用功能如下。触发器的常用功能如下。（1）完成更复杂的数据约束：触发器可以实现比约束更为）完成更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束。复杂的数据约束。（2）检查）检查SQL所做的操作是否允许：触发器可以检查所做的操作是否允许：触发器可以检查SQL所做的操作是否被允许。所做的操作是否被允许。
29、 7/29/202426SQL ServerSQL Server实用教程实用教程（3）修改其他数据表里的数据：当一个）修改其他数据表里的数据：当一个SQL语句对数据表语句对数据表进行操作的时候，触发器可以根据进行操作的时候，触发器可以根据SQL语句的操作情况来语句的操作情况来对另一个数据表进行操作。对另一个数据表进行操作。（4）调用更多的存储过程：约束是不能调用存储过程的，）调用更多的存储过程：约束是不能调用存储过程的，但触发器本身就是一种存储过程，而存储过程是可以嵌套但触发器本身就是一种存储过程，而存储过程是可以嵌套调用的，所以触发器也可能调用一个或多个存储过程。调用的，所以触发器也可能调用
30、一个或多个存储过程。（5）返回自定义的错误信息：约束只能通过标准的系统错）返回自定义的错误信息：约束只能通过标准的系统错误信息来传递错误信息，如果应用程序要求使用自定义信误信息来传递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。息和较为复杂的错误处理，则必须使用触发器。（6）更改原本要操作的）更改原本要操作的SQL语句：触发器可以修改原本要语句：触发器可以修改原本要操作的操作的SQL语句。语句。（7）防止数据表结构更改或数据表被删除：为了保护已经）防止数据表结构更改或数据表被删除：为了保护已经建立好的数据表，触发器可以在接收到以建立好的数据表，触发器可以在接收
31、到以DROP或或ALTER开头的语句后，不对数据表的结构做任何操作。开头的语句后，不对数据表的结构做任何操作。7/29/202427SQL ServerSQL Server实用教程实用教程10104 43 3 触发器的类型触发器的类型q1. DML触发器触发器 DML触发器是当数据库服务器中发生数据操纵语触发器是当数据库服务器中发生数据操纵语言言DML事件时执行的特殊存储过程，如事件时执行的特殊存储过程，如INSERT、UPDATE等。等。q2. DDL触发器触发器 DDL触发器是当数据库服务器中发生数据定义语触发器是当数据库服务器中发生数据定义语言言DDL事件时执行的特殊存储过程，如事件时执
32、行的特殊存储过程，如CREATE、ALTER等。等。7/29/202428SQL ServerSQL Server实用教程实用教程10.5 10.5 创建和应用创建和应用DMLDML触发器触发器q1051 DML触发器的类型触发器的类型q1AFTER触发器触发器q这类触发器是在记录已经改变之后，才会被激活这类触发器是在记录已经改变之后，才会被激活执行，它主要是用于记录变更后的处理或检查，执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用一旦发现错误，也可以用ROLLBACK TRANSACTION语句来回滚本次的操作。语句来回滚本次的操作。q2INSTEAD OF触发器触发器q这
33、类触发器一般是用来取代原本要进行的操作，这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来的在记录变更之前发生的，它并不去执行原来的SQL语句里的操作，而是去执行触发器本身所定语句里的操作，而是去执行触发器本身所定义的操作。义的操作。7/29/202429SQL ServerSQL Server实用教程实用教程q1052 DML触发器的工作原理触发器的工作原理q在在SQL Server2005中，为每个中，为每个DML触发器定义了触发器定义了两个特殊的表，一个是两个特殊的表，一个是Inserted表，一个是表，一个是Deleted表。表。qInserted表里存放
34、的是更新前的记录：对于插入记表里存放的是更新前的记录：对于插入记录操作来说，录操作来说，Inserted表里存储的是要插入的数据；表里存储的是要插入的数据；对于更新记录的操作来说，对于更新记录的操作来说，Inserted表里存放的是表里存放的是要更新的记录。要更新的记录。qDeleted表里存放的是更新后的记录：对于更新记表里存放的是更新后的记录：对于更新记录操作来说，录操作来说，Deleted表里存放的是更新前的记录；表里存放的是更新前的记录；对于删除记录操作来说，对于删除记录操作来说，Deleted表里存储的是被表里存储的是被删除的旧记录。删除的旧记录。7/29/202430SQL Ser
35、verSQL Server实用教程实用教程10105 53 3 创建创建DMLDML触发器的注意事项触发器的注意事项q（1）CREATE TRIGGER语句必须是批处理中的语句必须是批处理中的第一个语句，该语句后面的所有语句都被解释为第一个语句，该语句后面的所有语句都被解释为CREATE TRIGGER语句定义的一部分。语句定义的一部分。q（2）创建）创建DML触发器的权限默认分配给表的所触发器的权限默认分配给表的所有者，且不能将该权限转授给其他用户。有者，且不能将该权限转授给其他用户。q（3）DML可以引用当前数据库以外的对象，但可以引用当前数据库以外的对象，但只能在当前数据库中创建只能在当
36、前数据库中创建DML触发器。触发器。q（4）不能对系统表或临时表创建）不能对系统表或临时表创建DML触发器触发器q（5）对于含有）对于含有DELETE或或UPDATE操作定义的外操作定义的外键表，不能定义键表，不能定义INSTEAD OF触发器。触发器。7/29/202431SQL ServerSQL Server实用教程实用教程10105 54 4 创建触发器创建触发器CREATE TRIGGER 触发器名触发器名ON 表表|视图视图WITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETENOT FOR REPLICATIONASIF
37、UPDATE(列名列名)AND|OR UPDATE(列名列名)nSQL语句语句7/29/202432SQL ServerSQL Server实用教程实用教程q（1）WITH ENCRYPTION。加密。加密CREATE TRIGGER语语句文本的条目。句文本的条目。q（2）FOR|AFTER。FOR与与AFTER同义，指定触发器只有同义，指定触发器只有在触发器在触发器SQL语句中指定的所有操作都已成功后才激发。所语句中指定的所有操作都已成功后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。此触发器，即为后触
38、发。q（3）INSTEAD OF。指定执行触发器而不执行造成触发的。指定执行触发器而不执行造成触发的SQL语句，从而替代造成触发的语句。在表或视图上，每个语句，从而替代造成触发的语句。在表或视图上，每个INSERT、UPDATE或或DELETE语句只能定义一个语句只能定义一个INSTEAD OF触发器，即替代触发。触发器，即替代触发。q（4）INSERT,UPDATE,DELETE。是指定在表上执行。是指定在表上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用任意顺序组合的这些关一个选项。在触发器定义中
39、允许使用任意顺序组合的这些关键字。当进行触发条件的操作时（键字。当进行触发条件的操作时（INSERT、UPDATE或或DELETE），将执行），将执行SQL语句中指定的触发器操作。语句中指定的触发器操作。7/29/202433SQL ServerSQL Server实用教程实用教程q（5）NOT FOR REPLICATION。表示当复制进。表示当复制进程更改触发器所涉及的程更改触发器所涉及的q表时，不要执行该触发器。表时，不要执行该触发器。q（6）IF UPDATE(列名列名)。测试在指定的列上进。测试在指定的列上进行的行的INSERT或或UPDATE操作，不能用于操作，不能用于DELETE
40、操作，可以指定多列。因为已经在操作，可以指定多列。因为已经在ON子子句中指定了表名，所以在句中指定了表名，所以在IF UPDATE子句中的列子句中的列名前不要包含表名。若要测试在多个列上进行的名前不要包含表名。若要测试在多个列上进行的INSERT或或UPDATE操作，要分别单独地指定操作，要分别单独地指定UPDATE(列名列名)子句。在子句。在INSERT操作中操作中IF UPDATE将返回将返回TRUE值。值。7/29/202434SQL ServerSQL Server实用教程实用教程q1INSERT触发器触发器【例例10.11】在数据库在数据库xsgl中创建一触发器，当向中创建一触发器，
41、当向cj表插入一记表插入一记录时，检查该记录的学号在录时，检查该记录的学号在xs表中是否存在，检查课程号在表中是否存在，检查课程号在kc表中是否存在，若有一项为否，则不允许插入。表中是否存在，若有一项为否，则不允许插入。CREATE TRIGGER check_trigON cjFOR INSERTASIF EXISTS(SELECT * FROM INSERTED a WHERE a.学号学号 NOT IN (SELECT b.学号学号 FROM xs b) OR a.课程号课程号 NOT IN(SELECT c.课程号课程号 FROM kc c)BEGIN RAISERROR(违背数据的一
42、致性违背数据的一致性,16,1) ROLLBACK TRANSACTIONENDGO7/29/202435SQL ServerSQL Server实用教程实用教程q2UPDATE触发器触发器【例例10.12】在在xsgl数据库的数据库的cj表上创建一触发器，若对学号表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。列和课程号列修改，则给出提示信息，并取消修改操作。CREATE TRIGGER update_trig ON cj FOR updateASIF UPDATE(学号学号) OR UPDATE(课程号课程号)BEGIN RAISERROR(学号或课程号不能进行
43、修改学号或课程号不能进行修改!,7,2) ROLLBACK TRANSACTIONENDGO7/29/202436SQL ServerSQL Server实用教程实用教程q3DELETE触发器触发器q【例例10.13】当从当从xs表中删除一个学生的记录时，表中删除一个学生的记录时，相应的应从相应的应从cj表中删除该学生对应的所有记录。表中删除该学生对应的所有记录。CREATE TRIGGER delete_trigON xsAFTER DELETEASDELETE FROM cj WHERE 学号学号=(SELECT 学号学号 FROM DELETED)GO7/29/202437SQL Ser
44、verSQL Server实用教程实用教程q4INSTEAD OF触发器触发器q如果视图的数据来自于多个基表，则必须使用如果视图的数据来自于多个基表，则必须使用INSTAED OF触发器支持引用表中的数据的插入、触发器支持引用表中的数据的插入、更新和删除操作。更新和删除操作。q如果视图的列为以下几种情况之一：如果视图的列为以下几种情况之一：q基表中的计算列基表中的计算列q基表中的标识列基表中的标识列q具有具有timestamp数据类型的基表列数据类型的基表列q该视图的该视图的INSERT语句必须为这些列指定值，语句必须为这些列指定值，INSTEAD OF触发器在构成将值插入基表的触发器在构成将
45、值插入基表的INSERT语句时，会忽略指定的值。下面通过一语句时，会忽略指定的值。下面通过一个例子说明。个例子说明。7/29/202438SQL ServerSQL Server实用教程实用教程10.6 10.6 触发器的管理与维护触发器的管理与维护q1061 查看触发器的定义信息查看触发器的定义信息q1062 修改和删除触发器修改和删除触发器q1修改触发器修改触发器ALTER TRIGGER 触发器名触发器名ON 表表|视图视图WITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETENOT FOR REPLICATIONASIF UPD
46、ATE(列名列名)AND|OR UPDATE(列名列名)nSQL语句语句7/29/202439SQL ServerSQL Server实用教程实用教程q2删除触发器删除触发器q使用使用DROP TRIGGER 命令，即可命令，即可删除触发器。删除触发器。7/29/202440SQL ServerSQL Server实用教程实用教程10106 63 3 禁止或启用触发器禁止或启用触发器q禁止和启用触发器的语法格式如下。禁止和启用触发器的语法格式如下。qALTER TABLE 表名表名qENABLE|DISABLE TRIGGERqALL|触发器名触发器名,nq使用该语句可以禁用或启用指定表上的某
47、些触发使用该语句可以禁用或启用指定表上的某些触发器或所有触发器。器或所有触发器。7/29/202441SQL ServerSQL Server实用教程实用教程10.7 DDL10.7 DDL触发器触发器q一般来说，在以下几种情况下可以使用一般来说，在以下几种情况下可以使用DDL触发触发器。器。（1）防止数据库架构进行某些修改。）防止数据库架构进行某些修改。（2）防止数据库或数据表被误操作而删除。）防止数据库或数据表被误操作而删除。（3）希望数据库发生某种情况以响应数据库架构）希望数据库发生某种情况以响应数据库架构中的更改。中的更改。（4）要记录数据库架构的更改或事件。）要记录数据库架构的更改或
48、事件。q仅在运行仅在运行DDL触发器的触发器的DDL语句后，语句后，DDL触发器触发器才会激发。才会激发。DDL触发器无法作为触发器无法作为INSTEAD OF触触发器而使用。发器而使用。7/29/202442SQL ServerSQL Server实用教程实用教程10107 71 1 创建创建DDLDDL触发器触发器q创建创建DDL触发器的语法格式如下：触发器的语法格式如下：CREATE TRIGGER 触发器名触发器名ON 服务器服务器|数据库数据库WITH ENCRYPTIONFOR|AFTER DDL语句名称语句名称ASSQL语句语句7/29/202443SQL ServerSQL S
49、erver实用教程实用教程q【例例10.16】建立用于保护数据库建立用于保护数据库xsgl中的数据表不被删中的数据表不被删除的触发器。除的触发器。q运行如下命令。运行如下命令。CREATE TRIGGER dis_drop_table ON xsgl FOR DROP_TABLEASBEGIN RAISERROR(对不起，对不起，xsgl数据库中的表不能删除数据库中的表不能删除,16,10)ENDGO7/29/202444SQL ServerSQL Server实用教程实用教程q1072 查看和修改查看和修改DDL触发器触发器q（1）作用在当前）作用在当前SQL Server服务器上的服务器上
50、的DDL触发触发器所在的位置器所在的位置q选择所在的选择所在的SQL Server服务器上，定位到服务器上，定位到“服务器服务器对象对象”中的中的“触发器触发器”，在，在“摘要摘要”对话框中就可对话框中就可以看到所有作用在当前以看到所有作用在当前SQL Server服务器上的服务器上的DDL触发器。触发器。q（2）作用在当前数据库中的）作用在当前数据库中的DDL触发器所在位置触发器所在位置q在在SQL Server服务器上，通过服务器上，通过“数据库数据库”选择所在选择所在的数据库，然后定位到的数据库，然后定位到“可编程性可编程性”中的中的“数据库数据库触发器触发器”，在摘要对话框中就可以看到
51、所有的当前，在摘要对话框中就可以看到所有的当前数据库中的数据库中的DDL触发器。触发器。7/29/202445SQL ServerSQL Server实用教程实用教程10.8 10.8 事务事务q1081 基本概念基本概念q事务是作为单个逻辑工作单元执行的一系列操作。事务是作为单个逻辑工作单元执行的一系列操作。这一系列操作或者都被执行，或者都不被执行。这一系列操作或者都被执行，或者都不被执行。7/29/202446SQL ServerSQL Server实用教程实用教程q事务作为一个逻辑工作单元有事务作为一个逻辑工作单元有4个属性，称为个属性，称为ACID（原子（原子性、一致性、隔离性和持久性
52、）属性。性、一致性、隔离性和持久性）属性。q（1）原子性。事务必须是原子工作单元，对于其数据修改，）原子性。事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。要么全都执行，要么全都不执行。q（2）一致性。事务在完成时，必须使所有的数据都保持一）一致性。事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都应用于事务的修改，致状态。在相关数据库中，所有规则都应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。构都必须是正确的。q（3）隔离性。由并发事务所做的修改必须与任何
53、其他并发）隔离性。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。保证事务查看数据时数据所处的状态，事务所做的修改隔离。保证事务查看数据时数据所处的状态，只能是一并发事务修改它之前的状态或者是另一事务修改它只能是一并发事务修改它之前的状态或者是另一事务修改它之后的状态，而不能查看中间状态的数据。之后的状态，而不能查看中间状态的数据。q（4）持久性。事务完成之后对系统的影响是永久的。）持久性。事务完成之后对系统的影响是永久的。7/29/202447SQL ServerSQL Server实用教程实用教程qSQL Server有以下有以下3种事务模式。种事务模式。q（1）自动提交事务。这
54、是）自动提交事务。这是SQL Server的默认模式。的默认模式。每个单独的每个单独的SQL语句都是一个事务，并在其完成后语句都是一个事务，并在其完成后提交。不必指定任何语句控制事务。提交。不必指定任何语句控制事务。q（2）显式事务。每个事务均以）显式事务。每个事务均以BEGIN TRANSACTION语句显式开始，以语句显式开始，以COMMIT或或ROLLBACK语句显式结束。语句显式结束。q（3）隐性事务。通过）隐性事务。通过API函数或函数或Transact-SQL的的SET IMPLICIT_TRANSACTION ON语句，将隐语句，将隐性事务模式设置为打开。这样在前一个事务结束时性
55、事务模式设置为打开。这样在前一个事务结束时新事务隐式启动，但每个事务仍以新事务隐式启动，但每个事务仍以COMMIT或或ROLLBACK语句显式结束。语句显式结束。7/29/202448SQL ServerSQL Server实用教程实用教程10108 82 2 事务应用事务应用q事务组织结构的一般形式如下。事务组织结构的一般形式如下。q（1）定义一个事务的开始：）定义一个事务的开始：BEGIN TRANSACTION。q（2）提交一个事务：）提交一个事务：COMMIT TRANSACTION。q（3）回滚事务：）回滚事务：ROLLBACK TRANSACTION。qBEGIN TRANSACT
56、ION代表一个事务的开始点，代表一个事务的开始点，事务执行到事务执行到COMMIT TRANSACTION提交语句提交语句后结束事务，完成对数据库所做的永久改动。如后结束事务，完成对数据库所做的永久改动。如果遇上错误用果遇上错误用ROLLBACK TRANSACTION语句语句可以撤消所有改动。可以撤消所有改动。7/29/202449SQL ServerSQL Server实用教程实用教程q如果在事务活动时由于任何原因（如客户端应用如果在事务活动时由于任何原因（如客户端应用程序终止；客户端计算机关闭或重新启动；客户程序终止；客户端计算机关闭或重新启动；客户端网络连接中断等）中断了客户端和端网络连接中断等）中断了客户端和SQL Server实例之间的通信，实例之间的通信，SQL Server实例将在收到网络实例将在收到网络或操作系统发出的中断通知时自动回滚事务。在或操作系统发出的中断通知时自动回滚事务。在所有这些错误情况下，将回滚任何未完成的事务所有这些错误情况下，将回滚任何未完成的事务以保护数据的完整性和一致性。以保护数据的完整性和一致性。7/29/202450SQL ServerSQL Server实用教程实用教程7/29/202451
