了解SQL Server触发器及触发器中的事务

 在下面的内容,用到一些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触发器的执行先后顺序。    了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务  左边的图1,描述了After触发器执行顺序情况,我在这里通过一个简单的例子来说明After触发器的执行顺序,以便能加深对左图1 After触发器的理解。先创建表Contact:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
if  object_id(Contact) IsNotnull
DropTable Contact
Go
CreateTable Contact
(
ID
intPrimaryKeyIdentity(1,1),
Name
nvarchar(50),
Sex
nchar(2) Check(Sex In(NF,NM)) Default(M)
)
Go
了解SQL Server触发器及触发器中的事务

  再创建After触发器tr_Contact:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact After Insert
As
Select Name,Sex From Inserted /*显示Inserted表的内容,用来判断触发器执行的先后顺序*/
Go
了解SQL Server触发器及触发器中的事务

  然后Insert数据,判断After触发器的执行顺序:

use tempdb
Go
InsertInto Contact (Name,Sex) Values (Bill,U)
Go

  这里,在没有运行Insert语句之前,我们可以判断,执行Insert过程会触发Check错误,因为字段Sex的值必须是”F” Or “M”,而这里将要插入的是”U”.好了,再来看运行Insert语句后的情况。

了解SQL Server触发器及触发器中的事务  本例子,只看到引发Check约束冲突的错误,而无法看到Inserted表的数据,说明一点就是,引起Check约束之前,不会引发After触发器tr_Contact的操作。这就验证了图1的After触发器执行顺序情况。

  好了,接下来,我们再测试Instead Of触发器 图2的情况;我使用上边建好的测试表Contact来举例。

  先修改触发器tr_Contact内容:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact Instead OfInsert
As
print触发器作代替执行操作
InsertInto Contact (Name,Sex) Select Name,Sex From Inserted /*代替触发器外面的Insert行为*/
Go
了解SQL Server触发器及触发器中的事务

  再Insert数据,观察SQL Server执行后的提示信息:

use tempdb
Go
InsertInto Contact (Name,Sex) Values (Bill,U)
Go

了解SQL Server触发器及触发器中的事务  这里,看到,先是触发器操作,再是Check约束处理。本例中,在触发器里面使用一条Insert的语句来描述触发器的代替执行操作,这SQL语句通过Select表Inserted得到触发器外面Insert内容。当SQL Server执行到触发器里面的Insert语句,才会引起Check约束处理.倘若,在触发器tr_Contact没有Insert的代替行为,那么就不会出现Check约束处理错误的信息(注:没有Check错误信息,并不表示没有作Check处理)。修改上边的触发器tr_Contact内容,做个简易的验证。

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact Instead OfInsert
As
print触发器作代替执行操作
Go
use tempdb
Go
InsertInto Contact (Name,Sex) Values (Bill,U)
Go
Select*From Contact
了解SQL Server触发器及触发器中的事务
 

了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务  可以看到,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的操作顺序:    了解SQL Server触发器及触发器中的事务

  从图3.可以知道,在DDL触发器中,是没有创建Inserted & Deleted过程的,我们通过简单的例子去测试下。

  创建一个服务器范围内的DDL触发器,检查有没有Inserted 表:

了解SQL Server触发器及触发器中的事务
use master
Go
IfExists(Select1From sys.server_triggers Where name=tr_createDataBase)
DropTrigger tr_createDataBase OnAll Server
Go
CreateTrigger tr_createDataBase OnAll Server After Create_DataBase
As
Select*From inserted
Go
了解SQL Server触发器及触发器中的事务

  执行创建数据库SQL语句:

了解SQL Server触发器及触发器中的事务
use master
Go
CreateDatabase myDataBase OnPrimary
(Name
=MyDataBase_Data,Filename=E:\DATA\SQL2008DE01\MyDataBase_Data.mdf) LogOn
(Name
=MyDataBase_Log,Filename=E:\DATA\SQL2008DE01\MyDataBase_Log.ldf)
Go
了解SQL Server触发器及触发器中的事务
 

  返回错误信息:

了解SQL Server触发器及触发器中的事务  使用上边相同的方法,我们验证DDL触发器中,不会创建Deleted表;是否创建Deleted & Inserted,也可以认为是DDL触发器与DML触发器不同之处。在DLL触发器与DML触发器不同的一个重要特征是作用域,DML触发器只能应用在数据库层(Database Level)的表和视图上,而DDL触发器应用于数据库层(Database Level)和服务器层(Server Level);DDL触发器的作用域取决于事件。下面简单描述下事件组的内容。

  数据库层事件主要包含:

  1. DDL Table events: Create table, Alter table, Drop table
  2. DDL view events : Create view, Alter view, Drop view
  3. DDL trigger events :Create trigger, Drop trigger, Alter trigger
  4. DDL synonym events: Create synonym, drop synonym
  5. DDL Index events: Create index, Alter index, Drop Index
  6. 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
  7. 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

  服务器层事件主要包含:

  1. Create Database, Drop Database
  2. Create Login, Drop Login, Alter Login

  触发器和事务的故事

  在前面的几个例子中,如DML触发器例子,Insert 语句执行后,因为触发器操作 或 Check处理错误,没有把数据真正的插入到表Contact中。其实,当执行触发器时,触发器的操作好像有一个未完成的事务在起作用。 通过几个例子来讲解触发器和事务的故事。

  创建一个表ContactHIST,用于对表Contact作Update Or Delete操作时,把操作前的数据Insert到表ContactHIST中。

了解SQL Server触发器及触发器中的事务
use tempdb
Go
ifobject_id(ContactHIST) IsNotnull
DropTable ContactHIST
Go
CreateTable ContactHIST
(
ID
intPrimaryKeyIdentity(1,1),
ContactID
int,
Name
nvarchar(50),
Sex
nchar(2),
ActionType
nvarchar(10) Check(ActionType In(Update,Delete)),
LastUpdateDate
datetimeDefault(getdate())
)
Go
了解SQL Server触发器及触发器中的事务

  修改触发器tr_Contact内容:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact After Update,Delete
As
InsertInto ContactHIST(ContactID,Name,Sex)
Select ID,Name,Sex From deleted
RollbackTran
BeginTran
Go
了解SQL Server触发器及触发器中的事务

  测试数据:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
InsertInto Contact (Name,Sex) Values (Bill,F)
Go
--Update 
Update Contact
Set Sex=M
Where Name=Bill
Go
Select*From Contact
Select*From ContactHIST
Go
了解SQL Server触发器及触发器中的事务

  测试结果:

了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务  从上边的测试情况,看出,Update Contact触发tr_Contact触发器操作,触发器里面的Rollback Tran 动作导致了触发器外面的Update语句执行回滚,而Rollback Tran 语句后面的Begin Tran语句,主要是应用于保持整个事务的完整性。为了更能理解这一过程,我模拟了一个触发器中的事务开始结束过程。

了解SQL Server触发器及触发器中的事务

  在SQL Server 2005 和 SQL Server 2008上面,可以看到如图4.的效果。在低版本的SQL Server上,可能会出现错误提示情况,不管如何,在触发器外面,SQL Server都会Rollback Tran。下面我做个错误提示的例子。

  修改触发器tr_Contact内容:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact After Update,Delete
As
InsertInto ContactHIST(ContactID,Name,Sex)
Select ID,Name,Sex From deleted
RollbackTran
--Begin Tran
Go
了解SQL Server触发器及触发器中的事务

  重新执行Update操作:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
Update Contact
Set Sex=M
Where Name=Bill
Go
Select@@TRANCOUNT
Go
Select*From Contact
Select*From ContactHIST
Go
了解SQL Server触发器及触发器中的事务

