第十章存储过程触发器及自定义函数2

1、数据库实用技术第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数存储过程存储过程1触发器触发器2用户自定义函数用户自定义函数3sql server 2008实训：实训：t-sql编程的高级应用编程的高级应用4数据库实用技术q 为什么需要触发器(trigger)呢？典型的应用就是银行的取款机系统 为什么需要触发器为什么需要触发器演示： 为什么需要触发器.sql帐户信息表bank 交易信息表transinfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：l它是一种特殊的存储过程 l也具备事务的功能 l它能在多表之间执行特殊的业务规则 张三开户100

2、0元,李四开户1元 数据库实用技术张三张三李四李四王五王五赵二赵二王三王三宋二宋二刘五刘五插入什么是触发器 删除触发器触发赵二退休 赵二赵二员工表退休员工表数据库实用技术10.2 10.2 触发器触发器v 10.2.1 触发器概念l 触发器（trigger）是在对表进行插入、更新或删除操作时自动执行的存储过程。q 触发器通常用于强制业务规则q 触发器是一种高级约束，可以定义比用check 约束更为复杂的约束 q 可执行复杂的sql语句（if/while/case）q 可引用其它表中的列 第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实

3、用技术n 触发器的优点（1）触发器自动执行；（2）可以调用存储过程；（3）可以强化数据条件约束；常用来实现复杂的数据完整性。（4）触发器可以禁止或回滚违反引用完整性的更改；（5）级联、并行运行；（6）触发器可以嵌套。数据库实用技术10.2 10.2 触发器触发器l sql server 2008提供三类触发器：udml触发器： 在数据库中发生数据操作（如：insert、update、delete）事件时自动执行。uddl触发器： 在服务器或数据库中发生数据定义（如：create、alter、drop）事件时自动执行。该类触发器可用于执行一些数据库管理任务。u登录触发器： 在与sql serve

4、r实例建立用户会话时自动执行，主要用来审核和控制服务器会话。为响应 logon 事件而激发存储过程。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术q 触发器触发时：q系统自动在内存中创建deleted表或inserted表q只读，不允许修改；触发器执行完成后，自动删除q inserted 表 q临时保存了插入或更新后的记录行 q可以从inserted表中检查插入的数据是否满足业务需求q 如果不满足，则向用户报告错误消息，并回滚插入操作q deleted 表q临时保存了删除或更新前的记录行 q可以从deleted表中检查被删除的

5、数据是否满足业务需求q 如果不满足，则向用户报告错误消息，并回滚插入操作 inserted inserted 和deleted deleted 表2-12-1数据库实用技术 inserted inserted 和deleted deleted 表2-22-2修改操作inserted表deleted表增加增加(insert)记录记录存放新增的记录存放新增的记录-删除删除(delete)记录记录-存放被删除的记录存放被删除的记录修改修改(update)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录inserted表和deleted表存放的信息数据库实用技术触发器触发器v

6、10.2.2 创建触发器l 1.dml触发器udml触发器在用户对表中的数据进行插入（insert）、修改（update）和删除（delete）时自动运行。u使用触发器模板创建触发器（略）： 在展开sql server实例中，依次展开“数据库”“billingsys”“表”，继续展开要创建触发器的具体表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器的编程模板。 当模板修改完成后，单击窗口工具栏中的“执行”按钮，创建该触发器。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器

7、触发器比如选择customer表，打开触发器模板，其模板代码如下：create trigger . on after as begin- set nocount on added to prevent extra result sets from- interfering with select statements.set nocount on; - insert statements for trigger hereendgo第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器u使用create trigger创建

8、dml触发器：语法格式如下：create trigger on with encryptionfor|after|instead of -触发器的类型insert,update,delete -指定数据修改操作ast-sql语句或语句块;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器参数说明如下：触发器名：触发器名称，必须遵守标识符命名规则，不能以#或#开头。with encryption：指定对触发器进行加密处理。for|after：指定触发器中在相应的dml操作（insert、update、delete）成功执

