前言
SQL Server觸發器在非常有爭議的主題。它們能以較低的成本提供便利，但經常被開發人員、DBA誤用，導致性能瓶頸或維護性挑戰。
本文簡要回顧了觸發器，并深入討論了如何有效地使用觸發器，以及何時觸發器會使開發人員陷入難以逃脫的困境。
雖然本文中的所有演示都是在SQL Server中進行的，但這里提供的建議是大多數數據庫通用的。觸發器帶來的挑戰在MySQL、PostgreSQL、MongoDB和許多其他應用中也可以看到。
什么是觸發器
可以在數據庫或表上定義SQL Server觸發器，它允許代碼在發生特定操作時自動執行。本文主要關注表上的DML觸發器，因為它們往往被過度使用。相反，數據庫的DDL觸發器通常更集中，對性能的危害更小。
觸發器是對表中數據更改時進行計算的一組代碼。觸發器可以定義為在插入、更新、刪除或這些操作的任何組合上執行。MERGE操作可以觸發語句中每個操作的觸發器。
觸發器可以定義為INSTEAD OF或AFTER。AFTER觸發器發生在數據寫入表之后，是一組獨立的操作，和寫入表的操作在同一事務執行，但在寫入發生之后執行。如果觸發器失敗，原始操作也會失敗。INSTEAD OF觸發器替換調用的寫操作。插入、更新或刪除操作永遠不會發生，而是執行觸發器的內容。
觸發器允許在發生寫操作時執行TSQL，而不管這些寫操作的來源是什么。它們通常用于在希望確保執行寫操作時運行關鍵操作，如日志記錄、驗證或其他DML。這很方便，寫操作可以來自API、應用程序代碼、發布腳本，或者內部流程，觸發器無論如何都會觸發。
觸發器是什么樣的
用WideWorldImporters示例數據庫中的Sales.Orders 表舉例，假設需要記錄該表上的所有更新或刪除操作，以及有關更改發生的一些細節。這個操作可以通過修改代碼來完成，但是這樣做需要對表的代碼寫入中的每個位置進行更改。通過觸發器解決這一問題，可以采取以下步驟:
1. 創建一個日志表來接受寫入的數據。下面的TSQL創建了一個簡單日志表，以及一些添加的數據點：
CREATE TABLE Sales.Orders_log
( Orders_log_ID int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Sales_Orders_log PRIMARY KEY CLUSTERED,
OrderID int NOT NULL,
CustomerID_Old int NOT NULL,
CustomerID_New int NOT NULL,
SalespersonPersonID_Old int NOT NULL,
SalespersonPersonID_New int NOT NULL,
PickedByPersonID_Old int NULL,
PickedByPersonID_New int NULL,
ContactPersonID_Old int NOT NULL,
ContactPersonID_New int NOT NULL,
BackorderOrderID_Old int NULL,
BackorderOrderID_New int NULL,
OrderDate_Old date NOT NULL,
OrderDate_New date NOT NULL,
ExpectedDeliveryDate_Old date NOT NULL,
ExpectedDeliveryDate_New date NOT NULL,
CustomerPurchaseOrderNumber_Old nvarchar(20) NULL,
CustomerPurchaseOrderNumber_New nvarchar(20) NULL,
IsUndersupplyBackordered_Old bit NOT NULL,
IsUndersupplyBackordered_New bit NOT NULL,
Comments_Old nvarchar(max) NULL,
Comments_New nvarchar(max) NULL,
DeliveryInstructions_Old nvarchar(max) NULL,
DeliveryInstructions_New nvarchar(max) NULL,
InternalComments_Old nvarchar(max) NULL,
InternalComments_New nvarchar(max) NULL,
PickingCompletedWhen_Old datetime2(7) NULL,
PickingCompletedWhen_New datetime2(7) NULL,
LastEditedBy_Old int NOT NULL,
LastEditedBy_New int NOT NULL,
LastEditedWhen_Old datetime2(7) NOT NULL,
LastEditedWhen_New datetime2(7) NOT NULL,
ActionType VARCHAR(6) NOT NULL,
ActionTime DATETIME2(3) NOT NULL,
UserName VARCHAR(128) NULL);
該表記錄所有列的舊值和新值。這是非常全面的，我們可以簡單地記錄舊版本的行，并能夠通過將新版本和舊版本合并在一起來了解更改的過程。最后3列是新增的，提供了有關執行的操作類型(插入、更新或刪除)、時間和操作人。
2. 創建一個觸發器來記錄表的更改:
CREATE TRIGGER TR_Sales_Orders_Audit
ON Sales.Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Sales.Orders_log
(OrderID, CustomerID_Old, CustomerID_New,
SalespersonPersonID_Old, SalespersonPersonID_New,
PickedByPersonID_Old, PickedByPersonID_New,
ContactPersonID_Old, ContactPersonID_New,
BackorderOrderID_Old, BackorderOrderID_New,
OrderDate_Old, OrderDate_New, ExpectedDeliveryDate_Old,
ExpectedDeliveryDate_New,
CustomerPurchaseOrderNumber_Old,
CustomerPurchaseOrderNumber_New,
IsUndersupplyBackordered_Old,
IsUndersupplyBackordered_New,
Comments_Old, Comments_New,
DeliveryInstructions_Old, DeliveryInstructions_New,
InternalComments_Old, InternalComments_New,
PickingCompletedWhen_Old,
PickingCompletedWhen_New, LastEditedBy_Old,
LastEditedBy_New, LastEditedWhen_Old,
LastEditedWhen_New, ActionType, ActionTime, UserName)
SELECT
ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID,
Deleted.CustomerID AS CustomerID_Old,
Inserted.CustomerID AS CustomerID_New,
Deleted.SalespersonPersonID AS SalespersonPersonID_Old,
Inserted.SalespersonPersonID AS SalespersonPersonID_New,
Deleted.PickedByPersonID AS PickedByPersonID_Old,
Inserted.PickedByPersonID AS PickedByPersonID_New,
Deleted.ContactPersonID AS ContactPersonID_Old,
Inserted.ContactPersonID AS ContactPersonID_New,
Deleted.BackorderOrderID AS BackorderOrderID_Old,
Inserted.BackorderOrderID AS BackorderOrderID_New,
Deleted.OrderDate AS OrderDate_Old,
Inserted.OrderDate AS OrderDate_New,
Deleted.ExpectedDeliveryDate
AS ExpectedDeliveryDate_Old,
Inserted.ExpectedDeliveryDate
AS ExpectedDeliveryDate_New,
Deleted.CustomerPurchaseOrderNumber
AS CustomerPurchaseOrderNumber_Old,
Inserted.CustomerPurchaseOrderNumber
AS CustomerPurchaseOrderNumber_New,
Deleted.IsUndersupplyBackordered
AS IsUndersupplyBackordered_Old,
Inserted.IsUndersupplyBackordered
AS IsUndersupplyBackordered_New,
Deleted.Comments AS Comments_Old,
Inserted.Comments AS Comments_New,
Deleted.DeliveryInstructions
AS DeliveryInstructions_Old,
Inserted.DeliveryInstructions
AS DeliveryInstructions_New,
Deleted.InternalComments AS InternalComments_Old,
Inserted.InternalComments AS InternalComments_New,
Deleted.PickingCompletedWhen
AS PickingCompletedWhen_Old,
Inserted.PickingCompletedWhen
AS PickingCompletedWhen_New,
Deleted.LastEditedBy AS LastEditedBy_Old,
Inserted.LastEditedBy AS LastEditedBy_New,
Deleted.LastEditedWhen AS LastEditedWhen_Old,
Inserted.LastEditedWhen AS LastEditedWhen_New,
CASE
WHEN Inserted.OrderID IS NULL THEN 'DELETE'
WHEN Deleted.OrderID IS NULL THEN 'INSERT'
ELSE 'UPDATE'
END AS ActionType,
SYSUTCDATETIME() ActionTime,
SUSER_SNAME() AS UserName
FROM Inserted
FULL JOIN Deleted
ON Inserted.OrderID = Deleted.OrderID;
END
該觸發器的唯一功能是將數據插入到日志表中，每行數據對應一個給定的寫操作。它很簡單，隨著時間的推移易于記錄和維護，表也會發生變化。如果需要跟蹤其他詳細信息，可以添加其他列，如數據庫名稱、服務器名稱、受影響列的行數或調用的應用程序。
3.最后一步是測試和驗證日志表是否正確。
以下是添加觸發器后對表進行更新的測試:
UPDATE Orders
SET InternalComments = 'Item is no longer backordered',
BackorderOrderID = NULL,
IsUndersupplyBackordered = 0,
LastEditedBy = 1,
LastEditedWhen = SYSUTCDATETIME()
FROM sales.Orders
WHERE Orders.OrderID = 10;
結果如下：
點擊并拖拽以移動
上面省略了一些列，但是我們可以快速確認已經觸發了更改，包括日志表末尾新增的列。
INSERT和DELETE
前面的示例中，進行插入和刪除操作后，讀取日志表中使用的數據。這種特殊的表可以作為任何相關寫操作的一部分。INSERT將包含被插入操作觸發，DELETE將被刪除操作觸發，UPDATE包含被插入和刪除操作觸發。
對于INSERT和UPDATE，將包含表中每個列新值的快照。對于DELETE和UPDATE操作，將包含寫操作之前表中每個列舊值的快照。
觸發器什么時候最有用
DML觸發器的最佳使用是簡短、簡單且易于維護的寫操作，這些操作在很大程度上獨立于應用程序業務邏輯。
- 觸發器的一些重要用途包括:
- 記錄對歷史表的更改
- 審計用戶及其對敏感表的操作。
- 向表中添加應用程序可能無法使用的額外值(由于安全限制或其他限制)，例如:
- 簡單的驗證。
關鍵是讓觸發器代碼保持足夠的緊湊，從而便于維護。當觸發器增長到成千上萬行時，它們就成了開發人員不敢去打擾的黑盒。結果，更多的代碼被添加進來，但是舊的代碼很少被檢查。即使有了文檔，這也很難維護。
為了讓觸發器有效地發揮作用，應該將它們編寫為基于設置的。如果存儲過程必須在觸發器中使用，則確保它們在需要時使用表值參數，以便可以基于集的方式移動數據。下面是一個觸發器的示例，該觸發器遍歷id，以便使用結果順序id執行示例存儲過程:
CREATE TRIGGER TR_Sales_Orders_Process
ON Sales.Orders
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count INT;
SELECT @count = COUNT(*) FROM inserted;
DECLARE @min_id INT;
SELECT @min_id = MIN(OrderID) FROM inserted;
DECLARE @current_id INT = @min_id;
WHILE @current_id @current_id + @count
BEGIN
EXEC dbo.process_order_fulfillment
@OrderID = @current_id;
SELECT @current_id = @current_id + 1;
END
END
雖然相對簡單，但當一次插入多行時對 Sales.Orders的INSERT操作的性能將受到影響，因為SQL Server在執行process_order_fulfillment存儲過程時將被迫逐個執行。一個簡單的修復方法是重寫存儲過程，并將一組Order id傳遞到存儲過程中，而不是一次一個地這樣做:
CREATE TYPE dbo.udt_OrderID_List AS TABLE(
OrderID INT NOT NULL,
PRIMARY KEY CLUSTERED
( OrderID ASC));
GO
CREATE TRIGGER TR_Sales_Orders_Process
ON Sales.Orders
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderID_List dbo.udt_OrderID_List;
EXEC dbo.process_order_fulfillment @OrderIDs = @OrderID_List;
END
更改的結果是將完整的id集合從觸發器傳遞到存儲過程并進行處理。只要存儲過程以基于集合的方式管理這些數據，就可以避免重復執行，也就是說，避免在觸發器內使用存儲過程有很大的價值，因為它們添加了額外的封裝層，進一步隱藏了在數據寫入表時執行的TSQL。它們應該被認為是最后的手段，只有當可以在應用程序的許多地方多次重寫TSQL時才使用。
什么時候觸發器是危險的
架構師和開發人員面臨的最大挑戰之一是確保觸發器只在需要時使用，而不允許它們成為一刀切的解決方案。向觸發器添加TSQL通常被認為比向應用程序添加代碼更快、更容易，但隨著時間的推移，這樣做的成本會隨著每添加一行代碼而增加。
觸發器在以下情況下會變得危險:
- 保持盡可能少的觸發以減少復雜性。
- 觸發代碼變得復雜。如果更新表中的一行導致要執行數千行添加的觸發器代碼，那么開發人員就很難完全理解數據寫入表時會發生什么。更糟糕的是，當出現問題時，故障排除非常具有挑戰性。
- 觸發器跨服務器。這將網絡操作引入到觸發器中，可能導致在出現連接問題時寫入速度變慢或失敗。如果目標數據庫是要維護的對象，那么即使是跨數據庫觸發器也會有問題。
- 觸發器調用觸發器。觸發器中最令人痛苦的是，當插入一行時，寫操作會導致75個表中有100個觸發器要執行。在編寫觸發器代碼時，確保觸發器可以執行所有必要的邏輯，而不會觸發更多觸發器。額外的觸發通常是不必要的。
- 遞歸觸發器被設置為ON。這是一個默認設置為off的數據庫級別設置。打開時，它允許觸發器的內容調用相同的觸發器。遞歸觸發器會極大地損害性能，調試時也會非?；靵y。通常，當一個觸發器中的DML作為操作的一部分觸發其他觸發器時，使用遞歸觸發器。
- 函數、存儲過程或視圖都在觸發器中。在觸發器中封裝更多的業務邏輯會使它們變得更復雜，并給人一種觸發器代碼短小簡單的錯誤印象，而實際上并非如此。盡可能避免在觸發器中使用存儲過程和函數。
- 迭代發生。循環和游標本質上是逐行操作的，可能會導致對1000行的操作一次觸發1000次，這極大地損害了查詢性能。
這是一個很長的列表，但通常可以總結為短而簡單的觸發器會表現得更好，并避免上面的大多數陷阱。如果使用觸發器來維護復雜的業務邏輯，那么隨著時間的推移，越來越多的業務邏輯將被添加進來，并且不可避免地將違反上述最佳實踐。
重要的是要注意，為了維護原子的、事務，受觸發器影響的任何對象都將保持事務處于打開狀態，直到該觸發器完成。這意味著長觸發器不僅會使事務持續時間更長，而且還會持有鎖并導致持續時間更長。因此，在測試觸發器時，在為現有觸發器創建或添加額外邏輯時，應該了解它們對鎖、阻塞和等待的影響。
如何改善觸發器
有很多方法可以使觸發器更易于維護、更容易理解和性能更高。以下是一些關于如何有效管理觸發器和避免落入陷阱的建議。
觸發器本身應該有良好的文檔記錄:
- 這個觸發器為什么存在?
- 它能做什么?
- 它是如何工作的?
- 對于觸發器的工作方式是否有任何例外或警告?
此外，如果觸發器中的TSQL難以理解，那么可以添加內聯注釋，以幫助第一次查看它的開發人員。
下面是觸發器文檔的樣例:
/* 12/29/2020 EHP
This trigger logs all changes to the table to the Orders_log
table that occur for non-internal customers.
CustomerID = -1 signifies an internal/test customer and
these are not audited.
*/
CREATE TRIGGER TR_Sales_Orders_Audit
ON Sales.Orders
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Sales.Orders_log
(OrderID, CustomerID_Old, CustomerID_New,
SalespersonPersonID_Old, SalespersonPersonID_New,
PickedByPersonID_Old, PickedByPersonID_New,
ContactPersonID_Old, ContactPersonID_New,
BackorderOrderID_Old, BackorderOrderID_New,
OrderDate_Old, OrderDate_New,
ExpectedDeliveryDate_Old,
ExpectedDeliveryDate_New,
CustomerPurchaseOrderNumber_Old,
CustomerPurchaseOrderNumber_New,
IsUndersupplyBackordered_Old,
IsUndersupplyBackordered_New,
Comments_Old, Comments_New,
DeliveryInstructions_Old, DeliveryInstructions_New,
nternalComments_Old, InternalComments_New,
PickingCompletedWhen_Old, PickingCompletedWhen_New,
LastEditedBy_Old, LastEditedBy_New,
LastEditedWhen_Old, LastEditedWhen_New,
ActionType, ActionTime, UserName)
SELECT
ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID,
-- The OrderID can never change.
--This ensures we get the ID correctly,
--regardless of operation type.
Deleted.CustomerID AS CustomerID_Old,
Inserted.CustomerID AS CustomerID_New,
Deleted.SalespersonPersonID AS SalespersonPersonID_Old,
Inserted.SalespersonPersonID AS SalespersonPersonID_New,
Deleted.PickedByPersonID AS PickedByPersonID_Old,
Inserted.PickedByPersonID AS PickedByPersonID_New,
Deleted.ContactPersonID AS ContactPersonID_Old,
Inserted.ContactPersonID AS ContactPersonID_New,
Deleted.BackorderOrderID AS BackorderOrderID_Old,
Inserted.BackorderOrderID AS BackorderOrderID_New,
Deleted.OrderDate AS OrderDate_Old,
Inserted.OrderDate AS OrderDate_New,
Deleted.ExpectedDeliveryDate AS ExpectedDeliveryDate_Old,
Inserted.ExpectedDeliveryDate AS ExpectedDeliveryDate_New,
Deleted.CustomerPurchaseOrderNumber
AS CustomerPurchaseOrderNumber_Old,
Inserted.CustomerPurchaseOrderNumber
AS CustomerPurchaseOrderNumber_New,
Deleted.IsUndersupplyBackordered
AS IsUndersupplyBackordered_Old,
Inserted.IsUndersupplyBackordered
AS IsUndersupplyBackordered_New,
Deleted.Comments AS Comments_Old,
Inserted.Comments AS Comments_New,
Deleted.DeliveryInstructions
AS DeliveryInstructions_Old,
Inserted.DeliveryInstructions
AS DeliveryInstructions_New,
Deleted.InternalComments AS InternalComments_Old,
Inserted.InternalComments AS InternalComments_New,
Deleted.PickingCompletedWhen AS PickingCompletedWhen_Old,
Inserted.PickingCompletedWhen
AS PickingCompletedWhen_New,
Deleted.LastEditedBy AS LastEditedBy_Old,
Inserted.LastEditedBy AS LastEditedBy_New,
Deleted.LastEditedWhen AS LastEditedWhen_Old,
Inserted.LastEditedWhen AS LastEditedWhen_New,
CASE -- Determine the operation type based on whether
--Inserted exists, Deleted exists, or both exist.
WHEN Inserted.OrderID IS NULL THEN 'DELETE'
WHEN Deleted.OrderID IS NULL THEN 'INSERT'
ELSE 'UPDATE'
END AS ActionType,
SYSUTCDATETIME() ActionTime,
SUSER_SNAME() AS UserName
FROM Inserted
FULL JOIN Deleted
ON Inserted.OrderID = Deleted.OrderID
WHERE Inserted.CustomerID > -1
-- -1 indicates an internal/non-production
--customer that should not be audited.
OR Deleted.CustomerID > -1;
-- -1 indicates an internal/non-production
--customer that should not be audited.
END
請注意，該文檔并不全面，但包含了一個簡短的頭，并解釋了觸發器內的一些TSQL關鍵部分:
- 排除CustomerID = -1的情況。這一點對于不知道的人來說是不明顯的，所以這是一個很好的注釋。
- ActionType的CASE語句用于什么。
- 為什么在插入和刪除之間的OrderID列上使用ISNULL。
使用IF UPDATE
在觸發器中，UPDATE提供了判斷是否將數據寫入給定列的能力。這可以允許觸發器檢查列在執行操作之前是否發生了更改。下面是該語法的示例:
CREATE TRIGGER TR_Sales_Orders_Log_BackorderID_Change
ON Sales.Orders
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(BackorderOrderID)
BEGIN
UPDATE OrderBackorderLog
SET BackorderOrderID = Inserted.BackorderOrderID,
PreviousBackorderOrderID = Deleted.BackorderOrderID
FROM dbo.OrderBackorderLog
INNER JOIN Inserted
ON Inserted.OrderID = OrderBackorderLog.OrderID
END
END
通過首先檢查BackorderID是否被更新，觸發器可以在不需要時繞過后續操作。這是一種提高性能的好方法，它允許觸發器根據所需列的更新值完全跳過代碼。
COLUMNS_UPDATED指示表中的哪些列作為寫操作的一部分進行了更新，可以在觸發器中使用它來快速確定指定的列是否受到插入或更新操作的影響。雖然有文檔記錄，但它使用起來很復雜，很難進行文檔記錄。我通常不建議使用它，因為它幾乎肯定會使不熟悉它的開發人員感到困惑。
請注意，對于UPDATE或COLUMNS_UPDATED，列是否更改并不重要。對列進行寫操作，即使值沒有改變，對于UPDATE操作仍然返回1，對于COLUMNS_UPDATED操作仍然返回1。它們只跟蹤指定的列是否是寫操作的目標，而不跟蹤值本身是否改變。
每個操作一個觸發器
讓觸發代碼盡可能的簡單。數據庫表的觸發器數量增長會大大增加表的復雜性，理解其操作變得更加困難。。
例如，考慮以下表觸發器定義方式:
CREATE TRIGGER TR_Sales_Orders_I
ON Sales.Orders
AFTER INSERT
CREATE TRIGGER TR_Sales_Orders_IU
ON Sales.Orders
AFTER INSERT, UPDATE
CREATE TRIGGER TR_Sales_Orders_UD
ON Sales.Orders
AFTER UPDATE, DELETE
CREATE TRIGGER TR_Sales_Orders_UID
ON Sales.Orders
AFTER UPDATE, INSERT, DELETE
CREATE TRIGGER TR_Sales_Orders_ID
ON Sales.Orders
AFTER INSERT, DELETE
當插入一行時會發生什么?觸發器的觸發順序是什么?這些問題的答案需要研究。維護更少的觸發器是一個簡單的解決方案，并且消除了對給定表中如何發生寫操作的猜測。作為參考，可以使用系統存儲過程sp_settriggerorder修改觸發器順序，不過這只適用于AFTER觸發器。
再簡單一點
觸發器的最佳實踐是操作簡單，執行迅速，并且不會因為它們的執行而觸發更多的觸發器。觸發器的復雜程度并沒有明確的規則，但有一條簡單的指導原則是，理想的觸發器應該足夠簡單，如果必須將觸發器中包含的邏輯移到其他地方，那么遷移的代價不會高得令人望而卻步。也就是說，如果觸發器中的業務邏輯非常復雜，以至于移動它的成本太高而無法考慮，那么這些觸發器很可能變得過于復雜。
使用我們前面的示例，考慮一下更改審計的觸發器。這可以很容易地從觸發器轉移到存儲過程或代碼中，而這樣做的工作量并不大。觸發器中記錄日志的方便性使它值得一做，但與此同時，我們應該知道開發人員將TSQL從觸發器遷移到另一個位置需要多少小時。
時間的計算可以看作是觸發器的可維護性成本的一部分。也就是說，如果有必要，為擺脫觸發機制而必須付出的代價。這聽起來可能很抽象，但平臺之間的數據庫遷移是很常見的。在SQL Server中執行良好的一組觸發器在Oracle或PostgreSQL中可能并不有效。
優化表變量
有時，一個觸發器中需要臨時表，以允許對數據進行多次更新。臨時表存儲在tempdb中，并且受到tempdb數據庫大小、速度和性能約束的影響。
對于經常訪問的臨時表，優化表變量是在內存中(而不是在tempdb中)維護臨時數據的好方法。
下面的TSQL為內存優化數據配置了一個數據庫(如果需要):
ALTER DATABASE WideWorldImporters
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ALTER DATABASE WideWorldImporters ADD FILEGROUP WWI_InMemory_Data
CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE WideWorldImporters ADD FILE
(NAME='WideWorldImporters_IMOLTP_File_1',
FILENAME='C:\SQLData\WideWorldImporters_IMOLTP_File_1.mem')
TO FILEGROUP WWI_InMemory_Data;
一旦配置完成，就可以創建一個內存優化的表類型:
CREATE TYPE dbo.SalesOrderMetadata
AS TABLE
( OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
CustomerID INT NOT NULL,
SalespersonPersonID INT NOT NULL,
ContactPersonID INT NOT NULL,
INDEX IX_SalesOrderMetadata_CustomerID NONCLUSTERED HASH
(CustomerID) WITH (BUCKET_COUNT = 1000))
WITH (MEMORY_OPTIMIZED = ON);
這個TSQL創建了演示的觸發器所需要的表:
CREATE TABLE dbo.OrderAdjustmentLog
( OrderAdjustmentLog_ID int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_OrderAdjustmentLog PRIMARY KEY CLUSTERED,
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
SalespersonPersonID INT NOT NULL,
ContactPersonID INT NOT NULL,
CreateTimeUTC DATETIME2(3) NOT NULL);
下面是一個使用內存優化表的觸發器演示:
CREATE TRIGGER TR_Sales_Orders_Mem_Test
ON Sales.Orders
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderData dbo.SalesOrderMetadata;
INSERT INTO @OrderData
(OrderID, CustomerID, SalespersonPersonID,
ContactPersonID)
SELECT
OrderID,
CustomerID,
SalespersonPersonID,
ContactPersonID
FROM Inserted;
DELETE OrderData
FROM @OrderData OrderData
INNER JOIN sales.Customers
ON Customers.CustomerID = OrderData.CustomerID
WHERE Customers.IsOnCreditHold = 0;
UPDATE OrderData
SET ContactPersonID = 1
FROM @OrderData OrderData
WHERE OrderData.ContactPersonID IS NULL;
INSERT INTO dbo.OrderAdjustmentLog
(OrderID, CustomerID, SalespersonPersonID,
ContactPersonID, CreateTimeUTC)
SELECT
OrderData.OrderID,
OrderData.CustomerID,
OrderData.SalespersonPersonID,
OrderData.ContactPersonID,
SYSUTCDATETIME()
FROM @OrderData OrderData;
END
觸發器內需要的操作越多，節省的時間就越多，因為內存優化的表變量不需要IO來讀/寫。
一旦讀取了來自所插入表的初始數據，觸發器的其余部分就可以不處理tempdb，從而減少使用標準表變量或臨時表的開銷。
下面的代碼設置了一些測試數據，并運行一個更新來演示上述代碼的結果:
UPDATE Customers
SET IsOnCreditHold = 1
FROM Sales.Customers
WHERE Customers.CustomerID = 832;
UPDATE Orders
SET SalespersonPersonID = 2
FROM sales.Orders
WHERE CustomerID = 832;
一旦執行，OrderAdjustmentLog表的內容可以被驗證:
結果是意料之中的。通過減少對標準存儲的依賴并將中間表移動到內存中，內存優化表提供了一種大大提高觸發速度的方法。這僅限于對臨時對象有大量調用的場景，但在存儲過程或其他過程性TSQL中也很有用。
替代觸發器
像所有的工具一樣，觸發器也可能被濫用，并成為混亂、性能瓶頸和可維護性噩夢的根源。有許多比觸發器更可取的替代方案，在實現(或添加到現有的)觸發器之前應該考慮它們。
Temporal tables
Temporal tables是在SQL Server 2016中引入的，它提供了一種向表添加版本控制的簡單方法，無需構建自己的數據結構和ETL。這種記錄對應用程序是不可見的，并提供了符合ANSI標準的完整版本支持，使之成為一種簡單的方法來解決保存舊版本數據的問題。
Check約束
對于簡單的數據驗證，Check約束可以提供所需的內容，而不需要函數、存儲過程或觸發器。在列上定義Check約束，并在創建數據時自動驗證數據。
下面是一個Check約束的示例:
ALTER TABLE Sales.Invoices WITH CHECK ADD CONSTRAINT
CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON
CHECK ([ReturnedDeliveryData] IS NULL OR
ISJSON([ReturnedDeliveryData])>(0))
這段代碼檢查一個列是否是有效的JSON。如果是，則執行正常進行。如果不是，那么SQL Server將拋出一個錯誤，寫操作將失敗。Check約束可以檢查列和值的任何組合，因此可以管理簡單或復雜的驗證任務。
創建Check約束的成本不高，而且易于維護。它們也更容易記錄和理解，因為Check約束的范圍僅限于驗證傳入數據和確保數據完整性，而觸發器實際上可以做任何可以想象的事情!
唯一約束
如果一個列需要唯一的值，并且不是表上的主鍵，那么唯一約束是完成該任務的一種簡單而有效的方法。唯一約束是索引和唯一性的組合。為了有效地驗證唯一性，索引是必需的。
下面是一個唯一約束的例子:
ALTER TABLE Warehouse.Colors ADD CONSTRAINT
UQ_Warehouse_Colors_ColorName UNIQUE NONCLUSTERED (ColorName ASC);
每當一行被插入到 Warehouse.Colors表中，將檢查ColorName的唯一性。如果寫操作碰巧導致了重復的顏色，那么語句將失敗，數據將不會被更改。為此目的構建了唯一約束，這是在列上強制唯一性的最簡單方法。
內置的解決方案將更高效、更容易維護和更容易記錄。任何看到唯一約束的開發人員都將立即理解它的作用，而不需要深入挖掘TSQL來弄清事情是如何工作的，這種簡單性使其成為理想的解決方案。
外鍵約束
與Check約束和唯一約束一樣，外鍵約束是在寫入數據之前驗證數據完整性的另一種方式。外鍵將一一表中的列鏈接到另一張表。當數據插入到目標表時，它的值將根據引用的表進行檢查。如果該值存在，則寫操作正常進行。如果不是，則拋出錯誤，語句失敗。
這是一個簡單的外鍵例子:
ALTER TABLE Sales.Orders WITH CHECK ADD CONSTRAINT
FK_Sales_Orders_CustomerID_Sales_Customers FOREIGN KEY (CustomerID)
REFERENCES Sales.Customers (CustomerID);
當數據寫入Sales.Orders時，CustomerID列將根據Sales.Customers中的CustomerID列進行檢查。
與唯一約束類似，外鍵只有一個目的:驗證寫入一個表的數據是否存在于另一個表中。它易于文檔化，易于理解，實現效率高。
觸發器不是執行這些驗證檢查的正確位置，與使用外鍵相比，它是效率較低的解決方案。
存儲過程
在觸發器中實現的邏輯通?？梢院苋菀椎匾苿拥酱鎯^程中。這消除了大量觸發代碼可能導致的復雜性，同時允許開發人員更好的維護。存儲過程可以自由地構造操作，以確保盡可能多的原子性。
實現觸發器的基本原則之一是確保一組操作與寫操作一致。所有成功或失敗都是作為原子事務的一部分。應用程序并不總是需要這種級別的原子性。如果有必要，可以在存儲過程中使用適當的隔離級別或表鎖定來保證事務的完整性。
雖然SQL Server(和大多數RDBMS)提供了ACID保證事務將是原子的、一致的、隔離的和持久的，但我們自己代碼中的事務可能需要也可能不需要遵循相同的規則。現實世界的應用程序對數據完整性的需求各不相同。
存儲過程允許自定義代碼，以實現應用程序所需的數據完整性，確保性能和計算資源不會浪費在不需要的數據完整性上。
例如，一個允許用戶發布照片的社交媒體應用程序不太可能需要它的事務完全原子化和一致。如果我的照片出現在你之前或之后一秒，沒人會在意。同樣，如果你在我編輯照片的時候評論我的照片，時間對使用這些數據的人來說可能并不重要。另一方面，一個管理貨幣交易的銀行應用程序需要確保交易是謹慎執行的，這樣就不會出現資金丟失或數字報告錯誤的情況。如果我有一個銀行賬戶，里面有20美元，我取出20美元的同時，其他人也取出了20美元，我們不可能都成功。我們中的一個先得到20美元，另一個遇到關于0美元余額的適當錯誤消息。
函數
函數提供了一種簡單的方法，可以將重要的邏輯封裝到一個單獨的位置。在50個表插入中重用的單個函數比50個觸發器(每個表一個觸發器)執行相同邏輯要容易得多。
考慮以下函數:
CREATE FUNCTION Website.CalculateCustomerPrice
(@CustomerID INT, @StockItemID INT, @PricingDate DATE)
RETURNS DECIMAL(18,2)
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @CalculatedPrice decimal(18,2);
DECLARE @UnitPrice decimal(18,2);
DECLARE @LowestUnitPrice decimal(18,2);
DECLARE @HighestDiscountAmount decimal(18,2);
DECLARE @HighestDiscountPercentage decimal(18,3);
DECLARE @BuyingGroupID int;
DECLARE @CustomerCategoryID int;
DECLARE @DiscountedUnitPrice decimal(18,2);
SELECT @BuyingGroupID = BuyingGroupID,
@CustomerCategoryID = CustomerCategoryID
FROM Sales.Customers
WHERE CustomerID = @CustomerID;
SELECT @UnitPrice = si.UnitPrice
FROM Warehouse.StockItems AS si
WHERE si.StockItemID = @StockItemID;
SET @CalculatedPrice = @UnitPrice;
SET @LowestUnitPrice = (
SELECT MIN(sd.UnitPrice)
FROM Sales.SpecialDeals AS sd
WHERE ((sd.StockItemID = @StockItemID)
OR (sd.StockItemID IS NULL))
AND ((sd.CustomerID = @CustomerID)
OR (sd.CustomerID IS NULL))
AND ((sd.BuyingGroupID = @BuyingGroupID)
OR (sd.BuyingGroupID IS NULL))
AND ((sd.CustomerCategoryID = @CustomerCategoryID)
OR (sd.CustomerCategoryID IS NULL))
AND ((sd.StockGroupID IS NULL) OR EXISTS (SELECT 1
FROM Warehouse.StockItemStockGroups AS sisg
WHERE sisg.StockItemID = @StockItemID
AND sisg.StockGroupID = sd.StockGroupID))
AND sd.UnitPrice IS NOT NULL
AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
IF @LowestUnitPrice IS NOT NULL AND @LowestUnitPrice @UnitPrice
BEGIN
SET @CalculatedPrice = @LowestUnitPrice;
END;
SET @HighestDiscountAmount = (
SELECT MAX(sd.DiscountAmount)
FROM Sales.SpecialDeals AS sd
WHERE ((sd.StockItemID = @StockItemID)
OR (sd.StockItemID IS NULL))
AND ((sd.CustomerID = @CustomerID)
OR (sd.CustomerID IS NULL))
AND ((sd.BuyingGroupID = @BuyingGroupID)
OR (sd.BuyingGroupID IS NULL))
AND ((sd.CustomerCategoryID = @CustomerCategoryID)
OR (sd.CustomerCategoryID IS NULL))
AND ((sd.StockGroupID IS NULL) OR EXISTS
(SELECT 1 FROM Warehouse.StockItemStockGroups AS sisg
WHERE sisg.StockItemID = @StockItemID
AND sisg.StockGroupID = sd.StockGroupID))
AND sd.DiscountAmount IS NOT NULL
AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
IF @HighestDiscountAmount IS NOT NULL AND (
@UnitPrice - @HighestDiscountAmount) @CalculatedPrice
BEGIN
SET @CalculatedPrice = @UnitPrice - @HighestDiscountAmount;
END;
SET @HighestDiscountPercentage = (
SELECT MAX(sd.DiscountPercentage)
FROM Sales.SpecialDeals AS sd
WHERE ((sd.StockItemID = @StockItemID)
OR (sd.StockItemID IS NULL))
AND ((sd.CustomerID = @CustomerID)
OR (sd.CustomerID IS NULL))
AND ((sd.BuyingGroupID = @BuyingGroupID)
OR (sd.BuyingGroupID IS NULL))
AND ((sd.CustomerCategoryID = @CustomerCategoryID)
OR (sd.CustomerCategoryID IS NULL))
AND ((sd.StockGroupID IS NULL) OR EXISTS
(SELECT 1 FROM Warehouse.StockItemStockGroups AS sisg
WHERE sisg.StockItemID = @StockItemID
AND sisg.StockGroupID = sd.StockGroupID))
AND sd.DiscountPercentage IS NOT NULL
AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
IF @HighestDiscountPercentage IS NOT NULL
BEGIN
SET @DiscountedUnitPrice = ROUND(@UnitPrice *
@HighestDiscountPercentage / 100.0, 2);
IF @DiscountedUnitPrice @CalculatedPrice
SET @CalculatedPrice = @DiscountedUnitPrice;
END;
RETURN @CalculatedPrice;
END;
就復雜性而言，這絕對是一頭猛獸。雖然它接受標量參數來確定計算價格，但它執行的操作非常大，甚至包括對Warehouse.StockItemStockGroups, Warehouse.StockItems和Sales.Customers的額外讀取。如果這是一個經常針對單行數據使用的關鍵計算，那么將其封裝在一個函數中是獲得所需計算的一種簡單方法，而不會增加觸發器的復雜性。小心使用函數，并確保使用大型數據集進行測試。簡單的標量函數通?？梢院芎玫厣炜s性較大的數據，但更復雜的函數可能性能較差。
編碼
當從應用程序修改表中的數據時，還可以在寫入數據之前執行額外的數據操作或驗證。這通常代價低廉，性能很好，并有助于減少失控觸發器對數據庫的負面影響。
將代碼放入觸發器的常見理由是，這樣做可以避免修改代碼、推送構建，否則會導致更改應用程序。這與在數據庫中進行更改相關的任何風險直接相反。這通常是應用程序開發人員和數據庫開發人員之間關于誰將負責新代碼的討論。
這是一個粗略的指導方針，但有助于在代碼添加到應用程序或觸發器之后測量可維護性和風險。
計算列
其他列發生更改時，計算列可以包括通過各種各樣的算術運算和函數進行計算，得到結果。它們可以包含在索引中，也可以包含在唯一的約束中，甚至主鍵中。
當任何底層值發生變化時，SQL Server會自動維護計算的列。注意，每個計算出來的列最終都是由表中其他列的值決定的。
這是使用觸發器來維護指定列值的一種很好的替代方法。計算列是高效的、自動的，并且不需要維護。它們只是簡單地工作，甚至允許將復雜的計算直接集成到一個表中，而在應用程序或SQL Server中不需要額外的代碼。
使用SQL Server觸發器
觸發器在SQL Server中是一個有用的特性，但像所有工具一樣，它也可能被誤用或濫用。在決定是否使用觸發器時，一定要考慮觸發器的目的。
如果一個觸發器被用來將簡短的事務數據寫入日志表，那么它很可能是一個很好的觸發器。如果觸發器被用來強制執行復雜的業務規則，那么很可能需要重新考慮處理這類操作的最佳方式。
有很多工具可以作為觸發器的可行替代品，比如檢查約束、計算列等，解決問題的方法并不短缺。數據庫體系結構的成功在于為工作選擇正確的工具。
原文鏈接：https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-triggers-good-scary/
總結
到此這篇關于關于SQL Server觸發器的文章就介紹到這了,更多相關SQL Server觸發器內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 利用SQL Server觸發器實現表的歷史修改痕跡記錄
- SQLSERVER對加密的存儲過程、視圖、觸發器進行解密(推薦)
- SQL Server 使用觸發器（trigger）發送電子郵件步驟詳解
- SQL Server實現用觸發器捕獲DML操作的會話信息【實例】
- SQL Server：觸發器實例詳解
- SqlServer觸發器詳解
- SqlServer實現類似Oracle的before觸發器示例
- SQL SERVER中各類觸發器的完整語法及參數說明
- SQL SERVER 觸發器介紹
- SQL Server誤區30日談 第4天 DDL觸發器就是INSTEAD OF觸發器
- SQL Server 觸發器詳情
