SqlServer触发器的原理及案例.docx
《SqlServer触发器的原理及案例.docx》由会员分享，可在线阅读，更多相关《SqlServer触发器的原理及案例.docx（28页珍藏版）》请在冰点文库上搜索。
SqlServer触发器的原理及案例
SqlServer触发器的原理及案例
合理的选用触发器会让你的系统更高效
2010
Ssc
第1章
何为触发器
1.1触发器的'本质'
触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录、更改记录或者删除记录时，当事件发生时，才被自动地激活。
1.2这样做带来的'功能'：
触发器可以用来对表实施复杂的完整性约束，保持数据的一致性，当触发器所保护的数据发生改变时，触发器会自动被激活，响应同时执行一定的操作（对其它相关表的操作），从而保证对数据的不完整性约束或不正确的修改。
触发器可以查询其它表，同时也可以执行复杂的T-SQL语句。
触发器和引发触发器执行的命令被当作一次事务处理，因此就具备了事务的所有特征。
注意：
'事务具备什么特征？
在触发器中的作用？
'
如果发现引起触发器执行的T-SQL语句执行了一个非法操作，比如关于其它表的相关性操作，发现数据丢失或需调用的数据不存在，那么就回滚到该事件执行前的SQLSERVER数据库状态。
1.3触发器的作用
触发器可以对数据库进行级联修改，这一点刚才已经说过了。
需要说明的是：
'触发器和约束的关系和区别'
（1）一般来说，使用约束比使用触发器效率更高。
（2）同时，触发器可以完成比CHECK约束更复杂的限制。
1.4说明：
1.与CHECK约束不同，在触发器中可以引用其它的表。
2.触发器可以发现改变前后表中数据的不一致，并根据这些不同来进行相应的操作。
3.2对于一个表不同的操作（INSERT、UPDATE、DELETE）可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器来完成不同的操作。
举例1：
在签订一份订单时，货物的库存量应减少。
问？
这应用了触发器的什么特征？
CHECK约束能解决吗？
举例2：
正在进行整理的货物不能下订单。
问？
这应用了触发器的什么特征？
CHECK约束能解决吗
第2章对触发器3种操作的分析
在SQLSERVER为每个触发器都创建了两个专用表：
inserted表和deleted表。
这是两个逻辑表，由系统来维护，在触发执行时存在，在触发结束时消失。
这样有什么用途？
带着问题看，具体操作步骤和过程：
（1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。
在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted表，这两个表不会有共同的行。
（2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。
在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中，
inserted表的内容是激活触发器的表中新行的拷贝。
说明：
update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先
被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。
11.1.3insteadof和after触发器
主要包括定义和应用范围条件，操作执行时机；
2.1创建触发器
1、考虑为什么要设计出发器，为解决什么问题而设计？
2、应制定的内容：
为什么，大家思考一下？
不知道，看下面的例子，全看完！
T-SQL语句创建触发器
语法结构：
/****************************/
createtrigger触发器名
on表或视图
for|after|insteadof--操作时机
insert,update,delete
as
sql语句
/****************************/
2.2学习案例
2.2.1建立表
在northwind表中建立2个表：
cust_test和order_test
cust_test:
CustomerIDchar（5）PKorder_test:
CustomerIDchar（5）--对应关系
CustcityOrderidPK
CustnameOrderNames
CStatusintOStatusint--状态
CstorageintOrdersint--定购量和库存量
CdatedateOdatedate--日期
2.2.2触发器练习1
在cust_test表中建立删除触发器，实现上述2表的级联删除。
答案1：
/****************************/
usenorthwind
go
createtriggercust_orders_del1
onCust_test
afterdelete
as
deletefromorder_test
whereCustomerIDin
（selectCustomerIDfromdeleted）
Go
/****************************/
2.2.3触发器练习2：
在order_test表建立insert触发器，当向order_test表插入一行，如果cust_test表中对应
记录status值为1，说明处于准备状态不能写入该数据。
/****************************/
usenorthwind
go
createtriggercust_orders_ins2
onorder_test
afterinsert
as
if（selectcstatusfromcust_test,insertedwhere
cust_test.customerid=inserted.customerid）=1
begin
print'TheGoodsisbeingprocessed'
rollbacktransaction
end
go
/****************************/
第3章图形化操作触发器
3.1查看触发器情况
查看触发器的名称，拥有者和五个布尔值
supdate,isdelete,isinsert,isafter,isinsteadof
（2）sp_helptext触发器名
查看文本信息
（3）设置某一触发器的无效和重新有效
无效：
usenorthwind
altertable表名
disabletrigger触发器名
重新有效：
usenorthwind
altertable表名
enabletrigger触发器名
（4）删除触发器
usenorthwind
droptrigger触发器名，触发器名
作业3：
在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。
作业4：
在order_test表上建立一个插入触发器，规定订单日期（Odate）不能手工修改。
作业5：
要求订购的物品一定要在仓库中有的，并且数量足够。
例6：
在order_test表上建立一个插入触发器，同时插入多行数据时，要求订购的物品一定要在仓库中有的。
答案3：
usenorthwind
go
createtriggercust_orders_ins3
onorder_test
afterinsert
as
updatecust_testsetcstorage=cstorage-inserted.orders
fromcust_test,inserted
wherecust_test.customerid=inserted.customerid
答案4：
usenorthwind
go
createtriggerorderdateupdate
onorder_test
afterupdate
as
ifupdate（odate）
begin
raiserror（'Error',10,1）
rollbacktransaction
end
答案5：
usenorthwind
go
createtriggerorder_insert5
onorder_test
afterinsert
as
begin
if（selectcount（*）
fromcust_test,inserted
wherecust_test.customerid=inserted.customerid）=0
begin
print'Noentryingoodsforyourorder'
rollbacktransaction
end
if（selectcust_test.cstoragefromcust_test,inserted
wherecust_test.customerid=inserted.customerid）<
（selectinserted.ordersfromcust_test,inserted
wherecust_test.customerid=inserted.customerid）
begin
print'Noenoughentryingoodsforyourorder'
rollbacktransaction
end
end
答案6：
usenorthwind
go
createtriggerorder_insert6
onorder_test
afterinsert
as
if
（selectcount（*）fromcust_test,inserted
wherecust_test.customerid=inserted.customerid）<>@@rowcount
--可以在触发器逻辑中使用@@ROWCOUNT函数以区分单行插入和多行插入。
begin
deleteorder_testfromorder_test,inserted
whereorder_test.orderid=inserted.orderidand
inserted.customeridnotin（selectcustomeridfromcust_test）
end
print@@rowcount
Transact-SQL参考
SETROWCOUNT
使Microsoft?
SQLServer?
在返回指定的行数之后停止处理查询。
语法
SETROWCOUNT{number|@number_var}
参数
number|@number_var
是在停止给定查询之前要处理的行数（整数）。
注释
建议将当前使用SETROWCOUNT的DELETE、INSERT和UPDATE语句重新编写为使用TOP语法。
有关更多信息，请参见DELETE、INSERT或UPDATE。
对于在远程表和本地及远程分区视图上执行的INSERT、UPDATE和DELETE语句，忽略SETROWCOUNT选项设置。
若要关闭该选项（以便返回所有的行），请将SETROWCOUNT指定为0。
说明设置SETROWCOUNT选项将使大多数Transact-SQL语句在已受指定数目的行影响后停止处理。
这包括触发器和INSERT、UPDATE及DELETE等数据修改语句。
ROWCOUNT选项对动态游标无效，但限制键集的行集和不感知游标。
使用该选项时应谨慎，它主要与SELECT语句一起使用。
如果行数的值较小，则SETROWCOUNT替代SELECT语句TOP关键字。
SETROWCOUNT的设置是在执行或运行时设置，而不是在分析时设置。
权限
SETROWCOUNT权限默认授予所有用户。
示例
SETROWCOUNT在指定的行数后停止处理。
在下例中，注意有x行满足预付款少于或等于$5,000的条件；但是，从更新所返回的行数中可以看出并非所有的行都得到处理。
ROWCOUNT影响所有的Transact-SQL语句。
USEpubs
GO
SELECTcount（*）ASCnt
FROMtitles
WHEREadvance>=5000
GO
下面是结果集：
Cnt
-----------
11
（1row（s）affected）
现在，将ROWCOUNT设置为4，并更新预付款等于或大于$5,000的所有行。
SETROWCOUNTto4.
SETROWCOUNT4
GO
UPDATEtitles
SETadvance=5000
WHEREadvance>=5000
GO
第4章触发器中的变量操作
declare@djbh
select@djbh=字段名fromtable_name
第5章SQL触发器语法参考
CreateTRIGGERtrigger_name
ON{table|view}
[WITHENCRYPTION]
{
{{FOR|AFTER|INSTEADOF}{[Insert][,][Update]}
[WITHAPPEND]
[NOTFORREPLICATION]
AS
[{IFUpdate（column）
[{AND|or}Update（column）]
[...n]
|IF（COLUMNS_UpdateD（）{bitwise_operator}updated_bitmask）
{comparison_operator}column_bitmask[...n]
}]
sql_statement[...n]
}
}
参数
trigger_name
是触发器的名称。
触发器名称必须符合标识符规则，并且在数据库中必须唯一。
可以选择是否指定触发器所有者名称。
Table|view
是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。
可以选择是否指定表或视图的所有者名称。
WITHENCRYPTION
加密syscomments表中包含CreateTRIGGER语句文本的条目。
使用WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。
AFTER
指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。
所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定FOR关键字，则AFTER是默认设置。
不能在视图上定义AFTER触发器。
INSTEADOF
指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。
在表或视图上，每个Insert、Update或Delete语句最多可以定义一个INSTEADOF触发器。
然而，可以在每个具有INSTEADOF触发器的视图上定义视图。
INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义。
如果向指定了WITHCHECKOPTION选项的可更新视图添加INSTEADOF触发器，SQLServer将产生一个错误。
用户必须用AlterVIEW删除该选项后才能定义INSTEADOF触发器。
{[Delete][,][Insert][,][Update]}
是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。
必须至少指定一个选项。
在触发器定义中允许使用以任意顺序组合的这些关键字。
如果指定的选项多于一个，需用逗号分隔这些选项。
对于INSTEADOF触发器，不允许在具有ONDelete级联操作引用关系的表上使用Delete选项。
同样，也不允许在具有ONUpdate级联操作引用关系的表上使用Update选项。
WITHAPPEND
指定应该添加现有类型的其它触发器。
只有当兼容级别是65或更低时，才需要使用该可选子句。
如果兼容级别是70或更高，则不必使用WITHAPPEND子句添加现有类型的其它触发器（这是兼容级别设置为70或更高的CreateTRIGGER的默认行为）。
有关更多信息，请参见sp_dbcmptlevel。
WITHAPPEND不能与INSTEADOF触发器一起使用，或者，如果显式声明AFTER触发器，也不能使用该子句。
只有当出于向后兼容而指定FOR时（没有INSTEADOF或AFTER），才能使用WITHAPPEND。
以后的版本将不支持WITHAPPEND和FOR（将被解释为AFTER）。
NOTFORREPLICATION
表示当复制进程更改触发器所涉及的表时，不应执行该触发器。
AS
是触发器要执行的操作。
sql_statement
是触发器的条件和操作。
触发器条件指定其它准则，以确定Delete、Insert或Update语句是否导致执行触发器操作。
当尝试Delete、Insert或Update操作时，Transact-SQL语句中指定的触发器操作将生效。
触发器可以包含任意数量和种类的Transact-SQL语句。
触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。
触发器中的Transact-SQL语句常常包含控制流语言。
CreateTRIGGER语句中使用几个特殊的表：
deleted和inserted是逻辑（概念）表。
这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。
例如，若要检索deleted表中的所有值，请使用：
Select*
FROMdeleted
如果兼容级别等于70，那么在Delete、Insert或Update触发器中，SQLServer将不允许引用inserted和deleted表中的text、ntext或image列。
不能访问inserted和deleted表中的text、ntext和image值。
若要在Insert或Update触发器中检索新值，请将inserted表与原始更新表联接。
当兼容级别是65或更低时，对inserted或deleted表中允许空值的text、ntext或image列，将返回空值；如果这些列不可为空，则返回零长度字符串。
当兼容级别是80或更高时，SQLServer允许在表或视图上通过INSTEADOF触发器更新text、ntext或image列。
n
是表示触发器中可以包含多条Transact-SQL语句的占位符。
对于IFUpdate（column）语句，可以通过重复Update（column）子句包含多列。
IFUpdate（column）
测试在指定的列上进行的Insert或Update操作，不能用于Delete操作。
可以指定多列。
因为在ON子句中指定了表名，所以在IFUpdate子句中的列名前不要包含表名。
若要测试在多个列上进行的Insert或Update操作，请在第一个操作后指定单独的Update（column）子句。
在Insert操作中IFUpdate将返回TRUE值，因为这些列插入了显式值或隐性（NULL）值。
说明IFUpdate（column）子句的功能等同于IF、IF...ELSE或WHILE语句，并且可以使用BEGIN...END语句块。
有关更多信息，请参见控制流语言。
可以在触发器主体中的任意位置使用Update（column）。
column
是要测试Insert或Update操作的列名。
该列可以是SQLServer支持的任何数据类型。
但是，计算列不能用于该环境中。
有关更多信息，请参见数据类型。
IF（COLUMNS_UpdateD（））
测试是否插入或更新了提及的列，仅用于Insert或Update触发器中。
COLUMNS_UpdateD返回varbinary位模式，表示插入或更新了表中的哪些列。
COLUMNS_UpdateD函数以从左到右的顺序返回位，最左边的为最不重要的位。
最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。
如果在表上创建的触发器包含8列以上，则COLUMNS_UpdateD返回多个字节，最左边的为最不重要的字节。
在Insert操作中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_bitmask中指定的所有列是否都实际进行了更新。
使用大于号（>）检查updated_bitmask中指定的任一列或某些列是否已更新。
column_bitmask
是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。
注释
触发器常常用于强制业务规则和数据完整性。
SQLServer通过表创建语句（AlterTABLE和CreateTABLE）提供声明引用完整性（DRI）；但是DRI不提供数据库间的引用完整性。
若要强制引用完整性（有关表的主键和外键之间关系的规则），请使用主键和外键约束（AlterTABLE和CreateTABLE的PRIMARYKEY和FOREIGNKEY关键字）。
如果触发器表存在约束，则在INSTEADOF触发器执行之后和AFTER触发器执行之前检查这些约束。
如果违反了约束，则回滚INSTEADOF触发器操作且不执行（激发）AFTER触发器。
可用sp_settriggerorder指定表上第一个和最后一个执行的AFTER触发器。
在表上只能为每个Insert、Update和Delete操作指定一个第一个执行和一个最后一个执行的AFTER触发器。
如果同一表上还有其它AFTER触发器，则这些触发器将以随机顺序执行。
如果AlterTRIGGER语句更改了第一个或最后一个触发器，则将除去已修改触发器上设置的第一个或最后一个特性，而且必须用sp_settriggerorder重置排序值。
只有当触发SQL语句
升级会员 