9、行后才触发。视图上不能定义for和after触发器，只能定义instead of触发器。instead of：指定执行dml触发器用于“代替”引发触发器执行的insert、update或delete语句。在表或视图上，每个insert、update和delete语句最多可以定义一个instead of触发器。 insert,update,delete：指定能够激活触发器的操作，必须至少指定一个操作。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器【例10-4】在联系人表relationer中修改联系人id（rid）后

10、，同步修改客户表customer中的联系人id（rid），保证修改记录满足参照完整性。u这是一个update触发器，其代码如下：create trigger update_ridon relationerafter updateas begin set nocount on update customer set rid=(select rid from inserted) where rid=(select rid from deleted) endgo第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器u在对rela

11、tioner表中rid值进行修改时，需要执行update命令。当执行update命令时，会激发relationer表中的触发器update_rid，同步对customer表中的rid列值进行修改。由于customer表中的rid列存在外键约束fk_customer，因此，在执行update命令前，需要对外键约束进行禁用设置，执行完update命令后，再启用外键约束。具体代码如下：第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器-禁用外键约束alter table customer nocheck constraint

12、fk_customer -更新纪录 update relationer set rid=20106609 where rid=20101845-启用约束alter table customer check constraint fk_customer第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术【例】创建触发器stu_delete，实现如下功能：当按照学号删除student表中的某学生记录后，对应的该学生在sc表中的记录也被自动删除。v t-sql语句为：use stuinfogocreate trigger stu_dele

13、te on studentfor deleteasdelete from sc where sno=(select sno from deleted)v 执行后，我们查询student表和sc表，可以看到两个表中均存在学号为20070102的学生记录。v 在student表中执行数据删除语句：delete from student where sno=20070102delete from student where sno=20070102v student表中有一行受影响而sc表中有三行数据受影响。说明设定的触发器被触发，sc表中的相应数据被自动删除。数据库实用技术触发器触发器l2.ddl

14、触发器u使用create trigger创建 ddl触发器： 语法格式如下：create trigger on all server|database -指定触发器的作用域with encryptionfor|after -触发器的类型,nas ;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器 参数说明如下：all server：指定 ddl触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现命令中指定的事件类型或事件组，就会激发该触发器。database：指定ddl触发器的作用域为当前

15、数据库。如果指定了此参数，则只要当前数据库中出现命令中指定的事件类型或事件组，就会激发该触发器。with encryption：对 create taigger语句的文本进行加密处理。for|after：指定ddl触发器仅在命令中指定事件类型或事件组的所有操作都已成功执行时才被触发。事件类型：将激活ddl 触发器的t-sql语言事件的名称。例如create_table、alter_table、drop_table等操作。事件组：预定义的t-sql语句事件分组的名称。执行任何属于事件组的t-sql语句事件之后，都将激发ddl触发器。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定

16、义函数sql server 2008数据库实用技术触发器触发器【例10-5】设计 ddl触发器，禁止修改和删除当前数据库中的任何表。u代码如下：create trigger safe_databaseon databasefor alter_table,drop_tableasbegin print 不能修改或删除表！如果必须要完成此操作，请先禁用触发器safe_database。 rollbackendgo第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器v 10.2.3管理触发器l 1.查看触发器信息u因为触发器是

17、特殊的存储过程，所以查看触发器也是使用系统存储过程实现。如： sp_help 触发器名称。 sp_helptext 触发器名称。 sp_depends 触发器名称|表名。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术ddlddl触发器的应用【例】创建服务器范围的ddl触发器，当创建数据库时，系统返回提示信息：“database created”。vt-sql语句为：create trigger trig_createon all serverfor create_databaseasprint database created

18、v运行创建触发器后，使用语句：create database demov运行结果，消息栏内出现我们设定的“database created”数据库实用技术触发器触发器l 2.修改触发器u修改dml触发器，语法格式如下：alter trigger on with encryptionfor|after|instead ofinsert,update,deleteas ;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器u修改ddl触发器，其语法格式如下：alter trigger on all server|databa

