觸發器學習筆記

在SQL Server中,我們可以使用兩種方法來設定自動化的資料處理規則:

1.條件約束(Constraint)可以直接設定於資料表內,通常不需要另外撰寫程式.但此方法只能進行比較簡單的運作,包括

自動填入預設值(default),確保欄位資料不得重復(primary key/unique key),限制輸入值在某個範圍內(check),維護資料

表間的完整性(foreign key)...等

2.觸發程式(trigger)是針對單一資料表所撰寫的特殊預存程式,當該資料表發生insert,update,delete時會自動被觸發,以進行

各項必要的處理工作.由於是撰寫程式,因此無論是單純或復雜的工作都可以一手包辦.

 

 

觸發器到底有什麼特異功能呢?

1.檢查所做的更改是否允許:

2.進行其他相關資料的更改動作

3.發出更改或預警的通知(例如有新資料輸入時,可以立即MAIL通知該部門)

4.自訂錯誤訊息

5.更改原來所要進行的資料操作(instead of)

6.檢視表也可以有觸發程式(檢視表中的計算欄位通常是不允許更改的,但同樣是利用INSTEAD OF觸發程式,我們可以打破這個限制,將

預備要更改的欄位截出來另外處理)

 

其實觸發器就像是倉庫的管理員一樣,當有貨物要進出時,管理員即會出面做查核或協調,以維護整個倉庫的正常運作.因此,如果你是DBA,那麼

就應該好好利用觸發器的功能,為每個重要的資料表設計一個最佳的倉庫管理員,這樣就不用擔心使用者胡作非為,或是不按照牌理出牌拉.

 

 

DML觸發器,當資料表或檢視表發生修改時,會自動執行DML觸發程式.

DDL觸發器,則是被利用與資料庫發生變動時,例如執行create,alter,drop等敘述,就會自動執行DDL觸發器.

 

這裡主要學習DML觸發器

 

觸發器可以分為類:

1.after 觸發程式:這類的觸發程式要在資料變動完之後(after),才會被啟動並進行必要的處理和檢查.若發現有錯誤,可用

rollback transaction 敘述將此次操作所更動的資料全部回復.

2.instead of 觸發器:這類觸發器取代原本要進行的操作,因此會在資料變動之前就發生,而且資料如何變動也完全取決於觸發器.

 

instead of 觸發器能夠適用於資料表或檢視表,而after觸發器只適用與資料表.另外,在建立觸發器時要指定被觸發的操作時機,

insert,update,或delete.至少要指定一種.當然一個觸發器也可以通知指定種或種.在同一個資料表中,我們可以建立許多的

after觸發器.但instead of觸發器針對每種操作最多隻能有有一個.

 

 

如果同時指定了instead of 與after 觸發器,那麼只有前者會被觸發,後者未必會被觸發.

 

如果資料表的Foreign Key刪除規則選項設為重疊顯示(cascade)則不可建立delete 的instead of 觸發程式.同理更新.

因為當delete rule設為cascade時,若刪除一筆客戶資料,則相關記錄都會自動刪除,因此不允許設定INSTEAD OF觸發器.更新同理.

 

如果將資料表刪除了,那麼資料表的觸發程式也會隨之刪除.

如果相要變更觸發程式名稱,必須先將之刪除然後自再以新名稱重新建立.

 

檢視觸發器的相關資訊

1.sp_helptrigger 'table_name','type'--type 可為insert,update,delete

2.sp_help 'trigger_name'

3.sp_helptext 'trigger_name' --可列出指定觸發程式的內容

4.sp_depends 'trigger_name'--列出指定觸發程式中所使用到的物件

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-611611/，如需轉載，請註明出處，否則將追究法律責任。