SQL触发器存储过程课件

1、威迅教育开发部监制回顾数据类型常用函数运算符 T-SQL语句第1页，共62页。威迅教育开发部监制本章内容触发器概念和作用创建、删除、修改触发器解释触发器使用的Inserted和Deleted表说明触发器的触发类型：INSERT、UPDATE和 DELETE解释系统存储过程的类别解释创建、修改和执行用户定义存储过程的过程使用存储过程参数第2页，共62页。威迅教育开发部监制触发器第3页，共62页。威迅教育开发部监制问题 ？ 如果我向Top 10业绩表(专门存放前 10 的记录)中添加数据，业绩表中的数据如和来保证我插入的数据都在前 10里面呢?第4页，共62页。威迅教育开发部监制问题 ？ IBM公

2、司存放员工档案有两张表，在职员工档案表，和退休员工档案表。有一名叫“王肥”的在职员工退修了。那么我们使用SQL语句因该如何去操作？第5页，共62页。威迅教育开发部监制盖慈泽东介石Ret_Emp 表DerekEMP 表小李大熊小刚周亮王肥示例问题 退休第6页，共62页。威迅教育开发部监制大熊小刚周亮EMP 表Ret_Emp 表盖慈泽东王肥插入示例问题 删除触发器触发介石小李第7页，共62页。威迅教育开发部监制什么是触发器 触发器是一种特殊类型的存储过程当使用下面的一种或多种数据修改操作在指定表中对数据进行操作时，触发器会生效：UPDATE、INSERT 或 DELETE。第8页，共62页。威迅教

3、育开发部监制触发器的概念触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器可以确保数据的完整性和一致性SQL SERVER不支持行级触发器第9页，共62页。威迅教育开发部监制触发器的用途比较不同版本的数据读取其他数据库的表中的数据在数据库中所有的相关表中级联所作的修改或删除回滚无效的修改强制实现比由CHECK约束提供的限制更为复杂的限制执行本地和远程存储过程第10页，共62页。威迅教育开发部监制创建触发器触发器可以通过企业管理器或者查询分析器来创建语法：CREATE TRIGGER Trigger_nameON tableWITH ENCRYPTIONF

4、OR DELETE, INSERT, UPDATEAS Sql_statements第11页，共62页。威迅教育开发部监制示例创建触发器在titles表中防止被用户插入和更新数据create trigger tr_titles_1 on titlesfor insert, updateasprint 有触发器约束你向这个表插入或更新记录ROLLBACK TRANSACTION -取消前面的操作go第12页，共62页。威迅教育开发部监制创建触发器的注意事项触发器可以与对表执行的三个操作（INSERT、 UPDATE 和 DELETE）相关联一个触发器只应用于单独一个表WITH ENCRYPTIO

5、N选项可用于对用户隐藏触发器 的定义。但是，加密的触发器无法进行解密触发器可以引用视图或临时表，但不能和它们相关联触发器可以包含任意数量的 SQL 语句默认情况下，只有数据库所有者才具有创建触发器的权限。此权限不可转让。触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。第13页，共62页。威迅教育开发部监制触发器的类型insert触发器update触发器delete触发器第14页，共62页。威迅教育开发部监制触发器的两张特殊表 触发器可以访问两个逻辑表 Inserted 表：包含更新前的数据映像 Deleted 表：包含更新后的数据映像Inserted和Deleted表中

6、不包含表中不受更新操作 影响的数据第15页，共62页。威迅教育开发部监制Inserted 和Deleted 表create table TriggerTest (vid varchar(20) not null,vname varchar(20) not null )gocreate trigger TestUpdateon TriggerTestfor updateasselect * from deleted -更新前的记录信息select * from inserted-更新后的记录信息print records updatego第16页，共62页。威迅教育开发部监制Inserted 和

7、Deleted 表create trigger TestInsert on triggertestfor insertasselect * from inserted -插入的记录信息print records insertgoCreate trigger TestDelete on triggerTestfor deleteas select * from deleted -删除的记录信息print records deletedgo第17页，共62页。威迅教育开发部监制INSERT 触发器当试图向表中插入数据时，将执行 INSERT 触发器INSERT 触发器执行下列操作：向Inserte

8、d表中插入一个新行的副本。检查Inserted表中的新行，确定是否要阻止该插入操作。如果所插入的行中的值是有效的，则将该行插入到触发器表中。第18页，共62页。威迅教育开发部监制INSERT触发器INSERT buyers VALUES ( 2,dog)buyersidname143pigcatantinsertedidname2dogbuyersidname143pigcatant2dogInserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本（新行）第19页，共62页。威迅教育开发部监制示例创建触发器在roysched上，防止插入版费大于30的记录create t

