楼主同一个问题发一贴就行了create table media(ID int,parentID int) go
create function c_media_parentID(@parentID int)
returns bit
as
begin
if exists(select 1 from media where ID=@parentID) or @parentID=0
return 1
return 0
end
goalter table media add constraint c_media check(dbo.c_media_parentID(parentID)=1)goinsert into media(ID,parentID)values(1,0);
insert into media(ID,parentID)values(2,1);
insert into media(ID,parentID)values(3,1);
insert into media(ID,parentID)values(4,2);
insert into media(ID,parentID)values(5,2);
create function c_media_parentID(@parentID int)
returns bit
as
begin
if exists(select 1 from media where ID=@parentID) or @parentID=0
return 1
return 0
end
goalter table media add constraint c_media check(dbo.c_media_parentID(parentID)=1)goinsert into media(ID,parentID)values(1,0);
insert into media(ID,parentID)values(2,1);
insert into media(ID,parentID)values(3,1);
insert into media(ID,parentID)values(4,2);
insert into media(ID,parentID)values(5,2);
-- Author: happyflsytone
-- Date:2008-10-22 22:17:07
------------------------------------
create function f_exists(@id int)
returns int
as
begin
if ((@id = 0 ) or exists(select 1 from ta where id = @id))
return 1 return 0
end
goCREATE TABLE ta(ID INT,parentID int check(dbo.f_exists(parentID) = 1))
Go--Startinsert into ta values(1,0)
SELECT *
FROM ta
insert into ta values(2,1)
SELECT *
FROM tainsert into ta values(2,5)
SELECT *
FROM ta
/*
消息 547,级别 16,状态 0,第 11 行
INSERT 语句与 CHECK 约束"CK__ta__parentID__0663BBFA"冲突。该冲突发生于数据库"CSDN_TEST",表"dbo.ta", column 'parentID'。
语句已终止。
ID parentID
----------- -----------
1 0
2 1(2 行受影响)
*/
go
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
drop function f_exists
--Result:
/*
*/
--End
go
create trigger tri_media
on media
instead of insert
as
begin
insert into media(id,parentid)
select id,parentid from inserted
where id in(select id from media) or parentid=0
end
goinsert into media(ID,parentID)values(1,0);
insert into media(ID,parentID)values(2,2);
insert into media(ID,parentID)values(3,3);
insert into media(ID,parentID)values(4,4); select * from mediaid parentid
1 0
returns bit
as
begin
if exists(select 1 from media where ID=@parentID and ID<>parentID) or @parentID=0
return 1
return 0
end
goalter table media add constraint c_media check(dbo.c_media_parentID(parentID)=1)go