前言

SQL Server觸發(fā)器在非常有爭議的主題。它們能以較低的成本提供便利，但經(jīng)常被開發(fā)人員、DBA誤用，導(dǎo)致性能瓶頸或維護(hù)性挑戰(zhàn)。

本文簡要回顧了觸發(fā)器，并深入討論了如何有效地使用觸發(fā)器，以及何時(shí)觸發(fā)器會使開發(fā)人員陷入難以逃脫的困境。

雖然本文中的所有演示都是在SQL Server中進(jìn)行的，但這里提供的建議是大多數(shù)數(shù)據(jù)庫通用的。觸發(fā)器帶來的挑戰(zhàn)在MySQL、PostgreSQL、MongoDB和許多其他應(yīng)用中也可以看到。

什么是觸發(fā)器

可以在數(shù)據(jù)庫或表上定義SQL Server觸發(fā)器，它允許代碼在發(fā)生特定操作時(shí)自動執(zhí)行。本文主要關(guān)注表上的DML觸發(fā)器，因?yàn)樗鼈兺贿^度使用。相反，數(shù)據(jù)庫的DDL觸發(fā)器通常更集中，對性能的危害更小。

觸發(fā)器是對表中數(shù)據(jù)更改時(shí)進(jìn)行計(jì)算的一組代碼。觸發(fā)器可以定義為在插入、更新、刪除或這些操作的任何組合上執(zhí)行。MERGE操作可以觸發(fā)語句中每個(gè)操作的觸發(fā)器。

觸發(fā)器可以定義為INSTEAD OF或AFTER。AFTER觸發(fā)器發(fā)生在數(shù)據(jù)寫入表之后，是一組獨(dú)立的操作，和寫入表的操作在同一事務(wù)執(zhí)行，但在寫入發(fā)生之后執(zhí)行。如果觸發(fā)器失敗，原始操作也會失敗。INSTEAD OF觸發(fā)器替換調(diào)用的寫操作。插入、更新或刪除操作永遠(yuǎn)不會發(fā)生，而是執(zhí)行觸發(fā)器的內(nèi)容。

觸發(fā)器允許在發(fā)生寫操作時(shí)執(zhí)行TSQL，而不管這些寫操作的來源是什么。它們通常用于在希望確保執(zhí)行寫操作時(shí)運(yùn)行關(guān)鍵操作，如日志記錄、驗(yàn)證或其他DML。這很方便，寫操作可以來自API、應(yīng)用程序代碼、發(fā)布腳本，或者內(nèi)部流程，觸發(fā)器無論如何都會觸發(fā)。

觸發(fā)器是什么樣的

用WideWorldImporters示例數(shù)據(jù)庫中的Sales.Orders 表舉例，假設(shè)需要記錄該表上的所有更新或刪除操作，以及有關(guān)更改發(fā)生的一些細(xì)節(jié)。這個(gè)操作可以通過修改代碼來完成，但是這樣做需要對表的代碼寫入中的每個(gè)位置進(jìn)行更改。通過觸發(fā)器解決這一問題，可以采取以下步驟:

1. 創(chuàng)建一個(gè)日志表來接受寫入的數(shù)據(jù)。下面的TSQL創(chuàng)建了一個(gè)簡單日志表，以及一些添加的數(shù)據(jù)點(diǎn)：

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);