這些年來，我發(fā)現(xiàn)許多開發(fā)者對于何時使用數(shù)據(jù)操縱語言(DML)觸發(fā)器與何時使用約束感到迷惑。許多時候，如果沒有正確應(yīng)用這兩個對象，就會造成問題。本文將為您何時使用約束和DML觸發(fā)器提供一些啟示，以幫助你避免我遇到的糾正問題。
何為約束和DML觸發(fā)器?
約束是數(shù)據(jù)庫引擎用來約束一個表或一個表關(guān)系中的數(shù)據(jù)，以維持數(shù)據(jù)庫完整性的一個對象。這些約束包括CHECK、UNIQUE、PRIMARY KEY等。這里提供在TSQL中定義約束的更多細節(jié)。
AFTER觸發(fā)器是一種特殊類型的TSQL代碼塊，當一個DML語句根據(jù)觸發(fā)器定義的表執(zhí)行時，它得到執(zhí)行。(在本文中我僅指這種觸發(fā)器。)
何時使用約束和DML觸發(fā)器
使用約束比使用觸發(fā)器更加有利(如果你可以使用約束的話)。你總是可以寫出一個觸發(fā)器，完成和一個約束相同的工作，但這樣做一般沒有什么意義。
考慮使用一個外鍵約束和DML觸發(fā)器。使用外鍵約束的目的是為了確保允許進入一個表的一列或多列的值出現(xiàn)在一個單獨表的一列或多列中。你可以使用DML觸發(fā)器建立相同的功能。
列表A建立了SalesHistory和I_SalesProducts表，我將在例子中使用它們，并給它們加載一些數(shù)據(jù)。
以下為引用的內(nèi)容：
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’
現(xiàn)在我有了一些表和一些數(shù)據(jù)可供利用，讓我們在SaleHistory表中建立一個外鍵約束。
以下為引用的內(nèi)容：
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistory FOREIGN KEY (Product) REFERENCES
l_SalesProducts(Product)
這個約束保證：在SaleHistory表中插入一個記錄時，插入到Product域的值也必須出現(xiàn)在I_SaleProducts表中。你可以用一個DML觸發(fā)器實現(xiàn)相同的功能。列表B在SaleHistory表中建立一個觸發(fā)器，它檢查在SaleHistory表中插入或更新的值是否在I_SaleProducts表中生成產(chǎn)品值。
以下為引用的內(nèi)容：
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 以這種方式建立解決方案還涉及許多工作。工作時間并不是你在創(chuàng)立這種解決方案時遇到的問題。因為約束以SQL Server功能的形式建立，它們傾向于擅長完成它們旨在完成的工作，而且與在觸發(fā)器中執(zhí)行相同的功能相比，它們的表現(xiàn)也更好。
當執(zhí)行一個DML操作時，系統(tǒng)首先啟動一個INSTEAD OF觸發(fā)器，然后外鍵約束進行檢查，接著再運行AFTER觸發(fā)器。這表示，在調(diào)用AFTER觸發(fā)器前，表中定義的任何外鍵約束必須得到滿足。
現(xiàn)在我們考慮你想要使用觸發(fā)器而非約束的情況。在需要將表中的當前值與當前輸入值進行比較，以不能滿足某些極，就可以首選使用觸發(fā)器?？紤]下面的商業(yè)情形。
MyCompany.com最近實施了一項商業(yè)規(guī)則，如果產(chǎn)品的售價低于500美元，那么一天內(nèi)出售的BigScreen產(chǎn)品的數(shù)量就不能超過5件。此舉是為了保證折扣價格不會超出公司的期望。我們來了解滿足這種條件的其中一種方法。
首先，用戶界面可以查詢數(shù)據(jù)庫，了解輸入的產(chǎn)品數(shù)量是否超過上限。這種方法有效，但它需要在用戶界面中加入額外的商業(yè)邏輯，公司可能反對這樣做。
在這種情況下，可能很難實施一個約束，但如果要這樣做，仍然需要增加一些額外的編程邏輯。應(yīng)用觸發(fā)器可以有效地解決這個問題，因為觸發(fā)器擅長比較以往和當前的值，并根據(jù)這些值做出決策。列表C建立了實現(xiàn)這個目標的觸發(fā)器。
以下為引用的內(nèi)容：
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
在這個觸發(fā)器中，我執(zhí)行檢查，看看表中更新或插入的BigSrceen產(chǎn)品數(shù)量，以及當天以低于500美元的售價出售的BigSrceen產(chǎn)品的數(shù)量是否超過5件。如果超出5件，觸發(fā)器將會撤銷交易，不會添加產(chǎn)品。
結(jié)論
約束與DML觸發(fā)器各有其優(yōu)點。約束能夠維護數(shù)據(jù)庫表域和關(guān)系之間的數(shù)據(jù)庫完整性;而觸發(fā)器則擅長于比較以往和當前值，并根據(jù)這些數(shù)據(jù)做出決策。在我看來，一般來說，如有可能，使用約束更為有利，而將棘手的商業(yè)和審計邏輯留給觸發(fā)器來解決
何為約束和DML觸發(fā)器?
約束是數(shù)據(jù)庫引擎用來約束一個表或一個表關(guān)系中的數(shù)據(jù)，以維持數(shù)據(jù)庫完整性的一個對象。這些約束包括CHECK、UNIQUE、PRIMARY KEY等。這里提供在TSQL中定義約束的更多細節(jié)。
AFTER觸發(fā)器是一種特殊類型的TSQL代碼塊，當一個DML語句根據(jù)觸發(fā)器定義的表執(zhí)行時，它得到執(zhí)行。(在本文中我僅指這種觸發(fā)器。)
何時使用約束和DML觸發(fā)器
使用約束比使用觸發(fā)器更加有利(如果你可以使用約束的話)。你總是可以寫出一個觸發(fā)器，完成和一個約束相同的工作，但這樣做一般沒有什么意義。
考慮使用一個外鍵約束和DML觸發(fā)器。使用外鍵約束的目的是為了確保允許進入一個表的一列或多列的值出現(xiàn)在一個單獨表的一列或多列中。你可以使用DML觸發(fā)器建立相同的功能。
列表A建立了SalesHistory和I_SalesProducts表，我將在例子中使用它們，并給它們加載一些數(shù)據(jù)。
以下為引用的內(nèi)容：
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’
現(xiàn)在我有了一些表和一些數(shù)據(jù)可供利用，讓我們在SaleHistory表中建立一個外鍵約束。
以下為引用的內(nèi)容：
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistory FOREIGN KEY (Product) REFERENCES
l_SalesProducts(Product)
這個約束保證：在SaleHistory表中插入一個記錄時，插入到Product域的值也必須出現(xiàn)在I_SaleProducts表中。你可以用一個DML觸發(fā)器實現(xiàn)相同的功能。列表B在SaleHistory表中建立一個觸發(fā)器，它檢查在SaleHistory表中插入或更新的值是否在I_SaleProducts表中生成產(chǎn)品值。
以下為引用的內(nèi)容：
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 以這種方式建立解決方案還涉及許多工作。工作時間并不是你在創(chuàng)立這種解決方案時遇到的問題。因為約束以SQL Server功能的形式建立，它們傾向于擅長完成它們旨在完成的工作，而且與在觸發(fā)器中執(zhí)行相同的功能相比，它們的表現(xiàn)也更好。
當執(zhí)行一個DML操作時，系統(tǒng)首先啟動一個INSTEAD OF觸發(fā)器，然后外鍵約束進行檢查，接著再運行AFTER觸發(fā)器。這表示，在調(diào)用AFTER觸發(fā)器前，表中定義的任何外鍵約束必須得到滿足。
現(xiàn)在我們考慮你想要使用觸發(fā)器而非約束的情況。在需要將表中的當前值與當前輸入值進行比較，以不能滿足某些極，就可以首選使用觸發(fā)器?？紤]下面的商業(yè)情形。
MyCompany.com最近實施了一項商業(yè)規(guī)則，如果產(chǎn)品的售價低于500美元，那么一天內(nèi)出售的BigScreen產(chǎn)品的數(shù)量就不能超過5件。此舉是為了保證折扣價格不會超出公司的期望。我們來了解滿足這種條件的其中一種方法。
首先，用戶界面可以查詢數(shù)據(jù)庫，了解輸入的產(chǎn)品數(shù)量是否超過上限。這種方法有效，但它需要在用戶界面中加入額外的商業(yè)邏輯，公司可能反對這樣做。
在這種情況下，可能很難實施一個約束，但如果要這樣做，仍然需要增加一些額外的編程邏輯。應(yīng)用觸發(fā)器可以有效地解決這個問題，因為觸發(fā)器擅長比較以往和當前的值，并根據(jù)這些值做出決策。列表C建立了實現(xiàn)這個目標的觸發(fā)器。
以下為引用的內(nèi)容：
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
在這個觸發(fā)器中，我執(zhí)行檢查，看看表中更新或插入的BigSrceen產(chǎn)品數(shù)量，以及當天以低于500美元的售價出售的BigSrceen產(chǎn)品的數(shù)量是否超過5件。如果超出5件，觸發(fā)器將會撤銷交易，不會添加產(chǎn)品。
結(jié)論
約束與DML觸發(fā)器各有其優(yōu)點。約束能夠維護數(shù)據(jù)庫表域和關(guān)系之間的數(shù)據(jù)庫完整性;而觸發(fā)器則擅長于比較以往和當前值，并根據(jù)這些數(shù)據(jù)做出決策。在我看來，一般來說，如有可能，使用約束更為有利，而將棘手的商業(yè)和審計邏輯留給觸發(fā)器來解決