19、sewith encryptionfor|after ,nas ;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器l 3.删除触发器u如果确认触发器已经不再需要，可以使用drop trigger命令将其删除。u其语法格式如下：drop trigger u可以使用对象资源管理器来完成触发器的管理。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术触发器触发器l 4.禁用与启用触发器u当暂时不需要某个触发器时，可将其禁用。u禁用触发器的语法格式如下：disa

20、ble trigger on 对象名|database|all serveru已禁用的触发器可以被重新启用。u启用触发器的语法格式如下：enable trigger on 对象名|database|all server第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术10.3 10.3 用户自定义函数用户自定义函数v 10.3.1 自定义函数的基本概念l 用户自定义函数是为了实现某些功能，用户对多个t-sql语句进行封装，并返回结果。l 用户自定义函数与存储过程的比较：第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自

21、定义函数sql server 2008比较项比较项用户自定义函数用户自定义函数存储过程存储过程参数允许有0到多个输入参数，不允许有输出参数允许有多个输入/输出参数返回值有且只有一个返回值可以没有返回值调用在表达式或赋值语句中引用使用execute调用数据库实用技术用户自定义函数用户自定义函数v 1.自定义函数的基本概念l 使用用户定义函数的优点如下：u模块化程序设计：将特定的功能封闭在一个用户定义函数中，并存储在数据库中。这个函数只需创建一次，以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。 u执行速度快：与存储过程相似，用户定义函数实施缓存计划。即用户定义函数只需编

22、译一次，以后可以多次重用，从而降低了t-sql代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。u减少网络流量：和存储过程一样可以减少网络通信的流量。此外，用户定义函数还可以用在where子句中,在服务器端过滤数据，以减少发送至客户端的数字或行数。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术用户自定义函数用户自定义函数l2.自定义函数的分类：u标量值函数： 标量值函数的返回值是返回子句（returns子句）中定义的类型的单个数据值，不能返回多个值。 u内嵌表值函数： 内嵌表值函数返回

23、的是在returns子句中指定的“table”类型的数据行集（表）。在内嵌表值函数中，retuan子句在括号中含有一条单独的select查询语句，该语句的结果构成了内嵌表值函数所返回的表。u多语句表值函数： 与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集（表）。与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的tsql语句，并且returns子句指定的表带有列及其数据类型。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术用户自定义函数用户自定义函数v 10.3.2 创建自定义函数l 1.创建标量值

24、函数：u标量值函数的函数体由一条或多条tsql语句组成，写在begin与end之间。u其语法格式如下：create function (形参名 数据类型,n)returns 返回值数据类型with encryptionasbegin return 返回表达式endu参数说明如下： 返回值的数据类型不能是text、ntext、image和timestamp类型。 在beginend之间，必须有一条return语句，用于指定返回表达式，即函数的值。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术用户自定义函数用户自定义函数【例10-

25、6】定义函数eproduct_number，当给出一个客户id，返回该客户所拥有的产品数量。【分析】u该自定义函数接收的参数是客户id，数据类型应与eproduct中的cid一致；返回的值是产品数，该数据量一般不大，可以设为smallint类型。u代码如下：create function eproduct_number(ep_cid int)returns smallintasbegin declare epnum smallint select epnum =count(*) from eproduct where cid=ep_cid return epnumendgo第十章第十章 存储过

26、程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术n标量值函数的调用 n在select语句中调用：n调用形式： select 所有者名称.函数名称(实参1，实参n)n实参为已赋值的局部变量或表达式，实参的顺序要与函数创建时的顺序完全一致。 数据库实用技术【例】【例】 对创建的函数对创建的函数eproduct_table_1进行调用。进行调用。select dbo.select dbo.eproduct_table_1( 20100146) 20100146)数据库实用技术n利用利用execexec语句执行语句执行 n调用形式：调用形式：nexec e

