SqlServer觸發器的創建與使用

前言

上期我們介紹了SqlServer的視圖和存儲過程創建與使用，這期我們介紹一下觸發器。

有需要回顧的可以電梯直達看一下：

SqlServer視圖的創建與使用

SqlServer存儲過程的創建與使用

 

觸發器的定義

觸發器（Trigger）是針對某個表或視圖所編寫的特殊存儲過程，它不能被顯式地調用，

而是當該表或視圖中的數據發生添加INSERT、更新UPDATE或刪除DELETE等事件時自動被執行。

主要作用是實現由主鍵和外鍵所不能保證的複雜的參照完整性和數據一致性。

觸發器的作用

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性，

它能夠對數據庫中的相關表進行級聯修改，提高比CHECK約束更複雜的的數據完整性，並自定義錯誤消息。

觸發器的主要作用主要有以下接個方面：

  1. 強制數據庫間的引用完整性；
  2. 級聯修改數據庫中所有相關的表，自動觸發其它與之相關的操作；
  3. 跟蹤變化，撤銷或回滾違法操作，防止非法修改數據；
  4. 返回自定義的錯誤消息，約束無法返回信息，而觸發器可以；
  5. 觸發器可以調用更多的存儲過程；

觸發器的優點

  1. 觸發器是自動的。當對表中的數據做了任何修改之後立即被激活。
  2. 觸發器可以通過數據庫中的相關表進行層疊修改。
  3. 觸發器可以強制限制。這些限制比用CHECK約束所定義的更複雜。與CHECK約束不同的是，觸發器可以引用其他表中的列。

觸發器的缺點

  1. 增加了系統的複雜性。
  2. 嵌套觸發器容易出現死鎖現象。
  3. 觸發器並沒有提升多少性能。
  4. 可移植性差。
  5. 佔用服務器資源，給服務器造成壓力。
  6. 複雜的觸發器維護困難。

觸發器的分類

SqlServer包括三種常規類型的觸發器：DML觸發器、DDL觸發器和登錄觸發器。

 

DML觸發器

如果用戶要通過數據操作語言 (DML) 事件編輯數據，則執行 DML 觸發器。

DML 事件是針對錶或視圖的 INSERT、UPDATE 或 DELETE 語句。

在激發任何有效的事件時，將會激發這些觸發器，而無論是否會影響任何錶行。

當數據庫服務器中發生數據操作語言事件時執行這些操作。

SqlServer中的DML觸發器有三種：

  1. insert觸發器:向表中插入數據時被觸發；
  2. delete觸發器：從表中刪除數據時被觸發；
  3. update觸發器：修改表中數據時被觸發。

 

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

1. 通過數據庫中的相關表實現級聯更改

2. 防止惡意或者錯誤的insert、update和delete操作，並強制執行check約束定義的限制更為複雜的其他限制。

3. 評估數據修改前後表的狀態，並根據該差異才去措施。

DDL觸發器

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

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

 

登錄觸發器

登錄觸發器將為響應 LOGIN 事件而激發存儲過程。

與 SQL Server 實例建立用戶會話時將引發此事件。登錄觸發器將在登錄的身份驗證階段完成之後且用戶會話實際建立之前激發。

因此，來自觸發器內部且通常將到達用戶的所有消息（例如錯誤消息和來自 PRINT 語句的消息）會傳送到 SQL Server 錯誤日誌。

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

 

觸發器的種類

 

after觸發器（之後觸發）

在執行 INSERT、UPDATE、MERGE 或 DELETE 語句的操作之後執行 AFTER 觸發器。

如果違反了約束，則永遠不會執行 AFTER 觸發器；因此，這些觸發器不能用於任何可能防止違反約束的處理。

對於在 MERGE 語句中指定的每個 INSERT、UPDATE 或 DELETE 操作，將為每個 DML 操作觸發相應的觸發器。

 

instead of 觸發器

INSTEAD OF觸發器替代下列觸發語句的標準操作。

因此，觸發器可用於對一個或多個列執行錯誤或值檢查，然後在插入、更新或刪除行之前執行其他操作。

INSTEAD OF觸發器的主要優點是可以使不能更新的視圖支持更新。

INSTEAD OF觸發器的另一個優點是使您得以編寫這樣的邏輯代碼：在允許批處理的其他部分成功的同時拒絕批處理中的某些部分。

 

下表對 AFTER 觸發器和 INSTEAD OF 觸發器的功能進行了比較。

 

Transact-SQL DDL 觸發器

用於執行一個或多個 Transact-SQL 語句以響應服務器範圍或數據庫範圍事件的一種特殊類型的 Transact-SQL 存儲過程。

例如，如果執行某個語句（如 ALTER SERVER CONFIGURATION）或者使用 DROP TABLE 刪除某個表，則激發 DDL 觸發器。

 

CLR DDL 觸發器

CLR 觸發器將執行在託管代碼（在 .NET Framework 中創建並在 Transact-SQL 中上載的程序集的成員）中編寫的方法，而不用執行 SQL Server存儲過程。

 

 

使用T-SQL語句來增刪查改觸發器 

創建觸發器

--創建一個觸發器
--檢測到表數據有update時，更新CreateTime時間為當前時間
create trigger trigger_a_Students  --觸發器名稱
on a_Students for update
as
begin
update a_Students SET CreateTime=GETDATE()  --觸發器需要執行的sql
end

--測試
--執行一個修改語句
update a_Students
set Name='孫悟空1'
where Number=100001

--查看數據
select * from a_Students

 

刪除觸發器

--刪除觸發器
drop trigger trigger_a_Students   --觸發器名稱

 

查詢觸發器

--查看所有觸發器
select * from sysobjects where xtype='TR' 

-- 查看單個觸發器 
exec sp_helptext 'trigger_a_Students'   --觸發器名稱

 

修改觸發器

--修改一個觸發器
--檢測到表數據有update，insert時，更新CreateTime時間為當前時間
alter trigger trigger_a_Students  --觸發器名稱
on a_Students for update,insert
as
begin
update a_Students SET CreateTime=GETDATE()  --觸發器需要執行的sql
end

--測試
--執行一個插入語句
INSERT INTO a_Students(Number,Name,ClassId) 
VALUES('100016','李靖',3)

--查看數據
select * from a_Students

 

好了，我們就介紹到這裡吧，

拜拜，我們下次不見不散。

 

歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取

作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說


出處： //www.cnblogs.com/xiongze520/p/14606862.html


創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置註明作者和原文鏈接。
  

 

VirMach 便宜 VPS

QNews

QNews