了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务  在触发器里面没有Begin Tran语句动作,触发器外面也能回滚操作。这里我们可以通过查询表数据和@@Trancount来判断。

  其实,上面的例子,Update语句,是以自动提交事务(Autocommit Transactions)模式 开始执行的,触发器里Rollback Tran后面,不管有没有Begin Tran ,最后都会事务都会交回给SQL Server自动提交事务管理。当然,在DML触发器中,你可以使用显式事务(Explicit Transactions),或开启隐式事务(Implicit Transactions) 来控制,当然你也可以应用于批范围的事务(Batch-scoped Transactions) 中。这里,我通过开启隐式事务(Implicit Transactions) 的例子来说,触发器与事务的关系。

  修改触发器tr_Contact的内容:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact After Update,Delete
As
Print N触发器里Insert 前,@@Trancount=+Rtrim(@@Trancount)

InsertInto ContactHIST(ContactID,Name,Sex)
Select ID,Name,Sex From deleted

Print N触发器里Insert后,Rollback Tran 前,@@Trancount=+Rtrim(@@Trancount)

RollbackTran
Print N触发器里Rollback Tran 后,@@Trancount=+Rtrim(@@Trancount)
BeginTran
Go
了解SQL Server触发器及触发器中的事务

  开启隐式事务(Implicit Transactions) 来测试:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
Set Implicit_transactions On/**/
Go
Print NUpdate Contact前,@@Trancount=+Rtrim(@@Trancount)
Update Contact
Set Sex=M
Where Name=Bill

Print NUpdate Contact后,@@Trancount=+Rtrim(@@Trancount)
RollbackTran
Print N触发器外面Rollback Tran 后,@@Trancount=+Rtrim(@@Trancount)
Go
Set Implicit_transactions Off/**/
Go
Go
Select*From Contact
Select*From ContactHIST
Go
了解SQL Server触发器及触发器中的事务

了解SQL Server触发器及触发器中的事务

  这里,你是否发现一个很有意思的问题,在触发器理,执行Insert ContactHIST之前,@@Trancount=1,执行Insert后,@@Trancount还是为1,触发器外面Update Contact后,@@Trancount就变成了2,。这里可以理解成,你在触发器里面,发出一个Begin Tran,那么SQL Server 就会创建一个嵌套事务。当你在触发器里面,在Rollback Tran后面屏蔽掉Begin Tran,就会出现错误3609,如:

了解SQL Server触发器及触发器中的事务
use tempdb
Go
IfExists(Select1From sys.triggers Where name=tr_Contact)
DropTrigger tr_Contact
Go
CreateTrigger tr_Contact On Contact After Update,Delete
As
Print N触发器里Insert 前,@@Trancount=+Rtrim(@@Trancount)
InsertInto ContactHIST(ContactID,Name,Sex)
Select ID,Name,Sex From deleted
Print N触发器里Insert后,Rollback Tran 前,@@Trancount=+Rtrim(@@Trancount)
RollbackTran
Print N触发器里Rollback Tran 后,@@Trancount=+Rtrim(@@Trancount)
Go
了解SQL Server触发器及触发器中的事务

了解SQL Server触发器及触发器中的事务  这里,可以看到事务在触发器中Rollback,又没有开启新的事务,导致整个批处理就中止,不会继续执行触发器外面的Rollback Tran操作。倘若,你在触发器中使用Begin Tran …… Commit Tran格式,那么触发器Commit Tran不会影响到外面的事务;下面描述三种常见触发器中事务的情况:

了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务了解SQL Server触发器及触发器中的事务

  图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情况:

了解SQL Server触发器及触发器中的事务
CreateTrigger
As
……
Rollback
BeginTran
End
了解SQL Server触发器及触发器中的事务

  小结

  回顾前文至后文,从After触发器VsInstead Of 触发器,说到DML触发器 Vs DDL触发器,再到触发器中事务的故事。也许有些地方描述的有些模糊,有些地方只有一笔带过;你在测试代码过程中,可能发现有些地方与这里测试的情况不同,那可能是因为SQL Server版本的不同,导致一些测试结果不同。无论如何,只要你感觉对你了解触发器,有些帮助,就OK了

了解SQL Server触发器及触发器中的事务

上一篇:mysql主从复制部署


下一篇:mysql多实例安装