触发器与游标.ppt

1、第8章 触发器与游标,触发器是客户/服务器数据库的一种关键特性。使用触发器，开发人员可以在数据库引擎上稳固的实现复杂的、定制的业务。 游标是一种机制，对表中检索出的数据进行操作的灵活手段。,广西大学信息网络中心 卢豫开,8.1 触发器 （P192）,触发器是一种特殊类型的存储过程，不同于前面所讲的存储过程，主要通过事件触发而被执行。 当对某一个表进行update、delete、insert等操作时，SQLServer会自动执行触发器所事先定义好的语句。,广西大学信息网络中心 卢豫开,8.1.1 触发器的作用（P192）,触发器的主要作用是能实现主键和外键所不能保证的、复杂的参照完整性和数据的一

2、致性。 1.可以调用存储过程 2.强化数据条件约束 3.跟踪数据库内数据变化 4.级联合并运行 5.总之触发器可以解决高级形式的业务和复杂行为限制，实现定制记录。,广西大学信息网络中心 卢豫开,8.1.2 触发器的分类（P193）,1.事后触发器-只能定义在表上，可以针对表的同一操作定义多个触发器。在表上只能为每一个insert、update、delete操作指定一个事后触发器。 2.替代触发器并不执行预定的动作，而仅仅执行触发器本身的代码。对于每种操作insert、update、delete只能定义一个替代触发器。,广西大学信息网络中心 卢豫开,8.1.3 创建与执行触发器（P193）,创建

3、事后触发器： Create trigger 触发器名 on 表名with encryption for insert,update,delete as Begin 命令行或程序块 End,广西大学信息网络中心 卢豫开,8.1.3 创建替代触发器（P194）,Create trigger 触发器名 on 表名或视图名 instead of insert,update,delete as Begin 命令行或程序块 End,触发器的有关规则与条件,触发器名在数据库中是唯一的 触发器只能关联表或视图，利用with encryption可以加密触发器中的代码，增加安全性。 事后触发器只能定义在表，不能

4、定义到视图上；而替代触发器可以定义在视图上。,实例1: 创建一个触发器,当对仓库表update时，创建一个数据库新表并向新表中插入三条记录（P194）,Create trigger hytrigger1 on 仓库 for update as Begin Create table triuser( Userid int identity (1,1) primary key, Username varchar(50), Userpwd varchar(50) ) Insert into triuser(username,userpwd) values(hy1,111) Insert into t

5、riuser(username,userpwd) values(hy2,222) Insert into triuser(username,userpwd) values(hy3,333) end,广西大学信息网络中心 卢豫开,8.1.4 查看触发器基本信息（P195）,通过sp_help能够查看触发器的基本信息，包括触发器名、所有者、创建者和创建时间。语法格式为： Exec sp_help 触发器名,广西大学信息网络中心 卢豫开,8.1.5 查看触发器代码（P196）,用sp_helptext能查看触发器SQL的代码信息，但在创建触发器时使用了with encryption选项，则执行该命令

6、也看不到SQL代码。 Exec sp_helptext 触发器名,广西大学信息网络中心 卢豫开,8.1.6 修改触发器（P196）,修改事后触发器： alter trigger 触发器名 on 表名with encryption for insert,uodate,delete as Begin 命令行或程序块 End,广西大学信息网络中心 卢豫开,8.1.6 修改替代触发器（P196）,alter trigger 触发器名 on 表名或视图名 instead of insert,update,delete as Begin 命令行或程序块 End 修改触发器与创建触发器几乎相同，只是将cre

7、ate改为alter即可。,8.1.7 删除触发器（P196）,drop trigger 触发器名,广西大学信息网络中心 卢豫开,实例2: 触发器的管理操作（P196）,Use 企业销售管理系统 Exec sp_help hytrigger1 Exec sp_helptext hytrigger1,广西大学信息网络中心 卢豫开,将195页触发器例题修改(P197),alter trigger hytrigger1 on 仓库 for insert as Begin Create table triuser( Userid int identity (1,1) primary key, User

8、name varchar(50), Userpwd varchar(50) ) Insert into triuser(username,userpwd) values(hy1,111) Insert into triuser(username,userpwd) values(hy2,222) Insert into triuser(username,userpwd) values(hy3,333) Insert into triuser(username,userpwd) values(hy4, 444) End,广西大学信息网络中心 卢豫开,查看新例题,Use 企业销售管理系统 Exec

