为SQL增加自定义函数约束

首先新建函数

--检查输入到表goodsSaleArea的值是否符合要求
create function f_chk_goodsSaleArea(@areaID nchar(10),@goodsCode nchar(10))
returns bit
as
begin
declare @saleAreaid nchar(10),@effect int;
select @saleAreaid=saleAreaID from goodsSaleArea where saleAreaID=@areaID and goodsCode=@goodsCode;
set @effect=1;


while (@effect>0)
begin
select @saleAreaid=saleAreaID from goodsSaleArea where  saleAreaID=
(select parentAreaID from Area where areaID=@saleAreaid and goodsCode=@goodsCode)
set @effect=@@rowcount;
if(@effect>0)
return 1
end
return 0
end

然后增加约束到表。

--先生成f_chk_goodsSaleArea函数再执行


alter table goodsSaleArea
add constraint ck_goodsSaleArea_saleAreaID check (dbo.f_chk_goodsSaleArea(saleAreaID,goodsCode)=0)
go