数据库技术-数据库后台编程技术（二）

前言

上一篇文章数据库技术-数据库后台编程技术（一）介绍了数据库后台编程技术的存储过程以及用户定义函数相关内容，在这里，主要介绍一下触发器、游标。

目录

触发器

• 基本概念
• 创建触发器
• 删除触发器

游标

• 游标的组成
• 使用游标
• 游标示例

总结
习题

触发器

基本概念

「什么是触发器」
触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用,而是在对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的
。

「触发器作用及特点」
触发器通常用于保证业务规则和数据完整性,其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

「触发器的适用场景」

• 完成比CHECK约束更复杂的数据约束。注意：与CHECK约束不同，触发器可以引用其他表的列。
• 为保证数据库性能而维护的非规范化数据。
• 可实现复杂的商业规则。触发器可使业务的处理任务自动进行。
• 触发器也可以评估数据修改前后的表状态。并根据其差异来取对策。

「触发器的类型」

• DML
  如果用户要通过数据操作语言(DML)事件编相数据,则执行DML触发器。DML事件是针对表或视图的INSERT, UPDATE或DELETE语句。
• DDL
  DDL触发器用于响应各种数据定义语言(DDL)事件,这些事件主要对应T-SQL中的CREATE、ALTER和DROP语句,以及执行类似DDL操作的某些系统存储过程。
• 登录触发器
  登录触发器在遇到LOGON事件时触发, LOGON事件是在建立用户会话时引发的。

以下只介绍DML触发器。

创建触发器

建立DML触发器的SQL语句为CREATE TRIGGER ,其语法格式为:

CREATE TRIGGER trigger_name ON 
{ table | view }
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF }  #  INSTEAD OF指定执行触发器而不是执行引发触发器执行的SQL语句从而代替出发语句的操作
{ [ INSERT ] [ , ] [ UPDATE ] [ , ]  [ DELETE ] } # 引发触发器执行的操作若同时指定多个操作，则各操作之间用逗号分隔
AS 
sql_statement[…n] 

复制

「参数说明」

trigger_name: 触发器名称。该名称必须遵循标识符规则,而且不能以#或##开头。
table | view: 与触发器相关联的表或视图有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。
FOR I AFTER :指定触发器只有在引发的SQL语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后,才执行此触发器。如果仅指定FOR关键字,则AFTER为默认值。

复制

「注意」

❝

• 在视图上只能定义INSTEAD OF触发器。
• 一个表可建多个触发器，每个触发器可由三个操作触发。
• ALTER类型同一操作上建立多个触发器，INSTEAD OF类型同一操作上建立一个触发器。
• 在触发器定义中，可以使用IF UPDATE子句来测试INSERT和UPDATE语句是否对指定字段有影响。如果将一个值赋给招定字段或更改了指定字段，则这个子句就为真。
• 所有建立和更改数据库以及数据库对象的语句、DROP语句不允许砸在触发器中用。
• 触发器不要返回任何结果。

❞

在触发器语句中使用的两个特殊临时工作表：INSERTED以及DELETED表。「DELETED表」

• DELETED表用于存储DELETE和IUPDATE语句所影响的行的复本。
• 在执行DELETE操作时,被删除的数据被保存到DELETED表中。
• 在执行UPDATE操作时.对被修改操作影响的所有数据行,将更改前的数据(按行进行)保存到DELETED表中。
• DELETED表和执行操作的基本表通常没有相同的数据行。

「INSERTED表」

• INSERTED表用于存储INSERTUPDATE语句所影响的行的副本。
• 在执行INSERT操时,新插入的数据同时被保存到INSERTED表中。
• 在执行UPDATE操作时,对被修改操作影响所有数据行，将更改后的数据(按行进行)保存到INSERTED表中。
• INSERTED表中的内容是执行操作的基本表中新数据行的副本。

「注意」

❝

这两个临时工作表是在用户执行数据的更改操作时，SQL Server 自动创建和管理的，驻留在内存中，结构与触发器所作用的基本表一致，只可以被触发器使用，触发器结束时系统自动释放两个表的空间。在触发器中可以对临时表进行查询操作，但不能直接进行数据更改的操作。

❞

「创建后触发型触发器」
描述:使用FOR或AFTER选项定义的触发器为后触发型触发器,即只有在引发触发器执行的语句中的操作都已成功执行并且所有的约束检查也成功完成后,才执行触发器
。

「实例」
维护不同列的取值完整性的触发器。保证“商品表”中单价列值与“商品价格变动表”中单价列值一致。