9、sp_help hytrigger1 Exec sp_helptext hytrigger1,向仓库插入记录而调用触发器,Insert into 仓库 (仓库号,城市,面积,创建时间) values (wh8, 青岛,1500,2003-5-10),实例3: 删除功能触发器（P198）,定义触发器，实现当删除职工表中的记录时，就删除职工所对应的订购单信息。 Create trigger hydeletetrigger1 on 职工 for delete as Begin Declare t varchar(10) Select t=职工号 from deleted Delete from 订购

10、单 where 职工号=t End,广西大学信息网络中心 卢豫开,删除一名职工信息,Delete from 职工where 职工号=zg4 Select * from 职工 Select * from 订购单,实例4: 嵌套触发器（P199）,定义触发器，当删除仓库表中一条仓库信息时,同时删除该仓库中所有职工的信息,并把仓库中所有职工的订购单信息也删除。 定义两个触发器。第一个触发器的功能是：当删除仓库中信息时，把该仓库中的职工信息删除；第二个触发器功能是：当删除职工信息时，把职工的订购单信息也删除。,广西大学信息网络中心 卢豫开,第一个触发器,Create trigger hydeletet

11、rigger2 on 仓库 for delete As Begin Declare t varchar(10) Select t=仓库 from deleted Delete from 职工 where 仓库号=t end,第二个触发器,Create trigger hydeletetrigger3 on 职工 for delete as Begin Delete from 订购单 where 职工号 in(select 职工号 from deleted ) End,删除一个职工信息进行测试,Delete from 仓库 where 仓库号=wh1 Select * from 仓库 Selec

12、t * from 职工 Select * from 订购单,实例5: 插入功能触发器（P201）,定义触发器，向仓库表中插入一条记录时，把相应的仓库号插入到职工表中。 Create trigger hyinsertrigger4 on 仓库 for insert As Begin Declare t varchar(10) Select t=仓库号 from inserted Insert into 职工(仓库号) values(t) End,广西大学信息网络中心 卢豫开,向仓库表插入记录进行测试,Insert into 仓库(仓库 号,城市,面积,创建时间)values (wh1,上海,80

13、0,2002-05-06) Selete * from 仓库 Selete * from 职工,实例6: 更新功能触发器（P202）,定义触发器，更新仓库表中一条记录时，把职工表中的仓库号也进行修改。 Create trigger hyupdatetrigger5 on 仓库 for update as Begin Declare old char(10) Declare new char(10) Select new=仓库号 from inserted Select old=仓库号 from deleted Update 职工 set 仓库号=new where 仓库号=old End,广西

14、大学信息网络中心 卢豫开,更新仓库表中的一条记录进行测试,Update 仓库 set 仓库号=modifywh2 where 仓库号=wh2 Select * from 仓库 Select * from 职工,8.2 游标（P203）,游标提供了一种对表中检索出的数据进行操作的灵活手段。 游标从多数据记录的结果集中每次只提取一条记录。 游标总是与一条select语句相关。 游标由结果集和结果集中指向特定记录的游标位置组成。 使用时必须声明一个指向该结果集的游标。,广西大学信息网络中心 卢豫开,8.2.1 游标的优点（P203）,游标允许应用程序对查询语句返回的行结果集中的每一行进行操作，提供基

15、于游标位置而对表中数据进行删除和更新的功能。 1.定位在结果集中的指定行 2.从结果集的当前位置检索一行或多行 3.可对结果集中当前位置的行进行数据修改 4.为由其它用户对结果集中的数据库数据更改提供显示 5.提供存储过程和触发器中使用的访问结果集的T-SQL语句,广西大学信息网络中心 卢豫开,8.2.2 游标实现的方法（P203）,SQL Server支持3种类型的游标：T-SQL游标、API游标、客户游标,广西大学信息网络中心 卢豫开,8.2.2.1 T-SQL游标（P203）,T-SQL游标通过declare cursor等T-SQL语句定义、操作，主要用在T-SQL脚本、存储过程、触发

