濮阳杆衣贸易有限公司

主頁 > 知識庫 > SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)

SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)

熱門標(biāo)簽:al智能電話機(jī)器人 最新人工智能電銷機(jī)器人 成都電話外呼系統(tǒng)一般多少錢 陜西電銷外呼系統(tǒng)好用嗎 如何做地圖標(biāo)注圖鋪 銀川高頻外呼回?fù)芟到y(tǒng)多少錢 數(shù)字匠心電銷機(jī)器人 沒聽見電話機(jī)器人幫你接 蘭州語音外呼系統(tǒng)運營商
如果你有對觸發(fā)器和事務(wù)的概念,有些了解,這篇文章,對你來說會是很簡單,或能讓你更進(jìn)一步的了解觸發(fā)器里面的一些故事,和觸發(fā)器中事務(wù)個故事。在這邊文章里面,我不會從觸發(fā)器和事務(wù)的概念去講述,而是從常見的兩種觸發(fā)器類型(DML觸發(fā)器 DDL觸發(fā)器)和After觸發(fā)器   Instead Of 觸發(fā)器的應(yīng)用不同,開始說起它們,然后是說與事務(wù)有關(guān)的故事。如果,你有什么建議和意見,都可以通過文章后面的回復(fù)與我溝通,或者通過E-Mail方式,與 我交流;我的Email地址是:glal@163.com

     在下面的內(nèi)容,用到一些SQL Server 觸發(fā)器和事務(wù)的一些術(shù)語,如果有些不明白的地方,可以查閱MSDN資料庫,或SQL Server本地幫助文檔:

DML觸發(fā)器(DML Triggers) DDL觸發(fā)器(DDL Triggers) 事務(wù)模式(Transaction modes) 顯式事務(wù)(Explicit Transactions) 自動提交事務(wù)(Autocommit Transactions) 隱式事務(wù)(Implicit Transactions) 批范圍的事務(wù)(Batch-scoped Transactions)

After觸發(fā)器 Vs Instead Of觸發(fā)器

            After 觸發(fā)器將在處理觸發(fā)操作(Insert、Update 或 Delete)、Instead Of 觸發(fā)器和約束之后激發(fā)。Instead Of是將在處理約束前激發(fā),以替代觸發(fā)操作。下面兩張圖描述了After觸發(fā)器和Instead Of觸發(fā)器的執(zhí)行先后順序。

           
 

     圖1                                                                             圖2

     左邊的圖1,描述了After觸發(fā)器執(zhí)行順序情況,我在這里通過一個簡單的例子來說明After觸發(fā)器的執(zhí)行順序,以便能加深對左圖1 After觸發(fā)器的理解。

先創(chuàng)建表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

再創(chuàng)建After觸發(fā)器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表的內(nèi)容,用來判斷觸發(fā)器執(zhí)行的先后順序*/
Go

然后Insert數(shù)據(jù),判斷After觸發(fā)器的執(zhí)行順序

use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go

這里,在沒有運行Insert語句之前,我們可以判斷,執(zhí)行Insert過程會觸發(fā)Check錯誤,因為字段Sex的值必須是”F” Or “M”,而這里將要插入的是”U”.好了,再來看運行Insert語句后的情況。

本例子,只看到引發(fā)Check約束沖突的錯誤,而無法看到Inserted表的數(shù)據(jù),說明一點就是,引起Check約束之前,不會引發(fā)After觸發(fā)器tr_Contact的操作。這就驗證了圖1的After觸發(fā)器執(zhí)行順序情況。

     好了,接下來,我們再測試Instead Of觸發(fā)器 圖2的情況;我使用上邊建好的測試表Contact來舉例。

先修改觸發(fā)器tr_Contact內(nèi)容,

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 '觸發(fā)器作代替執(zhí)行操作'
Insert Into Contact (Name,Sex) Select Name,Sex From Inserted /*代替觸發(fā)器外面的Insert行為*/
Go

再Insert數(shù)據(jù),觀察SQL Server執(zhí)行后的提示信息,

use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go

  