9、rigger CheckRoyaltyon royschedfor insert as if (select royalty from inserted)30beginprint RoyaltyTrigger:版权费不能小于30print 请将版权费修改为小于30的值rollback transactionend第20页，共62页。威迅教育开发部监制UPDATE触发器当更新表中的数据时，将执行UPDATE触发器UPDATE 触发器执行下列操作：将原始数据行移到逻辑Deleted表中将一个新行插入Inserted表中，然后插入触发器表中计算Deleted表和Inserted表中的值以确定是否需要

10、进行干预可以创建Update触发器以验证对单个列或整个表的更新第21页，共62页。威迅教育开发部监制UPDATE触发器UPDATE buyers SET id = 17 WHERE id = 1buyersidname1243pigdogcatantdeletedidname1pig1pigbuyersidname243dogcatantinsertedidname17pigbuyersidname243dogcatantbuyersidname243dogcatantbuyersidname243dogcatant17pig第22页，共62页。威迅教育开发部监制表级触发器-示例CREATE

11、TRIGGER NoUpdateDiscountON discounts FOR UPDATE ASIF (SELECT discount FROM inserted) 12BEGIN PRINT 不能指定大于 12% 的折扣ROLLBACK TRANSACTIONEND第23页，共62页。威迅教育开发部监制列级触发器-示例CREATE TRIGGER NoUpdatePaytermsON sales FOR UPDATE ASIF UPDATE (payterms)BEGIN PRINT 不能修改订单的付费条款ROLLBACK TRANSACTIONEND第24页，共62页。威迅教育开发部监

12、制buyersidname1243pigdogcatant1pigsalesidProductid qty1134 101 711 1012 5deleted1piginserted17pigCreate Trigger Trigger1On buyers for updateAs if update( id ) begin update sales set id = (select id from inserted) where id = (select id from deleted) end第25页，共62页。威迅教育开发部监制DELETE 触发器当试图从表中删除数据时，将执行DELET

13、E 触发器。DELETE触发器执行下列操作：从触发器表中删除行。将删除的行插入到Deleted表中。检查Deleted表中的行，以确定是否需要或应如何执行触发器操作。第26页，共62页。威迅教育开发部监制buyersidname1243pigdogcatantDELETE buyers WHERE id = 2DELETE触发器deletedidname2dog2dogbuyersidname143pigcatantbuyersidname143pigcatant第27页，共62页。威迅教育开发部监制示例CREATE TRIGGER NoDelete9901ON pub_info FOR DE

14、LETE ASIF (SELECT pub_id FROM deleted) = 9901BEGIN PRINT 不能删除出版商 9901 的详细信息 ROLLBACK TRANSACTIONEND第28页，共62页。威迅教育开发部监制不能在触发器中使用的 SQL 语句SQL 语句CREATE DATABASEALTER DATABASEDROP DATABASELOAD DATABASERECONFIGURERESTORE LOGDISK INITDISK RESIZELOAD LOGRESTORE DATABASE第29页，共62页。威迅教育开发部监制触发器与性能由触发器引起的开销通常较低

15、。大部分的时间花费在引用逻辑表以外的其他表上。Deleted和Inserted逻辑表始终位于内存中。第30页，共62页。威迅教育开发部监制第31页，共62页。威迅教育开发部监制存储过程第32页，共62页。威迅教育开发部监制存储过程用来执行管理任务或应用复杂的业务规则存储过程（procedure）类似于C语言中的函数存储过程可以带参数，也可以返回结果int sum(int a,int b) int s; s =a+b; return s ;存储过程相当于C语言中的函数第33页，共62页。威迅教育开发部监制存储过程的概念一组使用一个名称存储的预编译T-SQL语句集合由数据库开发人员或数据库管理员编

16、写用来执行管理任务或应用复杂的业务规则包含DML语句第34页，共62页。威迅教育开发部监制存储过程-单个 DML 语句DML 语句块DML 语句与流程控键关键字数据修改或数据检索语句存储过程概念可以包含第35页，共62页。威迅教育开发部监制存储过程的优点执行速度更快允许模块化程序设计 提高系统安全性减少网络流通量第36页，共62页。威迅教育开发部监制存储过程分类系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数第37页，共62页。威迅教育开

