SqlServer觸發器的建立與使用

學習中的苦與樂發表於2021-04-01
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

 

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

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

 

歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
出處： https://www.cnblogs.com/xiongze520/p/14606862.html
創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置註明作者和原文連結。  

 

相關文章