Sql-server – Trigger to prevent insertion of duplicate data

sql serversql-server-2016unique-constraint

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.

Best Answer

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.

Related Question