解决立即给分,解决的好加分给

[xiongjun8136]

表名 (test)
ID(主键) leave(varchar)
1 001
2 001001
3 001002
4 002

现在我随机往test表里插入一条记录为ID值:5, leave值：001
则要写个触发器当插入记录时，原表的leave字段的值更改为
ID leave
1 002
2 002001
3 002002
4 003
5 001
要产生这种递归的样式值，每输入一条记录leave字段的其他值随当前值发生变化。

[rea1gz]

不知道加个条件够不够?

where leave >= @leave and len(leave) >= len(@leave) and id not in (select id from inserted)
and not exists (
select 1 from test tt
where leave like tt.leave+'%'
and leave >= @leave and len(leave) < len(@leave)
)

[rea1gz]

晕哦，看代码是很累的

[rea1gz]

楼主的代码貌似也有问题

楼主最好也测试一下我举的例子的情况

[xiongjun8136]

嘿嘿，我是初学者，第一次写触发器，以前都是在程序中写，上面的只实现了更改当前插入记录的子级,同级的没实现郁闷中

[冷箫轻笛]

--修改下
create table test
(
id int,
leave varchar(20)
)

insert into test select 1, '001'
insert into test select 2, '001001'
insert into test select 3, '001002'
insert into test select 4, '002'
insert into test select 5, '002001'
insert into test select 6, '002001001'
insert into test select 7, '002001002'

--插入数据
insert into test select 8,'001002'

--结果
1 001
2 001001
3 001003
4 002
5 002001
6 002001001
7 002001002
8 001002

--删除环境
drop table test

[xiongjun8136]

set @d= len(@c)-len(@e)
if(@d=0)
update PlanTask set ExpendNumber=@e+@f where (SubString(ExpendNumber,1,len(@c)) =@c) and (len(ExpendNumber)>len(@c))
else if(@d=1)
update PlanTask set ExpendNumber='0'+@e+@f where (SubString(ExpendNumber,1,len(@c)) =@c) and (len(ExpendNumber)>len(@c))
else
update PlanTask set ExpendNumber='00'+@e+@f where (SubString(ExpendNumber,1,len(@c)) =@c) and (len(ExpendNumber)>len(@c))

end
这一段才是我需求的代码

[冷箫轻笛]

晕,语句灭贴上

create trigger t_upleave on test
for insert
as
declare @leave varchar(20)
select @leave = leave from inserted
if exists(select 1 from test where leave = @leave)
begin
update test
set leave = right('000' + cast(cast(left(leave,len(@leave)) as int)+1 as varchar),len(@leave))+stuff(leave,1,len(@leave),'')
where leave >= @leave and len(leave) >= len(@leave)
and id not in (select id from inserted)
and left(leave,len(@leave) - 3) = left(@leave,len(@leave) - 3)
end
go

[冷箫轻笛]

继续上面的测试
insert into test select 9,'002002'
1 001
2 001001
3 001003
4 002
5 002001
6 002001001
7 002001002
8 001002
9 002002

insert into test select 10,'002001'

1 001
2 001001
3 001003
4 002
5 002002
6 002002001
7 002002002
8 001002
9 002003
10 002001

[rea1gz]

好像楼主该加分了

[冷箫轻笛]

where leave >= @leave and len(leave) >= len(@leave) and id not in (select id from inserted)
and not exists (
select 1 from test tt
where leave like tt.leave+'%' --这句应该是无效的吧?笔误?是不是别名有问题?
and leave >= @leave and len(leave) < len(@leave)
)

[xiongjun8136]

谢谢，两位，我测试成功了，

[xiongjun8136]

create trigger t_upleave on test
for insert
as
declare @leave varchar(20)
select @leave = leave from inserted
if exists(select 1 from test where leave = @leave)
begin
update test
set leave = right('000' + cast(cast(left(leave,len(@leave)) as int)+1 as varchar),len(@leave))+stuff(leave,1,len(@leave),'')
where leave >= @leave and len(leave) >= len(@leave)
and id not in (select id from inserted)
and left(leave,len(@leave) - 3) = left(@leave,len(@leave) - 3)
end
go



是正解

[jackeyabc]

学习

[rea1gz]

coolingpipe(冷箫轻笛) ( ) 信誉：100 Blog 2006-12-1 16:08:05 得分: 0



where leave >= @leave and len(leave) >= len(@leave) and id not in (select id from inserted)
and not exists (
select 1 from test tt
where leave like tt.leave+'%' --这句应该是无效的吧?笔误?是不是别名有问题?
and leave >= @leave and len(leave) < len(@leave)
)


------------------------------------------------------
因为外面没写别名，这里估计写乱了，自己都搞不清楚对不对了，最好外面的用别名

update t
set leave = right('000' + cast(cast(left(leave,len(@leave)) as int)+1 as varchar),len(@leave))+stuff(leave,1,len(@leave),'')
from test t
where leave >= @leave and len(leave) >= len(@leave) and id not in (select id from inserted)
and not exists (
select 1 from test
where tt.leave like leave+'%'
and leave >= @leave and len(leave) < len(@leave)
)

[rea1gz]

呵呵，混了40分

[xiongjun8136]

晕，测试后还是有点问题

[xiongjun8136]

你们有没有发现，插入一条记录后再插入一次上次的记录只更改以前的不更改当前的

[rea1gz]

当前的是不应该更改的？！

[xiongjun8136]

没有,是正解,是我测试没注意主键搞错了,不好意思,太激动了

[冷箫轻笛]

rea1gz(冒牌realgz V0.3) ( ) 信誉：100 Blog 2006-12-01 16:15:18 得分: 0


呵呵，混了40分


========================================

如果我是楼主我会给你45分！
:)