【教学课件】第7章存储过程和触发器.ppt

第7章 存储过程和触发器,7.1 存储过程7.1.1 存储过程的概念7.1.2 系统存储过程7.1.3 用户自定义存储过程,在大型数据库中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL语句的结合。就本质而言，触发器也是一种存储过程。,7.1.1 存储过程的概念,存储过程（Store Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，供用户调用。存储过程同其他编程语言中的过程或函数相似，有如下特点：接受参数并以输出参数的形式将多个值返回返回状态值，以表明成功或失败 在SQL中存储过程分为两类：系统存储过程用户自定义存储过程,7.1.2 系统存储过程,在SQL Server中，有许多管理工作是通过执行系统存储过程来完成的。系统过程以“sp_”为前缀，主要存储在master数据库中，但可以在任何数据库使用系统存储过程。,常用系统存储过程sp_helpdb 数据库名：查看数据库的一般信息sp_help 数据库对象名：查看数据库对象的一般信息sp_helptext 数据库对象名：查看数据库对象的文本信息sp_depends 数据库对象名：查看数据库对象的相关性sp_rename 旧名，新名：修改数据库对象的名称例：execute sp_help 学生,7.1.3 用户自定义存储过程,存储过程是一种数据库对象。创建存储过程的方法：向导 企业管理器 SQL语句,1.不带参数的存储过程例1创建一个存储过程，用于查询货品book的订单信息。create procedure sp1 as select*from orders where 货品名称=bookgo-注意:创建存储过程的语句必须在一个独立的批中execute sp1-执行存储过程例2（书P148）:在market数据库中建立一个名为st_cus的存储过程，用于检索客户信息与订单信息。,7.1.3 用户自定义存储过程,2.带参数的存储过程例1创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受货品名称。（注意：形式参数的定义和实际参数的使用）,create procedure sp2 x varchar(6)as select*from orders where 货品名称=xgoexecute sp2 book-执行存储过程-或者execute sp2 x=book,7.1.3 用户自定义存储过程,例2创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受货品名称，默认值为“book”。（注意：默认值的设定）,create procedure sp3 x varchar(6)=book as select*from orders where 货品名称=xgo-执行存储过程execute sp3 default-default关键字启用参数的默认值-或者execute sp3 忽略具有默认值的参数，表示启用参数的默认值-或者execute sp3 pen,7.1.3 用户自定义存储过程,3.带输出参数的存储过程例1创建一个存储过程，它带有一个输入参数，用于接受货品名称，带有一个输出参数，用于统计该货品的订单数。（注意：输出参数的定义和使用）,create procedure sp4 x varchar(6),y int output as select y=count(*)from orders where 货品名称=xgo-执行存储过程declare z intexecute sp4 book,z outputselect z,7.1.3 用户自定义存储过程,关于存储过程的返回值 任何存储过程都返回一个整数状态值，一般返回“”以表明调用成功。如果要返回其他值，在存储过程中写RETURN语句，但只能是整数值。,例1可以这样看到存储过程sp1的返回状态值：declare x intexecute x=sp1select x,7.1.3 用户自定义存储过程,关于存储过程的返回值例2创建一个存储过程，如果找到北京的客户，则返回北京的客户数，否则返回0。,create procedure sp5 as declare x int select x=count(*)from customers where 地址 like 北京%return xgodeclare x intexecute x=sp5-执行存储过程select x,7.1.3 用户自定义存储过程,存储过程的优点1.存储过程能够减少网络流量2.增强代码的重用性和共享性 3.存储过程能够实现较快的执行速度4.存储过程保证安全性5.允许不同的客户端应用程序访问存储过程存储过程的缺点1.存储过程不能实现复杂的逻辑操作2.存储过程的管理比较困难,执行字符串(略),EXECUTE语句的主要用途是执行存储过程。除此外，可以执行字符串。例1 execute(select*from orders)例2 declare x varchar(50)set x=select*from ordersexecute(x)例3 declare x varchar(50)set x=ordersexecute(select*from+x),7.2 触发器,7.2.1 触发器概述7.2.2 创建和使用触发器7.2.3 管理触发器,触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成。,7.2.1 触发器概述,触发器的概念触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成。触发器与表紧密相连触发器不能被直接调用，当对某表进行如UPDATE、INSERT、DELETE这些操作时，触发器被激发，SQL Server就会自动执行触发器所定义的SQL语句。触发器没有参数,触发器的类型SQL Server 2000支持两种类型的触发器：AFTER触发器(重点掌握)INSTEAD OF触发器,两种类型的触发器(两种触发方式),AFTER触发器称为后触发器，就是在修改语句成功执行之后激活触发器；AFTER触发器只能在表上定义，不能创建在视图上；可以为每个触发操作（INSERT、UPDATE、DELETE）定义多个触发器(默认先建的先触发)。INSTEAD OF触发器称为替代触发器，就是在修改语句执行之前激活触发器，但并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅执行触发器本身。既可以在表上定义，也可以在视图上定义，但对每个触发操作只能定义一个INSTEAD OF触发器。,7.2.2 创建和使用触发器,1、使用企业管理器创建触发器 右击表，“所有任务”“管理触发器”可以创建、查看、修改、删除触发器2、使用SQL语句创建触发器 CREATE TRIGGER 触发器名 ON 表名|视图名 FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEASSQL语句,7.2.2 创建和使用触发器,例一个最简单的触发器 use marketgocreate trigger tr1 on goods-创建触发器after insert,delete,updateasselect*from goodsgo,-检验触发器的作用insert goods(货品名称)values(computer)update goods set 库存量=10 where 货品名称=computerdelete from goods where 货品名称=computer,INSERT触发器,例1 当向orders表中插入一条订单时，检查goods表中该订单中的货品是否正在整理中，如是，则不能下订单。,CREATE TRIGGER tr2 ON ordersAFTER INSERTASdeclare y varchar(6)select y=货品名称 from insertedif(select 状态 from goods where 货品名称=y)=1 begin print 货品正在整理中，不能下订单 rollback transaction-事务回滚 endgoinsert orders(货品名称,客户编号,数量)values(pen,2,5),INSERT触发器,例2 当向orders表中插入一条订单时，检查goods表中该订单中的库存量够不够，如不够，则不能下订单；如够，则还要减去goods表中相应的库存量。,CREATE TRIGGER tr3 ON ordersAFTER INSERTASdeclare x int,y varchar(6),z intselect y=货品名称,z=数量 from insertedselect x=库存量 from goods where 货品名称=yif xz begin print 库存不够，不能下订单 rollback transaction endelse update goods set 库存量=库存量-z where 货品名称=y,说 明,注意：前2个触发器都是建立在orders表上的，都是INSERT触发器。谁触发？谁先谁后？,结论：可以为每个触发操作（INSERT、UPDATE和DELETE）定义多个触发器，默认先建的先触发，但是，其中任何一个触发器执行了rollback transaction语句，则不再有其他触发器被执行，且全部回滚。,INSERT触发器,重要说明 INSERTED表是触发器被激发时产生的临时表，触发器执行完成后，这个表被删除。要想看到它的内容，例：create trigger temp1 on ordersafter insertasselect*from insertedgo insert orders(货品名称,客户编号,数量)values(pen,2,5),rollback transaction 事务回滚触发器和触发它的语句是一个事务，执行rollback transaction后，整个事务将回滚。这是数据完整性的一种保护机制。,INSERT触发器能够参考数据库中其他表的信息，从而实现CHECK约束不能实现的、更复杂的数据完整性。,DELETE触发器,例1 删除goods表中的货品时，将这些货品在orders中的订单也全部删除。,CREATE TRIGGER tr4 ON goodsAFTER DELETEASdelete orders where 货品名称 in(select 货品名称 from deleted)go-检验触发器的作用delete goods where 货品名称=pen-有外键约束,注意：约束前反应，触发器后反应；要实现级联删除(级联修改)可以使用触发器或外键约束。,DELETE触发器,例2 不能删除有订单的客户。CREATE TRIGGER tr5 ON customersAFTER DELETEASif exists(select*from orders where 客户编号 in(select 客户编号 from deleted)begin print 不能删除有订单的客户 rollback transaction endgo-检验触发器的作用delete customers where 客户编号=2-有外键约束,DELETE触发器,问题 TRUNCATE TABLE语句删除表中所有记录，那么该语句会激活DELETE触发器吗？,答案 TRUNCATE TABLE语句的操作不记入事务日志，所以它不会激活DELETE触发器。,UPDATE触发器,例 创建一个UPDATE触发器，防止用户修改orders表的“订货日期”(订货日期默认值是getdate()。,CREATE TRIGGER tr6 ON ordersAFTER UPDATEASIF UPDATE(订货日期)-修改该字段时 begin raiserror(订货日期不准改,16,1)-功能同print rollback transaction endgo-检验触发器的作用update orders set 订货日期=2004/10/10,数量=1 where 订单号=5,问题-如何禁止删除课程表中的数据?,方法一：AFTER触发器CREATE TRIGGER tr11 ON 课程AFTER DELETE ASrollbackprint 禁止删除课程表中的数据,方法二：INSTEAD OF触发器,方法三：给课程表设置相应权限,INSTEAD OF触发器(非重点),例禁止删除课程表中的数据CREATE TRIGGER tr12 ON 课程INSTEAD OF DELETE ASprint 禁止删除课程表中的数据,当在表(或视图)上执行INSERT、UPDATE、DELETE时，如果系统发现有相应的INSTEAD OF触发器，就会取消修改操作，转为执行INSTEAD OF触发器。对每个触发操作(INSERT、UPDATE、DELETE)只能定义一个INSTEAD OF触发器。一般将INSTEAD OF触发器创建在视图上，通过操作视图更新基表。,问题-这2个触发器怎么触发？,CREATE TRIGGER tr11 ON 课程AFTER DELETE ASrollbackprint 禁止删除课程表中的数据,CREATE TRIGGER tr12 ON 课程INSTEAD OF DELETE ASprint 禁止删除课程表中的数据,答：INSTEAD OF 触发！,触发器的优点（P176）,1.触发器自动执行2.实现比CHECK约束更为复杂的数据完整性约束3.实现数据库中多张表的级联修改和删除（不过，外键约束效率更高）4.使用自定义的错误信息,7.2.3 管理触发器,1、使用企业管理器右击表，“所有任务”“管理触发器”可以创建、查看、修改、删触发器2、使用SQL语句execute sp_help 触发器名 查看触发器的一般信息execute sp_helptext 触发器名 查看触发器的定义execute sp_helptrigger 表名 查看表的触发器drop trigger 触发器名 删除触发器禁止和启用触发器:alter table 表名 disable|enable trigger 触发器名,两个特殊的表,每个触发器被激发时，系统会自动产生两个特殊的临时表：INSERTED表和DELETED表。这两个表的结构总是与激发触发器的表的结构相同。触发器执行完成后，这两个表被删除。用户可以查询这两个表中的内容但不能对它们进行修改。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。,补充：事务的概念,事务是由一条或多条SQL语句组成的逻辑工作单元，被当作一个整体看待要么全做，要么全不做。,如果说有一样东西能把数据库与文件系统区别开，那么它就是支持事务处理的能力。如果你正在往一个普通文件中写入数据，系统突然崩溃，则整个文件很可能遭到破坏。而如果你往一个数据库里写数据时遇到了这种情况，则可以利用事务使数据库恢复到它在写操作开始之前的状态。实例：银行从某客户的储蓄帐户中转出2000元到信用卡帐户。这个事务很简单，但也可能发生意外储蓄帐户的钱被扣走了2000元，而这2000元还没有到信用卡帐户！这时候的数据库处于不正确、不一致、不稳定的状态，即数据不完整。,补充：事务的例子,例1：Begin transactionInsert goods(货品名称)values(铅笔)Rollback-或Commit,例2：Begin transactionInsert If errror=0 Begin Update Insert Print 插入成功 Commit EndElse Begin Print 插入失败！Rollback End,事务的三个命令：BEGIN TRANSACTION：事务开始COMMIT：事务提交或事务结束ROLLBACK：事务回滚，回到事务开始状态,补充：事务的例子,在SQL Server里，每一条SQL语句都是一个独立的、隐含的事务。,例3：（学生表有CHECK约束：年龄 0 and 年龄 30）update 学生表 set 年龄=年龄+5,-只要有一个人的年龄+5的结果=30就全部失败,补充：事务的特性ACID,1、原子性(ATOMICITY):事务包含的一组操作是不可分的，要么全做，要么全不做。2、一致性(CONSISTENCY):事务执行的结果应使数据库从一个一致性状态转换到另一个一致性状态。例如，银行转帐工作。3、隔离性(ISOLATION):如果有多个事务并发执行，应使每个事务的执行不被其他事务干扰。并发控制就是保证事务的隔离性（独立性）。4、持久性(DURABILITY):一个事务一旦成功完成，它对数据库中数据的改变就应该是永久的。,