SQL Server約束和DML觸發器的比較

　　這個約束保證：在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

END

END

　　在這個觸發器中，我執行檢查，看看錶中更新或插入的BigSrceen產品數量，以及當天以低於500美元的售價出售的BigSrceen產品的數量是否超過5件。如果超出5件，觸發器將會撤銷交易，不會新增產品。

　　結論

　　約束與DML觸發器各有其優點。約束能夠維護資料庫表域和關係之間的資料庫完整性;而觸發器則擅長於比較以往和當前值，並根據這些資料做出決策。在我看來，一般來說，如有可能，使用約束更為有利，而將棘手的商業和審計邏輯留給觸發器來解決。

這些年來，我發現許多開發者對於何時使用資料操縱語言(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)