Throw error on invalid insertion in SQL Server

Question

I have a SQL Server 2012 database with two tables:

CREATE TABLE Products 
(
     Id INT IDENTITY(1, 1) NOT NULL,
     Code NVARCHAR(50) NOT NULL,
     Name NVARCHAR(50) NOT NULL,

     CONSTRAINT PK_Product 
         PRIMARY KEY CLUSTERED (Id ASC)
);

CREATE TABLE BlockedProductCodes 
(
     Code NVARCHAR(50) NOT NULL,
     ReasonCode INT NOT NULL

     CONSTRAINT PK_BlockedProductCodes 
         PRIMARY KEY CLUSTERED (Code ASC)
);

I want to be able to prevent products being inserted into the Products table if their product code exists in the BlockedProductCodes table.

The only way I could think of doing this was with a BEFORE INSERT trigger:

CREATE TRIGGER trg_Products_BEFORE_INSERT
ON Products
INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT Code
               FROM BlockedProductCodes BPC
               INNER JOIN inserted I ON BPC.Code = I.Code)
    BEGIN
        RAISERROR('The product has been blocked!', 16, 1);
    END
    ELSE
    BEGIN
        INSERT Product (Id, Code, Name)
            SELECT Id, Code, Name
            FROM INSERTED
    END

    SET NOCOUNT OFF;
END

But this caused an error with the identity column:

Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF

Can anyone suggest a way to fix this or a better approach?

Please note, this check is also made at the application level, but I want enforce that at the data table level.

Thanks.

Update: using check constraint

I have tried the following that seems to work..

CREATE FUNCTION dbo.IsCodeBlocked
(
   @code nvarchar(50)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ret bit 

   IF (@Code IN (SELECT Code FROM dbo.BlockedProductCodes))
      SET @ret = 1 
   ELSE  
      SET @ret = 0 

   RETURN @ret 
END
GO

ALTER TABLE Products
ADD CONSTRAINT CheckValidCode
   CHECK (dbo.IsCodeBlocked(Code) = 0);
GO

insert Products (Code, Name) values ('xyz', 'Test #1')
go

insert Products (Code, Name) values ('abc', 'Test #2')
-- Fails with "The INSERT statement conflicted with the 
-- CHECK constraint 'CheckValidCode'."
go

I am not sure if it is particularly 'safe' or performant. I will also test out the indexed view approach suggested by Damien.

Answer 1

One way you can implement this is by abusing an indexed view:

CREATE TABLE dbo.Products (
   Id     INT  IDENTITY (1, 1) NOT NULL,
   Code   NVARCHAR(50) NOT NULL,
   Name   NVARCHAR(50) NOT NULL,
   CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (Id ASC)
);
GO
CREATE TABLE dbo.BlockedProductCodes (
   Code   NVARCHAR(50) NOT NULL,
   ReasonCode INT NOT NULL
   CONSTRAINT PK_BlockedProductCodes PRIMARY KEY CLUSTERED (Code ASC)
);
GO
CREATE TABLE dbo.Two (
    N int not null,
    constraint CK_Two_N CHECK (N > 0 and N < 3),
    constraint PK_Two PRIMARY KEY (N)
)
GO
INSERT INTO dbo.Two(N) values (1),(2)
GO
create view dbo.DRI_NoBlockedCodes
with schemabinding
as
    select
        1 as Row
    from
        dbo.Products p
            inner join
        dbo.BlockedProductCodes bpc
            on
                p.Code = bpc.Code
            inner join
        dbo.Two t
            on
                1=1
GO
CREATE UNIQUE CLUSTERED INDEX IX_DRI_NoBlockedCodes on dbo.DRI_NoBlockedCodes (Row)

And now we attempt to insert:

INSERT INTO dbo.BlockedProductCodes (Code,ReasonCode) values ('abc',10)
GO
INSERT INTO dbo.Products (Code,Name) values ('abc','def')

And we get:

Msg 2601, Level 14, State 1, Line 42
Cannot insert duplicate key row in object 'dbo.DRI_NoBlockedCodes' with unique index 'IX_DRI_NoBlockedCodes'. The duplicate key value is (1).
The statement has been terminated.

So if that error message is acceptable to you, this could be one way to go. Note, that if you have a numbers table, you can use that instead of my dummy Two table.

The trick here is to construct the view in such a way so that, if there's ever a match between the Products and BlockedProductCodes tables, we produce a multi-row result set. But we've also ensured that all rows have a single constant column value and there's a unique index on the result - so the error is generated.

Note that I've used my convention of prefixing the table name with DRI_ when it exists solely to enforce an integrity constraint - I don't intend that anyone will ever query this view (indeed, as shown above, this view must always in fact be empty)