在 SQL Server 中插入无效时引发错误

Question

我有一个SQL Server 2012数据库，其中包含两个表：

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)
);

我希望能够防止将产品插入Products表中，如果产品代码存在于BlockedProductCodes表中。

我能想到的唯一方法是使用BEFORE INSERT触发器：

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

但这会导致标识列出错：

当IDENTITY_INSERT设置为 OFF 时，无法在表"产品"中插入标识列的显式值

任何人都可以提出解决此问题的方法或更好的方法吗？

请注意，此检查也是在应用程序级别进行的，但我想在数据表级别强制执行。

谢谢。

更新：使用检查约束

我已经尝试了以下似乎有效的方法。

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

我不确定它是否特别"安全"或性能。我还将测试Damien建议的索引视图方法。

Answer 1

实现此目的的一种方法是滥用索引视图：

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)

现在我们尝试插入：

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

我们得到：

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.

因此，如果您可以接受该错误消息，这可能是一种方法。请注意，如果你有一个数字表，你可以使用它代替我的虚拟Two表。

这里的诀窍是以这样一种方式构造视图，以便如果Products表和BlockedProductCodes表之间存在匹配项，我们将生成一个多行结果集。但我们还确保所有行都有一个常量列值，并且结果上有一个唯一的索引 - 因此会生成错误。

请注意，我使用了在表名存在时以 DRI_ 开头的约定，只是为了强制执行完整性约束 - 我不打算让任何人查询此视图(事实上，如上所示，此视图实际上必须始终为空(