摸底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触发器的履行先后顺序。

    
SQL Server 1      
SQL Server 2 

    
图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 From sys.triggers 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语句后的情事。

SQL Server 3

仍例子,只见到引发Check约束冲突之失实,而无法看出Inserted表的多寡,说明一些纵是,引起Check约束之前,不见面吸引After触发器tr_Contact的操作。这就印证了图1的After触发器执行顺序情况。

     好了,接下去,我们再度测试Instead Of触发器
图2的景象;我动用上建好之测试表Contact来比喻。

事先改触发器tr_Contact内容,

use tempdb

Go

If Exists(Select 1 From sys.triggers 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

SQL Server 4  

此间,看到,先是触发器操作,再是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 From sys.triggers 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

SQL Server 5SQL Server 6

得看到,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 7

     图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

回到错误信息,

SQL Server 8

使上相同的点子,我们作证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中。

use tempdb

Go

if object_id('ContactHIST') Is Not null 

    Drop Table ContactHIST

Go

Create Table ContactHIST

(

    ID int Primary Key Identity(1,1),

    ContactID int,

    Name nvarchar(50),

    Sex nchar(2),

    ActionType nvarchar(10) Check(ActionType In('Update','Delete')),

    LastUpdateDate datetime Default(getdate())

)

Go

改触发器tr_Contact内容,

use tempdb

Go

If Exists(Select 1 From sys.triggers Where name='tr_Contact')

    Drop  Trigger tr_Contact 

Go

Create Trigger     tr_Contact On Contact After Update,Delete

As

Insert Into ContactHIST(ContactID,Name,Sex)

    Select ID,Name,Sex From deleted 

 

Rollback Tran 

 

Begin Tran

Go

测试数据,

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

测试结果:

SQL Server 9SQL Server 10

从今上边的测试情况,看出,UpdSQL Serverate
Contact触发tr_Contact触发器操作,触发器里面的Rollback Tran
动作导致了触发器外面的Update语句执行回滚,而Rollback Tran
语句后的Begin
Tran语句,主要是行使为保持总体工作之完整性。为了更能掌握当下无异经过,我套了一个触发器中之工作开始终结过程。

SQL Server 11

图4.

于SQL Server 2005 和 SQL Server
2008方,可以望要图4.之功效。在小版本的SQL
Server上,可能会见面世错误提示情况,不管怎么,在触发器外面,SQL
Server都见面Rollback Tran。下面我做只错误提示的事例。

修改触发器tr_Contact内容

use tempdb

Go

If Exists(Select 1 From sys.triggers Where name='tr_Contact')

    Drop  Trigger tr_Contact 

Go

Create Trigger     tr_Contact On Contact After Update,Delete

As

Insert Into ContactHIST(ContactID,Name,Sex)

    Select ID,Name,Sex From deleted 

 

Rollback Tran 

 

--Begin Tran 

Go

双重履行Update操作,

use tempdb

Go

Update Contact 

    Set Sex='M'

    Where Name='Bill'

Go    

Select @@TRANCOUNT    

Go

Select * From Contact

Select * From ContactHIST

Go

SQL Server 12SQL Server 13

在触发器里面没有Begin
Tran语句子动作,触发器外面吗会回滚操作。这里我们得透过查询表数据以及@@Trancount来判断。

         其实,上面的事例,Update语句,是因活动提交业务(Autocommit
Transactions)模式 开始履行之,触发器里Rollback Tran后面,不管发生没起Begin
Tran ,最后都见面事务都见面暨回为SQL
Server自动提交事务管理。当然,在DML触发器中,你得运用显式事务(Explicit
Transactions),或开隐式事务(Implicit Transactions)
来控制,当然你吗可以动用被批判范围之事务(Batch-scoped Transactions)
中。这里,我透过开启隐式事务(Implicit Transactions)
的事例来说,触发器与业务的干。

改触发器tr_Contact的内容,

use tempdb

Go

If Exists(Select 1 From sys.triggers Where name='tr_Contact')

    Drop  Trigger tr_Contact 

Go

Create Trigger     tr_Contact On Contact After Update,Delete

As

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

 

Insert Into ContactHIST(ContactID,Name,Sex)

    Select ID,Name,Sex From deleted 

 

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

 

Rollback Tran 

 

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

 

Begin Tran 

Go

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

use tempdb

Go

Set Implicit_transactions On /**/

Go

Print N'Update Contact前,@@Trancount='+Rtrim(@@Trancount)

 

Update Contact 

    Set Sex='M'

    Where Name='Bill'



Print N'Update Contact后,@@Trancount='+Rtrim(@@Trancount)

 

Rollback Tran

 

Print N'触发器外面Rollback Tran 后,@@Trancount='+Rtrim(@@Trancount)



Go    

Set Implicit_transactions Off /**/

Go

 

Go

Select * From Contact

Select * From ContactHIST

Go

 

SQL Server 14

 

此处,你是不是发现一个百般有意思的问题,在触发器理,执行Insert
ContactHIST之前,@@Trancount=1,执行Insert后,@@Trancount还是吧1,触发器外面Update
Contact后,@@Trancount就成了2,。这里可以掌握成,你当触发器里面,发出一个Begin
Tran,那么SQL Server 就会创造一个嵌套事务。当你于触发器里面,在Rollback
Tran后面屏蔽掉Begin Tran,就会冒出错误3609,如,

use tempdb

Go

If Exists(Select 1 From sys.triggers Where name='tr_Contact')

    Drop  Trigger tr_Contact 

Go

Create Trigger     tr_Contact On Contact After Update,Delete

As

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

 

Insert Into ContactHIST(ContactID,Name,Sex)

    Select ID,Name,Sex From deleted 

 

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

 

Rollback Tran 

 

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

 

Go

 

SQL Server 15

这里,可以望工作在触发器中Rollback,又尚未拉开新的工作,导致整个批处理便搁浅,不会见继续执行触发器外面的Rollback
Tran操作。倘若,你以触发器中以Begin Tran …… Commit
Tran格式,那么触发器Commit
Tran不会见潜移默化至外围的政工;下面描述三种植常见触发器中工作的景象:

SQL Server 16SQL Server 17SQL Server 18

图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了。

     

相关文章