问将行数限制在范围内

EN

create table houses (id int primary key)
create table doors (
    id int primary key,
    house_id int references houses(id)
    )

create trigger doorsCondition on doors 
instead of insert
AS
    declare @max int, @min int
    select @max = max(i), @min = min(i) from (
        select count(1) i from (
            select house_id from doors where house_id = ANY (select house_id from inserted)
            union all
            select house_id from inserted) subquery
        group by house_id) subQuery2


    if (@max <= 10 and @min >= 2)
        insert into doors select * from inserted
    else
        print 'The insert violates business constraint'