SqlServer触发器的原理及案例.docx

1、SqlServer触发器的原理及案例 SqlServer触发器的原理及案例合理的选用触发器会让你的系统更高效2010Ssc第1章 何为触发器1.1 触发器的 本质 触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录、更改记录或者删除记录时，当事件发生时，才被 自动地激活。 1.2 这样做带来的 功能： 触发器可以用来对表实施复杂的完整性约束，保持数据的一致性，当触发器所保护的数据发生改变时，触发器会自动被激活，响应同时执行一定的操作（对其它相关表的操作），从而保证对数据的不完整性约束或不正确的修改。触发器可以查询其它表，同时也可以执行复杂的T-SQL语句。触发器和引发触发器执

2、行的命令被当作一次事务处理，因此就具备了事务的所有特征。注意： 事务具备什么特征？在触发器中的作用？ 如果发现引起触发器执行的T-SQL语句执行了一个非法操作，比如关于其它表的相关性操作，发现数据丢失或需调用的数据不存在，那么就回滚到该事件执行前的SQL SERVER数据库状态。 1.3 触发器的作用触发器可以对数据库进行级联修改，这一点刚才已经说过了。需要说明的是： 触发器和约束的关系和区别 (1)一般来说，使用约束比使用触发器效率更高。 (2)同时，触发器可以完成比CHECK约束更复杂的限制。 1.4 说明： 1. 与CHECK约束不同，在触发器中可以引用其它的表。 2. 触发器可以发现改

3、变前后表中数据的不一致，并根据这些不同来进行相应的操作。3. 2对于一个表不同的操作（INSERT、UPDATE、DELETE）可以采用不同的触 发器，即使是对相同的语句也可以调用不同的触发器来完成不同的操作。 举例1：在签订一份订单时，货物的库存量应减少。 问？这应用了触发器的什么特征？CHECK约束能解决吗？ 举例2：正在进行整理的货物不能下订单。 问？这应用了触发器的什么特征？CHECK约束能解决吗第2章 对触发器3种操作的分析在SQL SERVER为每个触发器都创建了两个专用表：inserted表和deleted表。 这是两个逻辑表，由系统来维护，在触发执行时存在，在触发结束时消失。这

