在Server中插入无效引发错误
在Server中插入无效引发错误
提问于 2017-03-15 15:37:58
我有一个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列的错误：
当IDENTITY_INSERT设置为OFF时，无法在表“Products”中插入标识列的显式值
有人能提出解决这个问题的方法或者更好的方法吗？
请注意，此检查也是在应用程序级别进行的，但我希望在数据表级别强制执行。
谢谢。
更新：使用检查约束
我试过以下几种似乎有效的方法。
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建议的索引视图方法。
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-03-15 15:52:19
实现此功能的一种方法是滥用索引视图：
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.因此，如果您可以接受该错误消息，这可能是一种方法。注意，如果您有一个numbers表，您可以使用它来代替我的虚拟Two表。
这里的诀窍是以这样的方式构造视图，这样，如果Products和BlockedProductCodes表之间有匹配，我们就生成一个多行结果集。但是我们还确保了所有行都有一个常数列值，并且结果有一个唯一的索引，因此会产生错误。
注意，当表名存在时，我使用了使用DRI_作为前缀的约定来强制执行完整性约束--我不想让任何人查询该视图(实际上，如上所述，该视图必须始终为空)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42814337
复制相关文章
相似问题
腾讯云开发者