這里,看到,先是觸發(fā)器操作,再是Check約束處理。本例中,在觸發(fā)器里面使用一條Insert的語句來描述觸發(fā)器的代替執(zhí)行操作,這SQL語句通過Select表Inserted得到觸發(fā)器外面Insert內(nèi)容。當(dāng)SQL Server執(zhí)行到觸發(fā)器里面的Insert語句,才會引起Check約束處理.倘若,在觸發(fā)器tr_Contact沒有Insert的代替行為,那么就不會出現(xiàn)Check約束處理錯誤的信息(注:沒有Check錯誤信息,并不表示沒有作Check處理)。修改上邊的觸發(fā)器tr_Contact內(nèi)容,做個簡易的驗證.

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 '觸發(fā)器作代替執(zhí)行操作'
Go
use tempdb
Go
Insert Into Contact (Name,Sex) Values ('Bill','U')
Go
Select * From Contact

可以看到,Instead Of 觸發(fā)器tr_Contact內(nèi)容沒有Insert的SQL語句,不會引發(fā)Check處理錯誤,而且檢查Insert動作后的結(jié)果,發(fā)現(xiàn)表Contact也沒有之前我們Insert的數(shù)據(jù)。這些足夠驗證了Instead Of觸發(fā)器的執(zhí)行先后順序和代替執(zhí)行操作。

 

 

 

DML 觸發(fā)器 Vs DDL 觸發(fā)器


      DML 觸發(fā)器在 Insert、Update 和 Delete 語句上操作,可以作為After 觸發(fā)器 和 Instead Of 觸發(fā)器。

     DDL 觸發(fā)器對 Create、Alter、Drop 和其他 DDL 語句以及執(zhí)行 DDL 式操作的存儲過程執(zhí)行操作,只可作為After觸發(fā)器,不能Instead Of觸發(fā)器。

     前面的內(nèi)容,有描述DML觸發(fā)器中的After Instead Of觸發(fā)器內(nèi)容,下面直接來看DDL的操作順序:

    

     圖3.

     從圖3.可以知道,在DDL觸發(fā)器中,是沒有創(chuàng)建Inserted Deleted過程的,我們通過簡單的例子去測試下。

     創(chuàng)建一個服務(wù)器范圍內(nèi)的DDL觸發(fā)器,檢查有沒有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

執(zhí)行創(chuàng)建數(shù)據(jù)庫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觸發(fā)器中,不會創(chuàng)建Deleted表;是否創(chuàng)建Deleted Inserted,也可以認(rèn)為是DDL觸發(fā)器與DML觸發(fā)器不同之處。在DLL觸發(fā)器與DML觸發(fā)器不同的一個重要特征是作用域,DML觸發(fā)器只能應(yīng)用在數(shù)據(jù)庫層(Database Level)的表和視圖上,而DDL觸發(fā)器應(yīng)用于數(shù)據(jù)庫層(Database Level)和服務(wù)器層(Server Level);DDL觸發(fā)器的作用域取決于事件。下面簡單描述下事件組的內(nèi)容。

 

數(shù)據(jù)庫層事件主要包含:

    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

服務(wù)器層事件主要包含:

    Create Database, Drop Database Create Login, Drop Login, Alter Login

 

 

觸發(fā)器和事務(wù)的故事


      在前面的幾個例子中,如DML觸發(fā)器例子,Insert 語句執(zhí)行后,因為觸發(fā)器操作 或 Check處理錯誤,沒有把數(shù)據(jù)真正的插入到表Contact中。其實,當(dāng)執(zhí)行觸發(fā)器時,觸發(fā)器的操作好像有一個未完成的事務(wù)在起作用。 通過幾個例子來講解觸發(fā)器和事務(wù)的故事。

創(chuàng)建一個表ContactHIST,用于對表Contact作Update Or Delete操作時,把操作前的數(shù)據(jù)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

修改觸發(fā)器tr_Contact內(nèi)容,

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

測試數(shù)據(jù),

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

測試結(jié)果:

