SQL Server 觸發(fā)器

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

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

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

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

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

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

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

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

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

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

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

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

3.登錄觸發(fā)器

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

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

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

DML觸發(fā)器

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

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

DML觸發(fā)器Demo

表結(jié)構(gòu)如下：

Insert 觸發(fā)器：

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

--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正常插入，否則數(shù)據(jù)回滾
	IF(@age<150)
		Begin
			Insert into PersonLog(PersonID, Name, Age, AddDate)
			Select ID, Name, Age, AddDate From inserted
		End
	ELSE
		Begin
		    print('年齡應(yīng)小于150')
			rollback transaction     --數(shù)據(jù)回滾
		END
    
END

Update 觸發(fā)器：

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

--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ā)器：

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

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

觸發(fā)器缺點：　

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

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

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

總結(jié)

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

最新評論