function do not work in CHECK Constraint on sql server

Question

I have problem on my Database, I try simplify my database.. after a lot of tries the function use 'X' table , then use the function on 'X' table.you can not use function use same table...

--after create table:

create table Items(
ID int,
Price money
);

--Insert some values

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

--create a function use above table

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

--then alter table to add constraint --accept any price less or equal price on table

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

--after that, i try to insert items

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 ??

---

I use sql server 2012 Express, Win7

Answer 1

The problem you have is that the new value exists in the table (inside an implicit transaction) when the check constraint fires, so when you insert 15, the max(Price) is 15, so the constraint is satisfied, and the INSERT succeeds. I've had a thorough Google to try and find where this is documented but not found anything definitive.

An alternative approach to achieve the effect you are after would be to use an INSTEAD OF trigger, example below.

A word of advice though - this sort of validation strikes me as prone to going wrong somehow. I'd try and separate your limit values from the data - probably in another table.

Hope this helps,

Rhys

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

Answer 2

It appears the check constraint isn't executed until after the initial insert. So the database engine

1. Inserts the data
2. Checks the constraint
3. If the insert violates the constraint, rollback the insert
4. If the insert is ok, commit the data

You can see the order this occurs in the query execution plan:

The description on the properties of the Assert highlighted in the picture says "Used to verify that a specified condition exists."

I am unable to find documentation on this from Microsoft, if anyone knows where to find it please let me know.