CREATE TRIGGER UnitPriceConsistent
ON Table_PriceHistory FOR INSERT,UPDATE
AS
   DECLARE @NewPrice money
   SELECT @NewPrice=SaleUnitPrice FROM inserted   UPDATE Table_Goods SET SaleUnitPrice=@NewPrice 
    WHERE GoodsID IN (SELECT GoodsID FROM inserted)

复制

「几点注意」

❝

• 触发器与引发触发器执行的操作共同构成了一个事务,事务的开始是引发触发器执行的操作,事务的结束是触发器的结束。
• 由于AFTER型的触发器在执行时引发触发器执行的操作已执行完了,因此在触发器中应使ROLLBACK撤销不正确的操作,这里的ROLLBACK实际是回滚到引发触发器执行的操作之前的状态。
• 如果不同表中的列之间存在取值约束关系,则只能用触发器实现,不能用CHECK约束实现,因为CHECK约束只能实现同一个表中列之间的取值约束。

❞

「创建前触发型触发器」
描述:使用INSTEAD OF选项定义的触发器为前触发型触发器。在这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句,从而替代引发语句的操作
。「在表或视图上,每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器」。

「实例」
创建只允许删除会员卡积分低于500分的顾客记录的触发器。

CREATE TRIGGER DeleteCust
ON Table_Customer INSTEAD OF DELETE 
AS
   IF NOT EXISTS(SELECT * FROM deleted WHERE CardID IN (SELECT CardID FROM Table_Card WHERE Score>=500))
   DELETE FROM Table_Customer WHERE CardID IN (SELECT CardID FROM deleted)

复制

删除触发器

删除触发器使用DROP TRIGGER语句实现，它从当前数据库中删除一个或多个触发器。语法格式为:

DROP TRIGGER schema_name.trigger_name[, ..n][;]

复制

游标

关系数据库中的操作是基于集合的操作，即对整个行集产生影响，由SELECT语句返回的行集包括所有满足条件子句的行，这一完整的行集称为结果集。有时用户需要对结果集中的某一行或部分行进行单独的处理，这时在SELECT结果集中是无法实现的，，游标就是提供这种机制的结果集扩展，即可以逐行处理结果集。

游标的组成

游标包括如下两部分内容:

• 游标结果集（SELECT返回结果集）:指定义游标的SELECT语句返回的结果的集合。
• 游标当前行指针:指向该结果集中的某行的指针。

游标具有以下特点：

• 允许定位结果集中的特定行
• 允许从结果集的当前位置检索一行或多行
• 支持对结果集中当前行的数据进行修改
• 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。

使用游标

使用游标的典型过程如下图所示:

「声明游标」
声明游标实际是定义服务器端游标的特性，例如游标的滚动行为和用于生成游标结果集的查询语句。SQL Server支持如下两种格式的声明游标语句:

• 基于ISO标准的语法
• 使用T-SQL扩展的语法

基于ISO标准的语法格式如下:

DECLARE cursor_name [INSENSITIVE ][SCROLL ] CURSOR
FOR select_statement
[FOR { READ ONLY| UPDATE [OF column name [,...n] ]|]

复制

「参数说明」

cursor_name :所定义的服务器游标名。
INSENSITIVE :定义一个游标,以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb数据库中的这一临时表中得到应答;因此,在对该游标进行提取操作时,返回的数据不反映对基本表所做的修改,并且在该游标中不允许修
改基本表数据。如果省略INSENSITIVE ,则已提交的(任何用户)对基本表的删除和更新都会反映在后面的提取操作中。
SCROLL :指定所有的提取选项( FIRST、 LAST、PRIOR、 NEXT、RELATIVE、 ABSOLUTE )均可用。如果未在DECLARE CURSOR中指定SCROLL ,则NEXT是唯一支持的提取选项。如果指定了FAST FORWARD，则不能指定SCROLL。
select_statement :定义游标结果集的标准SELECT语句。
READ ONLY :禁止通过该游标更新数据。在UPDATE或DELETE语句的WHERE CURRENTOF子句中不能引用该游标。
UPDATE[OF column_name[ , ..n]] :定义游标中可更新的列。如果指定了OF column name [, ..n]),则只允许修改所列出的列。如果指定了UPDATE ,但未指定column_name[ , ..n]) ,则可以更新所有的列。

复制

「提取数据」
游标被声明和打开之后,游标的当前行指针就位于结果集中的第一行位置
,可以使用FETCH语句从游标结果集中按行提取数据。
其语法格式如下

