第9章触发器和游标

第九章触发器和游标的管理

1．学习目标掌握触发器应用及工作原理；掌握AFTER与INSTEADOF触发器创建及差异；掌握游标的基本操作及应用；2.学习要点AFTER触发器（INSERT触发器、DELETE触发器、UPDATE触发器）使用；INSTEADOF触发器使用；游标的创建和执行流程；9-1触发器(Trigger)

步骤一：创建触发器前的准备工作：

在sell表和stock表中建立关联

步骤二：创建触发器，其中：Sale_num=原卖出数量+当前销售货物数量；

步骤三：测试触发器

任务：本月25日编号为9702的职员销售了货号为1001，货名为CPU的货物4个，销售价格为1000元。要求当插入销售记录时，能自动修改库存表中的卖出数量。什么是触发器

触发器是一类特殊的存储过程，其特殊性在于它并不需要由用户来直接调用，而是在对表或视图发出Insert、Update或Delete语句时自动触发执行的。因此，可将触发器看成是信息的后过滤器。特点：1、触发器是一种实施复杂数据完整性的特殊存储过程，但它不可以像调用存储过程一样由用户直接调用执行。2、触发器不能离开表而独立存在，其主要用于保护表中数据的完整性。触发器的类型（1）INSERT触发器：当向表中插入一条记录时，自动触发该表的INSERT触发器；（2）DELETE触发器：当删除表中的一条记录时，自动触发DELETE触发器；（3）UPDATE触发器：当修改表中的一条记录时，自动触发UPDATE触发器。任务：本月25日编号为9702的职员销售了货号为1001，货名为CPU的货物4个，销售价格为1000元。分析：本任务是添加了一条新的销售记录，因此触发器的类型为INSERT触发器。触发器的工作原理(insert触发器)

当插入新记录时，新的记录被增加到inserted表中，此时inserted表保存所插入记录的副本；

执行的操作针对SELL表数据执行触发器后生成的临时表Insert（插入）Inserted表：保存添加的新记录格式：Createtrigger触发器名称On表名WithencryptionForinsert，Update，deleteAsSql语句Go任务：在Users表中创建名为tr_user1的触发器，要求当添加一个新用户时，能马上显示添加的新用户信息。创建代码：Createtriggertr_user1On

users

For

insertAsSelect*frominsertedGo测试代码：Insertintousersvalues(‘a1’,’123’,’管理员’)执行的操作针对SELL表数据执行触发器后生成的临时表Delete（删除）deleted表：保存被删除的记录删除一条记录时，delete触发器自动创建一个deleted表，用于保存表中删除的记录，且原表中该记录已不存在。触发器的工作原理(delete触发器)任务：在Users表中创建名为tr_user2的触发器，当删除一个用户时，要求如下：1、能马上显示被删除的用户信息。2、能输出信息：被删除的用户是**创建代码：createtriggertr_user2onusersfordeleteasselect*fromdeletedselect'被删除的用户是：'+(selectusernamefromdeleted)go测试代码：Deleteuserswhereusername=‘a1’更新记录的步骤：1、删除原记录：表中原记录移动到deleted表中2、插入修改后的新记录：修改过的记录插入inserted表中。执行的操作针对SELL表数据执行触发器后生成的临时表Update（更新）deleted表：保存更新前的记录Inserted表：保存更新后的记录触发器的工作原理(update触发器)任务：在Users表中创建名为tr_user3的触发器，要求当修改一个用户信息时，能马上显示该用户修改前和修改后的信息。创建代码：Createtriggertr_user3OnusersForupdateAsSelect*fromdeletedSelect*frominserted测试代码：UpdateusersSetpwd=‘12345’Whereusername=‘guest’创建较复杂的INSERT触发器任务：本月25日编号为9702的职员销售了货号为1001，货名为CPU的货物4个，销售价格为1000元。要求当插入销售记录时，能自动修改库存表中的卖出数量。分析：1、该任务为添加一笔新的销售记录，因此要在sell表上创建INSERT触发器；2、添加的销售记录会更新STOCK表中的卖出数量，因此涉及的SQL语句为update语句。格式：Createtrigger触发器名称On表名WithencryptionForinsert，Update，deleteAsSql语句goSELLinsertUpdate语句Sale_num=原卖出数量+当前销售货物数量；创建触发器任务：本月25日编号为9702的职员销售了货号为1001，货名为CPU的货物4个，销售价格为1000元。要求当插入销售记录时，能自动修改库存表中的卖出数量。格式：Createtriggertr_sellOnsell

