--也可以在插入时先校验 if (select count(*) as 客户订单数 from 订单表 where 客户编号='001')>=5 --不能插入订单 else --可以插入订单
利用触发器实现:create table CustomerOrders(ID int,Username varchar(20), Orders varchar(20) ) insert into CustomerOrders select 1,'aaa','Order1' union all select 2,'aaa','Order2' union all select 3,'aaa','Order3' union all select 4,'aaa','Order4' union all select 5,'aaa','Order5' ------------------- Create TRIGGER OrderCount ON CustomerOrders FOR INSERT AS declare @count int select @count=count(orders) from CustomerOrders group by username if @count>5 begin delete from CustomerOrders where CustomerOrders.ID=(select ID from inserted) print 'Cannot Insert Orders More than Five' end --------------------- --Try It: insert into CustomerOrders select 6,'aaa','Order6'
if (select count(*) as 客户订单数 from 订单表 where 客户编号='001')>=5
--不能插入订单
else
--可以插入订单
insert into CustomerOrders
select 1,'aaa','Order1'
union all
select 2,'aaa','Order2'
union all
select 3,'aaa','Order3'
union all
select 4,'aaa','Order4'
union all
select 5,'aaa','Order5'
-------------------
Create TRIGGER OrderCount
ON CustomerOrders
FOR INSERT
AS
declare @count int
select @count=count(orders) from CustomerOrders group by username
if @count>5
begin
delete from CustomerOrders where CustomerOrders.ID=(select ID from inserted)
print 'Cannot Insert Orders More than Five'
end
---------------------
--Try It:
insert into CustomerOrders
select 6,'aaa','Order6'