SQL Server触发器及触发器中的事务学习
前端之家收集整理的这篇文章主要介绍了
SQL Server触发器及触发器中的事务学习，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
如果你有对触发器和事务的概念，有些了解，这篇文章，对你来说会是很简单，或能让你更进一步的了解触发器里面的一些故事，和触发器中事务个故事。在这边文章里面，我不会从触发器和事务的概念去讲述，而是从常见的两种触发器类型(DML触发器 & DDL触发器)和After触发器 & Instead Of 触发器的应用不同，开始说起它们，然后是说与事务有关的故事。如果，你有什么建议和意见，都可以通过文章后面的回复与我沟通，或者通过E-Mail方式，与 我交流；我的Email地址是：glal@163.com
在下面的内容，用到一些sql Server 触发器和事务的一些术语，如果有些不明白的地方，可以查阅MSDN资料库，或sql Server本地帮助文档：
DML触发器(DML Triggers) DDL触发器(DDL Triggers) 事务模式(Transaction modes) 显式事务(Explicit Transactions)
自动提交事务(Autocommit Transactions) 隐式事务(Implicit Transactions) 批范围的事务(Batch-scoped Transactions)
After触发器 Vs Instead Of触发器
After 触发器将在处理触发操作（Insert、Update 或 Delete）、Instead Of 触发器和约束之后激发。Instead Of是将在处理约束前激发，以替代触发操作。下面两张图描述了After触发器和Instead Of触发器的执行先后顺序。
图1 图2
左边的图1，描述了After触发器执行顺序情况，我在这里通过一个简单的例子来说明After触发器的执行顺序，以便能加深对左图1 After触发器的理解。
先创建表Contact
tempdb
object_id()
Contact
Contact
(1,1),
(2) (Sex (N,N)) ()
再创建After触发器tr_Contact
( 1 sys.triggers name=) tr_Contact
tr_Contact Contact Insert
Name,Sex Inserted 显示Inserted表的内容，用来判断触发器执行的先后顺序*/
然后Insert数据，判断After触发器的执行顺序
这里，在没有运行Insert语句之前，我们可以判断，执行Insert过程会触发Check错误，因为字段Sex的值必须是”F” Or “M”，而这里将要插入的是”U”.好了，再来看运行Insert语句后的情况。
本例子，只看到引发Check约束冲突的错误，而无法看到Inserted表的数据，说明一点就是,引起Check约束之前，不会引发After触发器tr_Contact的操作。这就验证了图1的After触发器执行顺序情况。
好了，接下来，我们再测试Instead Of触发器 图2的情况；我使用上边建好的测试表Contact来举例。
先修改触发器tr_Contact内容,
tr_Contact Contact Instead Insert
Name,Sex Inserted
再Insert数据，观察sql Server执行后的提示信息,monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none">Go
这里，看到，先是触发器操作，再是Check约束处理。本例中，在触发器里面使用一条Insert的语句来描述触发器的代替执行操作，这sql语句通过Select表Inserted得到触发器外面Insert内容。当sql Server执行到触发器里面的Insert语句，才会引起Check约束处理.倘若，在触发器tr_Contact没有Insert的代替行为，那么就不会出现Check约束处理错误的信息(注：没有Check错误信息，并不表示没有作Check处理)。修改上边的触发器tr_Contact内容，做个简易的验证.
* Contact
可以看到，Instead Of 触发器tr_Contact内容没有Insert的sql语句，不会引发Check处理错误，而且检查Insert动作后的结果，发现表Contact也没有之前我们Insert的数据。这些足够验证了Instead Of触发器的执行先后顺序和代替执行操作。
DML 触发器 Vs DDL 触发器
DML 触发器在 Insert、Update 和 Delete 语句上操作，可以作为After 触发器 和 Instead Of 触发器。
DDL 触发器对 Create、Alter、Drop 和其他 DDL 语句以及执行 DDL 式操作的存储过程执行操作，只可作为After触发器，不能Instead Of触发器。
前面的内容，有描述DML触发器中的After & Instead Of触发器内容，下面直接来看DDL的操作顺序：
图3.
从图3.可以知道，在DDL触发器中，是没有创建Inserted & Deleted过程的，我们通过简单的例子去测试下。
创建一个服务器范围内的DDL触发器，检查有没有Inserted 表，
master
( 1 sys.server_triggers name=)
tr_createDataBase Server
tr_createDataBase Server Create_DataBase
* inserted
执行创建数据库sql语句,monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: white; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none"> myDataBase ,Filename=sql2008DE01\MyDataBase_Data.mdf') Log
,Filename=sql2008DE01\MyDataBase_Log.ldf')
返回错误信息，
使用上边相同的方法，我们验证DDL触发器中，不会创建Deleted表；是否创建Deleted & Inserted,也可以认为是DDL触发器与DML触发器不同之处。在DLL触发器与DML触发器不同的一个重要特征是作用域，DML触发器只能应用在数据库层(Database Level)的表和视图上，而DDL触发器应用于数据库层(Database Level)和服务器层(Server Level)；DDL触发器的作用域取决于事件。下面简单描述下事件组的内容。
数据库层事件主要包含:
DDL Table events: Create table,Alter table,Drop table DDL view events : Create view,Alter view,Drop view DDL trigger events :Create trigger,Drop trigger,Alter trigger DDL synonym events: Create synonym,drop synonym DDL Index events: Create index,Alter index,Drop Index DDL Database level security events: Create User,Drop user,Alter user Create role,Drop role,Alter role Create application role,Drop application role,Alter Application role Create Schema,Drop Schema,Alter Schema Grant database access,Revoke database access,Deny Database access DDL Service broker events: Create Message type,Alter Message type,Drop Message type Create contract,Drop contract,Alter contract Create Service,Alter service,Drop Service Create route,Drop route,Alter route
服务器层事件主要包含：
Create Database,Drop Database Create Login,Drop Login,Alter Login
触发器和事务的故事
在前面的几个例子中，如DML触发器例子，Insert 语句执行后，因为触发器操作 或 Check处理错误，没有把数据真正的插入到表Contact中。其实，当执行触发器时，触发器的操作好像有一个未完成的事务在起作用。 通过几个例子来讲解触发器和事务的故事。
创建一个表ContactHIST，用于对表Contact作Update Or Delete操作时，把操作前的数据Insert到表ContactHIST中。
object_id(
) ContactHIST
ContactHIST
,monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none"> Name nvarchar(50),monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: white; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none"> Sex (2),monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none"> ActionType nvarchar(10) (ActionType (,)),monospace; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BACKGROUND-COLOR: white; TEXT-ALIGN: left; BORDER-BOTTOM-STYLE: none"> LastUpdateDate datetime (getdate())
修改触发器tr_Contact内容，
tr_Contact Contact , ContactHIST(ContactID,Name,Sex)
ID,Sex deleted
测试数据，
)
Contact
Sex=
Name=
* Contact
* ContactHIST
测试结果：
从上边的测试情况，看出，Update Contact触发tr_Contact触发器操作，触发器里面的Rollback Tran 动作导致了触发器外面的Update语句执行回滚，而Rollback Tran 语句后面的Begin Tran语句，主要是应用于保持整个事务的完整性。为了更能理解这一过程，我模拟了一个触发器中的事务开始结束过程。
@H_194_404@
图4.
在sql Server 2005 和 sql Server 2008上面，可以看到如图4.的效果。在低版本的sql Server上，可能会出现错误提示情况，不管如何，在触发器外面，sql Server都会Rollback Tran。下面我做个错误提示的例子。
修改触发器tr_Contact内容
重新执行Update操作，
Contact
Sex=
Name=
* Contact
* ContactHIST
在触发器里面没有Begin Tran语句动作，触发器外面也能回滚操作。这里我们可以通过查询表数据和@@Trancount来判断。
其实，上面的例子，Update语句，是以自动提交事务(Autocommit Transactions)模式 开始执行的，触发器里Rollback Tran后面，不管有没有Begin Tran ，最后都会事务都会交回给sql Server自动提交事务管理。当然，在DML触发器中，你可以使用显式事务(Explicit Transactions),或开启隐式事务(Implicit Transactions) 来控制，当然你也可以应用于批范围的事务(Batch-scoped Transactions) 中。这里，我通过开启隐式事务(Implicit Transactions) 的例子来说，触发器与事务的关系。
修改触发器tr_Contact的内容，
开启隐式事务(Implicit Transactions) 来测试，
Implicit_transactions
N N
N
Implicit_transactions
这里，你是否发现一个很有意思的问题，在触发器理，执行Insert ContactHIST之前，@@Trancount=1,执行Insert后，@@Trancount还是为1，触发器外面Update Contact后，@@Trancount就变成了2,。这里可以理解成，你在触发器里面，发出一个Begin Tran，那么sql Server 就会创建一个嵌套事务。当你在触发器里面，在Rollback Tran后面屏蔽掉Begin Tran，就会出现错误3609，如，
这里，可以看到事务在触发器中Rollback，又没有开启新的事务，导致整个批处理就中止，不会继续执行触发器外面的Rollback Tran操作。倘若，你在触发器中使用Begin Tran …… Commit Tran格式，那么触发器Commit Tran不会影响到外面的事务；下面描述三种常见触发器中事务的情况：
图5. 图6. 图7.
图5.描述在触发器中含有Begin Tran …… Commit Tran的情况，
图6.描述在触发器中含有Save Tran savepoint_name …… Rollback Tran savepoint_name 的情况，触发器中的Rollback Tran 只会回滚指定的保存点，不会影响到触发器外面的Commit Tran Or Rollback Tran操作。
图7.描述在触发器中含有Rollback Tran的情况，不管触发器里面有没有Begin Tran，都会出现错误3609，中止批处理。
注：
DDL触发器操作可以触发器中回滚操作，可以使用命令如Rollback，但严重错误可能会导致整个事务自动回滚。不能回滚发生在 DDL 触发器正文内的 Alter Database事件。在触发器中使用Rollback … Begin Tran 可能会导致意想不到的结果，在没有确认和测试情况下，请不要随便在触发器中直接使用Rollback …Begin Tran处理方式.特别是Create Database事件，在sql Server 2008和sql Server 2005环境下，产生的结果不同。
Rollback …Begin Tran情况：
Create Trigger ….
As
……
Rollback
Begin Tran
End
小结
回顾前文至后文，从After触发器VsInstead Of 触发器，说到DML触发器 Vs DDL触发器，再到触发器中事务的故事。也许有些地方描述的有些模糊，有些地方只有一笔带过；你在测试代码过程中，可能发现有些地方与这里测试的情况不同，那可能是因为sql Server版本的不同，导致一些测试结果不同。无论如何，只要你感觉对你了解触发器，有些帮助，就OK了。
原文链接：https://www.f2er.com/mssql/64014.html