FETCH[[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]
FROM
cursor_ name [INTO @ variable_ name [,.n]]

复制

「参数说明」

NEXT :返回紧跟在当前行之后的数据行,并且当前行递增为结果行。如果FETCH NEXT是对游标的第一次提取操作,则返回结果集中的第一行。NEXT为默认选项。
PRIOR :返回紧临当前行前面的数据行。并且当前行递减为结果行。如果FETCH PRIOR为对游标的第一次提取操作,则不返回任何结果并将游标当前行置于第一行之前。
FIRSTS :返回游标中的第一行并将其作为当前行。
LAST :返回游标中的最后一行并将其作为当前行。
ABSOLUTE ：如果n为正数,返回从游标第一行开始的第n行,并将返回的行变成新的当前行。如果n为负数,则返回从游标最后一行开始之前的第n行,并将返回的行变成新的当前行。如果n为0,则不返回任何结果。n必须为整型常量。

复制

INTO @variable_name [, ..n] :
将提取的列数据保存到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列对应。各变量的数据类型必须与相应的结果列的数据类型匹配。变量的数目必须与游标选择列表中的列的数目一致。在对游标数据进行提取的过程中,可以使用@@FETCH_STATUS全局变量判断数据提取的状态。@@FETCH_STATUS返回FETCH语句执行后的游标最终状态。

「@@FETCH_STATUS的一些注意事项」

❝

• 在对游标数据进行提取的过程中，可以使用@@FETCH_STATUS全局变量判断数据提取的状态。
• @@FETCH_STATUS返回FETCH语句执行后的游标最终状态。@@FETCH_STATUS返回的数据类型是int。
• 由于@@FETCH_STATUS对于在一个连接上的所有游标是全局性的，不管是对哪个游标，只要执行一次FETCH语句 ，系统都会对@@FETCH_STATUS赋一次值，以表明该FETCH语句的执行情况。因此，在每次执行完条FETCH语句后，都应该测试一下@@FETCH_STATUS全局变量的值，以观测当前提取游标数据语句的执行情况。
• 在对游标进行提取操作前，@@FETCH_STATUS的值没有定义。

❞

如下表是@@FETCH_STATUS的取值和含义:

「关闭游标」
关闭游标使用CLOSE语句，其语法格式为:

CLOSE cursor_name

复制

在使用CLOSE语句关闭游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用OPEN语句时可以重新打开此游标。「释放游标」
释放游标是释放分配给游标的所有资源。释放游标使用DEALLOCATE语句,其语法格式为:

DEALLOCATE Cursor_name

复制

游标示例

对Table_Customer表，定义一个查询“长沙岳麓区”姓“王”的顾客姓名和邮箱的游标，并输出游标结果。

DECLARE @cn VARCHAR(10 @cn VARCHAR(50)
DECLARE Cname_cursor CURSOR FOR
  SELECT Cname,Email FROM Table_Customer
  WHERE Cname LIKE ‘王%’AND Address LIKE ‘长沙岳麓区’
OPEN Cname_cursor
FETCH NEXT FROM Cname_cursor INTO @cn , @Email
WHILE @@ FETCH_STATUS=0
BEGIN
  PRINT’顾客姓名’+@cn +‘，邮箱：’+ @Email
  FETCH NEXT FROM Cname_cursor INTO @cn , @Email
END
CLOSE Cname_cursor
DEALLOCATE Cname_cursor

复制

总结

触发器是由INSERT、UPDATE、DELETE语句事件引发自动执行的一段程序代码，每当这几类事件在指定的表上出现时，定义在触发器中的代码会自动执行。触发器被用于加强数据的完整性，实现复杂的商业规则，常用于实现用申明完整性约束实现不了的复杂的数据约束条件。游标是一个查询结果,游标机制为用户提供了在查询结果集中进行行、列定位的操作,使得人们可以深入到结果集内部进行操作。

习题

设在SQL Server 2008某数据库中有商品表和销售表，表的定义如下：CREATE TABLE 商品表( 商品号     char(10) PRIMARY KEY, 商品名     char(20) NOT NULL, 单价       int  DEFAULT 0)

CREATE TABLE 销售表( 商品号     char(10), 销售时间   datetime, 销售数量   int NOT NULL, PRIMARY KEY(商品号,销售时间), FOREIGN KEY(商品号) REFERENCES 商品表(商品号))

现要创建一个具有如下功能的用户自定义标量函数：根据指定的商品号，返回该商品的销售总金额。请补全下列代码（请不要为表起别名）。