《存储过程和触发器PPT讲稿.ppt》由会员分享，可在线阅读，更多相关《存储过程和触发器PPT讲稿.ppt（69页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、存储过程和触发器存储过程和触发器第1页，共69页，编辑于2022年，星期六本章重点本章重点q存储过程的概念、分类存储过程的概念、分类q存储过程的创建、执行存储过程的创建、执行q触发器的概念、分类触发器的概念、分类q触发器的创建、触发触发器的创建、触发qDELETED与与INSERTED表表第2页，共69页，编辑于2022年，星期六2023/1/182本章难点本章难点q有参存储过程的创建、执行有参存储过程的创建、执行q触发器的触发时机触发器的触发时机qDELETED与与INSERTED表表第3页，共69页，编辑于2022年，星期六2023/1/18310.1 10.1 存储过程的概念存储过程的概
2、念1011基本概念基本概念存储过程是一组编译在单个执行计划中的存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给语句，将一些固定的操作集中起来交给SQLServer数数据库服务器据库服务器完成，以实现某个任务。完成，以实现某个任务。1012存储过程的优点存储过程的优点（1）与其他应用程序共享应用程序逻辑，因而确保了数据访）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。问和修改的一致性。（2）防止数据库中表的细节暴露给用户。）防止数据库中表的细节暴露给用户。（3）提供安全机制。）提供安全机制。（4）改进性能。）改进性能。（5）减少网
3、络流量。）减少网络流量。第4页，共69页，编辑于2022年，星期六2023/1/1841013存储过程的类型存储过程的类型存储过程分为三类：存储过程分为三类：q系统提供的存储过程系统提供的存储过程 系统提供的存储过程系统存储过程的名字都以系统提供的存储过程系统存储过程的名字都以“sp_sp_”为前缀为前缀例：例：sp_bindefault sp_bindefault，sp_bindrule sp_bindrule，sp_help sp_help，sp_helpdb,sp_helpindex sp_helpdb,sp_helpindex 等等q用户定义的存储过程用户定义的存储过程 用户定义的存储
4、过程是由用户为完成某一特定功能而编写用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。存储在创建时的数据库中。的存储过程。存储在创建时的数据库中。q扩展存储过程：扩展存储过程：扩展存储过程是用来调用操作系统提供的功能。扩展存储过程是用来调用操作系统提供的功能。第5页，共69页，编辑于2022年，星期六2023/1/185系系统统存存储过储过程程说说明明sp_databases列出服列出服务务器上的所有数据器上的所有数据库库。sp_helpdb报报告有关指定数据告有关指定数据库库或所有数据或所有数据库库的信息的信息sp_renamedb更改数据更改数据库库的名称的名称sp_table
5、s返回当前返回当前环环境下可境下可查询查询的的对对象的列表象的列表sp_columns回某个表列的信息回某个表列的信息sp_help查查看某个表的所有信息看某个表的所有信息sp_helpconstraint查查看某个表的看某个表的约约束束sp_helpindex查查看某个表的索引看某个表的索引sp_stored_procedures列出当前列出当前环环境中的所有存境中的所有存储过储过程。程。sp_password添加或修改登添加或修改登录帐户录帐户的密的密码码。sp_helptext显显示默示默认值认值、未加密的存、未加密的存储过储过程、用程、用户户定定义义的存的存储过储过程、触程、触发发器或
6、器或视图视图的的实际实际文本。文本。第6页，共69页，编辑于2022年，星期六2023/1/186EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 修改数据库的名
7、称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程第7页，共69页，编辑于2022年，星期六2023/1/18710.2 10.2 建立和执行存储过程建立和执行存储过程简单存储过程类似于将一组简单存储过程类似于将一组SQL语句起个名字，然后就语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。可以在需要时反复调用。复杂一些的则要有输入和输出参数。1021创建和执行简单存储过程创建和执行简单存储过程
8、创建存储过程的基本语法如下：创建存储过程的基本语法如下：CREATEPROC存储过程名存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句语句q其中各参数如下：其中各参数如下：WITHENCRYPTION：对存储过程进行：对存储过程进行加密加密。WITHRECOMPILE：对存储过程：对存储过程重新编译重新编译。第8页，共69页，编辑于2022年，星期六2023/1/188【例【例10.1】创建一个无参数的存储过程，在】创建一个无参数的存储过程，在SALES数据库中数据库中,创建存储过程创建存储过程xs,查询查询销售编号销售编号,商品名称商品名称,数量。数量。CREA
9、TEPROCEDURExsASSELECT销售编号销售编号,商品名称商品名称,sell.数量数量as销售数量销售数量FROMgoods,sellWHEREgoods.商品编号商品编号=sell.商品编号商品编号第9页，共69页，编辑于2022年，星期六2023/1/1891022存储过程的执行存储过程的执行q执行存储过程的基本语法如下：执行存储过程的基本语法如下：EXECUTE存储过程名存储过程名q同时同时EXECUTE命令除了可以执行存储过程外还可以执行存命令除了可以执行存储过程外还可以执行存放放Transact-SQL语句的字符串变量，或直接执行语句的字符串变量，或直接执行Transact
10、-SQL语句字符串。此时语句字符串。此时EXECUTE语句的语法格语句的语法格式如下。式如下。EXECUTE(字符串变量字符串变量|NSQL语句字符串语句字符串+.nq其中其中“字符串变量字符串变量”是局部字符串变量名，最大值为服务器是局部字符串变量名，最大值为服务器的可用内存。的可用内存。NSQL语句字符串语句字符串的语句字符串是一个由的语句字符串是一个由SQL语句构成的字符串常量。如果包含语句构成的字符串常量。如果包含N，则该字符串将解，则该字符串将解释为释为nvarchar数据类型。数据类型。EXECxs第10页，共69页，编辑于2022年，星期六2023/1/1810【例【例10.2】
11、建立一个批处理，查询相应表中的信息。】建立一个批处理，查询相应表中的信息。DECLAREtab_namevarchar(20)SETtab_name=xsEXECUTE(SELECT*FROM+tab_name)第11页，共69页，编辑于2022年，星期六2023/1/18111023带输入参数的存储过程带输入参数的存储过程1建立存储过程建立存储过程一个存储过程可以带一个或多个参数，输入参数是指由调用程序一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相
12、应的参数值。存储过程中给出相应的参数值。q声明带输入参数的存储过程的语法格式如下：声明带输入参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名存储过程名参数名参数名数据类型数据类型=默认值默认值，nWITHENCRYPTIONWITHRECOMPILEASSQL语句语句第12页，共69页，编辑于2022年，星期六2023/1/1812q其中其中“参数名参数名”和定义局部变量一样，必须以符和定义局部变量一样，必须以符号号为前缀，要指定数据类型，多个参数定义要用为前缀，要指定数据类型，多个参数定义要用“，”隔开。在执行存储过程时该参数将由指定隔开。在执行存储过程时该参数将由指定
13、的的参数值参数值来代替，如果执行时未提供该参数的参来代替，如果执行时未提供该参数的参数值，则使用时须数值，则使用时须定义默认值定义默认值（默认值可以是常（默认值可以是常量或空值量或空值null），否则将产生错误。），否则将产生错误。第13页，共69页，编辑于2022年，星期六2023/1/1813【例【例10.310.3】创建一个带输入参数的存储过程】创建一个带输入参数的存储过程PROC_GOODS,PROC_GOODS,查询指定员工所进商品信查询指定员工所进商品信息息CREATEPROCproc_goods员工编号员工编号char(6)=1001AsSelect商品编号商品编号,商品名称商品
14、名称,生产厂商生产厂商,进货价进货价,零售零售价价,数量数量,进货时间进货时间fromgoodsWhere进货员工编号进货员工编号=员工编号员工编号 第14页，共69页，编辑于2022年，星期六2023/1/18142执行存储过程执行存储过程在执行存储过程的语句中，有两种方式传递参数值，分别是在执行存储过程的语句中，有两种方式传递参数值，分别是使用使用参数名参数名传递参数值和按传递参数值和按参数位置参数位置传递参数值传递参数值。q使用参数名传递参数值，是通过语句使用参数名传递参数值，是通过语句“参数名参数名=参数值参数值”给参数传给参数传递值。当存储过程含有多个输入参数时，对数值可以按任意顺递
15、值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数序给出，对于允许空值和具有默认值的输入参数可以不给参数值，其语法格式为：值，其语法格式为：EXECUTE存储过程名存储过程名参数名参数名=参数值参数值，nq按参数位置传递参数值，不显式地给出按参数位置传递参数值，不显式地给出“参数名参数名”，而是按照参数，而是按照参数定义的定义的顺序顺序给出参数值。按位置传递参数时，也可以忽略允许给出参数值。按位置传递参数时，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的为空值和有默认值的参数，但不能因此破坏输入参数的指定顺指定顺序序。必要
16、时使用关键字。必要时使用关键字“DEFAULT”作为参数值的占位。作为参数值的占位。EXECproc_goods员工编号员工编号=1002或或EXECproc_goods1002第15页，共69页，编辑于2022年，星期六2023/1/181510102 24 4 带输出参数的存储过程带输出参数的存储过程如果我们需要从存储过程中返回一个或多个值，如果我们需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来可以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在创建存储过程的实现，为了使用输出参数，需要在创建存储过程的命令中使用命令中使用OUTPUT关
17、键字。关键字。q声明带输出参数的存储过程的语法格式如下：声明带输出参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名存储过程名参数名参数名数据类型数据类型VARYING=默认值默认值OUTPUT，nWITHENCRYPTIONWITHRECOMPILEASSQL语句语句第16页，共69页，编辑于2022年，星期六2023/1/1816q注意：注意：OUTPUT变量必须在变量必须在定义存储过程定义存储过程和和使用该使用该变量时都进行定义变量时都进行定义。定义时定义时的参数名和的参数名和调用时调用时的变的变量名不一定相同，不过量名不一定相同，不过数据类型数据类型和和参数的位置参
18、数的位置必须必须匹配。匹配。第17页，共69页，编辑于2022年，星期六2023/1/1817【例【例10.5】创建一个带有输入和输出函数的存储过程】创建一个带有输入和输出函数的存储过程proc_gno,查询指定厂查询指定厂商指定名称的商品对应的商品编号商指定名称的商品对应的商品编号CREATEPROCproc_gno商品名称商品名称varchar(20),生产厂商生产厂商varchar(30),商品编号商品编号intoutputAsSelect商品编号商品编号=商品编号商品编号FromgoodsWhere商品名称商品名称=商品名称商品名称and生产厂商生产厂商=生产厂商生产厂商 第18页，共
19、69页，编辑于2022年，星期六2023/1/1818执行存储过程Declare商品编号商品编号intExecproc_gno打印机打印机,惠普公司惠普公司,商商品编号品编号outputPrint该商品编号为该商品编号为:+cast(商品编号商品编号aschar(6)第19页，共69页，编辑于2022年，星期六2023/1/181910.3 10.3 存储过程的管理与维护存储过程的管理与维护1031查看存储过程的定义信息查看存储过程的定义信息在在SQLServerManagementStudio的的“对象资源管理器对象资源管理器”中，可以在要查看信息的存储中，可以在要查看信息的存储过程上单击鼠
20、标右键，在快捷菜单中选择过程上单击鼠标右键，在快捷菜单中选择“属性属性”，弹出，弹出“存储过程属性存储过程属性”窗口窗口.也可以通过系统存储过程也可以通过系统存储过程sp_helptext查看查看存储过程的存储过程的定义定义；通过；通过sp_help查看存储过程的查看存储过程的参数参数；通过；通过sp_depends查看存储过程的查看存储过程的相关性。相关性。第20页，共69页，编辑于2022年，星期六2023/1/1820【例【例10.7】在】在SQLServerManagementStudio服务器中新建查询，使用系统存储过程，查看例服务器中新建查询，使用系统存储过程，查看例10.5中所创
21、建存储过程的定义、参数和相关性。中所创建存储过程的定义、参数和相关性。EXECUTEsp_helptextproc_gnoEXECUTEsp_helpproc_gnoEXECUTEsp_dependsproc_gno第21页，共69页，编辑于2022年，星期六2023/1/182110103 32 2 存储过程的重编译存储过程的重编译存储过程所采用的执行计划，只在编译时优存储过程所采用的执行计划，只在编译时优化生成，以后便驻留在高速缓存中。当用户对数化生成，以后便驻留在高速缓存中。当用户对数据库新增了索引或其他影响数据库逻辑结构的更据库新增了索引或其他影响数据库逻辑结构的更改后，已编译的存储过
22、程执行计划可能会失去效改后，已编译的存储过程执行计划可能会失去效率。通过对存储过程进行重新编译，可以重新优率。通过对存储过程进行重新编译，可以重新优化存储过程的执行计划。化存储过程的执行计划。qSQLServer为用户提供了为用户提供了3种重新编译的方法。种重新编译的方法。第22页，共69页，编辑于2022年，星期六2023/1/18221在创建存储过程时设定在创建存储过程时设定在创建存储过程时，使用在创建存储过程时，使用WITHRECOMPILE子句时子句时SQLServer不将该存储过程的查询计划保存在缓存中，而是在每次运行不将该存储过程的查询计划保存在缓存中，而是在每次运行时重新编译和优
23、化，并创建新的执行计划。时重新编译和优化，并创建新的执行计划。2在执行存储过程时设定在执行存储过程时设定通过在执行存储过程时设定重新编译，可以让通过在执行存储过程时设定重新编译，可以让SQLServer在在执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被保存在缓存中。这样用户就可以根据需要进行重新执行计划又被保存在缓存中。这样用户就可以根据需要进行重新编译。编译。EXEC存储过程名存储过程名RECOMPILE3通过系统存储过程设定重编译通过系统存储过程设定重编译通过系统存储过程通过系统存储过程sp_recompile设
24、定重新编译标记，使存储过程在设定重新编译标记，使存储过程在下次运行时重新编译。下次运行时重新编译。q其语法格式如下：其语法格式如下：EXECsp_recompile数据库对象数据库对象第23页，共69页，编辑于2022年，星期六2023/1/182310103 33 3 修改和删除存储过程修改和删除存储过程1修改存储过程修改存储过程存储过程的修改是由存储过程的修改是由ALTER语句语句来完成的，来完成的，基本语法如下：基本语法如下：ALTERPROCEDURE存储过程名存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句语句第24页，共69页，编辑于2022年，星期六
25、2023/1/1824【例【例10.710.7】修改例修改例10.110.1的存储过程的存储过程,对其进行加密对其进行加密alterPROCEDURExsWITHENCRYPTIONASSELECT销售编号销售编号,商品名称商品名称,sell.数量数量as销售数销售数量量FROMgoods,sellWHEREgoods.商品编号商品编号=sell.商品编号商品编号第25页，共69页，编辑于2022年，星期六2023/1/18252删除存储过程删除存储过程存储过程的删除是通过存储过程的删除是通过DROP语句来实现的，语句来实现的，在在SQLServerManagementStudio的的“对象对
26、象资源管理器资源管理器”中也同样可以进行删除。命令方式中也同样可以进行删除。命令方式删除存储过程的方法也很简单删除存储过程的方法也很简单.语法格式语法格式:DROPPROCEDURE存储过程名存储过程名【例【例10.8】删除例】删除例10.1中创建的存储过程中创建的存储过程alterPROCEDURExs第26页，共69页，编辑于2022年，星期六2023/1/1826张三张三李四李四王五王五赵二赵二王三王三宋二宋二刘五刘五插入删除触发器触发赵二退休 赵二赵二员工表退休员工表触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用C
27、HECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case）可引用其它表中的列 触发器定义在特定的表上，与表相关自动触发执行不能直接调用是一个事务（可回滚）第27页，共69页，编辑于2022年，星期六2023/1/1827张三开户1000元,李四开户1元 10.4 10.4 触发器的概念触发器的概念1041基本概念基本概念2、为什么需要触发器、为什么需要触发器为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统 帐户信息表bank 交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：l它是一种特殊的存储过
28、程 l也具备事务的功能 l它能在多表之间执行特殊的业务规则 第28页，共69页，编辑于2022年，星期六2023/1/18281042触发器的功能触发器的功能SQLServer2005提供了两种方法来保证数据提供了两种方法来保证数据的有效性和完整性：的有效性和完整性：约束和触发器约束和触发器。触发器的常用功能如下。触发器的常用功能如下。（1）完成更复杂的数据约束：触发器可以实现比）完成更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束。约束更为复杂的数据约束。（2）检查）检查SQL所做的操作是否允许：触发器可以所做的操作是否允许：触发器可以检查检查SQL所做的操作是否被允许。所做的操作是
29、否被允许。第29页，共69页，编辑于2022年，星期六2023/1/1829（3）修改其他数据表里的数据：当一个）修改其他数据表里的数据：当一个SQL语句对数据表进行操作的语句对数据表进行操作的时候，触发器可以根据时候，触发器可以根据SQL语句的操作情况来对语句的操作情况来对另一个数据表另一个数据表进行进行操作。操作。（4）调用更多的存储过程：约束是不能调用存储过程的，但触）调用更多的存储过程：约束是不能调用存储过程的，但触发器本身就是一种存储过程，而存储过程是可以嵌套调用的，发器本身就是一种存储过程，而存储过程是可以嵌套调用的，所以触发器也可能所以触发器也可能调用一个或多个存储过程调用一个或
30、多个存储过程。（5）返回自定义的错误信息：约束只能通过标准的系统错误信）返回自定义的错误信息：约束只能通过标准的系统错误信息来传递错误信息，如果应用程序要求使用自定义信息和较为息来传递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。复杂的错误处理，则必须使用触发器。（6）更改原本要操作的）更改原本要操作的SQL语句：触发器可以修改原本要操作的语句：触发器可以修改原本要操作的SQL语句。语句。（7）防止数据表结构更改或数据表被删除：为了保护已经建立好的）防止数据表结构更改或数据表被删除：为了保护已经建立好的数据表，触发器可以在接收到以数据表，触发器可以在接收到以D
31、ROP或或ALTER开头的语句后，开头的语句后，不对数据表的结构做任何操作。不对数据表的结构做任何操作。第30页，共69页，编辑于2022年，星期六2023/1/183010104 43 3 触发器的类型触发器的类型1.DML触发器触发器DML触发器是当数据库服务器中发生数据操触发器是当数据库服务器中发生数据操纵语言纵语言DML事件时执行的特殊存储过程，如事件时执行的特殊存储过程，如INSERT、UPDATE等。等。2.DDL触发器触发器DDL触发器是当数据库服务器中发生数据定义语触发器是当数据库服务器中发生数据定义语言言DDL事件时执行的特殊存储过程，如事件时执行的特殊存储过程，如CREAT
32、E、ALTER等。等。第31页，共69页，编辑于2022年，星期六2023/1/183110.5 10.5 创建和应用创建和应用DMLDML触发器触发器1051DML触发器的类型触发器的类型1AFTER触发器触发器这类触发器是在记录已经改变之后，才会被这类触发器是在记录已经改变之后，才会被激活执行，它主要是用于记录变更后的处理或检激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用查，一旦发现错误，也可以用ROLLBACKTRANSACTION语句来回滚本次的操作。语句来回滚本次的操作。2INSTEADOF触发器触发器这类触发器一般是用来取代原本要进行的操这类触发器一般是用来取
33、代原本要进行的操作，在记录变更之前发生的，它并不去执行原来作，在记录变更之前发生的，它并不去执行原来的的SQL语句里的操作，而是去执行触发器本身所语句里的操作，而是去执行触发器本身所定义的操作。定义的操作。第32页，共69页，编辑于2022年，星期六2023/1/18321052DML触发器的工作原理触发器的工作原理在在SQLServer2005中，为每个中，为每个DML触发器定义了两个触发器定义了两个特殊的表，一个是特殊的表，一个是Inserted表，一个是表，一个是Deleted表。表。修改操作inserted表deleted表增加增加(INSERT)记录记录存放新增的存放新增的记录记录-
34、删删除除(DELETE)记录记录-存放被存放被删删除的除的记录记录修改修改(UPDATE)记录记录存放更新后的存放更新后的记录记录存放更新前的存放更新前的记录记录第33页，共69页，编辑于2022年，星期六2023/1/1833Inserted表里存放的是表里存放的是更新前更新前的记录：对于的记录：对于插插入记录入记录操作来说，操作来说，Inserted表里存储的是要插入的表里存储的是要插入的数据；对于数据；对于更新记录更新记录的操作来说，的操作来说，Inserted表里存表里存放的是要更新的记录。放的是要更新的记录。Deleted表里存放的是更新后的记录：对于更新表里存放的是更新后的记录：对
35、于更新记录操作来说，记录操作来说，Deleted表里存放的是表里存放的是更新前更新前的记录；的记录；对于删除记录操作来说，对于删除记录操作来说，Deleted表里存储的是表里存储的是被删被删除的旧记录除的旧记录。第34页，共69页，编辑于2022年，星期六2023/1/183410105 53 3 创建创建DMLDML触发器的注意事项触发器的注意事项（1）CREATETRIGGER语句必须是批处理中的第语句必须是批处理中的第一个语句，该语句后面的所有语句都被解释为一个语句，该语句后面的所有语句都被解释为CREATETRIGGER语句定义的一部分。语句定义的一部分。（2）创建）创建DML触发器的
36、权限默认分配给表的所有触发器的权限默认分配给表的所有者，且不能将该权限转授给其他用户。者，且不能将该权限转授给其他用户。（3）DML可以引用当前数据库以外的对象，但只可以引用当前数据库以外的对象，但只能在当前数据库中创建能在当前数据库中创建DML触发器。触发器。（4）不能对系统表或临时表创建）不能对系统表或临时表创建DML触发器触发器（5）对于含有）对于含有DELETE或或UPDATE操作定义的外操作定义的外键表，不能定义键表，不能定义INSTEADOF触发器。触发器。第35页，共69页，编辑于2022年，星期六2023/1/183510105 53 3 创建触发器创建触发器CREATETRI
37、GGER触发器名触发器名ON表表|视图视图WITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETENOTFORREPLICATIONASIFUPDATE(列名列名)AND|ORUPDATE(列名列名)nSQL语句语句第36页，共69页，编辑于2022年，星期六2023/1/1836（1）WITHENCRYPTION。加密加密CREATETRIGGER语句文本的条语句文本的条目。目。（2）FOR|AFTER。FOR与与AFTER同义，指定触发器只有在触发同义，指定触发器只有在触发器器SQL语句中指定的所有操作都已成功语句中指定的所有操作都已成功后
38、后才激发。所有的引用级联才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。（3）INSTEADOF。指定执行触发器而。指定执行触发器而不执行造成触发的不执行造成触发的SQL语语句句，从而替代造成触发的语句。在表或视图上，每个，从而替代造成触发的语句。在表或视图上，每个INSERT、UPDATE或或DELETE语句只能定义语句只能定义一个一个INSTEADOF触发器触发器，即，即替代触发。替代触发。（4）INSERT,UPDATE,DELETE。是指定在表上执行哪些数。是指定在表上执行哪些数据修改语句
39、时将激活触发器的关键字。必须据修改语句时将激活触发器的关键字。必须至少指定一个选项至少指定一个选项。在。在触发器定义中允许使用任意顺序组合的这些关键字。当进行触发条件的触发器定义中允许使用任意顺序组合的这些关键字。当进行触发条件的操作时（操作时（INSERT、UPDATE或或DELETE），将执行），将执行SQL语句中指定的语句中指定的触发器操作。触发器操作。第37页，共69页，编辑于2022年，星期六2023/1/1837（5）NOTFORREPLICATION。表示当复制进程。表示当复制进程更改触发器所涉及的表时，不要执行该触发器。更改触发器所涉及的表时，不要执行该触发器。（6）IFUPD
40、ATE(列名列名)。测试在指定的列上进行。测试在指定的列上进行的的INSERT或或UPDATE操作，不能用于操作，不能用于DELETE操作，可以指定多列。因为已经在操作，可以指定多列。因为已经在ON子句中指定子句中指定了表名，所以在了表名，所以在IFUPDATE子句中的列名前不要子句中的列名前不要包含表名。若要测试在多个列上进行的包含表名。若要测试在多个列上进行的INSERT或或UPDATE操作，要分别单独地指定操作，要分别单独地指定UPDATE(列列名名)子句。在子句。在INSERT操作中操作中IFUPDATE将返回将返回TRUE值。值。第38页，共69页，编辑于2022年，星期六2023/
41、1/18381UPDATE触发器触发器【例【例10.12】创建一个触发器】创建一个触发器,当有人试图更新当有人试图更新SALES数数据库中据库中goods表的商品编号或进货员工编号时表的商品编号或进货员工编号时,利用触利用触发器产生提示信息发器产生提示信息.并取消修改操作并取消修改操作.-创建触发器创建触发器CREATETRIGGERupdate_trigONgoodsFORupdateASIFUPDATE(商品编号商品编号)ORUPDATE(进货员工编号进货员工编号)BEGINRAISERROR(商品编号或进货员工编号不能进行修改商品编号或进货员工编号不能进行修改!,7,2)ROLLBACK
42、TRANSACTIONEND第39页，共69页，编辑于2022年，星期六2023/1/1839-测试触发器测试触发器UpdategoodsSet商品编号商品编号=120Where数量数量=8第40页，共69页，编辑于2022年，星期六2023/1/18402INSERT触发器触发器【例【例10.11】创建一个】创建一个AFTERINSERT触发器触发器,当在当在SALES数据库的数据库的employees表中插入一条新员工记表中插入一条新员工记录时录时,如果不是如果不是”采购部采购部”,”财务部财务部”,”销售部销售部”或或”库存部库存部”的员工的员工,则撤消该插入操作则撤消该插入操作,并返回
43、出错并返回出错消息消息.-创建触发器创建触发器第41页，共69页，编辑于2022年，星期六2023/1/1841CREATETRIGGERinsert_trigONemployeesFORinsertASDeclaredepartvarchar(16)Selectdepart=employees.部门部门fromemployees,insertedWhereemployees.编号编号=inserted.编号编号Ifdepart采购部采购部ordepart财务部财务部ordepart销售销售部部ordepart库存部库存部BEGINROLLBACKTRANSACTIONRAISERROR(不能
44、插入非本公司设定部门的员工信息不能插入非本公司设定部门的员工信息!,16,10)END第42页，共69页，编辑于2022年，星期六2023/1/1842-测试触发器测试触发器Insertemployess(编号编号,姓名姓名,性别性别,部门部门,电话电话,地址地址)Values(1511,杨过杨过,人事部人事部,1201200,.南昌市广场南路南昌市广场南路156号号)第43页，共69页，编辑于2022年，星期六2023/1/18433DELETE触发器触发器【例【例10.13】在】在sales数据库的数据库的emplyoees表和表和sell表表之间具有逻辑上的主外键关系之间具有逻辑上的主外
45、键关系,要求当删除或更新员要求当删除或更新员工记录的时候工记录的时候,要触发触发器要触发触发器update_delete_trig,在在sell表中也删除或更新相对应的记录行。表中也删除或更新相对应的记录行。-创建触发器创建触发器第44页，共69页，编辑于2022年，星期六2023/1/1844CREATETRIGGERupdate_delete_trigONemployeesFORupdate,deleteASDeclaredelcountintDeclareempidchar(6)-更新更新Ifupdate(编号编号)BeginUpdatesellSet售货员工编号售货员工编号=(sele
46、ct编号编号frominserted)Where售货员工编号售货员工编号in(select编号编号fromdeleted)End第45页，共69页，编辑于2022年，星期六2023/1/1845-删除删除Selectdelcount=count(*)fromdeletedIfdelcount0BeginSelectempid=编号编号fromdeletedDeletefromsellwhere售货员工编号售货员工编号=empidEND第46页，共69页，编辑于2022年，星期六2023/1/18466 6触发器案例分析触发器案例分析1 1(1)问题：问题：解决上述的银行取款问题：当向交易信息表
47、(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。(2)分析：分析：q在交易信息表上创建INSERT触发器 q从inserted临时表中获取插入的数据行q根据交易类型(transType)字段的值是存入/支取，q 增加/减少对应帐户的余额。第47页，共69页，编辑于2022年，星期六2023/1/1847CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SE
48、LECT type=transType,outMoney=transMoney，myCardID=cardID FROM inserted IF(type=支取)UPDATE bank SET currentMoney=currentMoney-outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID .GO 从inserted表中获取交易类型、交易金额等根据交易类型，减少或增加对应卡号的余额(3)实现方法实现方法第48页，共69页，编
49、辑于2022年，星期六2023/1/18486 6触发器案例分析触发器案例分析2 2如果你是图书管数据库管理员如果你是图书管数据库管理员,现在有现在有Student学生表和学生表和BorrowRecord学生借书记录表学生借书记录表,现有两现有两需求要你去实现其功能需求要你去实现其功能:(1)、如果我更改了学生的学号、如果我更改了学生的学号,我希望他的借书记录我希望他的借书记录仍然与这个学生相关仍然与这个学生相关(也就是同时更改借书记录表的也就是同时更改借书记录表的学号学号)(2)、如果该学生已经毕业，我希望删除他的学号的同、如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。时
50、，也删除它的借书记录。第49页，共69页，编辑于2022年，星期六2023/1/1849分析如何实现功能分析如何实现功能1:A在哪张表中创建触发器在哪张表中创建触发器?STUDENTB通过什么事件触发通过什么事件触发?UPDATEC事件触发后所要做的事情事件触发后所要做的事情?ifUpdate(StudentID)BEIGINUpdateBorrowRecordSetStudentID=i.StudentIDFromBorrowRecordbr,Deletedd,InsertediEND第50页，共69页，编辑于2022年，星期六2023/1/1850分析如何实现功能分析如何实现功能分析如何实