数据库后台编程技术(三)

三：触发器

3.1：基本概念

触发器是一种特殊的存储过程，它不需要用户直接调用，在对表中的数据进行UPDATE、INSERTE或DELETE操作时自动触发。

保证了业务规则和数据完整性，完成比CHECK约束更加复杂的数据约束。

在触发器中，用户执行数据的更改操作时，SQL Server 自动创建和管理着两张表：INSERTED 和 DELETED 表。

DELETED表用于存储被更改的所有行在更改前的数据（按行进行）；

INSERTED表用于存储INSERT和UPDATE语句所影响的行的副本，是更改后的数据的副本。

三种类型：DML、DDL和登录触发器。

3.2：创建触发器

创建DML触发器:

CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF}
{[INSERT] [,] [UPDATE] [,] [DELETE]}
AS {sql_statement}

FOR | AFTER 为后触发型触发器，只有引发的SQL语句都成功执行，并且所有的约束检查也成功后，才执行。（不能在视图上
定义AFTER触发器），在同一种操作上可以建立多个触发器。

INSTEAD OF 为执行触发器，也叫前触发型触发器。在同一种操作上只能建立一个触发器。

所有的建立和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。

删除触发器

DROP TRIGGER schema_name.trigger_name

3.2.1：创建后触发型触发器

例 1：维护数据操作完整性的触发器。当销售单据明细表中的商品销售数量大于此商品的库存数量（在商品表）时，撤销此次商品的销售并给出提示信息。如果销售数量小于库存数量，则在插入销售单据明细记录时，应同步修改此商品的库存数量。

CREATE TRIGGER OperateCon
  ON Table_SaleBillDetail FOR INSERT
AS
  IF EXISTS (SELECT * FROM inserted a
             JOIN Table_Goods b ON a.GoodsID = b.GoodsID
             WHERE a.Quantity > b.Quantity)
BEGIN
  ROLLBACK
  PRINT'此商品库存量不足'
END
ELSE
  UPDATE Table_Goods SET TotalStorage = TotalStorage - (SELECT Quantity FROM inserted)

触发器用于不同表的列之间的取值约束关系。
CHECK只能实现同一个表中列之间的取值约束。

例 2：维护不同列之间的取值完整性的触发器。保证“商品表”中单价列的值于“商品价格变动表”中单价列的值一致。

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

3.2.2：创建前触发型触发器

例 1：创建保证销售单据表中使用的会员卡时有效日期内的会员卡触发器

CREATE TRIGGER CardValid
  ON Table_SaleBill INSTEAD OF INSERT, UPDATE
AS
  IF NOT EXISTS(SELECT * FROM inserted a
                JOIN Table_Card b ON a.CardID = b.CardID
                WHERE SaleDate NOT BETWEEN StartDate AND EndDate)
  INSERT INTO Table_SaleBill SELECT * FROM inserted

四：游标

4.1：游标的组成

游标结果集；游标当前行指令

4.2：使用游标

1：声明游标

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
  FOR select_name
[FOR{READY ONLY | UPDATE[OF column_name[,...n] ] } ]  

2：打开游标

OPEN cursor_name

3：提取数据

FETCH [ [ NEXT | PRIOR | FIRST | LAST
          | ABOSLUTE  n
          | RELATIVE  n ]
          FROM
          ]
cursor_name [INTO @variable_name [,...n]]

4：关闭游标

CLOSE cursor_name

5：释放游标

DEALLOCATE cursor_name

例：对Table_Customer表，定义一个查询“北京市海淀区”姓“王”的顾客姓名和邮箱游标，并输出游标结果。

DECLARE @cn VARCHAR(10), @Email 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_cursorv INTO @cn, @Email
WHILE @ @FETCH _STATUS = 0
BEGIN
  PRINT'顾客姓名：'+@cn+',邮箱：'+@Email
END
CLOSE Cname_cursor
DEALLOCATE Cname_cursor

例 2：声明带SCROLL选项的游标，并通过绝对定位功能实现当前行的任意方向的滚动。定义查询“北京海淀区”的所有顾客姓名和生日的游标，并将游标内容按出生日期降序排列。显示出游标结果中的最后一行，第4行，当前行后行的第3行以及当前行前边的第2行数据。

DECLARE CS_cursor SCROLL CURSOR FOR
  SELECT Cname, BirthDate FROM Table_Customer
  JOIN SC ON S.Sno = SC.Sno
  WHERE Address LIKE '北京市海淀区'
  ORDER BY BirthDate DESC
OPEN CS_cursor
FETCH LAST FROM CS_cursor.........................提取游标中的最后一行数据
FETCH ABSOLUTE 4 FROM CS_cursor...................提取游标中的第4行数据
FETCH RELATIVE 3 FROM CS_cursor...................提取当前行后边的第3个数据
FETCH RELATIVE -2 FROM CS_cursor..................提取当前行的前边的第2个数据
CLOSE CS_cursor
DEALLOCATE CS_cursor