程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL Server觸發器及觸發器中的事務進修

SQL Server觸發器及觸發器中的事務進修

編輯：MSSQL

SQL Server觸發器及觸發器中的事務進修。本站提示廣大學習愛好者：（SQL Server觸發器及觸發器中的事務進修）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server觸發器及觸發器中的事務進修正文

假如你有對觸發器和事務的概念，有些懂得，這篇文章，對你來講會是很簡略，或能讓你更進一步的懂得觸發器外面的一些故事，和觸發器中事務個故事。在這邊文章外面，我不會從觸發器和事務的概念去講述，而是從罕見的兩種觸發器類型(DML觸發器 & DDL觸發器)和After觸發器 & Instead Of 觸發器的運用分歧，開端說起它們，然後是說與事務有關的故事。假如，你有甚麼建議和看法，都可以經由過程文章前面的答復與我溝通，或許經由過程E-Mail方法，與我交換；我的Email地址是：[email protected]

鄙人面的內容，用到一些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 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語句後的情形。

本例子，只看到激發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

這裡，看到，先是觸發器操作，再是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

可以看到，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中。

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

測試成果：

從上邊的測試情形，看出，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

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

在觸發器外面沒有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

這裡，你能否發明一個很成心思的成績，在觸發器理，履行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

這裡，可以看到事務在觸發器中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 ….

……

Rollback

Begin Tran

End

小結

回想前文至後文，從After觸發器VsInstead Of 觸發器，說到DML觸發器 Vs DDL觸發器，再到觸發器中事務的故事。或許有些處所描寫的有些隱約，有些處所只要一筆帶過；你在測試代碼進程中，能夠發明有些處所與這裡測試的情形分歧，那能夠是由於SQL Server版本的分歧，招致一些測試成果分歧。不管若何，只需你感到對你懂得觸發器，有些贊助，就OK了。