從上邊的測試情況,看出,Update Contact觸發(fā)tr_Contact觸發(fā)器操作,觸發(fā)器里面的Rollback Tran 動作導(dǎo)致了觸發(fā)器外面的Update語句執(zhí)行回滾,而Rollback Tran 語句后面的Begin Tran語句,主要是應(yīng)用于保持整個事務(wù)的完整性。為了更能理解這一過程,我模擬了一個觸發(fā)器中的事務(wù)開始結(jié)束過程。

圖4.

在SQL Server 2005 和 SQL Server 2008上面,可以看到如圖4.的效果。在低版本的SQL Server上,可能會出現(xiàn)錯誤提示情況,不管如何,在觸發(fā)器外面,SQL Server都會Rollback Tran。下面我做個錯誤提示的例子。

修改觸發(fā)器tr_Contact內(nèi)容

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

重新執(zhí)行Update操作,

use tempdb
Go
Update Contact 
 Set Sex='M'
 Where Name='Bill'
Go 
Select @@TRANCOUNT 
Go
Select * From Contact
Select * From ContactHIST
Go

在觸發(fā)器里面沒有Begin Tran語句動作,觸發(fā)器外面也能回滾操作。這里我們可以通過查詢表數(shù)據(jù)和@@Trancount來判斷。

         其實,上面的例子,Update語句,是以自動提交事務(wù)(Autocommit Transactions)模式 開始執(zhí)行的,觸發(fā)器里Rollback Tran后面,不管有沒有Begin Tran ,最后都會事務(wù)都會交回給SQL Server自動提交事務(wù)管理。當(dāng)然,在DML觸發(fā)器中,你可以使用顯式事務(wù)(Explicit Transactions),或開啟隱式事務(wù)(Implicit Transactions) 來控制,當(dāng)然你也可以應(yīng)用于批范圍的事務(wù)(Batch-scoped Transactions) 中。這里,我通過開啟隱式事務(wù)(Implicit Transactions) 的例子來說,觸發(fā)器與事務(wù)的關(guān)系。

修改觸發(fā)器tr_Contact的內(nèi)容,

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'觸發(fā)器里Insert 前,@@Trancount='+Rtrim(@@Trancount)
 
Insert Into ContactHIST(ContactID,Name,Sex)
 Select ID,Name,Sex From deleted 
 
Print N'觸發(fā)器里Insert后,Rollback Tran 前,@@Trancount='+Rtrim(@@Trancount)
 
Rollback Tran 
 
Print N'觸發(fā)器里Rollback Tran 后,@@Trancount='+Rtrim(@@Trancount)
 
Begin Tran 
Go

