Sql-server – Handling duplicate values using trigger

sql servert-sqltransactiontriggerunique-constraint

I have a database where tables are like:

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

I'm importing some paper-based records and they're in chronological (time) order, with following columns:

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

For some reason we don't have existing ID systems for customers so UserID will be auto-generated at import-time. In my scenario, any of Facebook, Twitter or Phone number can uniquely identify a customer so I have unique index for each of them to enforce uniqueness constraint.

I created a view to facilitate data import:

viewDataEntry(FaceBook,Twitter,PhoneNum,ProductID);

A common case is that customer's Facebook (or other contact method) appears in multiple sales records. A trigger is created to handle such cases:

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

Intended outcome is:

  • If duplicate Customer records are imported, drop duplicate, insert into sales table only;

  • If new Customer record is imported, create record for both customer and sales table.

However whenever duplicate values are entered I run into Error 3910 or 3616, which means the transaction is un-committable. I think it's because insertion into customer table need to be rolled back and I know that I cannot rollback part of the transaction while keeping the remaining part (which is, unfortunately, the intended outcome).

I found MERGE statement but it has too many restrictions (like WHEN MATCHED must be followed by UPDATE and DELETE).

Please kindly provide any working solution.

Best Answer

It's generally best to use stored procedures rather than triggers for business rules. Triggers are more appropriate to enforce data integrity rules in cases where declarative constraints can't be used.

Below is an example that uses a stored procedure instead of a trigger. See inline comments for considerations.

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';
Related Question