存储过程和触发器精.ppt

《存储过程和触发器精.ppt》由会员分享，可在线阅读，更多相关《存储过程和触发器精.ppt（69页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程和触发器存储过程和触发器第1页，本讲稿共69页本章重点本章重点q存储过程的概念、分类存储过程的概念、分类q存储过程的创建、执行存储过程的创建、执行q触发器的概念、分类触发器的概念、分类q触发器的创建、触发触发器的创建、触发qDELETED与与INSERTED表表第2页，本讲稿共69页2022/10/112本章难点本章难点q有参存储过程的创建、执行有参存储过程的创建、执行q触发器的触发时机触发器的触发时机qDELETED与与INSERTED表表第3页，本讲稿共69页2022/10/11310.1 10.1 存储过程的概念存储过程的概念1011基本概念基本概念存储过程是一组编译在单个执行计

2、划中的存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给语句，将一些固定的操作集中起来交给SQLServer数据数据库服务器库服务器完成，以实现某个任务。完成，以实现某个任务。1012存储过程的优点存储过程的优点（1）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。的一致性。（2）防止数据库中表的细节暴露给用户。）防止数据库中表的细节暴露给用户。（3）提供安全机制。）提供安全机制。（4）改进性能。）改进性能。（5）减少网络流量。）减少网络流量。第4页，本讲稿共69页2022/

3、10/1141013存储过程的类型存储过程的类型存储过程分为三类：存储过程分为三类：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/10/115系系统统存存储过储过程程说说明明sp_databases列出服列出服务务器上的所有数据器上的所有数据库库。sp_helpdb报报告有关指定数据告有关指定数据库库或所有数据或所有数据库库的信息的信息sp_renamedb更改数据更改数据库库的名称的名称sp_tables返回当前返回当前环环境下可境下可查询查询的的对对象的列表象的列表sp_columns回某个

5、表列的信息回某个表列的信息sp_help查查看某个表的所有信息看某个表的所有信息sp_helpconstraint查查看某个表的看某个表的约约束束sp_helpindex查查看某个表的索引看某个表的索引sp_stored_procedures列出当前列出当前环环境中的所有存境中的所有存储过储过程。程。sp_password添加或修改登添加或修改登录帐户录帐户的密的密码码。sp_helptext显显示默示默认值认值、未加密的存、未加密的存储过储过程、用程、用户户定定义义的存的存储过储过程、触程、触发发器或器或视图视图的的实际实际文本。文本。第6页，本讲稿共69页2022/10/116EXEC s

6、p_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 修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息

7、查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程第7页，本讲稿共69页2022/10/11710.2 10.2 建立和执行存储过程建立和执行存储过程简单存储过程类似于将一组简单存储过程类似于将一组SQL语句起个名字，然后就语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。可以在需要时反复调用。复杂一些的则要有输入和输出参数。1021创建和执行简单存储过程创建和执行简单存储过程创建存储过程的基本语法如下：创建存储过程的基本语法如下：CREATEPROC存储过程名存储过程名WITHENCRYPTIONWI

8、THRECOMPILEASSQL语句语句q其中各参数如下：其中各参数如下：WITHENCRYPTION：对存储过程进行：对存储过程进行加密加密。WITHRECOMPILE：对存储过程：对存储过程重新编译重新编译。第8页，本讲稿共69页2022/10/118【例【例10.1】创建一个无参数的存储过程，在】创建一个无参数的存储过程，在SALES数据库中数据库中,创建存储创建存储过程过程xs,查询查询销售编号销售编号,商品名称商品名称,数量。数量。CREATEPROCEDURExsASSELECT销售编号销售编号,商品名称商品名称,sell.数量数量as销售数量销售数量FROMgoods,sellW

