sql - 函数在 sql server 上的 CHECK 约束中不起作用

Question

我的数据库有问题，我尝试简化我的数据库..经过多次尝试，该函数使用“X”表，然后使用“X”表上的函数。您不能使用同一个表的函数...

--建表后:

create table Items(
ID int,
Price money
);

--插入一些值

insert into Items Values(1,4);
insert into Items Values(2,5);

--创建函数使用上面的表

CREATE FUNCTION GetMaxPrice
(
    -- Add the parameters for the function here

)
RETURNS Money
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result money = 5;

    select @result = max(Price)
    from Items;


    -- Return the result of the function
    RETURN @result;

END

--然后修改表以添加约束--接受低于或等于 table 上价格的任何价格

alter table Items add check(Price <= dbo.GetMaxPrice())

--之后，我尝试插入项目

insert into Items Values(3,4);
insert into Items Values(4,15); -- <-- here, I have problem. it inserted in Database..
--why inserted when max value is 5 ??

---

我用的是sql server 2012 Express, Win7

Answer 1

你遇到的问题是，当检查约束触发时，新值存在于表中(在隐式事务中)，所以当你插入 15 时，max(Price) 是 15，所以约束得到满足，INSERT 成功。我有一个彻底的谷歌试图找到记录的地方，但没有找到任何确定的东西。

另一种实现您所追求的效果的方法是使用 INSTEAD OF 触发器，如下例所示。

但有一点忠告 - 这种验证让我觉得很容易出错。我会尝试将您的极限值与数据分开 - 可能在另一个表中。

希望对您有所帮助，

瑞斯

create table dbo.Items(
ID int,
Price money
);

insert into dbo.Items Values(1,4);
insert into dbo.Items Values(2,5);
go

create trigger trgItemsInsert on dbo.Items instead of insert as
begin
    -- Lookup current max price
    declare @MaxPrice money = (select max(Price) from dbo.Items)
    if exists (select 1 from inserted where Price > @MaxPrice)
        begin
            -- If there is a price greater than the current max then reject the insert
            declare @msg varchar(255) = 'Maximum allowed price is ' + cast(@MaxPrice as varchar(32)) + '.'
            rollback
            raiserror('%s', 16, 1, @msg)
        end
    else
        begin
            -- Otherwise perform the insert
            insert into dbo.Items 
                select ID,Price from inserted
        end
end
go

insert into dbo.Items Values(3,4);
insert into dbo.Items Values(4,15);
go
select * from dbo.Items
go