SQL-Server-2012数据库技术及应用2.8.ppt

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,Microsoft,SQL Server 2012,SQL Server 2012,数据库技术及应用,人民邮电出版社,*,教学单元,2.8,第,10,章 数据库服务器编程,SQL Server 2012,数据库技术及应用,案例,2-10-1,图书管理存储过程的创建与管理,案例,2-10-2,图书管理触发器的创建与管理,案例,2-10-3,图书管理用户定义函数的创建与管理,数据库服务器编程,学习导航,2,数据库服务器编程 知识框架,3,单元,2.8,数据库服务器编程,能力目标,能够根据,数据库应用系统的功能需求和完整性需求,设计,存储过程,设计,触发器,设计,用户定义函数,能够阅读并熟练书写存储过程、触发器、用户定义函数有关的,T-SQL,命令（英文）,4,知识目标,存储,过程的概念与编程方法,触发器,的概念与编程方法,用户,定义函数的概念与编程方法,存储,过程、触发器和用户定义函数有关的英文,素质目标,养成,严谨、严格的软件编程职业素养,培养,学习新技术的能力,5,单元,2.8,数据库服务器编程,案例,2,图书管理系统,案例,2-10-1,图书管理存储过程的创建与管理,案例,2-10-2,图书管理触发器的创建与管理,案例,2-10-3,图书管理用户定义函数的创建与管理,工作任务,6,单元,2.8,数据库服务器编程,单元,2.8,数据库服务器编程,7,创建与管理存储过程,一,创建与管理触发器,二,创建与管理用户定义函数,三,一、创建与管理存储过程,案例,2-10-1,图书管理存储过程的创建与管理,根据图书管理系统的功能需求和完整性需求：,创建与管理数据库“,Library”,的存储过程，实现数据操作以及完整性控制。,工作任务,8,一、创建与管理存储过程,9,存储过程概述,1,创建存储过程,2,管理存储过程,3,（一）存储过程概述,存储过程（,Stored Procedure,）,一组编译好的、存储在数据库服务器上的和完成特定功能的,T-SQL,程序，是某数据库的对象。,客户端应用程序可以通过调用（指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。,1.,存储过程的概念,10,（一）存储过程概述,存储过程允许标准组件式编程。,存储过程能够实现较快的执行速度。,能够减少网络流量。,可被作为一种安全机制来充分利用。,2.,使用存储过程的优点,11,（一）存储过程概述,系统提供的存储过程,sp_*,例如,sp_rename,、,sp_help,等,用户定义存储过程,T-SQL,存储过程：,指保存的,T-SQL,程序，可以接受和返回用户提供的参数。存储过程也可能从数据库向客户端应用程序返回数据。,CLR,存储过程：指对,Microsoft.NET Framework,公共语言运行时方法的引用，可以接受和返回用户提供的参数，它们在,.NET Framework,程序集中是作为类的公共静态方法实现的。,3.,存储过程的分类,12,（一）存储过程概述,扩展存储过程,扩展存储过程是以在,SQL Server,环境之外执行的动态链接库（,DLL,，,Dynamic-Link Libraries,）来实现的，通常以前缀,xp_,开头。扩展存储过程用与存储过程相似的方式来执行。,3.,存储过程的分类,13,（二）创建存储过程,例,1,：,为数据库“,Library,”创建一个实现简单查询功能的存储过程，在读者表“,Reader,”中查找读者赵良宇借书数量的信息。,步骤：,在,【,对象资源管理器,】,窗口中，展开“数据库”“,Library,”“可编程性”节点，用右键单击“存储过程”节点，从快捷菜单中选择“新建存储过程”命令,1.,使用,SSMS,创建存储过程,14,（二）创建存储过程,步骤,在,【,查询编辑器,】,中出现存储过程编程模板，在此模板的基础上编写创建存储过程的,T-SQL,代码,1.,使用,SSMS,创建存储过程,15,（二）创建存储过程,步骤：,单击“,SQL,编辑器”工具栏上的“执行”按钮。,1.,使用,SSMS,创建存储过程,16,（二）创建存储过程,调用存储过程的语句,EXEC,存储过程名称,参数表,说明,如果执行存储过程语句是批处理中的第一条语句，那么也可以通过直接指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。,1.,使用,SSMS,创建存储过程,17,（二）创建存储过程,调用存储过程,Borrowed,或,EXEC borrowed,执行结果,1.,使用,SSMS,创建存储过程,18,（二）创建存储过程,语法,CREATE PROCEDURE,过程名,-,创建存储过程,形参名 数据类型,-,输入参数,|,形参名 数据类型,=,默认值,-,默认值参数,|,变参名 数据类型,OUTPUT-,输出参数,.,n,AS,BEGIN,T-SQL,语句,-,过程体,END,2.,使用,T-SQL,创建存储过程,19,（二）创建存储过程,例,2,（无参）：,为数据库“,Library,”创建一个多表查询的存储过程，查询出读者为“程鹏”的借阅信息。,代码,USE Library,GO,CREATE PROCEDURE borrowed_book1,AS,BEGIN,SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate,FROM Reader r INNER JOIN Borrow b ON r.RID=b.RID,INNER JOIN Book k ON b.BID=k.BID,WHERE Rname=,程鹏,END,2.,使用,T-SQL,创建存储过程,20,（二）创建存储过程,调用,borrowed_book1,或,EXEC borrowed_book1,执行结果,2.,使用,T-SQL,创建存储过程,21,（二）创建存储过程,例,3,（值参）：,为数据库“,Library,”创建一个查询某读者（姓名在执行存储过程时给出）借阅情况的存储过程。,代码,USE Library,GO,-,输入形式参数,name,CREATE PROCEDURE borrowed_book2,name,char(8),AS,BEGIN,SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate,FROM Reader r INNER JOIN Borrow b ON r.RID=b.RID,INNER JOIN Book k ON b.BID=k.BID,WHERE Rname=,name,END,2.,使用,T-SQL,创建存储过程,22,（二）创建存储过程,调用,EXEC borrowed_book2,杨淑华,-,实参表,杨淑华,或,DECLARE temp1 char(8),SET temp1=,杨淑华,EXEC borrowed_book2,temp1,-,实参表,temp1,执行结果,2.,使用,T-SQL,创建存储过程,23,（二）创建存储过程,例,4,（默认值参）：,为数据库“,Library,”创建一个查询某读者（姓名在执行存储过程时给出）借阅情况的存储过程。,代码,CREATE PROCEDURE borrowed_book3,name,char(8,)=NULL,-,默认参数,AS,BEGIN,IF,name,IS NULL,-name,为默认值,NULL,，,IF,条件成立,SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate,FROM Reader r INNER JOIN Borrow b ON r.RID=b.RID,INNER JOIN Book k ON b.BID=k.BID,ELSE,SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate,FROM Reader r INNER JOIN Borrow b ON r.RID=b.RID,INNER JOIN Book k ON b.BID=k.BID,WHERE Rname,=name,END,24,（二）创建存储过程,调用,EXEC borrowed_book3,执行结果,2.,使用,T-SQL,创建存储过程,25,（二）创建存储过程,例,5,（输出参数）：,为数据库“,Library,”创建一个查询某出版社图书总价值和平均价值的存储过程。,代码,USE Library,GO,CREATE PROCEDURE book_price -,创建存储过程,Publisher varchar(30),-,输入形式参数,SUMPrice decimal(9,2),OUTPUT,-,输出形式参数,1,AVGPrice decimal(9,2),OUTPUT,-,输出形式参数,2,AS,26,（二）创建存储过程,例,5,（输出参数）：,为数据库“,Library,”创建一个查询某出版社图书总价值和平均价值的存储过程。,代码,BEGIN,-,输出参数,1,得到图书总价值，赋值语句,SELECT,SUMPrice,=SUM(price),FROM Book WHERE Publisher=Publisher,-,输出参数,2,得到图书平均价值，赋值语句,SELECT,AVGPrice,=AVG(price),FROM Book WHERE Publisher=Publisher,END,27,（二）创建存储过程,调用,DECLARE ch varchar(30),ou1 decimal(9,2),ou2 decimal(9,2),SET ch=,人民邮电出版社,GO,EXEC book_price -,调用存储过程,ch,ou1 Output,ou2 Output -,输入实参，输出实参,1,，,2,GO,SELECT ch AS,书名,ou1 AS,总价值,ou2 AS,平均价值,-,输出变量（实参）值,执行结果,2.,使用,T-SQL,创建存储过程,28,（三）管理存储过程,修改,在,【,对象资源管理器,】,窗口中，展开“数据库”“具体数据库”“可编程性”节点，用右键单击“存储过程”节点，从弹出的快捷菜单中选择“修改”,1.,使用,SSMS,管理存储过程,29,（三）管理存储过程,删除等,从弹出的快捷菜单中选择“查看依赖关系”,/,“重命名”,/,“删除”等命令，即可进行相应管理存储过程的操作。,1.,使用,SSMS,管理存储过程,30,（三）管理存储过程,修改,ALTER PROCEDURE,过程名,-,修改存储过程,形参名 数据类型,-,输入参数,|,形参名 数据类型,=,默认值,-,默认值参数,|,变参名 数据类型,OUTPUT-,输出参数,.,n,AS,BEGIN,T-SQL,语句,-,过程主体,END,2.,使用,T-SQL,管理存储过程,31,（三）管理存储过程,删除,DROP PROC EDURE,存储过程名,.,n,例,10,删除数据库“,Library”,的存储过程“,borrowed”,。,代码：,USE Library,DROP PROC borrowed,2.,使用,T-SQL,管理存储过程,32,单元,2.8,数据库服务器编程,33,创建与管理存储过程,一,创建与管理触发器,二,创建与管理用户定义函数,三,案例,2-10-2,图书管理触发器的创建与管理,根据图书管理系统的功能需求和完整性需求：,创建与管理服务器、数据库“,Library”,及其表或视图的触发器，实现数据操作以及完整性控制。,工作任务,34,二、创建与管理触发器,二、创建与管理触发器,35,触发器概述,1,创建,DML,触发器,2,创建,DDL,触发器,3,管理触发器,4,（一）触发器概述,特殊的存储过程，是基于表,/,视图,/,服务器,/,数据库创建的。,触发器里也包含一系列的,T-SQL,语句，但它的执行不是用,EXEC,主动调用的，而是在满足一定条件下自动执行的。,当触发器所保护的数据库中的数据经过操作发生变化或者当服务器、数据库中发生数据定义事件时，系统将自动运行触发器中的程序以保证数据库的完整性、正确性和安全性。,通俗地说，触发器是基于一个基表,/,视图,/,服务器,/,数据库，并通过一个事件被调用的存储过程。,1.,触发器的概念,36,（一）触发器概述,DML,触发器,数据操作语言（,DML,）事件时启用,INSERT,、,UPDATE,、,DELETE,语句触发,作用,系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，多表操作不一致、不符合事务管理的规定和磁盘空间不足等），则整个事务自动回滚。其主要作用是实现较为复杂的数据完整性控制。,2.,触发器的分类,37,（一）触发器概述,DDL,触发器,数据定义语言（,DDL,）事件时启用,CREATE,、,ALTER,、,DROP,语句触发,作用,用于管理任务，例如审核和控制数据库操作。,2.,触发器的分类,38,（一）触发器概述,登录触发器,LOGON,事件触发,作用,用于控制数据库服务器的安全，例如拒绝某登录名启动的,SQL Server,登录尝试。,2.,触发器的分类,39,（二）创建,DML,触发器,例,7,创建基于表“,Book,”，执行,INSERT,操作的,AFTER,触发器。当添加一册图书信息之后，给出“您添加了一册书,!,”的提示。,步骤,在,【,对象资源管理器,】,窗口中，展开“数据库”“,Library,”节点，再展开“表”节点下要创建触发器的“,Book,”节点，右键“触发器”节点，从快捷菜单中选择“新建触发器”命令,1.,使用,SSMS,创建,DML,触发器,40,（二）创建,DML,触发器,步骤,在,【,查询编辑器,】,中出现,触发器编程模板，,在此模板的基础上编写创建存储过程的,T-SQL,代码,1.,使用,SSMS,创建,DML,触发器,41,（二）创建,DML,触发器,步骤,单击“,SQL,编辑器”工具栏上的“执行”按钮，即可在表“,Book,”“触发器”节点，可以看到新建的触发器“,T_BookIN,”,1.,使用,SSMS,创建,DML,触发器,42,（二）创建,DML,触发器,调用触发器,对表“,Book,”执行,INSERT,命令之后调用,假设添加一册图书，有以下,T-SQL,代码：,INSERT Book(BID,Bname,Author,Publisher,Price,LentOut),VALUES(TP311.138/236,SQL Server 2008,数据库设计与实现,Louis Davison,人民邮电出版社,89.0,0),执行结果如下：,您添加了一册书,!,(1,行受影响,),1.,使用,SSMS,创建,DML,触发器,43,（二）创建,DML,触发器,语法,CREATE TRIGGER,ON,FOR|AFTER|INSTEAD OF,UPDATE,INSERT,DELETE,AS,BEGIN,T-SQL,语句,END,2.,使用,T-SQL,创建,DML,触发器,44,（二）创建,DML,触发器,参数说明,FOR|AFTER,：仅在触发,T-SQL,语句中指定的,INSERT/UPDATE/DELETE,语句操作都成功执行之后才被执行。所有的引用级联操作和约束检查也必须在执行此触发器之前成功完成。如果仅指定,FOR,关键字，则,AFTER,为默认值。不能对视图定义,AFTER,触发器。,INSTEAD OF,：为表和视图指定的,DML,触发器用于“替代”引起触发器执行的,T-SQL,语句，因此其优先级高于触发语句的操作。在表或视图上，每个,INSERT,、,UPDATE,和,DELETE,语句最多可以定义一个,INSTEAD OF,触发器。,2.,使用,T-SQL,创建,DML,触发器,45,（二）创建,DML,触发器,临时表,inserted,和,deleted,说明,DML,触发器执行时自动创建两个临时表,inserted,表和,deleted,表，触发器工作完成后即被删除。,inserted,表用于临时保存被插入或被更新后的数据行副本。在执行,INSERT,或,UPDATE,语句时，新的数据行被插入到触发器表中，同时也被插入到,inserted,表中。可以从,inserted,表中读取所插入的数据，进一步进行对其他表的操作。也可以判断所插入的数据是否满足完整性规则，如不满足则可以回滚（撤消）此操作。,2.,使用,T-SQL,创建,DML,触发器,46,（二）创建,DML,触发器,deleted,表用于临时保存被删除或被更新后的数据行副本。在执行,DELETE,或,UPDATE,语句时，从触发器表中删除数据行并传输到,deleted,表中。可以从,deleted,表中检查所删除的数据行是否满足删除条件，如不满足则可以回滚（撤消）此操作。,执行,UPDATE,语句,更新数据时，类似于在删除之后执行插入；首先被删除的数据行被传输到,deleted,表中，然后新的数据行被插入到,inserted,表中。,2.,使用,T-SQL,创建,DML,触发器,47,（二）创建,DML,触发器,INSERT,事件的,AFTER,触发器是对指定的表执行插入数据行语句,INSERT INTO.VALUES,事件之后被激发执行的一段程序代码。,（,1,）创建,INSERT,事件的,AFTER,触发器,48,（二）创建,DML,触发器,例,8,：,读者借书,。,使用,INSERT,语句完成借阅表“,Borrow,”添加借书信息的操作。,使用触发器判断所借书是否已经借出并作相应处理。,尚未借出，则：,计算该书的应还日期“,SReturnDate,”为借期加限借天数；,将读者表“,Reader,”该读者的借阅数量“,Lendnum,”增加,1,本；,将图书表“,Book,”该书是否借出“,LentOut,”置为真。,已经借出，则：,提示此书已经借出；,所添加的借书信息撤销。,（,1,）创建,INSERT,事件的,AFTER,触发器,49,（二）创建,DML,触发器,CREATE TRIGGER T_Borrow -,创建触发器,ON Borrow -,基于表“,Borrow,”,AFTER INSERT AS -INSERT,操作之后激发,BEGIN,DECLARE dzbh char(10),tsbh char(15),dzlx int,xjts int,-,从添加行副本,inserted,中查询出,RID,并赋值给变量,dzbh,-,从添加行副本,inserted,中查询出,BID,并赋值给变量,tsbh,-,从表“,Reader”,中查询出添加行读者的,TypeID,赋值给变量,dzlx,SET dzbh=(SELECT RID FROM inserted),SET tsbh=(SELECT BID FROM inserted),SET dzlx=(SELECT TypeID FROM Reader WHERE RID=dzbh),（,1,）创建,INSERT,事件的,AFTER,触发器,50,（二）创建,DML,触发器,-,判断所借图书是否已经借出,IF,EXISTS,(SELECT*FROM Book WHERE BID=tsbh AND LentOut=0),BEGIN,-,应还日期为借期加限借天数,UPDATE Borrow,SET SReturnDate=,DATEADD(dd,(SELECT LimitDays FROM ReaderType WHERE TypeID=dzlx),LendDate),WHERE RID=dzbh AND BID=tsbh AND ReturnDate IS NULL,（,1,）创建,INSERT,事件的,AFTER,触发器,51,（二）创建,DML,触发器,-,将读者表,Reader,中该读者的借阅数量,Lendnum,加,1,UPDATE Reader SET Lendnum=Lendnum+1 WHERE RID=dzbh,-,将图书表,Book,中该书是否借出,LentOut,置为,1,UPDATE Book SET LentOut=1 WHERE BID=tsbh,END,ELSE,-,该图书已借出,BEGIN,ROLLBACK -,回滚数据行添加操作,PRINT,该书已借出,END,END,（,1,）创建,INSERT,事件的,AFTER,触发器,52,（二）创建,DML,触发器,假设,1,：,执行以下,INSERT,语句完成图书借阅的操作,INSERT INTO Borrow(RID,BID)VALUES(2000186010,TP312/429),INSERT,语句对借阅表“,Borrow,”的操作如下,读者编号,列“,RID,”得到“,2000186010,”,图书编号,列“,BID,”得到“,TP312/429,”,借期列“,LendDate,”得到定义表时设置的系统默认值,GETDATE(),，,假设,系统日期为“,2017-11-30,”,（,1,）创建,INSERT,事件的,AFTER,触发器,53,（二）创建,DML,触发器,激发触发器“,T_Borrow,”，,判断结果为所借书尚未借出,借阅表中,的应还日期列“,SReturnDate,”,得到,2018-02-28,在触发器中,从表“,Reader,”中查询出该读者的读者类型（教师），再从表“,ReaderType,”中查询出该读者类型的限借天数,90,，由借阅日期,“,2017-11-30,”,加,90,天得到应还日期,“,2018-02-28,”,（,1,）创建,INSERT,事件的,AFTER,触发器,54,（二）创建,DML,触发器,读者表中读者编号为,2000186010,的借阅数量,“Lendnum”,增加到,1,（,1,）创建,INSERT,事件的,AFTER,触发器,55,（二）创建,DML,触发器,图书表中图书编号为,TP312/429,的图书是否借出列“,LendOut,”置为,1（True）,（,1,）创建,INSERT,事件的,AFTER,触发器,56,（二）创建,DML,触发器,假设,2,：,执行以下,INSERT,语句完成借阅表“,Borrow,”添加借书信息的代码为：,INSERT INTO Borrow(RID,BID)VALUES(2014216117,TP312/429),激发触发器“,T_Borrow,”，,判断结果为所借书已经借出,提示该书已经借出,该书已借出,消息,3609,，级别,16,，状态,1,，第,1,行,事务在触发器中结束。批处理已中止。,对“,Borrow,”表的,INSERT,语句回滚，添加信息撤销。,（,1,）创建,INSERT,事件的,AFTER,触发器,57,（二）创建,DML,触发器,假设,2,：,执行以下,INSERT,语句完成借阅表“,Borrow,”添加借书信息的代码为：,INSERT INTO Borrow(RID,BID)VALUES(2014216117,TP312/429),激发触发器“,T_Borrow,”，,判断结果为所借书已经借出,提示该书已经借出,该书已借出,消息,3609,，级别,16,，状态,1,，第,1,行,事务在触发器中结束。批处理已中止。,对“,Borrow,”表的,INSERT,语句回滚，添加信息撤销。,（,1,）创建,INSERT,事件的,AFTER,触发器,58,（二）创建,DML,触发器,例,9,：,读者还书。,使用,UPDATE,语句完成借阅表“,Borrow,”更新还书信息操作，还期为当前系统日期。,使用触发器编程判断还书是否过期及以下处理：,如果过期，计算过期天数；,将读者表“,Reader,”中该读者的借阅数量“,Lendnum,”减少,1,本；,将图书表“,Book,”该书是否借出“,LentOut,”置为假。,（,2,）创建,UPDATE,事件的,AFTER,触发器,59,（二）创建,DML,触发器,USE Library,GO,CREATE TRIGGER T_Return -,创建触发器,ON Borrow -,基于表“,Borrow,”,AFTER UPDATE -,在更新操作之后激发执以下语句,AS,BEGIN,DECLARE days int,dzbh char(10),tsbh char(9),hsrq date,（,2,）创建,UPDATE,事件的,AFTER,触发器,60,（二）创建,DML,触发器,-,从更新行副本,inserted,中查询出,RID,并赋值给变量,dzbh,-,从更新行副本,inserted,中查询出,BID,并赋值给变量,tsbh,-,从更新行副本,inserted,中查询出,ReturnDate,并赋值给局部变量,hsrq,SET dzbh=(SELECT RID FROM inserted)-,读者编号,SET tsbh=(SELECT BID FROM inserted)-,图书编号,SET hsrq=(SELECT ReturnDate FROM inserted),-,还书日期,（,2,）创建,UPDATE,事件的,AFTER,触发器,61,（二）创建,DML,触发器,/*,从借阅表中查询出该读者的本次还书日期和应还日期之差,DATEDIFF,函数返回,ReturnDateSreturnDate,的值，单位为,DAY,，并赋值给,days*/,SELECT,days,=DATEDIFF(day,SReturnDate,ReturnDate),FROM Borrow,WHERE RID=dzbh AND BID=tsbh AND ReturnDate=hsrq,（,2,）创建,UPDATE,事件的,AFTER,触发器,62,（二）创建,DML,触发器,/*,如果,days,小于等于,0,，即,ReturnDateSReturnDate,，过期,days,天*,/,IF,days,0 -,如果借阅数量大于,0,BEGIN,PRINT,该读者不能删除！还有,+convert(char(2),LNum)+,本书未还。,ROLLBACK -,事务回滚取消所删除的数据行,END,ELSE -,如果借阅数量不大于,0,PRINT,该读者已被删除！,-,显示数据行已删除,END,（,3,）创建,DELETE,事件的,AFTER,触发器,70,（二）创建,DML,触发器,假设：,删除某读者。,DELETE Reader WHERE RID=2004216010,执行结果：,该读者不能删除！还有,1,本书未还。,事务在触发器中结束。批处理已中止。,查看读者表：可见该读者未被删除。,（,3,）创建,DELETE,事件的,AFTER,触发器,71,（二）创建,DML,触发器,例,11,：,删除图书保护。,在图书馆图书处理过程中，不允许随意删除表“,Book,”中的图书。创建表“,Book,”的,INSTEAD OF,触发器“,T_BookNoDEL,”,代码：,USE Library,GO,（,4,）创建,DELETE,事件的,INSTEAD OF,触发器,72,（二）创建,DML,触发器,例,11,：,删除图书保护。,CREATE TRIGGER T_BookNoDEL -,创建,INSTEAD,触发器,ON Book -,基于表“,Book,”,INSTEAD OF,DELETE -,替代触发事件,DELETE,AS,BEGIN,PRINT,图书未被删除！,-,显示数据行，图书未被删除,END,（,4,）创建事件的,INSTEAD OF,触发器,73,（二）创建,DML,触发器,假设：,删除某图书。,DELETE Book WHERE BID=TP311.138/235,执行结果：,图书未被删除！,(1,行受影响,),查看表“,Book,”，,可见,BID,为,TP311.138/235,的图书未被删除。,（,4,）创建事件的,INSTEAD OF,触发器,74,（三）创建,DDL,触发器,语法：,CREATE TRIGGER,触发器名,ON,ALL,SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER,.,n,AS,BEGIN,T-SQL,语句,END,CREATE,、,ALTER,和,DROP,触发,75,（三）创建,DDL,触发器,参数说明,ALL SERVER,：将,DDL,触发器的作用域应用于当前服务器。指定此参数，则当前服务器中的任何位置上出现事件类型或事件组，就会激发该触发器。,DATABASE,：将,DDL,触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现事件类型或事件组，就会激发该触发器。,WITH ENCRYPTION,：对,CREATE TRIGGER,语句的文本进行加密。,CREATE,、,ALTER,和,DROP,触发,76,（三）创建,DDL,触发器,参数说明,事件类型：导致激发,DDL,触发器的,T-SQL,语句事件的名称。,例如,CREATE_TABLE,、,ALTER_TABLE,、,DROP_TABLE,CREATE_PROCEDURE,等操作。,事件组：预定义的,T-SQL,语句事件分组的名称。执行任何属于事件组的,T-SQL,语句事件之后，都将激发,DDL,触发器。,T-SQL,语句：指定触发器所执行的,T-SQL,语句。,CREATE,、,ALTER,和,DROP,触发,77,（三）创建,DDL,触发器,触发器对象位置说明,在,SSMS,的,【,对象资源管理器,】,窗口中，服务器作用域的,DDL,触发器显示在“服务器对象”节点中；具有数据库作用域的,DDL,触发器位于相应数据库节点下的“可编程性”节点下的“数据库触发器”节点下。,CREATE,、,ALTER,和,DROP,触发,78,（三）创建,DDL,触发器,例,12,：,使用,DDL,触发器来防止数据库“,Library,”中的任意一个表被修改或删除。,代码,:,USE Library,GO,CREATE TRIGGER safety1,ON DATABASE,FOR,DROP_TABLE,ALTER_TABLE,AS,BEGIN,PRINT ,要删除和修改表之前，你必须先禁用触发器,safety1!,ROLLBACK,END,CREATE,、,ALTER,和,DROP,触发,79,（三）创建,DDL,触发器,执行结果：,新建触发器“,safety1,”,。,作用：,当用户试图使用,DROP,或,ALTER,语句删除或修改数据库“,Library,”中的表时，调用此,DDL,触发器，此触发器的事务回滚语句,ROLLBACK,将撤销,DROP,或,ALTER,语句的执行。,CREATE,、,ALTER,和,DROP,触发,80,（三）创建,DDL,触发器,例,13,：,在服务器上创建,DDL,触发器来防止服务器中的任意一个数据库被修改或删除。,代码,:,CREATE TRIGGER safety2 ON,ALL SERVER,FOR,DROP_DATABASE,ALTER_DATABASE,AS,BEGIN,PRINT,要删除和修改数据库之前，你必须先禁用触发器,safety2!,ROLLBACK,END,CREATE,、,ALTER,和,DROP,触发,81,（三）创建,DDL,触发器,执行结果：,新建触发器“,safety2,”,。,作用：,当用户试图使用,DROP,或,ALTER,命令删除或修改服务器中的数据库时，调用此,DDL,触发器，此触发器的事务回滚语句,ROLLBACK,将撤销,DROP,或,ALTER,命令的执行。,CREATE,、,ALTER,和,DROP,触发,82,（四）管理触发器,在,【,对象资源管理器,】,窗口中，展开“数据库”“具体数据库”“具体表”节点“触发器”节点，用右键单击“触发器”节点，从快捷菜单中选择“修改”命令,1.,使用,SSMS,管理触发器,83,（四）管理触发器,在,【,查询编辑器,】,中即可对打开的触发器进行修改,1.,使用,SSMS,管理触发器,84,（四）管理触发器,修改,DML,触发器。语法如下：,ALTER TRIGGER,ON,FOR|AFTER|INSTEAD OF,UPDATE,INSERT,DELETE,AS T-SQL,语句,2.,使用,T-SQL,管理触发器,85,（四）管理触发器,修改,DDL,触发器。语法如下：,ALTER TRIGGER,ON,ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER,n,AS T-SQL,语句,2.,使用,T-SQL,管理触发器,86,（四）管理触发器,删除触发器。语法如下：,DROP TRIGGER,禁用触发器。语法如下：,DISABLE TRIGGER,触发器名,ON,对象名,|DATABASE|ALL Server,启用触发器。语法如下：,ENABLE TRIGGER,触发器名,ON,对象名,|DATABASE|ALL Server,2.,使用,T-SQL,管理触发器,87,（四）管理触发器,例,14,：,禁用,DDL,触发器“,safety1,”，以便进行表的修改和删除。,代码如下：,DISABLE TRIGGER safety1 ON DATABASE,2.,使用,T-SQL,管理触发器,88,单元,2.8,数据库服务器编程,89,创建与管理存储过程,一,创建与管理触发器,二,创建与管理用户定义函数,三,三、创建与管理用户定义函数,案例,2-10-3,图书管理用户定义函数的创建与管理,根据图书管理系统的功能需求，在数据库“,Library”,中创建用户定义函数。,工作任务,90,（一）用户定义函数概述,用户定义函数,一组编译好的、存储在数据库服务器上的和完成特定功能的,T-SQL,程序，是某数据库的对象。,可以将一个或多个,T-SQL,语句的子程序定义成函数，从而实现代码的封装和重用。,用户定义函数（,User Defined Function,，,UDF,）可以有多个输入参数并返回标量（常量）或表，不支持输出参数。,1.,用户定义函数的概念,91,（一）用户定义函数概述,用户定义函数的优点,允许模块化程序设计。,能够实现较快的执行速度。,能够减少网络流量。,1.,用户定义函数的概念,92,（一）用户定义函数概述,用户定义函数与存储过程的比较,1.,用户定义函数的概念,项,目,用户定义函数,存 储 过 程,参数,允许多个输入参数，不允许输出参数,允许多个输入,/,输出参数,返回值,有且只有一个返回,值，可以,返回标量或表值,可以没有返回,值,，不能返回表值,调用,在表达式中引用，可以嵌入在查询语句的表达式中调用,必须单独调用,93,（一）用户定义函数概述,标量函数,返回的是在,RETURNS,子句中定义类型的标量表达式的值（单个数据值）。,2.,用户定义函数的分类,94,（一）用户定义函数概述,表值函数：,返回的是在,RETURNS,子句中指定的“,TABLE,”类型的数据行集（表值）。,内联表值函数：没有函数体，,RETURN,子句在括号中含有一条单独的,SELECT,查询语句。,多语句表值函数：在,BEGINEND,语句块中定义的函数体包含一系列,T-SQL,语句。,2.,用户定义函数的分类,95,（二）创建用户定义函数,语法,CREATE FUNCTION,函数名,-,创建标量函数,(,形参名 数据类型,.,n,)-,括号内输入参数,RETURNS,数据类型,-,定义返回标量值的数据类型,WITH,选项,AS,BEGIN,T-SQL,语句,-,函数体,RETURN,标量表达式,-,返回,RETURNS,子句中定义的数据类型