了解SQLServer触发器及触发器中的事务
引述
⾸先，说下我写篇⽂章的⽬的，我希望能把我对触发器的理解，分享出来与你⼀起学习。如果你有对触发器和事务的概念，有些了解，这篇⽂章，对你来说会是很简单，或能让你更进⼀步的了解触发器⾥⾯的⼀些故事，和触发器中事务个故事。在这边⽂章⾥⾯，我不会从触发器和事务的概念去讲述，⽽是从常见的两种触发器类型(DML 触发器 & DDL触发器)和After触发器 & Instead Of 触发器的应⽤不同，开始说起它们，然后是说与事务有关的故事。如果，你有什么建议和意见，都可以通过⽂章后⾯的回复与我沟通，或者通过E-Mail⽅式，与我交流；我的Email地址是：
在下⾯的内容，⽤到⼀些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
use tempdb
Go
if object_id('Contact') Is Not null
Drop Table Contact
Go
Create Table Contact
(
ID int Primary Key Identity(1,1),
Name nvarchar(50),
Sex nchar(2) Check(Sex In(N'F',N'M')) Default('M')
)
Go
再创建After触发器tr_Contact
use tempdb
Go
If Exists(Select 1 iggers Where name='tr_Contact')
Drop Trigger tr_Contact
Go
Create Trigger tr_Contact On Contact After Insert
As
Select Name,Sex From Inserted /*显⽰Inserted表的内容，⽤来判断触发器执⾏的先后顺序*/
Go
然后Insert数据，判断After触发器的执⾏顺序
use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go
这⾥，在没有运⾏Insert语句之前，我们可以判断，执⾏Insert过程会触发Check错误，因为字段Sex的值必须是”F” Or “M”，⽽这⾥将要插⼊的是”U”.好了，再来看运⾏Insert语句后的情况。
本例⼦，只看到引发Check约束冲突的错误，⽽⽆法看到Inserted表的数据，说明⼀点就是，引起Check约束之前，不会引发After触发器
tr_Contact的操作。这就验证了图1的After触发器执⾏顺序情况。
好了，接下来，我们再测试Instead Of触发器图2的情况；我使⽤上边建好的测试表Contact来举例。
先修改触发器tr_Contact内容,
use tempdb
Go
If Exists(Select 1 iggers Where name='tr_Contact')
Drop Trigger tr_Contact
Go
Create Trigger tr_Contact On Contact Instead Of Insert
As
print'触发器作代替执⾏操作'
Insert Into Contact (Name,Sex) Select Name,Sex From Inserted /*代替触发器外⾯的Insert⾏为*/
Go
再Insert数据，观察SQL Server执⾏后的提⽰信息,
use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go
这⾥，看到，先是触发器操作，再是Check约束处理。本例中，在触发器⾥⾯使⽤⼀条Insert的语句来描述触发器的代替执⾏操作，这SQL 语句通过Select表Inserted得到触发器外⾯Insert内容。当SQL Server执⾏到触发器⾥⾯的Insert语句，才会引起Check约束处理.倘若，在触发器tr_Contact没有Insert的代替⾏为，那么就不会出现Check约束处理错误的信息(注：没有Check错误信息，并不表⽰没有作Check处理)。修改上边的触发器tr_Contact内容，做个简易的验证.
use tempdb
Go
If Exists(Select 1 iggers Where name='tr_Contact')
Drop Trigger tr_Contact
Go
Create Trigger tr_Contact On Contact Instead Of Insert
As
print'触发器作代替执⾏操作'
Go
use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go
Select * From 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 表，
use master
Go
If Exists(Select 1 From sys.server_triggers Where name='tr_createDataBase')
Drop Trigger tr_createDataBase On All Server
Go
Create Trigger tr_createDataBase On All Server After Create_DataBase
As
Select * From inserted
Go
执⾏创建数据库SQL语句,
use master
Go
Create Database myDataBase On Primary
(Name='MyDataBase_Data',Filename='E:\DATA\SQL2008DE01\MyDataBase_Data.mdf') Log On
(Name='MyDataBase_Log',Filename='E:\DATA\SQL2008DE01\MyDataBase_Log.ldf')
Go
返回错误信息，
使⽤上边相同的⽅法，我们验证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中。
sql触发器的使用use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','F')
Go
--Update
Update Contact
Set Sex='M'
Where Name='Bill'
Go
Select * From Contact
Select * From ContactHIST
Go
测试结果：
从上边的测试情况，看出，Update Contact触发tr_Contact触发器操作，触发器⾥⾯的Rollback Tran 动作导致了触发器外⾯的Update语句执⾏回滚，⽽Rollback Tran 语句后⾯的Begin Tran语句，主要是应⽤于保持整个事务的完整性。为了更能理解这⼀过程，我模拟了⼀个触发器中的事务开始结束过程。
图4.
在SQL Server 2005 和 SQL Server 2008上⾯，可以看到如图4.的效果。在低版本的SQL Server上，可能会出现错误提⽰情况，不管如何，在触发器外⾯，SQL Server都会Rollback Tran。下⾯我做个错误提⽰的例⼦。
修改触发器tr_Contact内容
use tempdb
Go
Update Contact
Set Sex='M'
Where Name='Bill'
Go
Select@@TRANCOUNT
Go
Select * From Contact
Select * From ContactHIST
Go
在触发器⾥⾯没有Begin Tran语句动作，触发器外⾯也能回滚操作。这⾥我们可以通过查询表数据和@@Trancount来判断。
其实，上⾯的例⼦，Update语句，是以⾃动提交事务(Autocommit Transactions)模式开始执⾏的，触发器⾥Rollback Tran后⾯，不管有没有Begin Tran ，最后都会事务都会交回给SQL Server⾃动提交事务管理。当然，在DML触发器中，你可以使⽤显式事务(Explicit Transactions)，或开启隐式事务(Implicit Transactions)来控制，当然你也可以应⽤于批范围的事务(Batch-scoped Transactions)中。这⾥，我通过开启隐式事务(Implicit Transactions)的例⼦来说，触发器与事务的关系。
修改触发器tr_Contact的内容，
这⾥，可以看到事务在触发器中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 可能会导致意想不到的结果，在没有确认和测试情况下，请不要随便在触发器中直接使⽤Ro
llback …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了。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论