這些年來，我發現許多開發者對於何時使用數據操縱語言(DML)觸發器與何時使用約束感到迷惑。許多時候，如果沒有正確應用這兩個對象，就會造成問題。本文將為您何時使用約束和DML觸發器提供一些啟示，以幫助你避免我遇到的糾正問題。
何為約束和DML觸發器?
約束是數據庫引擎用來約束一個表或一個表關系中的數據，以維持數據庫完整性的一個對象。這些約束包括CHECK、UNIQUE、PRIMARY KEY等。這裡提供在TSQL中定義約束的更多細節。
AFTER觸發器是一種特殊類型的TSQL代碼塊，當一個DML語句根據觸發器定義的表執行時，它得到執行。(在本文中我僅指這種觸發器。)
何時使用約束和DML觸發器
使用約束比使用觸發器更加有利(如果你可以使用約束的話)。你總是可以寫出一個觸發器，完成和一個約束相同的工作，但這樣做一般沒有什麼意義。
考慮使用一個外鍵約束和DML觸發器。使用外鍵約束的目的是為了確保允許進入一個表的一列或多列的值出現在一個單獨表的一列或多列中。你可以使用DML觸發器建立相同的功能。
列表A建立了SalesHistory和I_SalesProducts表，我將在例子中使用它們，並給它們加載一些數據。
以下為引用的內容：
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO DECLARE @i SMALLINT
SET @i = 1WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))SET @i = @i + 1
END
GOINSERT INTO l_SalesProducts(Product)
SELECT 'BigScreen'
UNION
SELECT 'Computer'
UNION
SELECT 'PoolTable'
現在我有了一些表和一些數據可供利用，讓我們在SaleHistory表中建立一個外鍵約束。
以下為引用的內容：
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistory FOREIGN KEY (Product) REFERENCES
l_SalesProducts(Product)
這個約束保證：在SaleHistory表中插入一個記錄時，插入到Product域的值也必須出現在I_SaleProducts表中。你可以用一個DML觸發器實現相同的功能。列表B在SaleHistory表中建立一個觸發器，它檢查在SaleHistory表中插入或更新的值是否在I_SaleProducts表中生成產品值。
以下為引用的內容：
CREATE TRIGGER tr_SalesHistory on SalesHistory
FOR INSERT, UPDATE
AS
BEGIN
IF UPDATE(Product) AND
@@ROWCOUNT <>
(
SELECT COUNT(*)
FROM INSERTED i
JOIN l_SalesProducts s ON i.Product = s.Product)
BEGIN
ROLLBACK TRANSACTION
SELECT 'Different Results, an error has occurred.'
--//THROW CUSTOM ERROR MESSAGE
END
END
以這種方式建立解決方案還涉及許多工作。工作時間並不是你在創立這種解決方案時遇到的唯一問題。因為約束以SQL Server功能的形式建立，它們傾向於擅長完成它們旨在完成的工作，而且與在觸發器中執行相同的功能相比，它們的表現也更好。
當執行一個DML操作時，系統首先啟動一個INSTEAD OF觸發器，然後外鍵約束進行檢查，接著再運行AFTER觸發器。這表示，在調用AFTER觸發器前，表中定義的任何外鍵約束必須得到滿足。
現在我們考慮你想要使用觸發器而非約束的情況。在需要將表中的當前值與當前輸入值進行比較，以不能滿足某些極限時，就可以首選使用觸發器。考慮下面的商業情形。
MyCompany.com最近實施了一項商業規則，如果產品的售價低於500美元，那麼一天內出售的BigScreen產品的數量就不能超過5件。此舉是為了保證折扣價格不會超出公司的期望。我們來了解滿足這種條件的其中一種方法。
首先，用戶界面可以查詢數據庫，了解輸入的產品數量是否超過上限。這種方法有效，但它需要在用戶界面中加入額外的商業邏輯，公司可能反對這樣做。
在這種情況下，可能很難實施一個約束，但如果要這樣做，仍然需要增加一些額外的編程邏輯。應用觸發器可以有效地解決這個問題，因為觸發器擅長比較以往和當前的值，並根據這些值做出決策。列表C建立了實現這個目標的觸發器。
以下為引用的內容：
CREATE TRIGGER tr_MaxProductSales ON SalesHistory
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @Product CHAR(150)
DECLARE @Today SMALLDATETIME
DECLARE @InsertedCount INT, @CurrentCount INT
DECLARE @MaxRecordCount TINYINT
DECLARE @MinSalePrice MONEYIF @@ROWCOUNT > 0
BEGIN
SET NOCOUNT ON
SET @Today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)SET @Product = 'BigScreen'
SET @MaxRecordCount = 5
SET @MinSalePrice = 500SELECT @InsertedCount = COUNT(*)
FROM INSERTED
WHERE
Product = @Product AND
SalePrice < @MinSalePrice AND
SaleDate >= @Today AND
SaleDate > @TodaySELECT @CurrentCount = COUNT(*)
FROM SalesHistory
WHERE
Product = @Product AND
SalePrice < @MinSalePrice AND
SaleDate >= @Today AND
SaleDate > @TodayIF @CurrentCount + @InsertedCount > @MaxRecordCount
BEGIN
PRINT 'Too many product sales for today.'
ROLLBACK TRANSACTION
END
在這個觸發器中，我執行檢查，看看表中更新或插入的BigSrceen產品數量，以及當天以低於500美元的售價出售的BigSrceen產品的數量是否超過5件。如果超出5件，觸發器將會撤銷交易，不會添加產品。
結論
約束與DML觸發器各有其優點。約束能夠維護數據庫表域和關系之間的數據庫完整性;而觸發器則擅長於比較以往和當前值，並根據這些數據做出決策。在我看來，一般來說，如有可能，使用約束更為有利，而將棘手的商業和審計邏輯留給觸發器來解決。