9、HEREgoods.商品编号商品编号=sell.商品编号商品编号第9页，本讲稿共69页2022/10/1191022存储过程的执行存储过程的执行q执行存储过程的基本语法如下：执行存储过程的基本语法如下：EXECUTE存储过程名存储过程名q同时同时EXECUTE命令除了可以执行存储过程外还可以执行存命令除了可以执行存储过程外还可以执行存放放Transact-SQL语句的字符串变量，或直接执行语句的字符串变量，或直接执行Transact-SQL语句字符串。此时语句字符串。此时EXECUTE语句的语法格语句的语法格式如下。式如下。EXECUTE(字符串变量字符串变量|NSQL语句字符串语句字符串+.

10、nq其中其中“字符串变量字符串变量”是局部字符串变量名，最大值为服务器是局部字符串变量名，最大值为服务器的可用内存。的可用内存。NSQL语句字符串语句字符串的语句字符串是一个由的语句字符串是一个由SQL语句构成的字符串常量。如果包含语句构成的字符串常量。如果包含N，则该字符串将解，则该字符串将解释为释为nvarchar数据类型。数据类型。EXECxs第10页，本讲稿共69页2022/10/1110【例【例10.2】建立一个批处理，查询相应表中的信息。】建立一个批处理，查询相应表中的信息。DECLAREtab_namevarchar(20)SETtab_name=xsEXECUTE(SELECT

11、*FROM+tab_name)第11页，本讲稿共69页2022/10/11111023带输入参数的存储过程带输入参数的存储过程1建立存储过程建立存储过程一个存储过程可以带一个或多个参数，输入参数是指由调用一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。在执行存储过程中给出相应的参数值。q声明带输入参数的存储过程的语法格式如下：声明带输入参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名存储过程名参数名参数名数据类型数据类型

12、=默认值默认值，nWITHENCRYPTIONWITHRECOMPILEASSQL语句语句第12页，本讲稿共69页2022/10/1112q其中其中“参数名参数名”和定义局部变量一样，必须以符和定义局部变量一样，必须以符号号为前缀，要指定数据类型，多个参数定义要用为前缀，要指定数据类型，多个参数定义要用“，”隔开。在执行存储过程时该参数将由指定隔开。在执行存储过程时该参数将由指定的的参数值参数值来代替，如果执行时未提供该参数的参来代替，如果执行时未提供该参数的参数值，则使用时须数值，则使用时须定义默认值定义默认值（默认值可以是常（默认值可以是常量或空值量或空值null），否则将产生错误。），否

13、则将产生错误。第13页，本讲稿共69页2022/10/1113【例【例10.310.3】创建一个带输入参数的存储过程】创建一个带输入参数的存储过程PROC_GOODS,PROC_GOODS,查询指定员工所进商品信查询指定员工所进商品信息息CREATEPROCproc_goods员工编号员工编号char(6)=1001AsSelect商品编号商品编号,商品名称商品名称,生产厂商生产厂商,进货价进货价,零零售价售价,数量数量,进货时间进货时间fromgoodsWhere进货员工编号进货员工编号=员工编号员工编号 第14页，本讲稿共69页2022/10/11142执行存储过程执行存储过程在执行存储过

14、程的语句中，有两种方式传递参数值，分别是使在执行存储过程的语句中，有两种方式传递参数值，分别是使用用参数名参数名传递参数值和按传递参数值和按参数位置参数位置传递参数值传递参数值。q使用参数名传递参数值，是通过语句使用参数名传递参数值，是通过语句“参数名参数名=参数值参数值”给参数传给参数传递值。当存储过程含有多个输入参数时，对数值可以按任意顺序给递值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值，其语出，对于允许空值和具有默认值的输入参数可以不给参数值，其语法格式为：法格式为：EXECUTE存储过程名存储过程名参数名参数名=参数值参数

15、值，nq按参数位置传递参数值，不显式地给出按参数位置传递参数值，不显式地给出“参数名参数名”，而是按照，而是按照参数定义的参数定义的顺序顺序给出参数值。按位置传递参数时，也可以忽略允给出参数值。按位置传递参数时，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的许为空值和有默认值的参数，但不能因此破坏输入参数的指定指定顺序顺序。必要时使用关键字。必要时使用关键字“DEFAULT”作为参数值的占位。作为参数值的占位。EXECproc_goods员工编号员工编号=1002或或EXECproc_goods1002第15页，本讲稿共69页2022/10/111510102 24 4 带输出

16、参数的存储过程带输出参数的存储过程如果我们需要从存储过程中返回一个或多个值，如果我们需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来可以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在创建存储过程的实现，为了使用输出参数，需要在创建存储过程的命令中使用命令中使用OUTPUT关键字。关键字。q声明带输出参数的存储过程的语法格式如下：声明带输出参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名存储过程名参数名参数名数据类型数据类型VARYING=默认值默认值OUTPUT，nWITHENCRYPTIONWITHRECOMPILE

17、ASSQL语句语句第16页，本讲稿共69页2022/10/1116q注意：注意：OUTPUT变量必须在变量必须在定义存储过程定义存储过程和和使用该使用该变量时都进行定义变量时都进行定义。定义时定义时的参数名和的参数名和调用时调用时的变的变量名不一定相同，不过量名不一定相同，不过数据类型数据类型和和参数的位置参数的位置必须必须匹配。匹配。第17页，本讲稿共69页2022/10/1117【例【例10.5】创建一个带有输入和输出函数的存储过程】创建一个带有输入和输出函数的存储过程proc_gno,查询指查询指定厂商指定名称的商品对应的商品编号定厂商指定名称的商品对应的商品编号CREATEPROCpr

18、oc_gno商品名称商品名称varchar(20),生产厂商生产厂商varchar(30),商品编号商品编号intoutputAsSelect商品编号商品编号=商品编号商品编号FromgoodsWhere商品名称商品名称=商品名称商品名称and生产厂商生产厂商=生产厂商生产厂商 第18页，本讲稿共69页2022/10/1118执行存储过程Declare商品编号商品编号intExecproc_gno打印机打印机,惠普公司惠普公司,商品编商品编号号outputPrint该商品编号为该商品编号为:+cast(商品编号商品编号aschar(6)第19页，本讲稿共69页2022/10/111910.3

19、10.3 存储过程的管理与维护存储过程的管理与维护1031查看存储过程的定义信息查看存储过程的定义信息在在SQLServerManagementStudio的的“对象资源管理器对象资源管理器”中，可以在要查看信息的存储中，可以在要查看信息的存储过程上单击鼠标右键，在快捷菜单中选择过程上单击鼠标右键，在快捷菜单中选择“属性属性”，弹出，弹出“存储过程属性存储过程属性”窗口窗口.也可以通过系统存储过程也可以通过系统存储过程sp_helptext查看查看存储过程的存储过程的定义定义；通过；通过sp_help查看存储过程的查看存储过程的参数参数；通过；通过sp_depends查看存储过程的查看存储过程

20、的相关性。相关性。第20页，本讲稿共69页2022/10/1120【例【例10.7】在】在SQLServerManagementStudio服务器中新建查询，使用系统存储过程，查看例服务器中新建查询，使用系统存储过程，查看例10.5中所创建存储过程的定义、参数和相关性。中所创建存储过程的定义、参数和相关性。EXECUTEsp_helptextproc_gnoEXECUTEsp_helpproc_gnoEXECUTEsp_dependsproc_gno第21页，本讲稿共69页2022/10/112110103 32 2 存储过程的重编译存储过程的重编译存储过程所采用的执行计划，只在编译时优存储过

21、程所采用的执行计划，只在编译时优化生成，以后便驻留在高速缓存中。当用户对数化生成，以后便驻留在高速缓存中。当用户对数据库新增了索引或其他影响数据库逻辑结构的更据库新增了索引或其他影响数据库逻辑结构的更改后，已编译的存储过程执行计划可能会失去效改后，已编译的存储过程执行计划可能会失去效率。通过对存储过程进行重新编译，可以重新优率。通过对存储过程进行重新编译，可以重新优化存储过程的执行计划。化存储过程的执行计划。qSQLServer为用户提供了为用户提供了3种重新编译的方法。种重新编译的方法。第22页，本讲稿共69页2022/10/11221在创建存储过程时设定在创建存储过程时设定在创建存储过程时

22、，使用在创建存储过程时，使用WITHRECOMPILE子句时子句时SQLServer不将该存储过程的查询计划保存在缓存中，而是在每次不将该存储过程的查询计划保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。运行时重新编译和优化，并创建新的执行计划。2在执行存储过程时设定在执行存储过程时设定通过在执行存储过程时设定重新编译，可以让通过在执行存储过程时设定重新编译，可以让SQLServer在执行存在执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被保存在缓存中。这样用户就可以根据需要进行重新编译。保存在

23、缓存中。这样用户就可以根据需要进行重新编译。EXEC存储过程名存储过程名RECOMPILE3通过系统存储过程设定重编译通过系统存储过程设定重编译通过系统存储过程通过系统存储过程sp_recompile设定重新编译标记，使存储过程设定重新编译标记，使存储过程在下次运行时重新编译。在下次运行时重新编译。q其语法格式如下：其语法格式如下：EXECsp_recompile数据库对象数据库对象第23页，本讲稿共69页2022/10/112310103 33 3 修改和删除存储过程修改和删除存储过程1修改存储过程修改存储过程存储过程的修改是由存储过程的修改是由ALTER语句语句来完成的，来完成的，基本语法

24、如下：基本语法如下：ALTERPROCEDURE存储过程名存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句语句第24页，本讲稿共69页2022/10/1124【例【例10.710.7】修改例修改例10.110.1的存储过程的存储过程,对其进行加密对其进行加密alterPROCEDURExsWITHENCRYPTIONASSELECT销售编号销售编号,商品名称商品名称,sell.数量数量as销售数销售数量量FROMgoods,sellWHEREgoods.商品编号商品编号=sell.商品编号商品编号第25页，本讲稿共69页2022/10/11252删除存储过程删除存

25、储过程存储过程的删除是通过存储过程的删除是通过DROP语句来实现的，语句来实现的，在在SQLServerManagementStudio的的“对象对象资源管理器资源管理器”中也同样可以进行删除。命令方式中也同样可以进行删除。命令方式删除存储过程的方法也很简单删除存储过程的方法也很简单.语法格式语法格式:DROPPROCEDURE存储过程名存储过程名【例【例10.8】删除例】删除例10.1中创建的存储过程中创建的存储过程alterPROCEDURExs第26页，本讲稿共69页2022/10/1126张三张三李四李四王五王五赵二赵二王三王三宋二宋二刘五刘五插入删除触发器触发赵二退休 赵二赵二员工表

26、退休员工表触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case）可引用其它表中的列 触发器定义在特定的表上，与表相关自动触发执行不能直接调用是一个事务（可回滚）第27页，本讲稿共69页2022/10/1127张三开户1000元,李四开户1元 10.4 10.4 触发器的概念触发器的概念1041基本概念基本概念2、为什么需要触发器、为什么需要触发器为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统 帐户信息表bank 交易信息表tr

27、ansInfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：l它是一种特殊的存储过程 l也具备事务的功能 l它能在多表之间执行特殊的业务规则 第28页，本讲稿共69页2022/10/11281042触发器的功能触发器的功能SQLServer2005提供了两种方法来保证数据提供了两种方法来保证数据的有效性和完整性：的有效性和完整性：约束和触发器约束和触发器。触发器的常用功能如下。触发器的常用功能如下。（1）完成更复杂的数据约束：触发器可以实现比）完成更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束。约束更为复杂的数据约束。（2）检查）检查SQL所做的操作是否

28、允许：触发器可以所做的操作是否允许：触发器可以检查检查SQL所做的操作是否被允许。所做的操作是否被允许。第29页，本讲稿共69页2022/10/1129（3）修改其他数据表里的数据：当一个）修改其他数据表里的数据：当一个SQL语句对数据表进行操作的语句对数据表进行操作的时候，触发器可以根据时候，触发器可以根据SQL语句的操作情况来对语句的操作情况来对另一个数据表另一个数据表进行进行操作。操作。（4）调用更多的存储过程：约束是不能调用存储过程的，但触发器本）调用更多的存储过程：约束是不能调用存储过程的，但触发器本身就是一种存储过程，而存储过程是可以嵌套调用的，所以触发器也身就是一种存储过程，而存

29、储过程是可以嵌套调用的，所以触发器也可能可能调用一个或多个存储过程调用一个或多个存储过程。（5）返回自定义的错误信息：约束只能通过标准的系统错误信息来传）返回自定义的错误信息：约束只能通过标准的系统错误信息来传递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。理，则必须使用触发器。（6）更改原本要操作的）更改原本要操作的SQL语句：触发器可以修改原本要操作的语句：触发器可以修改原本要操作的SQL语句。语句。（7）防止数据表结构更改或数据表被删除：为了保护已经建立好）防止数据表结构更改或数据表被删除：为了

30、保护已经建立好的数据表，触发器可以在接收到以的数据表，触发器可以在接收到以DROP或或ALTER开头的语句开头的语句后，不对数据表的结构做任何操作。后，不对数据表的结构做任何操作。第30页，本讲稿共69页2022/10/113010104 43 3 触发器的类型触发器的类型1.DML触发器触发器DML触发器是当数据库服务器中发生数据操触发器是当数据库服务器中发生数据操纵语言纵语言DML事件时执行的特殊存储过程，如事件时执行的特殊存储过程，如INSERT、UPDATE等。等。2.DDL触发器触发器DDL触发器是当数据库服务器中发生数据定义语触发器是当数据库服务器中发生数据定义语言言DDL事件时执

31、行的特殊存储过程，如事件时执行的特殊存储过程，如CREATE、ALTER等。等。第31页，本讲稿共69页2022/10/113110.5 10.5 创建和应用创建和应用DMLDML触发器触发器1051DML触发器的类型触发器的类型1AFTER触发器触发器这类触发器是在记录已经改变之后，才会被这类触发器是在记录已经改变之后，才会被激活执行，它主要是用于记录变更后的处理或检激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用查，一旦发现错误，也可以用ROLLBACKTRANSACTION语句来回滚本次的操作。语句来回滚本次的操作。2INSTEADOF触发器触发器这类触发器一般是用来

32、取代原本要进行的操这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来作，在记录变更之前发生的，它并不去执行原来的的SQL语句里的操作，而是去执行触发器本身所语句里的操作，而是去执行触发器本身所定义的操作。定义的操作。第32页，本讲稿共69页2022/10/11321052DML触发器的工作原理触发器的工作原理在在SQLServer2005中，为每个中，为每个DML触发器定义了两个触发器定义了两个特殊的表，一个是特殊的表，一个是Inserted表，一个是表，一个是Deleted表。表。修改操作inserted表deleted表增加增加(INSERT)记录记录存放新增

33、的存放新增的记录记录-删删除除(DELETE)记录记录-存放被存放被删删除的除的记录记录修改修改(UPDATE)记录记录存放更新后的存放更新后的记录记录存放更新前的存放更新前的记录记录第33页，本讲稿共69页2022/10/1133Inserted表里存放的是表里存放的是更新前更新前的记录：对于的记录：对于插插入记录入记录操作来说，操作来说，Inserted表里存储的是要插入的表里存储的是要插入的数据；对于数据；对于更新记录更新记录的操作来说，的操作来说，Inserted表里存表里存放的是要更新的记录。放的是要更新的记录。Deleted表里存放的是更新后的记录：对于更新表里存放的是更新后的记录

34、：对于更新记录操作来说，记录操作来说，Deleted表里存放的是表里存放的是更新前更新前的记录；的记录；对于删除记录操作来说，对于删除记录操作来说，Deleted表里存储的是表里存储的是被删被删除的旧记录除的旧记录。第34页，本讲稿共69页2022/10/113410105 53 3 创建创建DMLDML触发器的注意事项触发器的注意事项（1）CREATETRIGGER语句必须是批处理中的第语句必须是批处理中的第一个语句，该语句后面的所有语句都被解释为一个语句，该语句后面的所有语句都被解释为CREATETRIGGER语句定义的一部分。语句定义的一部分。（2）创建）创建DML触发器的权限默认分配给

35、表的所有触发器的权限默认分配给表的所有者，且不能将该权限转授给其他用户。者，且不能将该权限转授给其他用户。（3）DML可以引用当前数据库以外的对象，但只可以引用当前数据库以外的对象，但只能在当前数据库中创建能在当前数据库中创建DML触发器。触发器。（4）不能对系统表或临时表创建）不能对系统表或临时表创建DML触发器触发器（5）对于含有）对于含有DELETE或或UPDATE操作定义的外操作定义的外键表，不能定义键表，不能定义INSTEADOF触发器。触发器。第35页，本讲稿共69页2022/10/113510105 53 3 创建触发器创建触发器CREATETRIGGER触发器名触发器名ON表表

36、|视图视图WITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETENOTFORREPLICATIONASIFUPDATE(列名列名)AND|ORUPDATE(列名列名)nSQL语句语句第36页，本讲稿共69页2022/10/1136（1）WITHENCRYPTION。加密加密CREATETRIGGER语句文本的条语句文本的条目。目。（2）FOR|AFTER。FOR与与AFTER同义，指定触发器只有在触发器同义，指定触发器只有在触发器SQL语句中指定的所有操作都已成功语句中指定的所有操作都已成功后后才激发。所有的引用级联操作和才激发。所有的引用级

37、联操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。约束检查也必须成功完成后，才能执行此触发器，即为后触发。（3）INSTEADOF。指定执行触发器而。指定执行触发器而不执行造成触发的不执行造成触发的SQL语语句句，从而替代造成触发的语句。在表或视图上，每个，从而替代造成触发的语句。在表或视图上，每个INSERT、UPDATE或或DELETE语句只能定义语句只能定义一个一个INSTEADOF触发器触发器，即替代，即替代触发。触发。（4）INSERT,UPDATE,DELETE。是指定在表上执行哪些数。是指定在表上执行哪些数据修改语句时将激活触发器的关键字。必须据修改语句时将激活触发

38、器的关键字。必须至少指定一个选项至少指定一个选项。在。在触发器定义中允许使用任意顺序组合的这些关键字。当进行触触发器定义中允许使用任意顺序组合的这些关键字。当进行触发条件的操作时（发条件的操作时（INSERT、UPDATE或或DELETE），将执行），将执行SQL语句中指定的触发器操作。语句中指定的触发器操作。第37页，本讲稿共69页2022/10/1137（5）NOTFORREPLICATION。表示当复制进程。表示当复制进程更改触发器所涉及的表时，不要执行该触发器。更改触发器所涉及的表时，不要执行该触发器。（6）IFUPDATE(列名列名)。测试在指定的列上进行。测试在指定的列上进行的的I

39、NSERT或或UPDATE操作，不能用于操作，不能用于DELETE操作，可以指定多列。因为已经在操作，可以指定多列。因为已经在ON子句中指定子句中指定了表名，所以在了表名，所以在IFUPDATE子句中的列名前不要子句中的列名前不要包含表名。若要测试在多个列上进行的包含表名。若要测试在多个列上进行的INSERT或或UPDATE操作，要分别单独地指定操作，要分别单独地指定UPDATE(列名列名)子句。在子句。在INSERT操作中操作中IFUPDATE将返将返回回TRUE值。值。第38页，本讲稿共69页2022/10/11381UPDATE触发器触发器【例【例10.12】创建一个触发器】创建一个触发

40、器,当有人试图更新当有人试图更新SALES数数据库中据库中goods表的商品编号或进货员工编号时表的商品编号或进货员工编号时,利用触利用触发器产生提示信息发器产生提示信息.并取消修改操作并取消修改操作.-创建触发器创建触发器CREATETRIGGERupdate_trigONgoodsFORupdateASIFUPDATE(商品编号商品编号)ORUPDATE(进货员工编号进货员工编号)BEGINRAISERROR(商品编号或进货员工编号不能进行修改商品编号或进货员工编号不能进行修改!,7,2)ROLLBACKTRANSACTIONEND第39页，本讲稿共69页2022/10/1139-测试触发

41、器测试触发器UpdategoodsSet商品编号商品编号=120Where数量数量=8第40页，本讲稿共69页2022/10/11402INSERT触发器触发器【例【例10.11】创建一个】创建一个AFTERINSERT触发器触发器,当在当在SALES数据库的数据库的employees表中插入一条新员工记表中插入一条新员工记录时录时,如果不是如果不是”采购部采购部”,”财务部财务部”,”销售部销售部”或或”库存部库存部”的员工的员工,则撤消该插入操作则撤消该插入操作,并返回出错并返回出错消息消息.-创建触发器创建触发器第41页，本讲稿共69页2022/10/1141CREATETRIGGERi

42、nsert_trigONemployeesFORinsertASDeclaredepartvarchar(16)Selectdepart=employees.部门部门fromemployees,insertedWhereemployees.编号编号=inserted.编号编号Ifdepart采购部采购部ordepart财务部财务部ordepart销销售部售部ordepart库存部库存部BEGINROLLBACKTRANSACTIONRAISERROR(不能插入非本公司设定部门的员工信息不能插入非本公司设定部门的员工信息!,16,10)END第42页，本讲稿共69页2022/10/1142-测试

43、触发器测试触发器Insertemployess(编号编号,姓名姓名,性别性别,部门部门,电话电话,地地址址)Values(1511,杨过杨过,人事部人事部,1201200,.南昌市广场南路南昌市广场南路156号号)第43页，本讲稿共69页2022/10/11433DELETE触发器触发器【例【例10.13】在】在sales数据库的数据库的emplyoees表和表和sell表表之间具有逻辑上的主外键关系之间具有逻辑上的主外键关系,要求当删除或更新员要求当删除或更新员工记录的时候工记录的时候,要触发触发器要触发触发器update_delete_trig,在在sell表中也删除或更新相对应的记录行。

44、表中也删除或更新相对应的记录行。-创建触发器创建触发器第44页，本讲稿共69页2022/10/1144CREATETRIGGERupdate_delete_trigONemployeesFORupdate,deleteASDeclaredelcountintDeclareempidchar(6)-更新更新Ifupdate(编号编号)BeginUpdatesellSet售货员工编号售货员工编号=(select编号编号frominserted)Where售货员工编号售货员工编号in(select编号编号fromdeleted)End第45页，本讲稿共69页2022/10/1145-删除删除Sele

45、ctdelcount=count(*)fromdeletedIfdelcount0BeginSelectempid=编号编号fromdeletedDeletefromsellwhere售货员工编号售货员工编号=empidEND第46页，本讲稿共69页2022/10/11466 6触发器案例分析触发器案例分析1 1(1)问题：问题：解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。(2)分析：分析：q在交易信息表上创建INSERT触发器 q从inserted临时表中获取插入的数据行q根据交易类型(transType)字段的值是存入/支

46、取，q 增加/减少对应帐户的余额。第47页，本讲稿共69页2022/10/1147CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney，myCardID=cardID FROM inserted IF(type=支取)UPDATE bank SET currentMoney=currentMoney-o

47、utMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID .GO 从inserted表中获取交易类型、交易金额等根据交易类型，减少或增加对应卡号的余额(3)实现方法实现方法第48页，本讲稿共69页2022/10/11486 6触发器案例分析触发器案例分析2 2如果你是图书管数据库管理员如果你是图书管数据库管理员,现在有现在有Student学生表和学生表和BorrowRecord学生借书记录表学生借书记录表,现有两现有两需求要你去实现其功能

48、需求要你去实现其功能:(1)、如果我更改了学生的学号、如果我更改了学生的学号,我希望他的借书记录我希望他的借书记录仍然与这个学生相关仍然与这个学生相关(也就是同时更改借书记录表的也就是同时更改借书记录表的学号学号)(2)、如果该学生已经毕业，我希望删除他的学号的、如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。同时，也删除它的借书记录。第49页，本讲稿共69页2022/10/1149分析如何实现功能分析如何实现功能1:A在哪张表中创建触发器在哪张表中创建触发器?STUDENTB通过什么事件触发通过什么事件触发?UPDATEC事件触发后所要做的事情事件触发后所要做的事情?ifU

49、pdate(StudentID)BEIGINUpdateBorrowRecordSetStudentID=i.StudentIDFromBorrowRecordbr,Deletedd,InsertediEND第50页，本讲稿共69页2022/10/1150分析如何实现功能分析如何实现功能分析如何实现功能分析如何实现功能2:2:A在哪张表中创建触发器在哪张表中创建触发器?STUDENTB通过什么事件触发通过什么事件触发?deleteC事件触发后所要做的事情事件触发后所要做的事情?DeleteBorrowRecordFromBorrowRecordbr,DelteddWherebr.Student

50、ID=d.StudentID 第51页，本讲稿共69页2022/10/11515INSTEADOF触发器触发器如果视图的数据来自于多个基表，则必须如果视图的数据来自于多个基表，则必须使用使用INSTAEDOF触发器支持引用表中的数据的触发器支持引用表中的数据的插入、更新和删除操作。插入、更新和删除操作。q如果视图的列为以下几种情况之一：如果视图的列为以下几种情况之一：q基表中的计算列基表中的计算列q基表中的标识列基表中的标识列q具有具有timestamp数据类型的基表列数据类型的基表列q该视图的该视图的INSERT语句必须为这些列指定值，语句必须为这些列指定值，INSTEADOF触发器在构成将