sqlserver 觸發器

標籤：style   blog   io   ar   color   使用   sp   for   strong   

 何為觸發器？

　　在SQL Server裡面也就是對某一個表的一定的操作，觸發某種條件，從而執行的一段程式。觸發器是一個特殊的預存程序，它不能被顯式地調用﹐而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地啟用。所以觸發器可以用來實現對錶實施複雜的完整性條件約束。

　　SQL Server為每個觸發器都建立了兩個專用表:Inserted表和Deleted表。Deleted和Inserted分別代表出發事件的表“舊的一條記錄”和“新的一條記錄”。這兩個表由系統來維護﹐它們存在於記憶體中而不是在資料庫中。這兩個表的結構總是與被該觸發器作用的表的結構相同。觸發器執行 完成後﹐與該觸發器相關的這兩個表也被刪除。  

Instead of 、 After、for觸發器

　　　Instead of觸發器用於替代引起觸發器執行的T-SQL語句。除表之外﹐Instead of 觸發器也可以用於視圖﹐用來擴充視圖可以支援的更新操作。        

　　After觸發器在一個Insert,Update或Deleted語句之後執行﹐進行約束檢查等動作都在After觸發器被啟用之前發生。After觸發器只能用於表。一個表或視圖的每一個修改動作(insert,update和delete)都可以有一個instead of 觸發器﹐一個表的每個修改動作都可以有多個After觸發器。

　　for觸發器與after相似。for與Insert,Update或Deleted語句同時執行。

觸發器的執行過程

　　 如果一個Insert﹑update或者delete語句違反了約束﹐那么After觸發器不會執行﹐因為對約束的檢查是在After觸發器被激動之前發生的。所以After觸發器不能超越約束。       

　　Instead of 觸發器可以取代激發它的操作來執行。它在Inserted表和Deleted表剛剛建立﹐其它任何操作還沒有發生時被執行。因為Instead of 觸發器在約束之前執行﹐所以它可以對約束進行一些預先處理。

說明：

本文只探討after觸發器，至於instead-of觸發器不在本文討論範圍之內。after觸發器的文法我也略過，重點分享下在應用觸發器時應該注意的地方。 

      第一：觸發器不會對每條記錄都觸發一次。例如我們在一次事務中更新或者刪除了一條以上的記錄，觸發器只會觸發一次，但能正常反映所有受影響的行。

      第二：使用觸發器一定要清楚兩個虛似表：     
          1:inserted,事務中被插入的所有記錄。
          2:deleted，事務中被刪除的所有記錄。
          說明：資料庫中不存在updated表，因為資料的更新是先刪除然後插入的過程。有了上面兩個虛似表，我們就可以在受影響的行上做其它操作，例如在錄入積分酒店資料後，利用inserted中的資料資訊來更新本記錄的某些欄位值。

      第三：after，從字面意義來看應該是某個操作之後的意思，after觸發器就是資料成功插入資料表後執行的操作，如果插入資料失敗，對應的觸發器是不會引發的。  

觸發器的定義和建立

　　觸發器可以在查詢分析器裡建立，也可以在表名上點右鍵->“所有任務”->“管理觸發器”來建立，不過都是要寫 T-SQL 陳述式的，只是在查詢分析器裡要先確定當前操作的資料庫。

　　建立觸發器用 CREATE TRIGGER　　

CREATE TRIGGER trigger_name  //觸發器名ON { table | view }       //視圖或表[ WITH ENCRYPTION ]       //加密{{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE ] }[ WITH APPEND ]          //指定應該添加現有類型的其它觸發器[ NOT FOR REPLICATION ]  //表示當複製進程更改觸發器所涉及的表時，不應執行該觸發器。AS                       //是觸發器要執行的操作。[ { IF UPDATE ( column )    //測試在指定的列上進行的 INSERT 或 UPDATE 操作，不能用於 DELETE 操作。[ { AND | OR } UPDATE ( column ) ]   //是要測試 INSERT 或 UPDATE 操作的列名[ ...n ]| IF ( COLUMNS_UPDATED ( ) updated_bitmask )     //IF ( COLUMNS_UPDATED ( )測試是否插入或更新了提及的列，僅用於 INSERT 或 UPDATE觸發器中  updated_bitmask是用於比較運算的位元運算符。column_bitmask [ ...n ]       //是整型位元遮罩，表示實際更新或插入的列} ]sql_statement [ ...n ]       //是比較子。}}

 

 

 

 例：向表“計0261”插入資料時，檢查學號是否存在於表“計026”中，如存在則進行插入操作，否則就不插入。

CREATE TRIGGER [checkid] ON [dbo].[計0261] INSTEAD OF insertASIF NOT EXISTS(SELECT * FROM 計026 WHERE 學號=(SELECT 學號 FROM INSERTED))  BEGIN    ROLLBACK TRANSACTION    PRINT ‘要處理記錄的學號不存在！‘  ENDELSE  BEGIN    INSERT INTO 計0261 select * from inserted    PRINT ‘已經成功處理記錄！‘  END

 

例：對訂貨表設定 AFTER（FOR） 類型的 INSERT 觸發器，用來在插入記錄時自動將統計值計算到訂貨統計表中。

CREATE TRIGGER [orderinsert] ON [dbo].[訂貨表]AFTER INSERTASDECLARE @bookid int, @ordernum int, @num intSELECT @bookid = 書籍編號, @ordernum = 數量 FROM INSERTEDSELECT @num = count(書籍編號) FROM 訂貨統計表 WHERE 書籍編號=@bookidIF @num = 0  --未找到該書，插入記錄  INSERT INTO 訂貨統計表 VALUES(@bookid, @ordernum)ELSE  --找到該書，更新記錄  UPDATE 訂貨統計表SET 總訂貨量 = 總訂貨量 + @ordernum WHERE 書籍編號 = @bookid

 

sqlserver 觸發器