atitit...觸發器機制 ltrigger mechanism sumup .的總結O8f,ltriggero8f
atitit...觸發器機制 ltrigger mechanism sumup .的總結O8f
1. 觸發器的類型 1
2. 實現原理 1
3. After觸發器 Vs Instead Of觸發器 1
4. DML 觸發器 Vs DDL 觸發器 3
5. 觸發器and事務 3
6. 下面描述三種常見觸發器中事務的情況: 5
7. 2、 instead of 觸發器 (之前觸發) 7
8. 觸發器有兩個特殊的表:插入表(instered表)和刪除表(deleted表)。 7
9. 表格記錄複製and遷移SELECT INTO 和 INSERT INTO SELECT 8
10. 參考 9
1. 觸發器的類型
· DML觸發器(DML Triggers)
· DDL觸發器(DDL Triggers)
· 事務模式(Transaction modes)
· 明確交易(Explicit Transactions)
· 自動認可事務(Autocommit Transactions)
· 隱含交易(Implicit Transactions)
· 批範圍的事務(Batch-scoped Transactions)
2. 實現原理3. After觸發器 Vs Instead Of觸發器
After 觸發器將在處理觸發操作(Insert、Update 或 Delete)、Instead Of 觸發器和約束之後激發。Instead Of是將在處理約束前激發,以替代觸發操作。下面兩張圖描述了After觸發器和Instead Of觸發器的執行先後順序。
作者::老哇的爪子Attilax艾龍,EMAIL:1466519819@qq.com
轉載請註明來源: http://blog.csdn.net/attilax
圖1 圖2
4. 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過程的,我們通過簡單的例子去測試下
5. 觸發器and事務
看出,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。下面我做個錯誤提示的例子。
不管如何,在觸發器外面,SQL Server都會Rollback Tran。下面我做個錯誤提示的例子。
在觸發器裡面沒有Begin Tran語句動作,觸發器外面也能復原操作。這裡我們可以通過查詢表資料和@@Trancount來判斷。
其實,上面的例子,Update語句,是以自動認可事務(Autocommit Transactions)模式 開始執行的,觸發器裡Rollback Tran後面,不管有沒有Begin Tran ,最後都會事務都會交回給SQL Server自動認可交易管理。當然,在DML觸發器中,你可以使用明確交易(Explicit Transactions),或開啟隱含交易(Implicit Transactions) 來控制,當然你也可以應用於批範圍的事務(Batch-scoped Transactions) 中。這裡,我通過開啟隱含交易(Implicit Transactions) 的例子來說,觸發器與事務的關係。
這裡,你是否發現一個很有意思的問題,在觸發器理,執行Insert ContactHIST之前,@@Trancount=1,執行Insert後,@@Trancount還是為1,觸發器外面Update Contact後,@@Trancount就變成了2,。這裡可以理解成,你在觸發器裡面,發出一個Begin Tran,那麼SQL Server 就會建立一個嵌套事務。當你在觸發器裡面,在Rollback Tran後面屏蔽掉Begin Tran,就會出現錯誤3609,如,
6. 下面描述三種常見觸發器中事務的情況:
圖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環境下,產生的結果不同。
7. 2、 instead of 觸發器 (之前觸發)
其中after觸發器要求只有執行某一操作insert、update、delete之後觸發器才被觸發,且只能定義在表上。而instead of觸發器表示並不執行其定義的操作(insert、update、delete)而僅是執行觸發器本身。既可以在表上定義instead of觸發器,也可以在視圖上定義。
8. 觸發器有兩個特殊的表:插入表(instered表)和刪除表(deleted表)。
這兩張是邏輯表也是虛表。有系統在記憶體中建立者兩張表,不會儲存在數 據庫中。而且兩張表的都是唯讀,只能讀取資料而不能修改資料。這兩張表的結果總是與被改觸發器應用的表的結構相同。當觸發器完成工作後,這兩張表就會被 刪除。Inserted表的資料是插入或是修改後的資料,而deleted表的資料是更新前的或是刪除的資料。
對錶的操作 |
Inserted邏輯表 |
Deleted邏輯表 |
增加記錄(insert) |
存放增加的記錄 |
無 |
刪除記錄(delete) |
無 |
存放被刪除的記錄 |
修改記錄(update) |
存放更新後的記錄 |
存放更新前的記錄 |
Update資料的時候就是先刪除表記錄,然後增加一條記錄。這樣在inserted和deleted表就都有update後的資料記錄了。注意的是:觸 發器本身就是一個事務,所以在觸發器裡面可以對修改資料進行一些特殊的檢查。如果不滿足可以利用交易回復,撤銷操作。
9. 表格記錄複製and遷移SELECT INTO 和 INSERT INTO SELECT
Insert是T-sql中常用語句,Insert INTO table(field1,field2,...) values(value1,value2,...)這種形式的在應用程式開發中必不可少。但我們在開發、測試過程中,經常會遇到需要表複製的情況,如將 一個table1的資料的部分欄位複製到table2中,或者將整個table1複製到table2中,這時候我們就要使用SELECT INTO 和 INSERT INTO SELECT 表複製語句了。
SELECT INTO 要新的建立表格...INSERT INTO SELECT 可以存在的表格insert 資料..所以,還是.INSERT INTO SELECT 使用的常用...
ALTER TRIGGER [dbo].[trg_inst]
ON [dbo].[t_mb_weixinuser]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into wechatuhstry(userid, [subscribe] ,[openid] ,[subscribe_time],[groupid] ,[createTime] ,[updateTime] ,nickname) select userid,[subscribe] ,[openid] ,[subscribe_time],[groupid] ,[createTime] ,[updateTime] ,nickname from inserted;
-- Insert statements for trigger here
END
#----最佳實務
copy2表格的id自動incream最好關閉..中間id一樣..分享一樣的主鍵.
text類型的不能copy,只好指定欄位了...
#---rest風格安全可以多次複製
Insert into wechatuhstry
select top 100000 * from t_mb_weixinuser where UserID not in
(select UserID from wechatuhstry)
10. 參考
SQL Server 觸發器 - hoojo - 部落格園.htm
瞭解SQL Server觸發器及觸發器中的事務 - OK_008 - 部落格園.htm