資料庫物件——觸發器——輕鬆走進資料庫系列六__資料庫

觸發器看的我一知半解，看到師兄的一篇觸發器的部落格特別好，看完以後覺得茅塞頓開，我這個悶葫蘆終於也開竅了。先跟大家分享一下這犀利的好文章，其後是我個人的一下補充和思維導圖。

分享：

觸發器簡介：

觸發器（trigger）是種特殊的預存程序，它的執行不是由程式調用，也不需要手動操作，它是由事件來觸發，事件大家應該非常熟悉吧，比如按鈕的Click事件、網頁的Load事件等。按鈕的Click事件是通過按一下滑鼠按鈕觸發的，而觸發器的事件，是由對錶進行增刪改操作所觸發的。當對一個資料庫或表進行增刪改（ Insert，Delete，Update）的時就會啟用觸發器。

從SQL2005開始，根據SQL語句的不同將觸發器分成了兩類，一類是DML觸發器，一類是DLL觸發器。其中DML觸發器又分為兩類：After觸發器和Instead Of觸發器。

 

觸發器的分類：

DML觸發器：DML（Data Manipulation Language）觸發器是當資料庫伺服器中發生資料操作語言事件時執行的預存程序。DML觸發器又分為兩類：After觸發器和Instead Of觸發器

DDL觸發器：DDL觸發器是在響應資料定義語言 (Data Definition Language)（Data Definition Language）事件時執行的預存程序。DDL觸發器一般用於執行資料庫中管理工作。如審核和規範資料庫操作、防止資料庫表結構被修改等。

DML觸發器：

今天我們我們主要介紹DML觸發器，DML觸發器分為After觸發器和Instead Of觸發器。

After觸發器：這類觸發器是在記錄已經改變完之後（after），才會被啟用執行，它主要是用於記錄變更後的處理或檢查，一旦發現錯誤，也可以用Rollback Transaction語句來復原本次的操作。

Instead Of觸發器：這類觸發器一般是用來取代原本的操作，在記錄變更之前發生的，它並不去執行原來SQL語句裡的操作（Insert、Update、Delete），而去執行觸發器本身所定義的操作。

在SQL Server裡，每個DML觸發器都分配有兩個特殊的表，一個是Inserted表，一個是Deleted表。它們兩個存在於資料庫伺服器的記憶體中，是由系統管理的邏輯表，是兩個暫存資料表，而不是真正儲存在資料庫中的物理表。使用者對這兩個表只有讀取的許可權，沒有修改的許可權。

這兩個表的結構(主外鍵、欄位、資料類型等)與觸發器所在資料表的結構是完全一致的，當觸發器的工作完成之後，這兩個表也將會從記憶體中刪除。

Inserted和Deleted兩個表的作用：

Inserted：對於插入記錄操作來說，插入表裡存放的是要插入的資料；對於更新記錄操作來說，插入表裡存放的是要更新的記錄。

Deleted：對於更新記錄操作來說，刪除表裡存放的是被更新記錄；對於刪除記錄操作來說，刪除表裡存入的是被刪除的舊記錄。

看上面兩句話可能不太明白，那麼看看下面這張表是不是就明白了。

 

工作原理：  

After觸發器的工作原理： 

After觸發器是在SQL語句執行之後才被啟用的。以刪除記錄為例：當SQL Server接收到一條刪除操作的SQL語句時，SQL Server先將要刪除的記錄存放在Deleted表裡，然後把資料表裡的記錄刪除，再啟用After觸發器，執行After觸發器裡的SQL語句。執行完畢之後，刪除記憶體中的Deleted表，操作結束。

還是舉上面的例子：在產品庫存表裡，如果要刪除一條產品記錄，在刪除的時候，觸發器可以檢查該產品庫存數量是否為零，如果不為零則取消刪除操作。資料庫的操作如下：

1，接收SQL語句，將要從產品庫存表裡刪除的產品記錄取出來，放在刪除表裡。

2，從產品庫存表裡刪除該產品記錄。

3，從刪除表裡讀出該產品的庫存數量欄位，判斷是不是為零，如果為零的話，完成操作，從記憶體裡清除刪除表；如果不為零的話，用Rollback Transaction語句來復原操作(即將庫存表還原成刪除之前的狀態)。

 

Instead Of 觸發器的工作原理：

Instead Of觸發器與After觸發器不同。After觸發器是在Insert、Update和Delete操作完成後才啟用的，而Instead Of觸發器，是在這些操作進行之前就啟用了，並且不再去執行原來的SQL操作，而是用觸發器本身的SQL語句代替原來的語句去執行。

還拿上面那個例子來說，刪除一條產品記錄的時候，用Instead Of將刪除操作替換成查詢該產品的庫存。資料庫的操作如下：

1，接收SQL語句，但不執行，而是跳轉到Instead Of後面的SQL語句

2，根據傳入的產品ID，將該產品的庫存取出，完成操作。

文法：

REATE TRIGGER <Trigger_Name>

   ON  < Table_Name>

   AFTER|instead of < INSERT,DELETE,UPDATE>

AS

BEGIN

sql語句

END

GO

 

注意事項：

1，After觸發器只能用於資料表不能用於視圖；Instead Of觸發器兩者皆可，設定為With Check Option的視圖也不允許建立Instead Of觸發器。兩種觸發器都不可以建立在暫存資料表上。

2，一個資料表可以有多個觸發器，但是一個觸發器只能對應一個表。

3，在同一個資料表中，對每個操作（如Insert、Update、Delete）而言可以建立許多個After觸發器，而Instead Of觸發器針對每個操作只有建立一個。

4，如果針對某個操作即設定了After觸發器又設定了Instead Of觸發器，那麼Instead of觸發器一定會啟用，而After觸發器就不一定會啟用。

5，不同的SQL語句，可以觸發同一個觸發器，如Insert和Update語句都可以啟用同一個觸發器。

6，觸發器名在所在的資料庫裡必須是唯一的。由於觸發器是建立中資料表或視圖中的，所以有很多人都以為只要是在不同的資料表中，觸發器的名稱就可以相同，其實觸發器的全名（Server.Database.Owner.TriggerName）是必須唯一的，這與觸發器在哪個資料表或視圖無關。

7，關鍵字AFTER可以用For來代取，它們的意思都是一樣的，代表只有在資料表的操作都已正確完成後才會啟用的觸發器。

 

 

個人理解：

補充一點：after觸發器就是在事件發生之後進行檢查確認，

               而instead of 就是在事件發生之前進行替換操作。

DDL觸發器也分為兩類：資料庫層級和伺服器層級。

附上一種思維導圖協助消化。