開啟隱式事務(wù)(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'觸發(fā)器外面Rollback Tran 后,@@Trancount='+Rtrim(@@Trancount)
 
Go 
Set Implicit_transactions Off /**/
Go
 
Go
Select * From Contact
Select * From ContactHIST
Go

 

 

這里,你是否發(fā)現(xiàn)一個很有意思的問題,在觸發(fā)器理,執(zhí)行Insert ContactHIST之前,@@Trancount=1,執(zhí)行Insert后,@@Trancount還是為1,觸發(fā)器外面Update Contact后,@@Trancount就變成了2,。這里可以理解成,你在觸發(fā)器里面,發(fā)出一個Begin Tran,那么SQL Server 就會創(chuàng)建一個嵌套事務(wù)。當(dāng)你在觸發(fā)器里面,在Rollback Tran后面屏蔽掉Begin Tran,就會出現(xiàn)錯誤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'觸發(fā)器里Insert 前,@@Trancount='+Rtrim(@@Trancount)
 
Insert Into ContactHIST(ContactID,Name,Sex)
 Select ID,Name,Sex From deleted 
 
Print N'觸發(fā)器里Insert后,Rollback Tran 前,@@Trancount='+Rtrim(@@Trancount)
 
Rollback Tran 
 
Print N'觸發(fā)器里Rollback Tran 后,@@Trancount='+Rtrim(@@Trancount)
 
Go

 

這里,可以看到事務(wù)在觸發(fā)器中Rollback,又沒有開啟新的事務(wù),導(dǎo)致整個批處理就中止,不會繼續(xù)執(zhí)行觸發(fā)器外面的Rollback Tran操作。倘若,你在觸發(fā)器中使用Begin Tran …… Commit Tran格式,那么觸發(fā)器Commit Tran不會影響到外面的事務(wù);下面描述三種常見觸發(fā)器中事務(wù)的情況:

圖5.                                                                             圖6.                                                                           圖7.

圖5.描述在觸發(fā)器中含有Begin Tran …… Commit Tran的情況,

圖6.描述在觸發(fā)器中含有Save Tran savepoint_name …… Rollback Tran savepoint_name 的情況,觸發(fā)器中的Rollback Tran 只會回滾指定的保存點,不會影響到觸發(fā)器外面的Commit Tran Or Rollback Tran操作。

圖7.描述在觸發(fā)器中含有Rollback Tran的情況,不管觸發(fā)器里面有沒有Begin Tran,都會出現(xiàn)錯誤3609,中止批處理。

    注:DDL觸發(fā)器操作可以觸發(fā)器中回滾操作,可以使用命令如Rollback,但嚴(yán)重錯誤可能會導(dǎo)致整個事務(wù)自動回滾。不能回滾發(fā)生在 DDL 觸發(fā)器正文內(nèi)的 Alter Database事件。在觸發(fā)器中使用Rollback … Begin Tran 可能會導(dǎo)致意想不到的結(jié)果,在沒有確認(rèn)和測試情況下,請不要隨便在觸發(fā)器中直接使用Rollback …Begin Tran處理方式.特別是Create Database事件,在SQL Server 2008和SQL Server 2005環(huán)境下,產(chǎn)生的結(jié)果不同。

Rollback …Begin Tran情況:

Create Trigger ….

As

……

Rollback

Begin Tran

End

小結(jié)


 

     回顧前文至后文,從After觸發(fā)器VsInstead Of 觸發(fā)器,說到DML觸發(fā)器 Vs DDL觸發(fā)器,再到觸發(fā)器中事務(wù)的故事。也許有些地方描述的有些模糊,有些地方只有一筆帶過;你在測試代碼過程中,可能發(fā)現(xiàn)有些地方與這里測試的情況不同,那可能是因為SQL Server版本的不同,導(dǎo)致一些測試結(jié)果不同。無論如何,只要你感覺對你了解觸發(fā)器,有些幫助,就OK了。

您可能感興趣的文章:
  • SQLServer觸發(fā)器創(chuàng)建、刪除、修改、查看示例代碼
  • SQL Server 觸發(fā)器 表的特定字段更新時,觸發(fā)Update觸發(fā)器
  • sqlserver 觸發(fā)器實例代碼
  • sqlserver 禁用觸發(fā)器和啟用觸發(fā)器的語句
  • SQL SERVER中各類觸發(fā)器的完整語法及參數(shù)說明
  • SQL Server:觸發(fā)器實例詳解
  • sqlserver中觸發(fā)器+游標(biāo)操作實現(xiàn)
  • sqlserver 觸發(fā)器學(xué)習(xí)(實現(xiàn)自動編號)
  • sqlserver 觸發(fā)器教程
  • 關(guān)于喜憂參半的SQL Server觸發(fā)器詳解

標(biāo)簽:宜春 朔州 通化 邢臺 鹽城 巴彥淖爾 遼源 本溪

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)》,本文關(guān)鍵詞  SQL,Server,觸發(fā)器,及,中的,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)》相關(guān)的同類信息!
  • 本頁收集關(guān)于SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章
    四平市| 宜阳县| 富阳市| 荣成市| 中方县| 随州市| 萨迦县| 金沙县| 清丰县| 金溪县| 朔州市| 台山市| 文水县| 临夏县| 鹰潭市| 湟中县| 滦南县| 同江市| 红安县| 隆子县| 藁城市| 聊城市| 醴陵市| 迁安市| 达日县| 富宁县| 四会市| 道真| 棋牌| 长白| 白朗县| 富平县| 宾川县| 佛冈县| 军事| 鱼台县| 宁陵县| 山阳县| 阳曲县| 三门县| 梅州市|