16、器中。 T-SQL游标主要用在服务器上，处理由客户端发送给服务器的T-SQL语句或是批处理、存储过程、触发器中的数据处理请求。 T-SQL游标不提取数据块或多行数据。,广西大学信息网络中心 卢豫开,8.2.2.2 API游标（P203）,API游标主要应用在服务器，支持OLEDB、ODBC、ADO中使用的游标函数。 使用API函数的方法可实现如下功能: 1.打开一个链接 2.设置定义游标特征的特性或属性,自动映射到每个结果集。 3.执行一条或多条T-SQL语句。 4.使用API函数提取结果集中的行。 5.与API服务器游标一起使用游标数据库。,广西大学信息网络中心 卢豫开,8.2.2.3 客户

17、游标（P204）,客户游标主要在客户机上缓存结果集时才使用。 客户游标仅支持静态游标。 将整个结果集缓存在客户端上,所有游标操作都在客户端缓存中执行。 服务器游标称为后台游标，客户端游标称为前台游标。,广西大学信息网络中心 卢豫开,8.2.3 游标类型（P204）,1. 静态游标-按游标打开时的原样显示结果集。 2. 动态游标-游标滚动时动态显示结果集中所有的更新。 3. 只进游标-不支持滚动，仅能顺序显示。也显示所有更新。 4. 键集驱动游标-由一套称为键集的唯一标识控制。,广西大学信息网络中心 卢豫开,8.2.4 游标的基本操作（P205）,声明游标 打开游标 从游标中提取信息 关闭游标

18、释放游标,广西大学信息网络中心 卢豫开,8.2.4.1 游标的声明（P205）,Declare 游标名 insensitive cursor local | global forward_only | scroll static | keyset | dynamic | fast_forward read_only | scroll_locks | optimistic type_warning For SQL 语句 for read_only | update,广西大学信息网络中心 卢豫开,实例7: 创建三种游标：标准游标、只读游标、更新游标（P205）,创建标准游标 Use 企业销售管理系

19、统 Declare hycursorzf1 cursor for select * from 职工 where 工资 between 1000 and 2000,广西大学信息网络中心 卢豫开,创建只读游标和更新游标,创建只读游标 Use 企业销售管理系统 Declare hycursorzf2 cursor for select * from 职工 where 工资1200 for read only 创建更新游标 Use 企业销售管理系统 Declare hycursorzf3 cursor for select * from 职工 where 仓库号!=wh1 For update,8.2

20、.4.2 游标的打开（P206）,Open 游标名,广西大学信息网络中心 卢豫开,8.2.4.3 读取游标的数据（P207）,Fatch next | prior | first | last absolute n | relative n from 游标名 into 变量名 Next-返回当前行的下一行记录 Prior-返回第一行记录 Last-返回最后一行记录 Absolute n-返回第n行记录，当n为负值则从最后一行算起。 Relative -游标当前位置起向后第n行，若n为负值则向前第n行。,广西大学信息网络中心 卢豫开,8.2.4.4 游标的关闭（P207）,Close 游标,广西

21、大学信息网络中心 卢豫开,8.2.4.5 游标的释放（P207）,关闭游标并没有释放游标所占用的系统资源，因此要释放游标。 Deallocate 游标名,广西大学信息网络中心 卢豫开,实例8: 游标的操作（P208）,Use 企业销售管理系统 Declare hycursorzf4 scroll cursor for Select * from 供应商 Open hycursorzf4 Fetch first from hycursorzf4 Fetch last from hycursorzf4 Fetch prior from hycursorzf4 Fetch absolute 2 fr

22、om hycursorzf4 Fetch relative 2 from hycursorzf4 Close hycursorzf4 Deallocate hycursorzf4,广西大学信息网络中心 卢豫开,实例9: 利用变量输出游标中的字段值（P208）,Use 企业销售管理系统 Declare hycursorzf5 scroll cursor for Select 职工号,姓名,工资 from 职工 Declare t varchar(10) Declare t1 varchar(10) Declare t2 int Open hycursorzf5 Fetch absolute 4

23、from hycursorzf5 into t,t1,t2 Select t as 职工号,t1 as 职工名,t2 as 工资 Close hycursorzf5 Deallocate hycursorzf5,广西大学信息网络中心 卢豫开,实例10: 利用游标修改数据库中的数据（P209）,Use 企业销售管理系统 select * from 职工 Declare hycursorzf6 cursor scroll for Select * from 职工 Open hycursorzf6 Fetch first from hycursorzf6 Update 职工set 仓库号=modifybycursor where