SQL Server 觸發(fā)器

觸發(fā)器（trigger）是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執(zhí)行不是由程序調用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作（ insert，delete， update）時就會激活它執(zhí)行。觸發(fā)器經常用于加強數據的完整性約束和業(yè)務規(guī)則等。

SQL Server包括三種常規(guī)類型的觸發(fā)器：

• DML觸發(fā)器
• DDL觸發(fā)器
• 登錄觸發(fā)器

1.DML(數據操作語言,Data Manipulation Language)觸發(fā)器

DML觸發(fā)器是一些附加在特定表或視圖上的操作代碼，當數據庫服務器中發(fā)生數據操作語言事件時執(zhí)行這些操作。

SqlServer中的DML觸發(fā)器有三種：

• insert觸發(fā)器：向表中插入數據時被觸發(fā)；
• update觸發(fā)器：修改表中數據時被觸發(fā)；
• delete觸發(fā)器：從表中刪除數據時被觸發(fā)。

當遇到下列情形時，應考慮使用DML觸發(fā)器：

• 通過數據庫中的相關表實現級聯更改
• 防止惡意或者錯誤的insert、update和delete操作，并強制執(zhí)行check約束定義的限制更為復雜的其他限制。
• 評估數據修改前后表的狀態(tài)，并根據該差異才去措施。

2.DDL(數據定義語言,Data Definition Language)觸發(fā)器

DDL觸發(fā)器是當服務器或者數據庫中發(fā)生數據定義語言(主要是以create,drop,alter開頭的語句)事件時被激活使用

使用DDL觸發(fā)器可以防止對數據架構進行的某些更改或記錄數據中的更改或事件操作

3.登錄觸發(fā)器

登錄觸發(fā)器將為響應 LOGIN 事件而激發(fā)存儲過程。與 SQL Server 實例建立用戶會話時將引發(fā)此事件。

登錄觸發(fā)器將在登錄的身份驗證階段完成之后且用戶會話實際建立之前激發(fā)。因此，來自觸發(fā)器內部且通常將到達用戶的所有消息（例如錯誤消息和來自 PRINT 語句的消息）會傳送到 SQL Server 錯誤日志。

如果身份驗證失敗，將不激發(fā)登錄觸發(fā)器。

DML觸發(fā)器

• DML觸發(fā)器執(zhí)行時，系統(tǒng)內存會自動生成deleted表或inserted表，執(zhí)行結束會自動消失。
• Insert觸發(fā)器，使用到inserted表；
• Update觸發(fā)器，使用到deleted表和inserted表；
• Delete觸發(fā)器，使用到deleted表。

下面引用一張圖，簡單明了展示了DML觸發(fā)器：

DML觸發(fā)器Demo

表結構如下：

Insert 觸發(fā)器：

在向目標表中插入數據后，會觸發(fā)該表的Insert 觸發(fā)器，系統(tǒng)自動在內存中創(chuàng)建inserted表； 下面的demo中對Age加了判斷，如果不滿足判斷數據會進行回滾，插入的數據操作會失敗。

--Insert 觸發(fā)器
Create TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;

	Declare @age int;
	Select @age=Age  From inserted

	--如果年齡小于150正常插入，否則數據回滾
	IF(@age<150)
		Begin
			Insert into PersonLog(PersonID, Name, Age, AddDate)
			Select ID, Name, Age, AddDate From inserted
		End
	ELSE
		Begin
		    print('年齡應小于150')
			rollback transaction     --數據回滾
		END
    
END

Update 觸發(fā)器：

在向目標表中更新數據后，會觸發(fā)該表的Update 觸發(fā)器，系統(tǒng)自動在內存中創(chuàng)建deleted表和inserted表，deleted表存放的是更新前的數據，inserted表存放的是更新的數據。

--Update 觸發(fā)器
Create TRIGGER [dbo].[Trigger_Update]
   ON  [dbo].[Person]
   AFTER UPDATE
AS 

BEGIN	
	SET NOCOUNT ON;

	--這里是先刪除后插入，存在一張臨時表deleted
    Insert Into PersonLog(PersonID, Name, Age, AddDate, UpdateDate)
	Select ID, Name, Age, AddDate, UpdateDate From inserted

END

Delete 觸發(fā)器：

在向目標表中刪除數據后，會觸發(fā)該表的Delete 觸發(fā)器，系統(tǒng)自動在內存中創(chuàng)建deleted表，deleted表存放的是刪除的數據。

--Delete 觸發(fā)器
Create TRIGGER [dbo].[Trigger_Delete]
   ON  [dbo].[Person]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

    Insert Into PersonLog(PersonID, Name, Age, AddDate, UpdateDate, DeleteDate)
	Select ID, Name, Age, AddDate, UpdateDate, GETDATE() From deleted

END

觸發(fā)器優(yōu)點：　　

• 1.強化約束：強制復雜業(yè)務的規(guī)則和要求，能實現比check語句更為復雜的約束?！　?/li>
• 2.跟蹤變化：觸發(fā)器可以偵測數據庫內的操作，從而禁止數據庫中未經許可的更新和變化?！　?/li>
• 3.級聯運行：偵測數據庫內的操作時，可自動地級聯影響整個數據庫的各項內容。　　
• 4.嵌套調用：觸發(fā)器可以調用一個或多個存儲過程。觸發(fā)器最多可以嵌套32層。

觸發(fā)器缺點：　

• 1. 可移植性差。
• 2.占用服務器資源，給服務器造成壓力?！　?/li>
• 3.執(zhí)行速度主要取決于數據庫服務器的性能與觸發(fā)器代碼的復雜程度?！　?/li>
• 4.嵌套調用一旦出現問題，排錯困難，而且數據容易造成不一致，后期維護不方便。

觸發(fā)器使用建議：

• 1.盡量避免在觸發(fā)器中執(zhí)行耗時操作，因為觸發(fā)器會與SQL語句認為在同一事務中，事務不結束，就無法釋放鎖。
• 2.避免在觸發(fā)器中做復雜操作，影響觸發(fā)器性能的因素比較多（Eg:產品版本，所使用的架構等），要想編寫高效的觸發(fā)器考慮因素比較多，編寫高性能觸發(fā)器還是很難的。
• 3.觸發(fā)器編寫時注意多行觸發(fā)時的處理。（一般不建議使用游標）

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論