主页 / dba / 问题 / 163222
Accepted
Ryan
Asked: 2017-02-05 07:20:28 +0800 CST

使用触发器处理重复值

  • 772

我有一个数据库，其中的表如下：

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）。

请提供任何可行的解决方案。

sql-server t-sql

1 个回答

  1. Best Answer

    通常最好对业务规则使用存储过程而不是触发器。在不能使用声明性约束的情况下，触发器更适合强制执行数据完整性规则。

    下面是一个使用存储过程而不是触发器的示例。有关注意事项，请参阅内联注释。

    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';
    • 4

相关问题