SQL Server 觸發(fā)器

觸發(fā)器（trigger）是SQL server 提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來觸發(fā)，比如當(dāng)對(duì)一個(gè)表進(jìn)行操作（ insert，delete， update）時(shí)就會(huì)激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiá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ā)器是一些附加在特定表或視圖上的操作代碼，當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言事件時(shí)執(zhí)行這些操作。

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

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

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

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

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

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

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

3.登錄觸發(fā)器

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

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

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

DML觸發(fā)器

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

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

DML觸發(fā)器Demo

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

Insert 觸發(fā)器：

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

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

在向目標(biāo)表中更新數(shù)據(jù)后，會(huì)觸發(fā)該表的Update 觸發(fā)器，系統(tǒng)自動(dò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;

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

END

Delete 觸發(fā)器：

在向目標(biāo)表中刪除數(shù)據(jù)后，會(huì)觸發(fā)該表的Delete 觸發(fā)器，系統(tǒng)自動(dò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)點(diǎn)：　　

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

觸發(fā)器缺點(diǎn)：　

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

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

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

總結(jié)

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

最新評(píng)論