2

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.

  • I think what you're looking for is IGNORE_DUP_KEY? – Erik Darling Feb 4 '17 at 15:30
  • 3
    Consider using a stored procedure rather than a trigger. – Dan Guzman Feb 4 '17 at 15:44
  • @sp_BlitzErik IGNORE_DUP_KEY is nice but will not work in my case because the index is filtered to allow multiple null values, i.e. CREATE UNIQUE NONCLUSTERED INDEX idx_Fecebook_notnull ON Customer(Facebook) WHERE Facebook IS NOT NULL;. – Ryan Feb 5 '17 at 3:19
  • Multiple null values make sense in my scenario since customers may not necessarily leave all of their contact method every time in a single record. – Ryan Feb 5 '17 at 3:22
  • @DanGuzman could you please elaborate? – Ryan Feb 5 '17 at 3:24
4

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';
  • 2
    I would suggest including a check constraint to make sure at least one of (Facebook,Twitter,PhoneNum) is not null. e.g.: alter table dbo.tblCustomer add constraint chk_dbo_tblCustomer_not_all_null check (coalesce(Facebook,Twitter,PhoneNum) is not null); – SqlZim Feb 5 '17 at 16:08
  • @SqlZim, I agree a check constraint is a good idea. Added to my answer. – Dan Guzman Feb 5 '17 at 16:16
  • @DanGuzman Thanks and really helped a lot. And I noticed your use of lock hint. I may need to pay more attention to that. – Ryan Feb 6 '17 at 10:05
  • @Ryan, concurrency is often overlooked during development and race conditions not found until one goes live with a multi-user app. Although concurrency problems may be rare if the server is fast and/or few users, I recommend one address possible issues early in development. – Dan Guzman Feb 6 '17 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.