《存储过程触发器及自定义函数.ppt》由会员分享，可在线阅读，更多相关《存储过程触发器及自定义函数.ppt（49页珍藏版）》请在三一办公上搜索。
1、数据库实用技术SQL Server 2008,第十章 存储过程、触发器 和自定义函数,第十章 存储过程、触发器、自定义函数,SQL Server 2008,为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统,为什么需要触发器,演示：为什么需要触发器.sql,帐户信息表bank,交易信息表transInfo,张三取钱200 问题：没有自动修改张三的余额,最优的解决方案就是采用触发器：它是一种特殊的存储过程 也具备事务的功能 它能在多表之间执行特殊的业务规则,张三开户1000元,李四开户1元,赵二,插入,什么是触发器,删除,触发器触发,赵二退休,赵二,员工表,退休员工表,10.2
2、 触发器,10.2.1 触发器概念触发器（Trigger）是在对表进行插入、更新或删除操作时自动执行的存储过程。触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case）可引用其它表中的列,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器的优点（1）触发器自动执行；（2）可以调用存储过程；（3）可以强化数据条件约束；常用来实现复杂的数据完整性。（4）触发器可以禁止或回滚违反引用完整性的更改；（5）级联、并行运行；（6）触发器可以嵌套。,10.2 触发器,SQL Server 200
3、8提供三类触发器：DML触发器：在数据库中发生数据操作（如：INSERT、UPDATE、DELETE）事件时自动执行。DDL触发器：在服务器或数据库中发生数据定义（如：CREATE、ALTER、DROP）事件时自动执行。该类触发器可用于执行一些数据库管理任务。登录触发器：在与SQL Server实例建立用户会话时自动执行，主要用来审核和控制服务器会话。为响应 LOGON 事件而激发存储过程。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted 表
4、 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作,inserted 和deleted 表2-1,inserted 和deleted 表2-2,inserted表和deleted表存放的信息,触发器,10.2.2 创建触发器1.DML触发器DML触发器在用户对表中的数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。使用
5、触发器模板创建触发器（略）：在展开SQL Server实例中，依次展开“数据库”“BillingSys”“表”，继续展开要创建触发器的具体表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器的编程模板。当模板修改完成后，单击窗口工具栏中的“执行”按钮，创建该触发器。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,比如选择Customer表，打开触发器模板，其模板代码如下：CREATE TRIGGER.ON AFTER AS BEGIN-SET NOCOUNT ON added to prevent extra re
6、sult sets from-interfering with SELECT statements.SET NOCOUNT ON;-Insert statements for trigger hereENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,使用CREATE TRIGGER创建 DML触发器：语法格式如下：CREATE TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OF-触发器的类型INSERT,UPDATE,DELETE-指定数据修改操作AST-SQL语句或语句块;,第十章 存储过程、触发器、自定义函数
7、,SQL Server 2008,触发器,参数说明如下：触发器名：触发器名称，必须遵守标识符命名规则，不能以#或#开头。WITH ENCRYPTION：指定对触发器进行加密处理。FOR|AFTER：指定触发器中在相应的DML操作（INSERT、UPDATE、DELETE）成功执行后才触发。视图上不能定义FOR和AFTER触发器，只能定义INSTEAD OF触发器。INSTEAD OF：指定执行DML触发器用于“代替”引发触发器执行的INSERT、UPDATE或DELETE语句。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。INSERT
8、,UPDATE,DELETE：指定能够激活触发器的操作，必须至少指定一个操作。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,【例10-4】在联系人表Relationer中修改联系人ID（RID）后，同步修改客户表Customer中的联系人ID（RID），保证修改记录满足参照完整性。这是一个UPDATE触发器，其代码如下：CREATE TRIGGER Update_RIDON RelationerAFTER UPDATEAS BEGIN SET NOCOUNT ON UPDATE Customer SET RID=(SELECT RID FROM INSERTE
9、D)where RID=(SELECT RID FROM DELETED)ENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,在对Relationer表中RID值进行修改时，需要执行UPDATE命令。当执行UPDATE命令时，会激发Relationer表中的触发器Update_RID，同步对Customer表中的RID列值进行修改。由于Customer表中的RID列存在外键约束fk_Customer，因此，在执行UPDATE命令前，需要对外键约束进行禁用设置，执行完UPDATE命令后，再启用外键约束。具体代码如下：,第十章 存储过程、触发器、自定义函数,S
10、QL Server 2008,触发器,-禁用外键约束ALTER TABLE Customer NOCHECK CONSTRAINTfk_Customer-更新纪录 UPDATE Relationer SET RID=20106609 where RID=20101845-启用约束ALTER TABLE Customer CHECK CONSTRAINT fk_Customer,第十章 存储过程、触发器、自定义函数,SQL Server 2008,【例】创建触发器stu_delete，实现如下功能：当按照学号删除student表中的某学生记录后，对应的该学生在sc表中的记录也被自动删除。T-SQ
11、L语句为：USE stuinfoGOCREATE TRIGGER stu_Delete ON studentFOR DELETEASDELETE FROM sc WHERE sno=(SELECT sno FROM deleted)执行后，我们查询student表和sc表，可以看到两个表中均存在学号为20070102的学生记录。在student表中执行数据删除语句：DELETE FROM student WHERE sno=20070102student表中有一行受影响而sc表中有三行数据受影响。说明设定的触发器被触发，sc表中的相应数据被自动删除。,触发器,2.DDL触发器使用CREATE
12、TRIGGER创建 DDL触发器：语法格式如下：CREATE TRIGGER ON ALL SERVER|DATABASE-指定触发器的作用域WITH ENCRYPTIONFOR|AFTER-触发器的类型,nAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,参数说明如下：ALL SERVER：指定 DDL触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现命令中指定的事件类型或事件组，就会激发该触发器。DATABASE：指定DDL触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中出现命令中指定的事件类型或事件组，就会
13、激发该触发器。WITH ENCRYPTION：对 CREATE TAIGGER语句的文本进行加密处理。FOR|AFTER：指定DDL触发器仅在命令中指定事件类型或事件组的所有操作都已成功执行时才被触发。事件类型：将激活DDL 触发器的T-SQL语言事件的名称。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE等操作。事件组：预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后，都将激发DDL触发器。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,【例10-5】设计 DDL触发器，禁止修改和删除当前数据库中的任何
14、表。代码如下：CREATE TRIGGER safe_databaseON DATABASEFOR ALTER_TABLE,DROP_TABLEASBEGIN PRINT 不能修改或删除表！如果必须要完成此操作，请先禁用触发器safe_database。ROLLBACKENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,管理触发器1.查看触发器信息因为触发器是特殊的存储过程，所以查看触发器也是使用系统存储过程实现。如：Sp_help 触发器名称。sp_helptext 触发器名称。sp_depends 触发器名称|表名。,第十章 存储过程、触发器、自定义函
15、数,SQL Server 2008,DDL触发器的应用,【例】创建服务器范围的DDL触发器，当创建数据库时，系统返回提示信息：“DATABASE CREATED”。T-SQL语句为：CREATE TRIGGER trig_createON ALL SERVERFOR CREATE_DATABASEASPRINT DATABASE CREATED运行创建触发器后，使用语句：CREATE DATABASE demo运行结果，消息栏内出现我们设定的“DATABASE CREATED”,触发器,2.修改触发器修改DML触发器，语法格式如下：ALTER TRIGGER ON WITH ENCRYPTIO
16、NFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,修改DDL触发器，其语法格式如下：ALTER TRIGGER ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER,nAS;,第十章 存储过程、触发器、自定义函数,SQL Server 2008,触发器,3.删除触发器如果确认触发器已经不再需要，可以使用DROP TRIGGER命令将其删除。其语法格式如下：DROP TRIGGER 可以使用对象资源管理器来完成触发器的管理。,第十章 存储过
17、程、触发器、自定义函数,SQL Server 2008,触发器,4.禁用与启用触发器当暂时不需要某个触发器时，可将其禁用。禁用触发器的语法格式如下：DISABLE TRIGGER ON 对象名|DATABASE|ALL SERVER已禁用的触发器可以被重新启用。启用触发器的语法格式如下：ENABLE TRIGGER ON 对象名|DATABASE|ALL SERVER,第十章 存储过程、触发器、自定义函数,SQL Server 2008,10.3 用户自定义函数,10.3.1 自定义函数的基本概念用户自定义函数是为了实现某些功能，用户对多个T-SQL语句进行封装，并返回结果。用户自定义函数与存
18、储过程的比较：,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,1.自定义函数的基本概念使用用户定义函数的优点如下：模块化程序设计：将特定的功能封闭在一个用户定义函数中，并存储在数据库中。这个函数只需创建一次，以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。执行速度快：与存储过程相似，用户定义函数实施缓存计划。即用户定义函数只需编译一次，以后可以多次重用，从而降低了T-SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。减少网络流量：和存储过程一样可以减少网络通信的流量。此外，用户定义
19、函数还可以用在WHERE子句中,在服务器端过滤数据，以减少发送至客户端的数字或行数。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,2.自定义函数的分类：标量值函数：标量值函数的返回值是返回子句（RETURNS子句）中定义的类型的单个数据值，不能返回多个值。内嵌表值函数：内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集（表）。在内嵌表值函数中，RETUAN子句在括号中含有一条单独的SELECT查询语句，该语句的结果构成了内嵌表值函数所返回的表。多语句表值函数：与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集（
20、表）。与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的TSQL语句，并且RETURNS子句指定的表带有列及其数据类型。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,10.3.2 创建自定义函数1.创建标量值函数：标量值函数的函数体由一条或多条TSQL语句组成，写在BEGIN与END之间。其语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表达式END参数说明如下：返回值的数据类型不能是text、ntext、image和times
21、tamp类型。在BEGINEND之间，必须有一条RETURN语句，用于指定返回表达式，即函数的值。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,【例10-6】定义函数EProduct_Number，当给出一个客户ID，返回该客户所拥有的产品数量。【分析】该自定义函数接收的参数是客户ID，数据类型应与EProduct中的CID一致；返回的值是产品数，该数据量一般不大，可以设为smallint类型。代码如下：CREATE FUNCTION EProduct_Number(EP_CID int)RETURNS smallintASBEGIN DECLARE
22、epnum smallint SELECT epnum=count(*)FROM EProduct WHERE CID=EP_CID RETURN epnumENDGO,第十章 存储过程、触发器、自定义函数,SQL Server 2008,标量值函数的调用 在select语句中调用：调用形式：Select 所有者名称.函数名称(实参1，实参n)实参为已赋值的局部变量或表达式，实参的顺序要与函数创建时的顺序完全一致。,【例】对创建的函数EProduct_Table_1进行调用。SELECT dbo.EProduct_Table_1(20100146),利用Exec语句执行 调用形式：EXEC 变
23、量名=所有者名称.函数名称(实参1，实参n)或EXEC 变量名=Schema_name.function_name 形参名1=实参1，形参名n=实参n,【例】利用Exec调用函数getAverAge。在查询编辑器中，输入以下代码：USE jxglDECLARE age intEXEC age=dbo.getaverage tabname=students,sex=男PRINT 男学生的平均年龄：+CONVERT(varchar(10),age),用户自定义函数,2.创建内联表值函数：语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS TableWITH ENCR
24、YPTIONAS RETURN(SELECT查询语句)参数说明如下：内联表值函数没有函数体。RETURNS Table子句说明返回值是一个表。RETURN子句中的SELECT语句是返回表中的数据。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,【例10-7】定义函数EProduct_Table，当给出一个客户ID，返回该客户所拥有的产品号码。代码如下：CREATE FUNCTION EProduct_Table(EP_CID int)RETURNS TableAS RETURN(SELECT ENO,EName FROM EProduct WHERE c
25、id=EP_CID,第十章 存储过程、触发器、自定义函数,SQL Server 2008,内联表值函数,内联表值函数的调用 内联表值函数只能通过Select语句进行调用，调用时，可以仅使用函数名。【例】调用函数getCourseScore，查询学生张小峰所选修的课程及其成绩。USE jxglSELECT*FROM getCourseScore(张小峰)GO,3.多语句表值函数,多语句表值函数和内联表值函数都返回表，二者的不同之处在于：内联表值函数没有函数主体，返回的表是单个Select语句的结果集；而多语句表值函数在BeginEnd块中定义的函数主体由T-SQL语句序列构成，这些语句可生成记录
26、行并将行插入到表中，最后返回表。,用户自定义函数,创建多语句表值函数：语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回变量 Table(表结构定义)WITH ENCRYPTIONASBEGIN RETURNEND参数说明如下：RETURNS 返回变量子句指明该函数的返回局部变量，该变量的数据类型是Table，而且在该子句中还需要对返回的表进行表结构的定义。在BEGINEND之间的语句是函数体，函数体中必须包括一条不带参数的RETURN语句用于返回表。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,【例10-8】定义
27、多语句表值函数EProduct_Table_1，完成上例功能。代码如下：CREATE FUNCTION EProduct_Table_1(EP_CID int)RETURNS tb Table-定义tb表变量的结构，其中的列名可以和原数据表名不一样，但数据类型要一致。(tb_Eno char(11),tb_EName varchar(50),tb_EJoinData DateTime)ASBEGIN INSERT INTO tb SELECT ENO,EName,EJoinDate FROM EProduct WHERE CID=EP_CID RETURNEND,第十章 存储过程、触发器、自定
28、义函数,SQL Server 2008,多语句表值函数的调用,应用举例【例】调用多语句表值函数EProduct_Table_1，查询20100146客户拥有的产品。在查询分析器中输入如下代码：SELECT*FROM EProduct_Table_1 20100146),用户自定义函数,查看多语句表值函数：多语句表值函数创建后，可以在SQL Server实例中，依次展开“数据库”“BillingSys”“可编程性”“函数”“表值函数”节点，即可看到dbo.EProduct_Table_1函数。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,4.使用菜单命令
29、创建函数：在“对象资源管理器”中也可以完成创建函数的操作：新建标量值函数 新建表值函数,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,10.3.3 修改和删除自定义函数1.查看用户自定义函数：sp_help 函数名称：用于查看函数的一般信息，如函数的名称及相关参数。sp_helptext 函数名称：用于查看函数的正文信息。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,用户自定义函数,2.修改用户自定义函数：语法格式如下：ALTER FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIO
30、NASBEGIN RETURN 返回表达式END3.删除用户自定义函数:语法格式如下：DROP FUNCTION 4.使用菜单命令管理自定义函数：在“对象资源管理器”中选择需要修改或删除的自定义函数，右击，选择相应的菜单命令执行操作即可。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,实验8：T-SQL编程的高级应用,1.存储过程应用编写一个存储过程，实现由产品表（EProduct）中的单价（EUnivalence）列按月统计产品的通信费用数据，并为帐单信息表（Bills）添加一数据行。2.触发器应用一般情况下产品表EProduct中的客户ID（CID）是相对不变的，只
31、有一种情况会发生变化，即原来客户已不再使用该产品了，相隔一段时间后有新的客户重新使用它，因此发生CID的变更。创建一个触发器，在产品表EProduct中修改客户ID（CID）后，如果在开通服务表户表StartAdditionalService有相应的产品，同步删除包含该产品号码的数据行，保证EProduct表中修改记录满足参照完整性。3.自定义函数应用定义函数EProduct_AddNumber，当给出一个产品号码，返回该产品所绑定的附加服务数量。,第十章 存储过程、触发器、自定义函数,SQL Server 2008,小结,存储过程基本概念。创建及管理存储过程（创建、修改、删除）调用存储过程。触发器基本概念。创建触发器（创建DML，DDL）管理触发器（查看、修改、删除、禁用、启用）用户自定义函数基本概念。创建自定义函数（创建标量值、内联表值、多语句表值函数）管理自定义函数（查看、修改、删除等）,第十章 存储过程、触发器、自定义函数,SQL Server 2008,Thank You!,