4、样有什么用途？ 带着问题看，具体操作步骤和过程： (1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。 在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted 表，这两个表不会有共同的行。 (2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。 在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中， inserted表的内容是激活触发器的表中新行的拷贝。 说明：update事务可以看作是先执行一个delete操作，再执行一个inser

5、t操作，旧的行首先 被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。 11.1.3 instead of 和 after 触发器 主要包括定义和应用范围条件，操作执行时机； 2.1 创建触发器 1、考虑为什么要设计出发器，为解决什么问题而设计？ 2、应制定的内容：为什么，大家思考一下？不知道，看下面的例子，全看完！ T-SQL语句创建触发器 语法结构：/*/create trigger 触发器名 on 表或视图for|after|instead of -操作时机insert,update,deleteassql语句/*/2.2 学习案例2.2.1 建立表在n

6、orthwind表中建立2个表：cust_test和order_testcust_test: CustomerID char(5) PK order_test:CustomerID char(5)-对应关系Custcity Orderid PKCustname OrderNamesCStatus int OStatus int -状态Cstorage int Orders int -定购量和库存量Cdate date Odate date-日期2.2.2 触发器练习1在cust_test表中建立删除触发器，实现上述2表的级联删除。答案1：/*/use northwindgocreate tri

7、gger cust_orders_del1on Cust_testafter deleteas delete from order_testwhere CustomerID in(select CustomerID from deleted)Go/*/2.2.3 触发器练习2： 在order_test表建立insert触发器，当向order_test表插入一行，如果cust_test表中对应 记录status值为1，说明处于准备状态不能写入该数据。/*/use northwindgocreate trigger cust_orders_ins2on order_testafter insert

8、asif (select cstatus from cust_test,inserted where cust_test.customerid=inserted.customerid)=1beginprint The Goods is being processedrollback transactionendgo/*/第3章 图形化操作触发器 3.1 查看触发器情况 查看触发器的名称，拥有者和五个布尔值 supdate,isdelete,isinsert,isafter,isinsteadof(2)sp_helptext 触发器名 查看文本信息 (3)设置某一触发器的无效和重新有效 无效：u

9、se northwindalter table 表名disable trigger 触发器名重新有效：use northwindalter table 表名enable trigger 触发器名(4）删除触发器 use northwinddrop trigger 触发器名，触发器名作业3： 在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。 作业4： 在order_test表上建立一个插入触发器，规定订单日期(Odate)不能手工修改。 作业5： 要求订购的物品一定要在仓库中有的，并且数量足够。例6： 在order_test表上建

10、立一个插入触发器，同时插入多行数据时，要求订购的物品一定要在仓库中有的。 答案3： use northwindgo create trigger cust_orders_ins3on order_testafter insertas update cust_test set cstorage=cstorage-inserted.ordersfrom cust_test,insertedwhere cust_test.customerid=inserted.customerid答案4： use northwindgocreate trigger orderdateupdateon order_t

11、estafter updateasif update (odate)beginraiserror(Error,10,1)rollback transactionend答案5： use northwindgocreate trigger order_insert5on order_testafter insertasbeginif(select count(*)from cust_test,insertedwhere cust_test.customerid=inserted.customerid)=0beginprint No entry in goods for your orderroll

12、back transactionendif(select cust_test.cstorage from cust_test,inserted where cust_test.customerid=inserted.customerid)(select inserted.orders from cust_test,inserted where cust_test.customerid=inserted.customerid)beginprint No enough entry in goods for your orderrollback transactionendend答案6： use n

13、orthwindgocreate trigger order_insert6on order_testafter insertasif (select count(*) from cust_test,insertedwhere cust_test.customerid=inserted.customerid)rowcount-可以在触发器逻辑中使用 ROWCOUNT 函数以区分单行插入和多行插入。begindelete order_test from order_test,insertedwhere order_test.orderid=inserted.orderid andinserted

14、.customerid not in (select customerid from cust_test)endprint rowcountTransact-SQL 参考 SET ROWCOUNT 使 Microsoft? SQL Server? 在返回指定的行数之后停止处理查询。 语法 SET ROWCOUNT number | number_var 参数 number | number_var是在停止给定查询之前要处理的行数（整数）。 注释 建议将当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句重新编写为使用 TOP 语法。有关更多信息，请参见 D

15、ELETE、INSERT 或 UPDATE。 对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句，忽略 SET ROWCOUNT 选项设置。 若要关闭该选项（以便返回所有的行），请将 SET ROWCOUNT 指定为 0。 说明 设置 SET ROWCOUNT 选项将使大多数 Transact-SQL 语句在已受指定数目的行影响后停止处理。这包括触发器和 INSERT、UPDATE 及 DELETE 等数据修改语句。ROWCOUNT 选项对动态游标无效，但限制键集的行集和不感知游标。使用该选项时应谨慎，它主要与 SELECT 语句一起使用。 如果行数的

16、值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。 SET ROWCOUNT 的设置是在执行或运行时设置，而不是在分析时设置。 权限 SET ROWCOUNT 权限默认授予所有用户。 示例 SET ROWCOUNT 在指定的行数后停止处理。在下例中，注意有 x 行满足预付款少于或等于 $5,000 的条件；但是，从更新所返回的行数中可以看出并非所有的行都得到处理。ROWCOUNT 影响所有的 Transact-SQL 语句。 USE pubsGOSELECT count(*) AS CntFROM titles WHERE advance = 5000GO下面是结

17、果集： Cnt - 11 (1 row(s) affected)现在，将 ROWCOUNT 设置为 4，并更新预付款等于或大于 $5,000 的所有行。SET ROWCOUNT to 4.SET ROWCOUNT 4GOUPDATE titlesSET advance = 5000WHERE advance = 5000GO第4章 触发器中的变量操作declare djbhselect djbh = 字段名from table_name 第5章 SQL触发器语法参考CreateTRIGGER trigger_nameON table | view WITH ENCRYPTION FOR |AF

18、TER | INSTEAD OF Insert , Update WITH APPEND NOT FOR REPLICATION AS IF Update ( column ) AND | or Update ( column ) .n | IF ( COLUMNS_UpdateD ( ) bitwise_operator updated_bitmask) comparison_operator column_bitmask .n sql_statement .n 参数trigger_name是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称

19、。Table | view是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。WITH ENCRYPTION加密 syscomments 表中包含 Create TRIGGER语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server复制的一部分发布。AFTER指定触发器只有在触发 SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER是默认设置。不能在视图上定义 AFTER 触发器。INSTEAD OF指定执行触

20、发器而不是执行触发 SQL语句，从而替代触发语句的操作。在表或视图上，每个 Insert、Update 或 Delete语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有INSTEAD OF 触发器的视图上定义视图。INSTEAD OF 触发器不能在 WITH CHECK OPTION的可更新视图上定义。如果向指定了 WITH CHECK OPTION选项的可更新视图添加 INSTEAD OF 触发器，SQL Server将产生一个错误。用户必须用 Alter VIEW 删除该选项后才能定义 INSTEADOF 触发器。 Delete , Insert ,Update 是

21、指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于 INSTEAD OF 触发器，不允许在具有 ON Delete级联操作引用关系的表上使用 Delete 选项。同样，也不允许在具有 ONUpdate 级联操作引用关系的表上使用 Update 选项。WITH APPEND指定应该添加现有类型的其它触发器。只有当兼容级别是 65或更低时，才需要使用该可选子句。如果兼容级别是 70或更高，则不必使用 WITH APPEND子句添加现有类型的其它触发器（这是兼容级别设置

22、为 70 或更高的 CreateTRIGGER 的默认行为）。有关更多信息，请参见sp_dbcmptlevel。WITH APPEND 不能与 INSTEAD OF触发器一起使用，或者，如果显式声明 AFTER触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持WITH APPEND 和 FOR（将被解释为 AFTER）。NOT FOR REPLICATION表示当复制进程更改触发器所涉及的表时，不应执行该触发器。AS是触发器要执行的操作。sql_statement是触发器的条件和操作。

23、触发器条件指定其它准则，以确定Delete、Insert 或 Update 语句是否导致执行触发器操作。当尝试 Delete、Insert 或 Update操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的 Transact-SQL语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的Transact-SQL 语句常常包含控制流语言。Create TRIGGER语句中使用几个特殊的表：deleted 和 inserted是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操

24、作可能更改的行的旧值或新值。例如，若要检索deleted 表中的所有值，请使用：Select *FROM deleted如果兼容级别等于 70，那么在 Delete、Insert 或Update 触发器中，SQL Server 将不允许引用 inserted 和 deleted 表中的text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的text、ntext 和 image 值。若要在 Insert 或 Update触发器中检索新值，请将 inserted 表与原始更新表联接。当兼容级别是 65或更低时，对 inserted 或 deleted 表中允

25、许空值的text、ntext 或 image列，将返回空值；如果这些列不可为空，则返回零长度字符串。当兼容级别是 80 或更高时，SQL Server 允许在表或视图上通过 INSTEADOF 触发器更新 text、ntext 或 image 列。n是表示触发器中可以包含多条 Transact-SQL语句的占位符。对于 IF Update (column) 语句，可以通过重复 Update(column) 子句包含多列。IF Update (column)测试在指定的列上进行的 Insert 或 Update操作，不能用于 Delete 操作。可以指定多列。因为在 ON子句中指定了表名，所以在

26、 IF Update子句中的列名前不要包含表名。若要测试在多个列上进行的 Insert 或Update 操作，请在第一个操作后指定单独的 Update(column) 子句。在Insert 操作中 IF Update 将返回 TRUE值，因为这些列插入了显式值或隐性 (NULL) 值。说明 IF Update (column) 子句的功能等同于IF、IF.ELSE 或 WHILE 语句，并且可以使用 BEGIN.END语句块。有关更多信息，请参见控制流语言。可以在触发器主体中的任意位置使用 Update (column)。column是要测试 Insert 或 Update 操作的列名。该列可

27、以是 SQL Server支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。IF (COLUMNS_UpdateD()测试是否插入或更新了提及的列，仅用于 Insert 或 Update触发器中。COLUMNS_UpdateD 返回 varbinary位模式，表示插入或更新了表中的哪些列。COLUMNS_UpdateD函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含8 列以上，则 COLUMNS_UpdateD返回多个字节，最左边的为最不重要的字节。在 Insert 操作中

28、COLUMNS_UpdateD 将对所有列返回 TRUE值，因为这些列插入了显式值或隐性 (NULL) 值。可以在触发器主体中的任意位置使用 COLUMNS_UpdateD。bitwise_operator是用于比较运算的位运算符。updated_bitmask是整型位掩码，表示实际更新或插入的列。例如，表 t1 包含列C1、C2、C3、C4 和 C5。假定表 t1 上有 Update 触发器，若要检查列C2、C3 和 C4 是否都有更新，指定值 14；若要检查是否只有列 C2有更新，指定值 2。comparison_operator是比较运算符。使用等号 (=) 检查 updated_bit

29、mask中指定的所有列是否都实际进行了更新。使用大于号 () 检查updated_bitmask 中指定的任一列或某些列是否已更新。column_bitmask是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。注释触发器常常用于强制业务规则和数据完整性。SQL Server通过表创建语句（Alter TABLE 和 Create TABLE）提供声明引用完整性(DRI)；但是 DRI不提供数据库间的引用完整性。若要强制引用完整性（有关表的主键和外键之间关系的规则），请使用主键和外键约束（AlterTABLE 和 Create TABLE 的 PRIMARY KEY 和 FOREIGN

30、 KEY关键字）。如果触发器表存在约束，则在 INSTEAD OF 触发器执行之后和AFTER 触发器执行之前检查这些约束。如果违反了约束，则回滚 INSTEAD OF触发器操作且不执行（激发）AFTER 触发器。可用 sp_settriggerorder 指定表上第一个和最后一个执行的 AFTER触发器。在表上只能为每个 Insert、Update 和 Delete操作指定一个第一个执行和一个最后一个执行的 AFTER触发器。如果同一表上还有其它 AFTER触发器，则这些触发器将以随机顺序执行。如果 Alter TRIGGER语句更改了第一个或最后一个触发器，则将除去已修改触发器上设置的第一个或最后一个特性，而且必须用sp_settriggerorder 重置排序值。只有当触发 SQL语句