Trigger to prevent insertion of duplicate data
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.