0

I have a table named dbo.Inventory in SQL Server 2016 like this:

ProdCode     UPCCode     
   1          11111
   2          11112
   3          11113

When adding a new row, I need to write a trigger that will check for a duplicate in that column. If it is a duplicate I need it to give an error and not change the column. Otherwise, I need it to insert like normal.

I can't use a unique index because I have a lot of rows with a value of NULL.

  • Is there a primary key defined? Which column are you checking for duplicates? – Mark Stewart Nov 22 '19 at 2:44
4

I'd stay away from triggers as much as you can, cause they're generally pretty bad for performance. I'd suggest using a non clustered index with a filter.

See below example

CREATE TABLE dbo.Inventory( ProdCode INT, UPCCode Char(5) )

INSERT INTO dbo.Inventory(prodcode,UPCCode)
VALUES  ('1','11111'),
        ('2','11112'),
        ('3','11113'),
        ('4','11114'),
        ('5',NULL),
        ('6',NULL)


CREATE UNIQUE NONCLUSTERED INDEX NCIU_UPCCode
ON dbo.Inventory(UPCCode)
WHERE UPCCode IS NOT NULL;

-- Works
INSERT INTO dbo.Inventory
VALUES  ('7','11115')

-- Works
INSERT INTO dbo.Inventory
VALUES  ('8',NULL)

-- Fails
INSERT INTO dbo.Inventory
VALUES  ('9','11115')


DROP TABLE dbo.Inventory  

That should give you the requested results, without needing to use a trigger.

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.