使用觸發器處理重複值

我有一個數據庫，其中的表如下：

tblCustomer(UserID [Primary Key],Facebook,Twitter,PhoneNum);
tblSales(InvoiceID [Primary Key],CustomerID [Foreign Key],ProductID [Foreign Key]);

我正在導入一些紙質記錄，它們按時間（時間）順序排列，包括以下列：

(Customer's)FaceBook,Twitter,PhoneNum,ProductID;

出於某種原因，我們沒有現有的客戶 ID 系統，因此 UserID 將在導入時自動生成。在我的場景中，Facebook、Twitter 或電話號碼中的任何一個都可以唯一地標識一個客戶，因此我為每個人都有唯一的索引來強制執行唯一性約束。

我創建了一個視圖來方便數據導入：

viewDataEntry(FaceBook,Twitter,PhoneNum,ProductID);

一個常見的情況是客戶的 Facebook（或其他聯繫方式）出現在多個銷售記錄中。創建一個觸發器來處理這種情況：

CREATE TRIGGER
ON dbo.viewDataEntry
INSTEAD OF INSERT
AS

BEGIN TRY
INSERT INTO dbo.tblCustomer(Facebook,Twitter,PhoneNum)
SELECT Facebook,Twitter,PhoneNum FROM inserted;
END TRY

BEGIN CATCH
IF ERROR_NUMBER() != 2601 --To ignore uniqueness violation exception
THROW;
END CATCH

DECLARE @UserID INT;
SET @UserID = (SELECT UserID FROM dbo.tblCustomer AS O,inserted AS I WHERE (O.PhoneNum = I.PhoneNum OR O.Facebook = I.Facebook OR O.Twitter = I.Twitter));

INSERT INTO dbo.tblSales(CustomerID,ProductID)
SELECT @UserID,ProductID FROM inserted;

GO

預期結果是：

• 如果導入了重複的客戶記錄，刪除重複的，只插入到銷售表中；
• 如果導入了新的客戶記錄，則為客戶和銷售表創建記錄。

但是，每當輸入重複值時，我都會遇到錯誤 3910 或 3616，這意味著該事務是不可送出的。我認為這是因為需要回滾到客戶表中的插入，並且我知道我不能在保留剩餘部分的同時回滾部分事務（不幸的是，這是預期的結果）。

我找到了 MERGE 語句，但它有太多的限制（比如 WHEN MATCHED 必須後跟 UPDATE 和 DELETE）。

請提供任何可行的解決方案。

通常最好對業務規則使用儲存過程而不是觸發器。在不能使用聲明性約束的情況下，觸發器更適合強制執行數據完整性規則。

下面是一個使用儲存過程而不是觸發器的範例。有關注意事項，請參閱內聯註釋。

CREATE TABLE dbo.tblCustomer(
     UserID int NOT NULL IDENTITY
       CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED
   , Facebook varchar(100) NULL
   , Twitter varchar(100) NULL
   , PhoneNum varchar(20) NULL
   , CONSTRAINT CK_tblCustomer_not_all_null CHECK (COALESCE(Facebook,Twitter,PhoneNum) IS NOT NULL)
   );
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Facebook ON dbo.tblCustomer(Facebook) WHERE Facebook IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Twitter ON dbo.tblCustomer(Twitter) WHERE Twitter IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_PhoneNum ON dbo.tblCustomer(PhoneNum) WHERE PhoneNum IS NOT NULL;

CREATE TABLE dbo.tblSales(
     InvoiceID int NOT NULL IDENTITY
       CONSTRAINT PK_tblSales PRIMARY KEY
   , CustomerID int NOT NULL
       CONSTRAINT FK_tblSales_tblCustomer
       FOREIGN KEY (CustomerID) REFERENCES dbo.tblCustomer(UserID)
   , ProductID int NOT NULL
--      CONSTRAINT FK_tblSales_tblProduct
--      FOREIGN KEY REFERENCES dbo.tblProduct(ProductID)
   );
GO

CREATE OR ALTER PROCEDURE dbo.usp_InsertCustomerSale
     @ProductID int
   , @Facebook varchar(100)
   , @Twitter varchar(100)
   , @PhoneNum varchar(100)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

   BEGIN TRAN;

   --This statement will error with a 'subquery returned more than one value' if more than one customer already has these alternate keys.
   DECLARE @UserID int = (
       SELECT UserID
       FROM dbo.tblCustomer WITH(UPDLOCK, HOLDLOCK) --locking hint to serialize concurrent insert attempts
       WHERE
              Facebook = @Facebook
           OR Twitter = @Twitter
           OR PhoneNum = @PhoneNum
       );

   IF @UserID IS NULL
   BEGIN
           --insert new customer row and get assigned identity value
       INSERT INTO dbo.tblCustomer(Facebook, Twitter, PhoneNum)
           VALUES(@Facebook, @Twitter, @PhoneNum);
       SET @UserID = SCOPE_IDENTITY();
   END;

   --insert sales row
   INSERT INTO dbo.tblSales(CustomerID,ProductID)
       VALUES (@UserID, @ProductID);

   COMMIT;

END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0 ROLLBACK;
   THROW;
END CATCH;
GO

--insert customer and sale for customer 1
EXEC dbo.usp_InsertCustomerSale
     @ProductID = 1
   , @Facebook = 'fb1'
   , @Twitter = 'tw1'
   , @PhoneNum = '(111)111-1111';
--insert customer and sale for customer 2
EXEC dbo.usp_InsertCustomerSale
     @ProductID = 2
   , @Facebook = 'fb2'
   , @Twitter = 'tw2'
   , @PhoneNum = '(222)222-2222';

--insert sale only for customer 2 (with no natural key on sales table, nothing will prevent dup sales)
EXEC dbo.usp_InsertCustomerSale
     @ProductID = 2
   , @Facebook = 'fb2'
   , @Twitter = 'tw2'
   , @PhoneNum = '(222)222-2222';

--this will err due to ambiguous customer (customer 1 has phone and customer 2 has facebook and twitter
EXEC dbo.usp_InsertCustomerSale
     @ProductID = 3
   , @Facebook = 'fb2'
   , @Twitter = 'tw2'
   , @PhoneNum = '(111)111-1111';

引用自：https://dba.stackexchange.com/questions/163222