27、xec 变量名变量名= = 所有者名称所有者名称. .函数名称函数名称( (实参实参1 1，实参实参n) n) 或或nexec exec 变量名变量名=schema_name.function_name =schema_name.function_name 形形参名参名1=1=实参实参1 1，形参名形参名n=n=实参实参n n 数据库实用技术【例】利用【例】利用execexec调用函数调用函数getaveragegetaverage。在查询编辑器中，输入以下代码：在查询编辑器中，输入以下代码：use jxgluse jxgldeclare age intdeclare age intexec

28、age=dbo.getaverage exec age=dbo.getaverage tabname=students,sex=tabname=students,sex=男男 print print 男学生的平均年龄：男学生的平均年龄：+convert(varchar(10),age)+convert(varchar(10),age)数据库实用技术用户自定义函数用户自定义函数l 2.创建内联表值函数：u语法格式如下：create function (形参名 数据类型,n)returns tablewith encryptionas return(select查询语句)u参数说明如下： 内联表值

29、函数没有函数体。 returns table子句说明返回值是一个表。 return子句中的select语句是返回表中的数据。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术用户自定义函数用户自定义函数【例10-7】定义函数eproduct_table，当给出一个客户id，返回该客户所拥有的产品号码。u代码如下：create function eproduct_table(ep_cid int)returns tableas return (select eno,ename from eproduct where cid=ep_c

30、id第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术内联表值函数n内联表值函数的调用内联表值函数的调用 n内联表值函数只能通过内联表值函数只能通过selectselect语句进行调用，语句进行调用，调用时，可以仅使用函数名。调用时，可以仅使用函数名。n【例】【例】 调用函数调用函数getcoursescoregetcoursescore，查询学，查询学生张小峰所选修的课程及其成绩。生张小峰所选修的课程及其成绩。nuse jxgluse jxglnselect select * * from getcoursescore( fro

31、m getcoursescore(张小峰张小峰) )ngogo数据库实用技术3.3.多语句表值函数n多语句表值函数和内联表值函数都返回表，多语句表值函数和内联表值函数都返回表，二者的不同之处在于：内联表值函数没有函二者的不同之处在于：内联表值函数没有函数主体，返回的表是单个数主体，返回的表是单个selectselect语句的结果语句的结果集；而多语句表值函数在集；而多语句表值函数在beginbeginendend块中块中定义的函数主体由定义的函数主体由t-sqlt-sql语句序列构成，这语句序列构成，这些语句可生成记录行并将行插入到表中，最些语句可生成记录行并将行插入到表中，最后返回表。后返回

32、表。数据库实用技术用户自定义函数用户自定义函数l 创建多语句表值函数：u语法格式如下：create function (形参名 数据类型,n)returns 返回变量 table (表结构定义)with encryptionasbegin returnendu参数说明如下： returns 返回变量子句指明该函数的返回局部变量，该变量的数据类型是table，而且在该子句中还需要对返回的表进行表结构的定义。 在beginend之间的语句是函数体，函数体中必须包括一条不带参数的return语句用于返回表。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2

33、008数据库实用技术用户自定义函数用户自定义函数【例10-8】定义多语句表值函数eproduct_table_1，完成上例功能。u代码如下：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 wher

34、e cid=ep_cid returnend第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术多语句表值函数的调用n 应用举例应用举例 n【例】【例】 调用多语句表值函数调用多语句表值函数eproduct_table_1 ，查询，查询2010014620100146客户拥有客户拥有的产品。的产品。n在查询分析器中输入如下代码：在查询分析器中输入如下代码：nselect select * * from from eproduct_table_1 20100146)20100146)数据库实用技术用户自定义函数用户自定义函数u查看多语句表值函数： 多语句表值函数创建后，可以在sql server实例中，依次展开 “数据库”“billingsys”“可编程性”“函数”“表值函数”节点，即可看到dbo.eproduct_table_1函数。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数sql server 2008数据库实用技术用户自定义函数用户自定义函数l 4.使用菜单命令创建函数：u在“对象资源管理器”中也可以完成创建函数的操作： 新建标量值函数 新建表值函数 第十章第十章 存