17、发部监制EXECUTE（执行）语句用来调用存储过程调用的语法 EXEC 过程名 参数调用存储过程第38页，共62页。威迅教育开发部监制常用的系统存储过程 系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_p

18、assword添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。第39页，共62页。威迅教育开发部监制示例EXEC 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_st

19、uMarks EXEC sp_stored_procedures 第40页，共62页。威迅教育开发部监制自定义存储过程语法：CREATE PROCEDURE schema_name. procedure_name parameter data_type = default OUTPUT ，.n WITH RECOMPILE | ENCRYPTION | RECOMPILE ，ENCRYPTION FOR REPLICATION AS sql_statement .n 第41页，共62页。威迅教育开发部监制示例显示id号为A001的客户姓名CREATE PROCEDURE Cust_A001 A

20、S PRINT 此代码客户 A001 的信息 SELECT * FROM Customers WHERE pub_id = A001第42页，共62页。威迅教育开发部监制存储过程注意事项存储过程的名称必须遵守标识符的命名规则存储过程中可以创建所有数据库对象，但默认值、 规则、触发器、过程和视图除外。创建对象后，就可以在过程内部进行引用。存储过程可以引用临时表第43页，共62页。威迅教育开发部监制存储过程注意事项在存储过程内调用的过程可以访问所有在调用过程中创建的对象在存储过程中可使用2100个参数只要内存空间足够，可以在存储过程中创建任意多个局部变量。存储过程的最大大小为128 MB第44页，

21、共62页。威迅教育开发部监制存储过程的参数存储过程的参数分两种：输入参数：用于向存储过程传入值，类似C语言的按值传递;输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递; 第45页，共62页。威迅教育开发部监制存储过程的参数int sum (int a, int b) int s; s=a+b; return s;c=sum（5, 8）传入参数值返回结果第46页，共62页。威迅教育开发部监制带参数的存储过程示例：CREATE PROCEDURE Product_Y05 pro_id char(4) AS SELECT * FROM Product WHERE pub_id = p

22、ro_id go- 调用存储过程EXECUTE Product_Y05 0877第47页，共62页。威迅教育开发部监制带参数的存储过程通过为可选参数指定默认值，可创建带有可选参数的存储过程如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误第48页，共62页。威迅教育开发部监制带参数的存储过程示例：CREATE PROCEDURE Product_Y05 pro_id char(4) = 1389AS SELECT * FROM Product WHERE pub_id = pro_id go- 调用存储过程EXECUTE Product_

23、Y05EXECUTE Product_Y05 0877第49页，共62页。威迅教育开发部监制带输出参数的存储过程执行存储过程时，也可为output参数指定输出值。在执行存储过程时对参数指定output，而在存储过程中又不是用output定义的，将出错在执行带有output参数的存储过程时，可以不指定output，这样不会返回错误，但将无法在调用程序中使用该输出值第50页，共62页。威迅教育开发部监制示例查询指定的出版社出版了几本书？create proc pp pubid char(10) , count int output asselect count = count(title_id)

24、from titles where pub_id = pubid- 调用存储过程declare count int exec pp 1389 , count outputselect count 第51页，共62页。威迅教育开发部监制带返回值的存储过程return :通过return可以返回一个数据给调用该存储过程的位置 示例：输入一个提供价格,和所有产品的平均价格比较.返回3种状态 0,1,-1create proc pp price money asdeclare avgPrice money select avgPrice = avg(price) from titles if( avg

25、Price price )return -1 else if ( avgPrice = price )return 0elsereturn 1 -* 执行存储过程 *declare result int exec result = pp 20select result第52页，共62页。威迅教育开发部监制存储过程-重新编译需要重新编译存储过程，以反映对存储过程所做的修改重新编译过程的方法有三种： 使用系统存储过程sp_recompile使用CREATE PROCEDURE时指定WITH RECOMPILE使用EXECUTE时指定WITH RECOMPILE第53页，共62页。威迅教育开发部监制

26、存储过程执行过程中的错误处理可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户 RAISERROR 显示用户定义的错误信息时可指定严重级别， 设置系统变量ERROR记录所发生的错误等第54页，共62页。威迅教育开发部监制使用RAISERROR 语句RAISERROR (msg_id | msg_str,severity, state WITH option,.n) RAISERROR语句的用法如下：msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为018级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中 第55页，共62页。威迅教育开发部监制示例触发一个错误：create proc pp price money ,titleId varchar(20