WithencryptionForinsertAsUpdatestockSetsale_num=sale_num+(selectsel_numfrominserted)Whereware_id=(selectware_idfrominserted)goSale_num=原卖出数量+当前销售货物数量；Inserted表测试触发器insertintosellValues('1001',1000,'2012-12-25',4,'9702')任务：本月25日编号为9702的职员销售了货号为1001，货名为CPU的货物4个，销售价格为1000元。现场测试要求：在“电脑销售管理系统”中，现在要求往货物表ware中插入一个货物时，自动的在库存表stock中插入该货物的库存信息，买入数量、卖出数量、库存量初始均为0。例如：在WARE表中插入记录'1007','cpu','intel','片‘则STOCK表中会自动插入一行，结果如下：创建UPDATE触发器要求当卖出数量修改时，能自动修改该货物的库存数量。其中：

库存数量=买入数量-卖出数量更新记录的步骤：1、删除原记录：表中原记录移动到deleted表中2、插入修改后的新记录：修改过的记录插入inserted表中。ifexists(select*fromsysobjectswherename='tr_stock_num')droptriggertr_stock_numgocreatetriggertr_stock_numonstockforupdateasupdatestocksetstock_num=buy_num-(selectsale_numfrominserted)whereware_id=(selectware_idfrominserted)go（1）在一个表上可建立多个名称不同、类型各异的触发器，每个触发器可由所有三种动作（Insert、Update、Delete）来引发，但每个触发器只能作用于一个表上。（2）所有建立和修改数据库及其对象的语句、所有Drop语句都不允许在触发器中使用。（3）在触发器定义中，可以使用IFUpdate子句来测试在Insert或Update语句是否对指定字段中有影响。如果将一个值赋给指定字段，则这个子句中的条件为真。（4）通常不要在触发器中返回任何结果，即不要在触发器定义中使用Select语句或变量赋值语句。如果必须使用变量赋值语句，请在触发器定义的开始部分使用SETNocount语句来避免返回结果使用CreateTrigger语句建立触发器时，需要注意5点：修改触发器AlterTrigger

触发器名重命名触发器sp_rename触发器原来的名称,触发器的新名称2、修改和重命名触发器3、查看触发器使用系统存储过程sp_help查看一个触发器的名称、所有者、类型以及建立时间。

sp_help触发器名称使用系统存储过程sp_helpTrigger查看一个表中的触发器类型。

sp_helpTrigger触发器所属表的名称使用系统存储过程sp_helpText查看一个未加密的触发器的定义。

sp_helpText触发器名称使用系统存储过程sp_depends查看一个触发器的依赖关系。

sp_depends触发器名称当删除一个表时，该表中包含的触发器自然也被删掉。如果只想从表中删除一个触发器，则可以使用企业管理器或DropTrigger语句来完成。使用企业管理器删除触发器a.在“树”窗格中展开服务器组，然后展开服务器；b.展开“数据库”文件夹，然后展开触发器所在的数据库；c.单击“表”节点，在内容窗格中右击触发器所在的表名称，并从弹出菜单选择“所有任务/管理触发器”命令；d.在触发器属性对话框中，从“名称”下拉列表中选择想要删除的触发器名称；e.单击“确定”，关闭触发器属性对话框。

4、删除触发器使用DropTrigger语句删除触发器DropTrigger语句用于从当前数据库中删除一个或多个触发器。

DropTrigger触发器名称[,…n]例如下面的语句从学生成绩数据库中删除一个名为InsertScoreTrigger的触发器：

USE学生成绩数据库

GoDropTriggerInsertScoreTrigger9-2游标

1.游标的概念

游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。Transact-SQL游标遵循ANSI-92标准。

2.使用游标的优点

允许程序对由查询语句SELECT返回的行集合中的每一行数据执行相同或不同的操作，而不是对整个行集合执行同一个操作 提供对基于游标位置的表中的行进行删除和更新的能力 游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来3游标的使用（1）使用游标的步骤

使用游标具体地说，有如下几个步骤：①创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。②打开游标。③从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。④对游标中的数据逐行操作。⑤关闭和释放游标。3游标的使用（2)游标的定义及使用过程

1）.声明游标声明游标是指用DECLARE语句声明或创建一个游标。声明游标的语法如下：DECLAREcursor_name[SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name_list]}]其中：

cursor_name:是游标的名字，为一个合法的SQLServer标识符，游标的名字必须遵循SQLServer命名规范。

SCROLL：表示取游标时可以使用关键字NEXT、PRIOR、

FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字的含义将在介绍FETCH子句时讲解。

select_statement：是定义游标结果集的标准SELECT语句，它可以是一个完整语法和语义的Transact-SQL的SELECT语句。但是这个SELECT语句必须有FROM子句，不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。

FORREADONLY：指出该游标结果集只能读，不能修改。

FORUPDATE：指出该游标结果集可以被修改。

OFcolumn_name_list：列出可以被修改的列的名单。应该注意:

游标有且只有两种方式：FORREADONLY或FORUPDATE。当游标方式指定为FORREADONLY时，游标涉及的表不能被修改。当游标方式指定为FORUPDATE时，可以删除或更新游标涉及的表中的行。通常，这也是缺省方式，即不指定游标方式时为FORUPDATE方式。声明游标的DECLARECURSOR语句必须是在该游标的任何OPEN语句之前。。3游标的使用2）.打开游标打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。打开游标的语法如下：

OPEN

cursor_name其中：

cursor_name是一个已声明的尚未打开的游标名。注意：当游标打开成功时，游标位置指向结果集的第一行之前。只能打开已经声明但尚未打开的游标。3游标的使用3）.从打开的游标中提取行游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（FETCH）行。SQLServer将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游标当前的位置，直到结果集中的行全部被提取。

从打开的游标中提取行的语法如下：

FETCH

[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE]

FROM]

cursor_name[INTOfetch_target_list]其中：

cursor_name：为一已声明并已打开的游标名字。NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是NEXT，即向下移动。

NEXT：取下一行数据。

PRIOR：取前一行数据。

FIRST：取第一行数据。3游标的使用

LAST：取最后一行数据。

ABSOLUTE：按绝对位置取数据。

RELATIVE：按相对位置取数据。另外，有２个全局变量@@FETCH_STATUS

和@@rowcount提供关于游标活动的信息

4）.关闭游标关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以再次用open语句打开它，SQLServer会用该游标的定义重新创建这个游标的一个结果集。关闭游标的语法如下：CLOSEcursor_name其中：cursor_name：是已被打开并将要被关闭的游标名字。在如下情况下，SQLServer会自动地关闭已打开的游标：当你退出这个SQLServer会话时从声明游标的存储过程中返回时3游标的使用5).释放游标释放(Deallocate)游标是指释放所有分配给此游标的资源，包括该游标的名字。释放游标的语法是：DEALLOCATECURSORcursor_name其中：

cursor_name：将要被DEALLOCATE释放的游标名字。如果释放一个已打开但未被关闭的游标，SQLServer会自动先关闭这个游标，然后再释放它。

关闭游标与释放游标的区别：关闭游标并不改变游标的定义，一个游标关闭后，不需要再次声明，就可以重新打开并使用它。但一个游标释放后，就释放了与该游标有关的一切资源，也包括游标的声明，游标释放后就不能再使用该游标了，如需再次使用游标，就必须重新定义。3游标的使用（1）更新数据通过在UPDATE语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位置的当前值。更新数据语法形式如下：

UPDATE

{table_name|view_name}SET[[{table_name.|view_name.}]

column_name

={new_value}[...n]

WHERECURRENTOFcursor_name其中：紧跟UPDATE之后的table_name|view_name：要更新的表名或视图名，可以加或不加限定。但它必须是声明该游标的SELECT语句中的表名或视图名。4使用游标修改数据4使用游标修改数据

column_name：是要更新的列的列名，可以加或不加限定。但它们必须是声明游标的SELECT语句中UPDATEOFcolumn_name_list的子集

new_value：为被更新列的新值，它可以是一个表达式、空值或子查询。

WHERECURRENTOF：使SQLServer只更新由指定游标的游标位置当前值确定的行。

cursor_name：是已声明为FORUPDATE方式并已打开的游标名。注意：①使用UPDATE...CURRENTOF语句一次只能更新当前游标位置确定的那一行，OPEN语句将游标位置定位在结果集第一行前，可以使用FETCH语句把游标位置定位在要被更新的数据行处。②用UPDATE...WHERECURRENTOF语句更新表中的行时，不会移动游标位置，被更新的行可以再次被修改，直到下一个FETCH语句的执行。③UPDATE...WHERECURRENTOF语句可以更新多表视图或被连接的多表，但只能更新其中一个表的行，即所有被更新的列都来自同一个表。4使用游标修改数据（2）删除数据通过在DELETE语句中使用游标来删除表或视图中的行。被删除的行依赖于游标位置的当前值。

删除数据语法形式如下：DELETE[FROM][[database.]owner.]{tab